Everyone,

I wanted to set defaults values for all the measures in
the cube to be zero. The reason is that this will allow
the pivot table to show all records regardless if the
measure had a value or not.

Thanks all in advance,
SAJ

I'm not gung-ho about what you're up to here, but... hide the physical
measures and create calculated members like so:

IIF(IsEmpty(Measures.HiddenMeasure),
0,
Measures.HiddenMeasure)

public @ the domain below
www.tomchester.net

"Saj" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks a lot. I was able to produce measures this way
which are empty. However I have 70-80 measures and doing
this for every one will be tedious.

Is there a way I can always show all the dimension members
irrespective of whether the measure has a value or not.

Thanks,
Saj

Depends on your front-end.

public @ the domain below
www.tomchester.net

"Saj" <saurabhajmera (AT) hotmail (DOT) com> wrote

Wait a second. In AS2K measures always default to zero even if the
associated column in the fact table has NULL values.

Try changing the Display Format from #,# (the default) to #,0 Then
you'll see the zeros. Or try using Excel as a client -- it always
shows the zeros.

Regarding showing dimension members, the simple answer is don't use
the "NON EMPTY" keyword.

However forget trying to coerce Excel not to use NON EMPTY which isn't
possible with Excel XP. See KB article 234700 for details.
http://support.microsoft.com/default...b;en-us;234700

"'The Show items with no data' option in the PivotTable Field dialog
box is not available on PivotTable reports based on OLAP source data."

Oops, I misspoke. The Display Format change only works if you have
fact table rows for all crossjoin combinations.

Tom's right. Use calculated members or find a client tool that will
display the empty measures as zero.

One final item.

You can simplify the calculated measure to:

Measures.HiddenMeasure

and set the Display Format to #,0;;;"0"
The fourth field (separated by semicolons) is used for null values.

Unfortunately, setting the Display Format to #,0;;;"0" on the base
measure doesn't work. That's really what you want.

The inconsistency seems like a bug but there's probably some good
reason for this behavior.