Subtotal filtering without a group

Question

I have a report with 5 groups in a Tablix. I want to have 15 total lines based on a filter of amounts within that group. A simplified version:

Group1  Cost Center Relationship Type  Amount
A       100         Rel1         Small 100
A       100         Rel1         Med   100
A       100         Rel2         Small 200
Total for A Small:                     300
Total for A Medium:                    100

I cannot group on Type because I am ordering by Relationship.

I added 2 more group rows to make 3 (one for each Type). I used "Add total" to the existing group. I tried using an IIF(Fields!Type.value="Small",SUM(Fields!Amount.value),0) in the group row but all I get is zeroes. I am not sure if I need to specify a scope because it is in the group already.

D.S. D.S. · Accepted Answer · 2016-08-16T17:58:16

Well, IIF(Fields!Type.value="Small",SUM(Fields!Amount.value,0) wont work as it is.

You would need:

IIF(Fields!Type.value="Small",SUM(Fields!Amount.value),0)

Or try this:

SUM(IIF(Fields!Type.value="Small",Fields!Amount.value,0))

I forget in a GROUP footer if you need to SUM() inside the logic (top example) or SUM() the result of the logic (bottom example)

Subtotal filtering without a group

1 Answers