I currently have a problem with my MDX expression that I can't figure
out. Here's the information:

MDX Expression:

IIF(IsEmpty(([Measures].[Expended Dollars],Ancestor([Business
Group].Currentmember,1))),1,[Measures].[Expended
Dollars]/([Measures].[Expended Dollars],Ancestor([Business
Group].Currentmember,1)))

I'm displaying the percentage of expended dollars relative to the total
for the group. When I place other dimensions to the left of the
Business Group dimension in Excel the percentages do display correctly
for any grouping that has a grand total value for the grouping. An
example is the following:

GENERAL OPERATIONS
Dummy Value1 $91,121.55 1.78%
Dummy Value2 $286.00 0.01%
Dummy Value3 $397.21 0.01%
Dummy Value4
Dummy Value5 $5,020,816.91 98.20%
GENERAL OPERATIONS Total $5,112,621.67 100.00%

The problem occurs when there is no expended values at all for a
grouping as in the following example:

TRAINING
Dummy Value1 100.00%
Dummy Value2 100.00%
Dummy Value3 100.00%
Dummy Value4 100.00%
Dummy Value5 100.00%
TRAINING Total 100.00%

I want my MDX to be able to display this situation correctly as it does
for groupings that have expenditures (note in the top example that the
Dummy Value 4 line is blank for both since it did not have an
expenditure.

My question. How can I modify my MDX to test for a null or
non-existent grand total so it will not incorrectly display 100% for
every item in the grouping?

Thanks so much! This is my first project with MDX so it may be an easy
answer and I appreciate your patience!!

Martin

Martin, it looks like you're instructing the immediate if to return a 1 if
the ancestor is empty. You should be returning NULL, if you want NULL
returned.

If you want to check for null, something like:

IIF(([Measures].[Expended Dollars],Ancestor([Business
Group].Currentmember,1)) = NULL, NULL, etc....

....should work.

Good Luck!

"halil" wrote:

Thanks for the reply.

I tried it and I'm still stuck.

I use the IsEmpty function to check for the existence of a parent and
if none (I assume it is a grand total) and place a 1 (which will show
as 100%). This works perfect for a single grand total (i.e.
Dimension1=>Dimension2=>measures) but fails whenever I place another
dimension in front of them.

I may be off base in what I'm trying to do, maybe it's not possible
without specifying the individual dimensions in the MDX expression??

I got it. I used your suggestion to check for null in combination with
a nested IIF to check for the existence of the parent. Works perfect.

Thanks for the suggestion. Without it I would not have thought to
combine two checks instead of the single check I was performing.

Respectfully,

Martin