OLAP cube - errorhandling

Dear all

i have inherited a SSAS 2k5 cube with a large number of calculated members.

I have a number of problems:

sometimes the calculated amounts are very very small like: 0.00000000000034.

This however, does not play nicely with excell... heximal display and so on. anyone know of a way to show these as being 0?

secondly

I have a number of calculated members who themselves contain calculated measures.

When one of their parents is 0, they resort into div/0 erros, etc

For my current project it is unacceptable that these errors are visible for the end user

is there any way that i can catch all the errors at once? or do in eed to include iif statements in the calculations of each calculated member?

Hi, unfortunately you need to put IIF statements around them. For example direct queries from the cube.

with

member measures.test as

0.000000003

member measures.test2 as

IIF(measures.test<0.00001,0,measures.test)

member measures.test3 as

0

member measures.test4 as

IIF(measures.test3=0,0,measures.test/measures.test3)

select {measures.test2,measures.test4} on 0 from [mycube]

or calculated members on the cube

CREATE MEMBER CURRENTCUBE.[MEASURES].[PercentUnavailable] as

IIF([Measures].[Total Count]=0,0,[Measures].[Total Unavailable])*100/[Measures].[Total Count]),

visible=1;

John

|||

Alternative which will perform better in cases where you have a large number of these is to use the format statement

member measures.test as

0.000000003,format='#.00'