July 11, 2011 at 8:37 am
I have a matrix displaying about 10 rows of data so our managers can see how their numbers sit on a given period. They need to see their miscellaneous cash collections, but it can't be added into the total.
Is there a way to exclude that row from the total calculation?
July 12, 2011 at 12:56 pm
This seems pretty similar to another question from earlier this week:
http://qa.sqlservercentral.com/Forums/Topic1138567-1063-1.aspx
You should be able to use one of the two functions below (changing field and group names to those consistant with your report).
For use in the values field:
=iif(InScope("matrix1_RowGroup1")="True",SUM(Fields!Rent.Value),Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value),"matrix1_RowGroup1"))
For use in the subtotal text box:
Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value))
Good luck,
Steve
July 12, 2011 at 2:12 pm
I swear I looked before I asked! 🙂
I see what you mean by your formula, but I'm having to go back and use 2005, so I'm unsure even where to look up the group names.
If I interpret your formula, your asking for things that are inScope for matrix_RowGroup1 and if so, sum all of the rent values and leave out the ones that are zero? What if I have multiple Rows
I'm looking at a matrix by payer - Medicaid, Medicare, etc. but I want to exclude the miscellaneous cash collected - I don't want a 7$ pizza from the cafeteria counted in my cash collection, but I do want to show it so the mgr can see it. (Trust me, the logic of this report is a little silly in my opinion.)
so if that were the case, I'd want something like this?:
iif(InScope("matrix_RowGroup1")="True", Sum( Fields!CashAmount.Value), Sum(iif(Fields!PayerName.Value = "Misc Cash"), 0, Fields!PayerName.Value),"matrix_RowGroup1"))
:w00t: ??
July 12, 2011 at 2:48 pm
No problems, there are lots of topics and lots of questions...
To figure out the group name, select the field you are looking for (Fields!PayerName), right click and Edit Group. The value in the Name field is Group Name. This would replace matrix1_RowGroup1.
The expression: =iif(InScope("matrix1_RowGroup1")="True",SUM(Fields!Rent.Value),Sum(iif(IsNothing(Fields!Vendor.Value),0,Fields!Rent.Value),"matrix1_RowGroup1"))
Would replace the expression just below Collected (starts with =Sum...)
And I think you may have more change to make in the expression you provided, based on what I can see and infer.
=iif(InScope("matrix_RowGroup1")="True", Sum( Fields!CashAmount.Value), Sum(iif(Fields!PayerName.Value = "Misc Cash"), 0, Fields!CashAmount.Value),"matrix_RowGroup1"))
PayerName probably needs to be changed to CashAmount in the final part of the inner iif (as shown above).
Basically what this is saying is if you are in scope (meaning the the regular rows not the subtotal, then just show the CashAmount value. Otherwise if you are not in scope (you are now in the subtotal) then Sum the rows but if the PayerName is "Misc Cash" replace with 0 otherwise use the regular CashAmount value. I don't know how many other values besides Misc Cash there can be that should not be included in the subtotal? If it's a few you can maybe use a switch instead of the iif, if it's dynamic and many, it could get messy. Let me know if you are getting closer.
July 12, 2011 at 2:57 pm
I think I understand 🙂
I guess the only thing I don't understand is where to put all of this? I have the Totals box, and then a gray region to the right. how do I put this in for the one column total?
July 12, 2011 at 3:28 pm
The expression goes in the textbox just belowthe Collected header (starts with =Sum...). This is editable and should work for you, if not let me know.
As a note, it can be put in any of those values textboxes, but the one you highlighted in the screenshot is Collected, I think?...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply