SSRS Group Total to contain the values of Specific Groups Only

Hi All,

I have a requirement to display the total of a Group after subtracting a specific value from the same Group.

Example: Say the below data is grouped on a particular column 

Group Values Month

 
  Jan-15  Feb-15 Mar-15
A 10 20 30
B 5 10 25
C 1 2 3
D 5 10 15
Total 11 22 33




Formula is :  Sum(A+C+D)- Sum(B)

What is the best way to Group the above scenario from SSRS level and display the result as shown above. I am able to display all the values except the last total row where am displaying the complete total i.e. 21  42  73.

How do I dynamically subtract the values for row B which is one of the group values.

Please share the steps to achieve the above.

Thanks in Advance
Mahesh MSBI

May 25th, 2015 1:18pm
Give this a go. 
=SUM(IIF(Fields!columnName.Value != "B", Fields.summedColumnName.Value, 0))

Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 1:53pm

Hi Mahesh,

According to your description, you want to custom a formula to calculate the column total values. Right?

In this scenario, the formula is like A+D+C-B. Its easier to make it work like A+B+C+D-2*B. We could apply IIf() condition inside of sum() function to subtract double B . We have tested in our local environment. Please refer to steps below:

  1. Create a matrix and drag name, date and value field into the matrix.
  2. Right-click the name in the Row Groups window and select Add Total (after). Type in the expression:= Sum(Fields!value.Value-IIf(Fields!name.Value="B",Fields!value.Value,0)*2)
    The screenshot is like below:

The result is like below:

If you have any question, please feel free to ask.

Regards,
Shrek Li

May 25th, 2015 11:36pm

Hi Shrek Li,

The example that you have shared works fine with matrix and can you please highlight the same using Tablix so that I can try.

Thanks,

Mahesh

Free Windows Admin Tool Kit Click here and download it now
May 30th, 2015 10:25pm

Hi Mahesh,

In my understanding, your screenshot tells us that your tablix is matrix. Now you want to use the formula in the table. For your requirement, it could not be achieved.

Regards,
Shrek Li

May 31st, 2015 9:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics