Sum only part of a column group?

Question

Hello,

SSRS 2008R2 / ReportBuilder 3.0

I have column data with Channel-column and I have created a table with column group 'Channel'. It displays nicely sums:

Channel           1                  2                  3        Total

Sales            300               500               100      900

But now I want to have a total of just Channels 2 and 3 like this:

Channel           1                  2                  3        Total2-3    Total

Sales            300               500               100      600           900

But how? I have created another column groups with filtering to have only channels 2 and 3 but when i toggle it, the sum shows the whole total 1-3?!?

Any suggestions?

Thanks,

PetteriP

Answer 1

Hi PetteriP,

In order to get this result, you need to use two groupings i.e. Parent and child.

1) Use Parent group on all three columns i.e. 1,2 and 3.

2) Then add child group on column 2 and 3.

3) Then use ADD TOTAL AFTER on both groupings

4) Delete any extra columns if it adds to your matrix.

Hope this helps.

Answer 2

Hello again,

thanks for the answer. I tried your solution, but unfortunately it still sums up either single channel or the whole 1-3 total.

Br, PetteriP

Answer 3

I found solution or several actually

I created a calculated field to my dataset that included only values 2 and 3

In the calculated field: =IIf(Fields!Channel.Value <> 1, Fields!Amout.Value, 0)

and then in the table [Sum(CDbl(CalculatedField))]

I found that the CDbl is needed, otherwise I'll get an error.

Or I could create a similar field to the source itself.

Br, PetteriP

Answer 4

Hello,

No worries, i have another solution :)

Use expression as in the screenshot.

After this you'll get

If still not clear, please give me your email id, I'll email you .rdl file.

Hope this helps.