Search code examples
reporting-services

SSRS report builder sum all and exclude one

I HAVE TAX COLUMN AND AT THE BOTTOM I WANT TO HAVE THE SUM OF ALL ROWS. IN MY CASE I WANT TO EXCLUDE ONE PRODUCT FROM IT BELOW IS THE RESULT THAT IS NEED ('C' IS EXCLUDED)

  | ROW | TAX  |
  | ----| -----|
  | A   | 20   |
  | B   | 30   |
  | C   | 10   |

|SUM | 50 |

Solution

  • An IIF can be used inside the aggregate function to replace the value of TAX with zero if ROW is C.

    =SUM(IIF(Fields!ROW.Value = "C", 0, Fields!TAX.Value))

    If the TAX field is not an INTEGER, then you may need to wrap the 0 in a CDEC function to convert it to decimal.

    =SUM(IIF(Fields!ROW.Value = "C", CDEC(0), Fields!TAX.Value))