July 7, 2011 at 3:21 pm
I have a SSRS report with a Matrix. I need to be able to only subtotal for certain subtotals.
I have given an example in the attachment provided, basically for the Yellow highlighted subtotal I need to just subtotal the subtotals in Green (Where vendor does exist) and exclude the subtotals in Red (where vendor does not exist.
I have limited knowledge of how to use an InScope function but figure this will have to be used here.
I know there would be an easier way to break this out into more groups but this customer is sticking to this.
Any help or ideas is very much appreciated!
Marty
July 8, 2011 at 9:19 am
Marty,
I think it may be easier. Assuming you have a separate subtotal for each group, you should be able to do a conditional expression in the subtotal line for Vendor.
Try using an expression similar to this:
=Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value))
Let me know if this works of if I'm missing something.
Good luck, Steve
July 8, 2011 at 9:43 am
This is in a matrix so you can't add code to the subtotal line I thought?
July 8, 2011 at 9:46 am
What version are you using? I just ran a quick test on 2008 R2 (you can modify totals expressions in this version), but I may have missed something else with the number of groups you are using..
July 8, 2011 at 9:49 am
I am in 2005
July 8, 2011 at 10:23 am
You are certainly more restricted in SSRS 2005, but you can move the expression into the textbox associated with the subtotal. Which could work depending on the formatting. Try putting something like this in the textbox:
="Total: " & Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value))
July 8, 2011 at 11:01 am
After playing around a little more with the scope (like you mentioned) you may be able to get the subtotal to work even better if you use an expression like this in the data field of the matrix. This will eliminate the need to modify anything in the subtotal or modifying the text box with an expression and the values should line up better.
=iif(InScope("matrix1_RowGroup1")="True",SUM(Fields!Rent.Value),Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value),"matrix1_RowGroup1"))
The "matrix1_RowGroup1" is the default name of the highest level group in my matrix (the equilivant of your campus group), so this will need to change to your group name. You also may not need the final "matrix1_RowGroup1" before the final close parens, but I left it in there.
Let me know if it works for you, good luck,
Steve
July 8, 2011 at 2:54 pm
Along those lines we got it to work, there was a key change that needed to be made in the stored procedure and then use of your code. Thanks for the help Steve, really appreciate it
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply