Dumb Grouping/Sorting Question

[Auguy]

I have an invoice report that groups by customer with a drill down to the individual invoice records. The group band has billed & profit total fields (ie: sum(Fields!CustAmount.Value). Currently the report is ordered by customer. I would like to have an option to order by total sales or profit. When I look in the help it says to right click in the report designer, point to Report and click "Top N/Sort Group Expert". No matter what I right click on I do not get anything like this. What exactly am I supposed to right click on? It also refers me to "Summarizing Grouped Data" and in that help topic it also refers to righ clicking in the report designer. I have my report design loaded in a vb.net 2003 project with the Layout tab selected. I must be missing something. Can someone shed some light on my dim brain?


Auguy
Northwest Ohio

 

[xlbo]

Are you using 2005?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

2003. Sorry, forgot to include that important point.

Auguy
Northwest Ohio

 

[xlbo]

?SSRS is either 200 or 2005.....

In 2000, some of the sort options aren't available - have got around this by using a parameter that te user can select to choose the sort order

For 2005, I'm not sure - hopefully some of the other users here on 2005 could help...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

?SSRS is either 2000 or 2005.....

In 2000, some of the sort options aren't available - have got around this by using a parameter that te user can select to choose the sort order

For 2005, I'm not sure - hopefully some of the other users here on 2005 could help...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

SSRS 2000, using vb.net 2003. If some of the sort options aren't available in 2000, it seems strange they are in the help file? I am using an SQL stored proc to get the data and have done the sort order thing before. The problem is how do I get SSRS to group on things like the total $ while still maintaining the drill down feature to the detail rows.

Auguy
Northwest Ohio

 

[xlbo]

Maybe the help file is Vis Studio 2003 and that expects RS 2005?

I know there are custom "user" sort options in 2005 that aren;t there in 2000

Not sure exactly for your grouping - will depend on your exact setup - but for sorting I just set up a sort on the table which references the parameter

=parameters!ParamName.Value

Where the values returned in the param list are the same as the field names you want to sort on...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

I think I found a solution. Right-click on the icon to the left of the header row where your group totals are located and select "Edit Group" and then select the "Sorting" tab. You can enter an expression there that includes a sum, ie: =Sum(Fields!custamount.Value). Still have some testing to do, but it appears promising.

Auguy
Northwest Ohio

 

[Auguy]

This following solved my sorting issues. Maybe it will help someone else.

http://www.codeproject.com/KB/reporting-services/DataGrouping.aspx

Auguy
Northwest Ohio