This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Thursday 18 August 2011

SSRS - SUM IF

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))