sorting in ssrs

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  reporting services   » sorting in ssrs

sorting in ssrs

Topic RSS Feed

Posts under the topic: sorting in ssrs

Posted: 4/3/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

hi

 

how to do sorting by option.

 

for example, if there are option like item1,item2,item3

 

if user click item1,the report should sort by item1,

any help?


Posted: 4/3/2012

Padawan 404  points  Padawan
  • Joined on: 7/27/2010
  • Posts: 122

In design go to Group properties, sorting, add -> put needed expression.


Posted: 4/4/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

i dotn hv group in my report,what expression do i need to put


Posted: 4/4/2012

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

The expression on your sort should be something like this:

Parameters!ParameterName.Value

 


Posted: 4/4/2012

Jedi Knight 1516  points  Jedi Knight
  • Joined on: 1/3/2010
  • Posts: 266

I believe what you need is dynamic sorting.

This can be done by selecting your header cell and go to Interactive Sort.

If this is not what you need, you may try this approach too.

 


Posted: 4/4/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

i dnt need interactive sorting.

 

i have 1 multivalue param so when i run report

i m seeing option like

sort by  : (1) account

(2) mv

 

so whatever option user select,repoirt should sort by that only.

 

my param like this

label value

accout act

market  mv

 

 

how to write expression in tablix  ?


Posted: 4/4/2012

Padawan 404  points  Padawan
  • Joined on: 7/27/2010
  • Posts: 122

1.Go to Tablix Member properties (one of the way to get there is click on Row Groups icon in design mode)

2.Go to SortExpression and put  something like that:

=IIf(Parameters!MyParam.Value="act",Fields!Account.Value,Fields!Market.Value)


Posted: 4/4/2012

Jedi Knight 1516  points  Jedi Knight
  • Joined on: 1/3/2010
  • Posts: 266

you got your solution in my last link.


Posted: 4/4/2012

Jedi Master 3059  points  Jedi Master
  • Joined on: 2/19/2010
  • Posts: 450

Let's say your parameter is named Parameter!Sort.  You say your parameter is multi value, for the formula below I will assume that you can only select one value at a time.

In the tablix dialog, sorting tab you can use an expression something like this:

=SWITCH(Parameter!Sort.Value = "act",Fields!Account.Value, Parameter!Sort.Value="mv",Fields!Market.Value, Parameter!Sort.Value = "NextSortValue", Fields.NextSortValue.Value)


Posted: 4/4/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

hi

i dont need interactive sorting


Posted: 4/4/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

ok.

 

this is how its works

 

what i have done is in tablix propereties 

i add expression for each field

like

iif(instr(join(paramet!sort.value,","),"act"),fileds.value,0)

 

its working ,but user can select sorting by multiple field,i want user can select only 1 filed by sorting

 


Posted: 4/4/2012

Padawan 378  points  Padawan
  • Joined on: 4/3/2012
  • Posts: 189

ok.thisa isnot working,i put expression and then desceding.

 

but without selection anything ,aqll fileds r desceding.

 

why it is like that


Posted: 4/4/2012

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

ssrsnew,

I couple friendly suggestions when posting to the forum.

  1. Verify what it is that you want when you post so that it is easily understandable.
  2. Proofread your post so that typo's are somewhat reduced.

These two suggestions will help others understand what it is that you are asking and save you and them time. We do this as a community because we love helping others come up with possible solutions their problems. Again, these are just friendly suggestions.

As for your problem, I would suggest passing this into a stored procedure as a parameter. Then I would create my query dynamically. I normally stay away from dynamic SQL, but this sounds like an optimal opportunity to pass multiple selected items to be used as a SORT. An example of a simple stored procedure might look like:

CREATE PROC DynamicSort

(

@OrderBy VARCHAR(100)

)

AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT ColumnList FROM TableName ORDER BY ' + @OrderBy

--now that you have your SQL set, execute it

EXEC sp_executesql @SQL

GO

 

Good luck.


Page 1 of 1 (13 items)