Display of NULL attribute elements

[MSIsam]

MSI has the ability to display NULL values on metrics as a character string such as '--'. Is there any way for the tool to do the same for NULL attribute values without having the back end do it (i.e. CASE statement)?

 

[nlim]

i don't think so. It can confuse the sql engine when there are joins needed...but as you mentioned, the back end case statement is definitely doable.

 

[entaroadun]

Rather than using NULLs, you should use a proxy value, like 0 or "" to specify an unknown value. DBMSs don't join on or group by NULLs.

 

[MSIsam]

Our goal is to change the NULL to '--' AFTER it retuns to MSI (have the analytical engine perform the change). We have it working right now with CASE statements but due to the number of attributes that have this requirement, the SQL has become pretty ugly. Thanks for you're imput.

 

[chael]

I know you stated that your goal is to have the engine do the work, but is the data really 'clean' if there are that many null values?

Which can't Null values be updated to display what you want for the output?

I'm sure you have a valid business case for not taking this approach, but are there any other ways to attack this problem? For example, if the output is being delivered via web, an XSL stylesheet might be the answer. If your reports are exported to Excel, a VBA macro could handle the job. Last but not least, how about opening up a case with MicroStrategy and sugggesting this as an enhancement and maybe they will step up and hit a home run for you?

Chael