Sub totals

[unclejimbob]

Hi all, I have a minor issue with a matrix report.

I would like the output to look like this...I have made up the column names for the purposes of illustrating the point.

[tt]
Male Female Total
Single Married Single Married Single Married
USA 10 5 2 20 12 25
UK 20 6 3 30 23 36

[/tt]

The result set from my sp that I use to feed into the report looks like this...

[tt]
Count Country
10 USA Male Single
5 USA Male Married
2 USA Female Single
20 USA Female Married
20 UK Male Single
6 UK Male Married
3 UK Female Single
30 UK Female Married
[/tt]

The problem I have is that I cannot figure out how to get the totals to look like this
using Reporting Servies, I have looked at the tutorials, googled it, read newsgroups,
read thr FAQ here, looked at my RS books but can't find anything that resembles what I need. At the moment
I have 'resolved' the problem by simply creating another sex type named 'Total' with
the correct values like this...

[tt]
12 USA Total Single
25 USA Total Married
23 UK Total Single
36 UK Total Married
[/tt]

...and it works but its not _right_ if you see what I mean, I don't want to have to jump through hoops if RS can do what I need.

Question: Is the above output achievable using RS ?

thanks
ujb

 

[xlbo]

I can't see any easy way of creating this layout in RS (other than doing what you are doing). The subtotal feature is not particularly customisable and there doesn't appear to be any options in terms of how the data is organised.

To be honest though, quite a lot of what I have read suggests pre-aggregating anyway as the RS aggregation engine is not as quick as doing it as part of the extract...

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

 

[unclejimbob]

Thanks for the reply Geoff, have a good one
ujb

 

[fsugeiger]

In order to get the following column data layout all you need to do is stack the Sex over the Married status and add a sub total to sex.

Male Female Total
Single Married Single Married Single Married
USA 10 5 2 20 12 25
UK 20 6 3 30 23 36

bg

 

[unclejimbob]

No, what your suggestion will give me is this:

[tt]
Male Female Total
Single Married Single Married
USA 10 5 2 20 37
UK 20 6 3 30 59

[/tt]

If I add a subtotal to sex, I get precisely that - a sub total of sex.

cheers
ujb