# 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)
Monday, March 30, 2009 1:08 PM

• 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."

• Proposed as answer by Monday, March 30, 2009 7:24 PM
• Marked as answer by Tuesday, March 31, 2009 7:38 AM
Monday, March 30, 2009 7:24 PM

### All replies

• 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."

• Proposed as answer by Monday, March 30, 2009 7:24 PM
• Marked as answer by Tuesday, March 31, 2009 7:38 AM
Monday, March 30, 2009 7:24 PM
• What do you mean by "Create a new dimension using the "GrowthSelect" "table", with only one attribute???
what attribute should that be?
Tuesday, March 31, 2009 6:34 AM
• 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
Tuesday, March 31, 2009 7:01 AM