Dynamic Report Split

Question

Dynamic Report Split

Dale Burrell

SSCommitted

Points: 1737
More actions
September 21, 2009 at 3:20 pm

#214595

The accountant I am preparing reports for has a requirement to be able to sub-total the top 10 lines of the report, and the second 20.
So to explain further the report is a list of clients ordered by the amount of business they do with the client who placed the most business at the top.
Now the accountant would like to see a sub-total of the top 10 clients. They would also like to see a sub-total of the top 20 client (which includes the top 10).
I'm not really sure how best to approach this problem. Any ideas?

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

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

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

I'm not sure that there is a simple or elegant way to do this. Is there a fixed number of rows returned by the report. Like top 50 clients then the sub groups (top 10, 20, etc...)?

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

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 2

This is a link to an article that desribes a method for custom grouping and subtotaling.

http://www.simple-talk.com/sql/reporting-services/reporting-at-the-top/

It may be flexible enough for your requirements...

Bill

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Bill-89778 (9/22/2009)
This is a link to an article that desribes a method for custom grouping and subtotaling.
http://www.simple-talk.com/sql/reporting-services/reporting-at-the-top/
It may be flexible enough for your requirements...
Bill

Ummm... can someone tell me what's happening in the ON clause of Listing 1 on that, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cris E Ten Centuries Points: 1255 More actions · Answer 4

INNER JOIN Person.Address
ON Sales.SalesOrderHeader.BillToAddressID = Person.Address.AddressID
AND Sales.SalesOrderHeader.ShipToAddressID = Person.Address.AddressID
INNER JOIN Person.StateProvince
ON Person.Address.StateProvinceID =
Person.StateProvince.StateProvinceID
AND Person.Address.StateProvinceID =
Person.StateProvince.StateProvinceID
AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
AND Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
Ummm... can someone tell me what's happening in the ON clause of Listing 1 on that, please?

See, whatcha got there is a reeeeallly accurate state lookup. I sometimes confuse Vermont and New Hampshire or get my square states swapped, but here, well here there's no problem with that sort of thing. It's six times as accurate as the join you or I might have gone with.

[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner