Search code examples
sql-serverreporting-servicesssrs-2008ssrs-2012

Extra rows for custom expression to display "filtered" sum

So I have a table that gets its resultset from a procedure and everything looks great, including the totals at the bottom. The user has asked for a few custom rows at the bottom that are only to represent the totals for specific criteria or group. Say you have a field called AuctionPrice. In the dataset it is from an aggregate within a sql procedure.

If I place an extra row at the bottom of the table and the "AuctionPrice" field, I use an expression like this =SUM(IIF(Fields!AuctionPrice > 0, Fields!AuctionPrice, 0))

This produced an error, whereas an expression with using a different field in the comparision works fine.

Is it true that you can not use an expression using a field that is already included in an aggregate (sum) in the dataset?

Solution

  • For the ssrs expression to work you forgot to define .value after your field

    The correct expression must be like the one below

    =SUM(IIF(Fields!AuctionPrice.value > 0, Fields!AuctionPrice.value, 0))