This issue pertains to fact record measure values (I am not talking
about null dimension members)

I am creating a cube that includes measure values that are either null,
zero or non-zero.

I want to be able to display the measure values as blank for null
values, OR use a specific display format for not-null values (like
#,###.#)

No matter what formatting option I choose it appears I can only display
the measure values as blank for zeros or nulls using #,### or I can get
formatted zero values for zero or null values using #,###.0.

What I can't seem to get is Blank for Nulls and Fomatted results for
non-null values.

Any suggestions are greatly appreciated.

Thanks

Depends on how you define a "Null" value - see this MS KB article for
details. AS 2005 can distinguish 0 and Null:

http://support.microsoft.com/default...b;en-us;244650
Question: How does OLAP Services read NULL measure data in a fact table?

Answer: If there is no record in the fact table for a certain
combination of dimension members, (such as [1997].[Quarter1].[January],
[USA].[Texas].[Dallas] ) then, on cube processing, OLAP Services treats
the corresponding cell in the cube as empty. However, when there are
records in the fact table that have one or more null measure columns,
OLAP Services assigns a 0 value to the corresponding cells in the cube.

This behavior may not always be desirable, particularly, when the user
has to make a distinction in the cube's measure between a fact table
NULL and zero values. If such a distinction must be made, then the user
has to make sure that the fact table (or tables) do not have records
with empty measure columns. One solution is to break these empty measure
columns into different fact tables. The steps necessary to implement
this solution are:

1. Create multiple fact tables. One for each measure that needs a
distinction to be made between a NULL and a zero value.

2. Create a cube based on each fact table.

3. Create a virtual cube that includes all the base cubes to view all
the measures together.
...

http://groups-beta.google.com/group/...rver.olap/msg/
3eeb36051dce12c1

Newsgroups: microsoft.public.sqlserver.olap
From: "Dave Wickert [MSFT]" <dwick... (AT) online (DOT) microsoft.com>
Date: Thu, 17 Feb 2005 10:45:48 -0800

Subject: Re: Creating Dimensions with NULL and 0 Member

AS does not understand 3-value logic. It has no concept of NULL as a
RDBMS
handles ANSI NULLs -- it translates it to zero automatically. What I
typically recommend to customers is to create a view where you cast
NULLs to
whatever value you wish (e.g. create a "unknown" member within your
dimension tables).

With SQL Server 2005, AS does have specific logic for handling NULLs and
unknown members automatically.

--
Dave Wickert [MSFT]
dwick... (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!