Yet another irritating error message from reporting services 🙂
You will typically get that error, when you try to create a calculated member in the visual designer like so:
IIF( [Measures].[x]=0 , [Measures].[y] , 0,00)
And then use the SUM or another aggregate function on the new measure.
If you create the same calculated member in pure MDX, and run it in for instance management studio, it will not yield any errors. Like so:
WITH MEMBER [Measures].[z] AS
IIF( [Measures].[x]=0 ,
[Measures].[y] , 0,00)
Just to describe the calculated member in words. If the measure x equals 0, then the new measure z will be the value of the measure y, and if the measure x not equals 0, then the new measure z will take the value of 0,00.
The problem is that, when reporting services SUM the new measure z, it Sums data from y and 0,00, and reporting services will in some cases see the values as different data types. In my example the measure x was a decimal, and reporting services interpreted the 0,00 as an integer.
To avoid this problem, you have to tell reporting services that 0,00 is a decimal like this:
IIF( [Measures].[x]=0 , [Measures].[y] , Cdec( 0,00))
Leave a Reply