matrix report and customized subtotals - can it be done?

I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.

The data used for the report is like following (INV is sold and FC is forecasted):

rtype region

week

wgt

INV EU 1 150 INV US 2 200 FC US 2 400 FC US 3 1000 FC EU 2 100

I want the report to show data like this:

WEEK 1

WEEK 2

WEEK 3

TOTAL

INV

INV

FC

FC

INV

FC

EU 150 100 150 100 US 200 400 1000 200 1400

So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:

WEEK 1 WEEK 2 WEEK 3 TOTAL INV INV FC FC EU 150 100 250 US 200 400 1000 1600

How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.

I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.

Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?

Thank you in advance,

Wapper

In regards to the first question...I had a simular question:

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

as for the second question...

search forum for conditional formatting along with Inscope to accomplish what you desire...

Hope that helps...

|||

Thanks for your reply.

Yes, of course I can add my own calculated subtotals to the data list using UNION ALL and GROUP BY, so that I don't need to use totals provided by the matrix report. But is it really the only way? This looks a bit ugly because I need to query my data source twice to get basically same data. Another approach with second matrix just for totals is also ugly for the same reason. It would be much better if matrix report could do this kind of summary for me.

But of course it is still a solution.

About conditional format, I found out that this can be done without InScope function. InScope is not required because I am always within the scope of "rtype". So the expression for conditional background color should be:

=SWITCH(MAX(Fields!rtype.Value, "matrix1_rtype") = "INV", "PaleGreen", MAX(Fields!rtype.Value, "matrix1_rtype") = "FC", "LightBlue")

Wapper

|||

Hello Wapper,

It seems that a lot of the features that are desired are not yet implemented yet in SSRS 2005 and Microsoft is looking to a future release to handle additional features. I too feel that many elements of aggregation is missing and needs to be upgraded so that the majority of the calculation can be done with in SSRS instead of MS SQL Data structures.

goodguy1974