Excel 2007 and MDX

Question

Hi, I'm making my trenx expression for the KPI.

I would like to know if it is possible to adress a cell in the spreadsheet of Excel 2007 to use as a numeric expression in MDX

For example :

Case
    When IsEmpty
         (
           ParallelPeriod
           (
              [Dwh Dimtijd].[Jaar - Maand].[Jaar - Maand],
             1,
             [Dwh Dimtijd].[Jaar - Maand].CurrentMember
           )
         )
    Then 0  
    When VBA!Abs
         (
          ( 
            KpiValue( "Market share" ) 
            - 
            (
              KpiValue( "Market share" ),
              ParallelPeriod
              ( 
                [Dwh Dimtijd].[Jaar - Maand].[Jaar - Maand],
            1,
             [Dwh Dimtijd].[Jaar - Maand].CurrentMember
              )
            )
          ) 
          /
          (
            KpiValue( "Market share" ),
            ParallelPeriod
            ( 
                [Dwh Dimtijd].[Jaar - Maand].[Jaar - Maand],
             1,
             [Dwh Dimtijd].[Jaar - Maand].CurrentMember
            )
          )  
         ) <=.02
    Then 0
    When ( 
           KpiValue( "Market share" ) 
           - 
           (
             KpiValue( "Market share" ),
             ParallelPeriod
             ( 
                 [Dwh Dimtijd].[Jaar - Maand].[Jaar - Maand],
             1,
             [Dwh Dimtijd].[Jaar - Maand].CurrentMember
             )
           ) 
         )
         /
         (
           KpiValue( "Market share" ),
           ParallelPeriod
           ( 
              [Dwh Dimtijd].[Jaar - Maand].[Jaar - Maand],
             1,
             [Dwh Dimtijd].[Jaar - Maand].CurrentMember
           )
         ) >.02
    Then 1
    Else -1
End

I want to change the "1" into an numeric expression that gets his value out of a cell in Excel. (it can only be 1 or 12)

Answer 1

Hi Evelyn,

It appears that what you are trying to do is let the user choose whether they want month-over-month growth (by selecting 1) or year-over-year growth (by selecting 12).

The best way I've found to give the user a choice--especially one like this, where there's a finite list of options--is to create a new dimension in the cube--one that is not related to anything. Here's the basic idea:

* Create a Named Query, say 'GrowthSelect' in the DSV that looks something like this:
     SELECT 1 as Key, 'Prior Month' as Name
     UNION
     SELECT 12 as Key, 'Prior Year' as Name
* Create a new dimension using the 'GrowthSelect' "table", with only one attribute.
* Set the attribute to IsAggregateable = False (to eliminate the All member), and pick one of the options as the default.
* Add the dimension to the cube, but with no Dimension Usage. (Make sure "ignore unrelated attributes" is True for the measure group.)
* Revise your MDX to use [GrowthSelect].[GrowthSelect].CurrentMember.Properties("Key") in place of 1

The user can then select which time horizon they want. (You can, of course, leave out the descriptive names; it just seemed like that was the intent of the option; you can also call the dimension/attribute whatever you want--something that would be meaningful to the user.)

HTH.
Let me know if I misunderstood the question.
Cheers,
Reed

Hitachi Consulting
"I may work for Hitachi Consulting, but my dumb ideas all belong to me."

Answer 2

What do you mean by "Create a new dimension using the "GrowthSelect" "table", with only one attribute???
what attribute should that be?

Answer 3

I tried to do it by your way, but i must have made a mistake.

When i choose the default member I only can choose 12, and when i look into the members I also only see 12