CMedin


I'll admit I am fairly new to the reporting services, but I managed to figure most things out so far except this one. I have a matrix report where I have column groupings of:

Sales person
Region

Basically, the report shows sales by sales person and region, and I added subtotals to each so each salesman/region combination gets subtotals, as does each salesman entry. Now, the one piece missing is the 'total of the subtotals' so to speak. So if I have something like:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
002 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
Grand Total $800 $950 $400

What I want to do is sum up the totals and add another column like so:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
002 A $100 $175 $50
B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
Grand Total $800 $950 $400 $2150

And that is where I am stuck. In Report Designer, with the existing matrix report as above, how do I get these horizontal totals of the subtotals






Re: Horizontal totals of subtotals in a matrix

?®?§Q?


You need to create another column grouping for sales. Then right click the column grouping and click subtotal.

If you have hard coded the months into sales columns then you'd need to rework the field so that it displays sales grouped by month.







Re: Horizontal totals of subtotals in a matrix

CMedin

Well that was so obvious I didn't think about it... I already had the grouping for sales, so adding a subtotal worked perfectly. Thanks!

Two more questions if you don't mind related to this:

Is there a way to sdistinguish the 'total' lines easily For instance, space them out a bit from the other data, make them a different color/font, or something like it... if I change the 'Total' box properties in the designer, it only affects that one field, not the entire line.

And second, is it possible to only show the sales total I just added on specific lines In other words, if I only want to show that 'horizontal total' on lines containing subtotals, is that doable

I'm pretty new to this...





Re: Horizontal totals of subtotals in a matrix

CMedin

OK, all the row grouping subtotals are now nicely formatted with expressions and InScope().

However, I am struggling with the column grouping subtotal. I can use InScope() to format the data since it doesn't appear to be in ANY scope... but I can't figure out how to create an expression to evaluate whether you are in that column AND in a row grouping subtotal row. Any suggestions