matrix subtotal

Hi Everyone,

I am trying to get something like this using matrix in reporting services

SE CP CG Total Qty Value Qty Value Qty Value Qty Value GST Net 7/07/2006 124 233 552 525 141 2544 563 4215 321 4536 8/07/2006 1245 211 11 142 555 1444 1987 1854 124 1978

The problem is, in the total group, the GST value and Net ( Net = Value + GST) can't added into the total group in the matrix.

Can somebody help... Thanks a lot.

You can use InScope in a matrix to determine 'where you are' e.g. are you a subtotal cell, are you within a particular column or row grouping

You can also create hidden groups

This helps to deal with non additive measures

In fact here's another post that may help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1

|||

Thanks a lot..

That help me a lot...

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)