Monday, March 22, 2010

Case Statement Issues in 8.4 with Dimensional Model

This particular issue deals with Coercion errors. Your report works fine in 8.1 and when you have converted it to 8.4.1 you get an error that looks similar to the following:

QE-DEF-0478 Invalid coercion from 'value' to 'measure' for 'when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then
[Quantity] ' in 'case when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then [Quantity] end'.

This is due to the usage of Case statements against dimensional models. Cognos had earlier warned against usage of this against Dimensional Models now they seem to have started restricting the same.

Try using an If Then Else to work around this. But Cognos definitely needs to come up with a similar construct for Dimensional Models as well as most work revolves around the usage of Case statements.

2 comments:

Anonymous said...

thanks buddy11 I was trying the same for some time.

Ed said...

Thanks for that - saved me a model redesign.