Search code examples
reporting-servicesssrs-2012

SSRS problem gruping RowWise and perform Sum or Dfference

i'm working on a SSRS Report whose structre is the following:

ID CostCode Amount
1 79 $ 100,00
1 80 $ 80,00
2 79 $ 850,00
2 79 $ 500,00
3 79 $ 200,00
3 79 $ 265,00
3 79 $ 478,00
4 79 $ 665,00
4 79 $ 130,00
4 79 $ 380,00
4 80 $ 50,00
4 80 $ 100,00

Basically I need to group by ID, and

  • if all codes for that ID are 79, sum the amount
  • if there is one (or more) row with code 80, I need to sum the amount for the rows with code 79 and then subtract the amount for the rows with code 80

The output should be like this (the CostCode column can be left empty):

ID CostCode Amount
1 - $ 20,00
2 - $ 1350,00
3 - $ 943,00
4 - $ 1025,00

Thanks in advance for the help :)

Solution

  • That should be simple to do... Something like this...

    =SUM(Fields!Amount.Value * (IIF(Fields!CostCode.Value = 80, -1, 1)))

    All we do here is check if the costcode field is 80 and if so multiple the Amount by -1, if not we multiply it by 1.