Thanks in advance for any responses.

In the past I have handled empty cells by substituting 0, NULL or a "NA"
string like the example where member month count can often be empty:
Iif(IsEmpty([Measures].[Member Month Count]), NULL, [Measures].[Total Paid
Professional]/[Measures].[Member Month Count])

However, the business requirement for this virtual cube is to use the value
at the all level and member month count intersections instead of NULL, 0,
etc. This virtual cube is made up of 5 physical cubes, over 30 dimensions,
about 30 measures and 30 calculated members so there are a lot of empty cells
where intersections do not make business sense. I have tried to get the MDX
syntax to fetch the total contained in the all level of any dimension. This
does not work and just returns error in the cell:
Iif(IsEmpty([Measures].[Member Month Count]), [Professional].[All
Professional], [Measures].[Total Paid Professional]/[Measures].[Member Month
Count])

I manually substituted the number and the calculation works with the
dimension rollup:
Iif(IsEmpty([Measures].[Member Month Count]), [Measures].[Total Paid
Professional]/2328188, [Measures].[Total Paid
Professional]/[Measures].[Member Month Count])

The number 2,328,188.00 is at the all level of any of the dimensions
including the measure dimension.
select
{[Measures].[Member Month Count]} on columns
from ECGR_PRG

Is there correct MDX syntax to reference that number at the all level of any
of the dimensions that will work and not return an error? Or will a
different solution be necessary?

--
Paul G

How is [ Measures].[Member Month Count]defined - if it is empty in these
cases because some of the selected dimension members don't apply, then
will wrapping it in ValidMeasure() apply the appropriate All dimension
levels?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Are your problems with dimensions from other cubes that are not valid?

If so, have you seen the ValidMeasure() function? It is used in virtual
cubes to force inapplicable dimensions up to the all level.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

The correct solution (thanks to Deepak and Darren):
Iif(IsEmpty([Measures].[Member Month Count]), [Measures].[Total Paid
Professional]/ValidMeasure(([Measures].[Member Month Count] ,
[Professional].[All Professional])), [Measures].[Total Paid
Professional]/[Measures].[Member Month Count])

--
Paul G


"Paul G" wrote: