SSRS 2005 - Adding your own totals in a matrix

Question

SSRS 2005 - Adding your own totals in a matrix

Vicki Peters

SSCommitted

Points: 1866
More actions
March 10, 2009 at 6:11 am

#218971

I am relatively new to using SSRS and am stuck trying to figure out how to do something.
I have a stored proc in which I need to compare sales from this year to goals from this year/sales from last year. I opted to handle all the math in my proc because it was much easier than dealing with in SSRS. My result set contains columns similar to this: Department, Publication, YTDGoal, LastYrSales, ThisYrSales, GoalDifferential$, GoalDifferential%, SalesDifferential$, SalesDifferential%. There are actually more of these $/% columns, but you get the gist.
I then created a matrix in my report to display the data the way it need to be displayed. The first row grouping is the department, and then all those sales/$/% categories are the next grouping. The publications are the columns. Everything worked out great for me...until I tried to add in the subtotals. I'm guessing that because I handled the math in my procedure and am simply using the matrix to display the data, it doesn't know how to summarize the data.
I am now wondering...do I need to change my proc to pull in the $ and then do the math on the differentials in the report, or is there a way to sort of create my own total column and force it to do the correct math there. Since I am new to SSRS and don't know how to code in .NET at all, I am unclear as to how I would accomplish this. I guess first and foremost I need to know if I can even do what I'm thinking about doing, and then I need to know how to get started.
Thanks for any help you can give! Let me know if I can provide you with more information to help.

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

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

kenno_rules SSC-Addicted Points: 473 More actions · Answer 1

Hi Vicki,

I think you can potentially achieve what you need to do, without changing your result set, by getting familiar with the InScope function. I used it recently to stop the subtotal section displaying the "sum" of my values, as I really wanted to show the last value (the numbers in the cells were balances and so semi-additive). You have to change the formula in the cell from:

=sum(TheValue.Value), to something like:

=iif(InScope("TheNameOfYourColumnGroup"), sum(TheValue.Value), last(TheValue.Value))

This is basically saying that if the displayed cell is NOT within the regular set of cells (ie it's in the SubTotal section) then show the last value, don't attempt to sum the values.

That might help?

Regards,

Matt.

Vicki Peters SSCommitted Points: 1866 More actions · Answer 2

I can try to play with the InScope function to see if it helps me out in any way; however, I'm not sure what you did is what I need to do.

For the row groups that are dollars, I actually do need the subtotal column to summarize those dollars. The issue comes in when the row group is a percent field. Rather than the subtotal knowing how to calculate the percent (which of course it couldn't know), it is just adding up the percents. I need to be able to reference the subtotal dollar rows and do the math in the subtotal percent rows. Does this make sense?

RMaughan SSC Enthusiast Points: 125 More actions · Answer 3

I'm not sure if this reply will help you out, but there is an article that discusses the topic of adding a column to the right part of a matrix. Maybe you have seen it already. It is:

http://www.sqlservercentral.com/articles/Reporting+Services/63415/[p]I believe that, when working with a matrix, a person has to think "outside of the box" a great deal and be prepared for much frustration in making it do what it was not really designed to do.[/p]My sympathies and good luck.[p][/p]

Vicki Peters SSCommitted Points: 1866 More actions · Answer 4

I had not seen this link, but it seems it's explaining exactly what I need to do. Oh joy! It seems every report I touch becomes quite the learning experience! Thanks for your help!