sql server data warehouse: I have a Calulated Member that is calculating an average.

[Measures].[Overall Satisfaction]/[Measures].[Count Of Survey]

The problem is that for some of the items there are no surveys, so there are
NULL values in the database for these.

When I view the cube as a pivot table in Excel I am getting #NUM! for any
dimentions that do not have actual numbers.

How can I change my calculation so it will just return NULL instead of the
dreaded #NUM! value that drives managers so crazy?

Thank you!


:-J
Jeremy

Use COALESCE or ISNULL function to change a NULL to a valid value or a
space.

--
Anith

REGARDS MY EXAMPL
-------
[Measures].[Average] AS ' IIF(ISNULL([Measures].[Count Of Survey]), NULL, [Measures].[Overall Satisfaction]/[Measures].[Count Of Survey])
-------
You colud use ISNULL() with a tuple, for example ISNULL(([Time].[2004].CURRENTMEMBER, [Measures].[Overall Sastisfaction)
I hope it would help yo

Thank you for the help it got me going on the right track.

What I ended up with was:

IIF([Measures].[Count Of Survey]=0, NULL, [Measures].[Overall
Satisfaction]/[Measures].[Count Of Survey])

This seems to work perfectly!

Thanks agian!

-Jeremy


[quoted text, click to view]