Had nothing but problems today with Microsoft Business Intelligence Development Studio, so after spending nearly 7 working hours on the problem I 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)))
