Hello!

I'm all new to MDX and have come to a point where I need some
assistance. I'm using MDX with SAS OLAP and not with any Microsoft
products but I guess this is the best group to get an answer from.

I want to calculate a new variable by looking at a value for a certain
period and compare it with the period before. Period in this case is
one of the following on my TIME hierarchy: Year-Quarter-Month-Day. The
code I written so far is the following:

----------
case
when ((Measures.[p2SUM], [Time].PrevMember) / ((Measures.[p2N],
[Time].PrevMember) - (Measures.[p2NMISS], [Time].PrevMember))) * 0.9 >=
(<<Measures.p2SUM>> / (<<Measures.p2N>> - <<Measures.p2NMISS>>)) THEN
'Increasing'
when ((Measures.[p2SUM], [Time].PrevMember) / ((Measures.[p2N],
[Time].PrevMember) - (Measures.[p2NMISS], [Time].PrevMember))) * 1.1
<= (<<Measures.p2SUM>> / (<<Measures.p2N>> - <<Measures.p2NMISS>>))
THEN 'Decreasing'
else '+/-0'
end
----------

This works just fine as long as I'm just moving up and down on the TIME
hierarchy, but I have another hierarchy called AREA. As soon as I drill
down a level on the AREA hierarchy the calculation doesn't work. My
guess is that I have to add some MDX code that tells it should stay on
the current level on the AREA hierarchy. I know there is a function
called currentMember in MDX and I guess this is the one to use,
unfortunately, I don't know how...

Any Ideas?

Best regards
Henrik

Hi Henrik,

In most cases with Analysis Services, the context of the "CurrentMember"
of a hierarchy is applied by default - maybe the SAS MDX implementation
is different, though?

From a cursory inspection of the calculation below, it might be useful
to refactor it as below, so that you can see how the values behave when
you drill down into [AREA}:

Member [Measures].[ValCurrent] as
[Measures].[p2SUM]/
([Measures].[p2N] - [Measures].[p2NMISS])

Member [Measures].[ValTrend] as
case
when ([Measures].[ValCurrent], [Time].PrevMember) * 0.9
when ([Measures].[ValCurrent], [Time].PrevMember) * 1.1
<= [Measures].[ValCurrent]
THEN "Decreasing"
else "+/-0"
end


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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