Dynamic sort on two columns

[rprameshwor]

My requirement is to let the user  sort the items in the list .
The sorting is based on two columns , and ASC/DSC on both. [ Clicking on Header/list  preferred rather than making prompts ]
eg:-

Division | Depart | Quantity
A                1              #
A                2              #
B                1              #
C                2              #

The user should be allowed to  sort by Division first then Depart   
eg:- [ in both ASC ]


Division | Depart | Quantity
A                1              #
A                2              #
B                1              #
C                2              #

or even Depart ASC first then Div DESC.
eg :-

Division | Depart | Quantity
B                1              #
A                1              #
C                2              #
A                2              #

So, user should be allowed to sort asc/dsc on both div/department  and sort by both of them . one of them first then other the second.
Anyways i can accomplish this in ReportStudio ?

Thanks in advance.

[Lynn]

Attached is a document I wrote about how to achieve this for one column using a drill through to the same report. Not sure how easily adaptable it might be for two columns but perhaps it might stimulate some ideas for you to play around with.

[sthabinash]

My requirement is to let the user  sort the items in the list .
The sorting is based on two columns , and ASC/DSC on both. [ Clicking on Header/list  preferred rather than making prompts ]
eg:-

Division | Depart | Quantity
A                1              #
A                2              #
B                1              #
C                2              #

The user should be allowed to  sort by Division first then Depart   
eg:- [ in both ASC ]


Division | Depart | Quantity
A                1              #
A                2              #
B                1              #
C                2              #

or even Depart ASC first then Div DESC.
eg :-

Division | Depart | Quantity
B                1              #
A                1              #
C                2              #
A                2              #

So, user should be allowed to sort asc/dsc on both div/department  and sort by both of them . one of them first then other the second.
Anyways i can accomplish this in ReportStudio ?

Thanks in advance.

You can achieve this by creating 4 prompts and 4 data items. 2 prompts are for the first sorting field and second sorting field. And the remaining fields are for the type of sorting i.e.(Ascending and Descending). Each data item stores the data for each combination of the two prompts (i.e. the field and the sort type). Then you can select the data item you require and sort it based on the data item you want using the grouping and sorting option of the list.

If this works for you (as this is based on the prompts and not click on the header), I can describe it in detail.

[rprameshwor]

Could you please elaborate a little

[sthabinash]

For sorting through two columns can be done by following these steps.

• 
  Create four prompt, lets parameterize them first,second, sort1 and sort2. 
  First and second are for the column names and sort1 & sort2 are for the ascending or descending for the first column and second column respectively.
  
• 
  Then in the first and second prompts add static choice which are the column name. For the above questions, it will be Division, Depart and Quantity.   As for the sort1 and sort2 create static choices asc and dsc (asc for ascending and dsc for descending)
  
• 
  Now create 4 different data types, lets name them asc1, asc2, dsc1, dsc2
  In the asc1 datatype add following expression:
  
  case
  when ?sort1?='asc'
  then
  (
  case
  when ?first?='Division'
  then
  [Division_field]
  when ?first?='Depart'
  then
  [Depart_field]
  when ?first?='Quantity'
  then
  [Quantity_field]
  else
  ''
  end
  )
  else
  ''
  end
  
• 
  Similary, for asc2 write the above expression with ?sort1? replaced with ?sort2? and ?first? replaced with ?second?
  For dsc1, in above expression replace 'asc' with 'dsc'
  For dsc2, in above expression replace 'asc' with 'dsc', ?sort1? replaced with ?sort2? and ?first? replaced with ?second?
  
• 
  And finally, in the 'Grouping & Sorting' property of the list, under the 'Detail Sort List' option add the data items in following order:
  asc1
  dsc1
  asc2
  dsc2
  
  Then in asc1 and asc2 sort it in ascending order and dsc1 and dsc2 in descending order.
  

This will give you the report for sorting.