Multiple Sorting

Hello All!
My report has columns Date, Year, Model, Cost, Color
I have users that will require the abililty to sort by multiple criteria.
Is it possible to configure a primary AND secondary AND maybe a THIRD sort
in Reporting Services?
For example: Sort the above columns as: Date, Model and Cost at the same
time?
This sorting criteri will change based on the user and the info sought. So
another user may want to
sort on Cost and Year, while another person wants Color,Cost, Year etc..
Currently, as far as I know, Reporting Services limits one to one column
sort at a time in a report.
Thanks in advance !
ScottOn Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
wrote:
> Currently, as far as I know, Reporting Services limits one to one column
> sort at a time in a report.
By default, all of your objects will be populated in the order of the
data in the DataSet. You can define this with a ORDER BY clause in
your SQL.
But, you can create Sort rules by object in your Report. Go to your
Table Properties, then the Sort tab, and you can add multiple sorting
rules to your table. Each rule is applied in sequence, with an
Ascending or Descending. Note that the sorting value is actually an
Expression, which means you can make it dynamic with a little code.
For your problem, what I would do is create a Parameter called
SortRules, make it a Integer, then define Available values as:
"Date, Model, Cost", 1
"Cost, Year", 2
"Color, Cost, Year", 3
etc
Then, in your table, add three Sort rules, each Ascending
Rule 1:
= IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
Nothing ) ) )
Rule 2:
= IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
Nothing ) ) )
Rule 3:
= IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 2, Nothing,
IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
Nothing ) ) )
Now, when the user views the report, they will get a Parameter Prompt
at the top to select a sort method, then after choosing one and
hitting the View Report, the table will sort based on that method.
-- Scott|||Thanks so much for your idea! have a great weekend!
Scott
"Orne" wrote:
> On Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
> wrote:
> > Currently, as far as I know, Reporting Services limits one to one column
> > sort at a time in a report.
> By default, all of your objects will be populated in the order of the
> data in the DataSet. You can define this with a ORDER BY clause in
> your SQL.
> But, you can create Sort rules by object in your Report. Go to your
> Table Properties, then the Sort tab, and you can add multiple sorting
> rules to your table. Each rule is applied in sequence, with an
> Ascending or Descending. Note that the sorting value is actually an
> Expression, which means you can make it dynamic with a little code.
> For your problem, what I would do is create a Parameter called
> SortRules, make it a Integer, then define Available values as:
> "Date, Model, Cost", 1
> "Cost, Year", 2
> "Color, Cost, Year", 3
> etc
> Then, in your table, add three Sort rules, each Ascending
> Rule 1:
> = IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
> Nothing ) ) )
> Rule 2:
> = IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
> Nothing ) ) )
> Rule 3:
> = IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 2, Nothing,
> IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
> Nothing ) ) )
> Now, when the user views the report, they will get a Parameter Prompt
> at the top to select a sort method, then after choosing one and
> hitting the View Report, the table will sort based on that method.
> -- Scott
>