SSRS - How to sum the different group total

Kinetic ERP
, ,
1

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

image
image979×653 36.5 KB

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)

image
image877×598 12.5 KB

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 C 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

1 Like
2

I am not sure this is the perfect approach, but you could try using variables in your SSRS. Just add a new variable to the report and set the value to one of the totals. Right click on your report in the space off the page, to go to Report Properties. There you should see variables in the list. Do the same for each sub-total. Then in your grand total expression you can just add up the variables instead of trying to calculate the whole thing.
Good luck!

1 Like
3

I can vouch for this approach.