SSRS - How to Sum the Different Group Total

Question

I have designed a report to show a Project wise Profit and Loss details as shown in the below screenshot

My challenge is to find an expression that could sum the previous group`s (PNLGroup) total together and show it as the Total at the end of each group

Group a,b,c,d

Current Expression:- (Yello Highlighted Field)

=SWITCH

(

Fields!Calculated_PNLGroup.Value = "a",iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value)),

Fields!Calculated_PNLGroup.Value = "b", Previous( iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value))) + iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value)),

Fields!Calculated_PNLGroup.Value = "c",Previous( iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value))) + iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value)),

Fields!Calculated_PNLGroup.Value = "d", Previous( iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value))) + iif(IsNothing(Sum(Fields!Calculated_NetAmount.Value)),0,Sum(Fields!Calculated_NetAmount.Value))

)

The Output is shown below (Yellow Highlighted)

The total of group a & b is correct according to the expression but Group C (Net Profit) total is wrong

The expected output should be (Group B Total + Sum(Groupc))

Group B Total = -41,893,316.92 + (2,341,630 + 1,331,053 + 780,193 + 231,123) = -37,209,317 instead its showing the value as 12,999,855.77

Kindly advise the correct expression to be used

Answer 1

Hi @Mohamed Yusuff

Do you want to add up the totals for each group? If your expression meets the requirements, then you can try using the RunningValue function. For example:

Preview:

For more details, you can refer to this link: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-runningvalue-function?view=sql-server-ver16.

Best regards,

Aniya

Answer 2

Hi @Mohamed Yusuff

Sorry for getting back to you so late. I just noticed that you're using matrices, whereas I'm testing tables. I redesigned a matrix to test it out for you.

When I changed the scope to the parent group, it did show only the total for each group, not the sum of the totals, as you showed. You can try changing the scope to Nothing. Nothing specifies the outermost context, usually a report dataset.

Here are my test results.

Best regards,

Aniya