Friday, April 13, 2012 6:46 AM
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?!?
- Edited by PetteriP Friday, April 13, 2012 6:48 AM
Friday, April 13, 2012 11:55 AM
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.
- Proposed As Answer by Neeraj Jindal Friday, April 13, 2012 5:28 PM
Friday, April 13, 2012 4:50 PM
thanks for the answer. I tried your solution, but unfortunately it still sums up either single channel or the whole 1-3 total.
Friday, April 13, 2012 5:30 PM
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.
Friday, April 13, 2012 6:01 PM
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.