I wrote this guide around 14 years ago after having nothing but problems with Microsoft Business Intelligence Development Studio for around 7 hours, so thought I’d post the solution to save someone else the hassle!

Problem

When using groupings in a table, an expression consisting of a SUM and an immediate if (IIF) can be used to sum/count/aggregate values where another value in the dataset meets a certain condition. In my example below where the academic year is = “2010/2011” I want to include the grade value (a numeric).

=SUM(IIF(fields!Academic_year.value = “2010/2011”,Fields!Grade.value,0))

However despite the report working in in BIDS, after deploying the report and viewing it on the SSRS server I get #error on some/all of the groups.

Solution

For some reason the SSRS server when rendering the report is struggling to interpret the grade field as a numeric and also the 0. The use of the VAL conversion function removes this problem.

=SUM(VAL(IIF(fields!Academic_year.value = “2010/2011”,Fields!Grade.value,0)))