Aggregate Sorting?

Question

Aggregate Sorting?

brentwillian

Grasshopper

Points: 11
More actions
September 10, 2008 at 8:23 am

#192641

Hi,
I have a somewhat complicated report, but to make things simple I'll use a generic example.
Say there is a report that shows employees and the total number of units they have sold. I add a grouping that breaks it down by area. In the group row, I sum up everything below it so I can have a total by area. So it would look something like this:
Area #1 (24)
Emp #1 (13)
Emp #2 (11)
Area #2 (28)
Emp #3 (10)
Emp #4 (18)
Is there a way to sort by the sum at the area level? I've read in a few places that you can't sort by an aggregate function... Is there a workaround for this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 1

Hi, I've been learning SSRS by writing the reports we need for the last six months. I have searched endlessly, and can't find a solution to that question. I think MS intends us to use the drill down capability for things like this: The first level report with summaries only in the detail band. You can sort on that. Then, make a field in the summary clickable, and set it up to navigate to the detail report for that subtotal.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I subscribed to this thread hoping someone would post a solution since I don't know of one within RS. I'll give you a couple of ideas, the first one should be doable, the second is just a SWAG:

Re-write your query to include the aggregate data as a column and then order by it in the query or use that column to order by.

Write a function in custom code that you can use in the order by.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 3

Jack Corbett (9/12/2008)

Re-write your query to include the aggregate data as a column and then order by it in the query or use that column to order by.

quote]

Yes, this is what I do for my top level report, I was lazy and didn't provide details in my previous post, but:

SELECT

sum(this)

,sum(that)

,sum(theother)

FROM

...

That's the way my first SP looks for the summarized level report. Then if they click on "that", it feeds a parameter to the detail report and selects all the detail that went into that sum. This may be the wrong/least efficient way to solve the problem, but I'm out here by myself trying to make SSRS work for my consumers. 😉 I'm always willing to learn if someone wants to point out a better way...