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.

are you want group rows in total area ? – Sanjay Radadiya Aug 16 '16 at 17:09 — Sanjay Radadiya, Aug 16 '16 at 17:09
I want to filter group totals. – user1612851 Aug 16 '16 at 17:16 — user1612851, Aug 16 '16 at 17:16

score 0 · Accepted Answer · answered Aug 16 '16 at 17:58

0

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)

answered Aug 16 '16 at 17:58

D.S.

1,413
2
16
26

That was a typo. The ,0 is outside the sum. The IIF in the sum generates an #Error. The IIF on the outside gives wrong results. I can't figure out why. – user1612851 Aug 16 '16 at 18:02
SSRS is case sensitive, are all of your "Small" just that? Capital 'S' lowercase 'mall'? Or is it mixed "small", "Small", "SMALL" If so, try converting the results to a single type and compare correctly against that – D.S. Aug 16 '16 at 18:09
Yes, I am matching the case. I am getting results, but they don't make sense. I am getting the sum for Small when I used Medium as the criteria in some cases and no results in other cases. – user1612851 Aug 16 '16 at 18:19
I had to convert the value using CDBL and it worked. Thanks – user1612851 Aug 16 '16 at 19:11

Subtotal filtering without a group

1 Answers1