How to use a parameter cube in PAW MDX

[ice1000]

I have a calculated member in a PAW view. Instead of hardcoding the values in the calculation I would like to retrieve the element name from a parameter cube. I can't figure out the correct syntax.

This is the MDX with the hardcoded values:

Code: Select all

WITH 
   MEMBER [Scenario].[Scenario].[Plan / Actuals] AS [Scenario].[Scenario].[Plan] / [Scenario].[Scenario].[Actuals], SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)' 
SELECT 
   {
      [Period].[Period].[All Years^2020^2020Q2^202004]
   } ON 0, 
   {
      [Scenario].[Scenario].[Scenario Hierarchies^Actuals],
      [Scenario].[Scenario].[Scenario Hierarchies^Plan],
      [Scenario].[Scenario].[Plan / Actuals]
   } ON 1 
FROM
   [Finance] 
WHERE (
   [Finance_Msr].[Finance_Msr].[Original], 
   [Company].[Company].[001], 
   [Department].[Department].[Total Departments^Sales and Marketing^40], 
   [Currency].[Currency].[USD], 
   [Account].[Account].[Net Income After Allocations^Net Income^EBITDA^Gross Margin^Net Sales^Sales^410000])

I want the denominator of

Code: Select all

MEMBER [Scenario].[Scenario].[Plan / Actuals] AS [Scenario].[Scenario].[Plan] / [Scenario].[Scenario].[Actuals]

to be retrieved from a cube. The cube will have the name of the scenario as a string data point.

I've tried this and I get ERROR in the cube (MDX compiles but I see the word ERROR in the data point):

Code: Select all

            (
                [Control].[Control Parameter].[Target Scenario],
                [Controls].[Measure].[String]
            )

I created a dynamic subset that returns the correct scenario using a rule-based attribute. Then I tried this:

Code: Select all

STRTOMEMBER("[Scenario].[Scenario].[" + SETTOSTR( TM1SubsetToSet([Scenario].[Scenario] , "TargetScenarioSubset" , "public")) + "]")

That also results in an ERROR value.

How can I do this?

[ice1000]

For posterity, I got these two to work. I do want to get the TM1SUBSETTOSET to work. Still testing that one.

Code: Select all

STRTOMEMBER("[Scenario].[Scenario].["+LOOKUPCUBE( "Control","([Control Parameter].[Control Parameter].[Target Scenario],[Control Measure].[Control Measure].[String])" )+"]")

STRTOMEMBER("[Scenario].[Scenario].["+[Control].([Control Parameter].[Control Parameter].[Target Scenario],[Control Measure].[Control Measure].[String])+"]")

[gtonkin]

Try Part 7 of my Introduction to MDX for more insights...

[gtonkin]

To get a member from the subset try something this:

Code: Select all

{
TM1SubsetToSet([Scenario] , "Default").ITEM(0).ITEM(0)
}

That should return the first member.

Code: Select all

{
TM1SubsetToSet([Scenario] , "Default").ITEM(1).ITEM(0)
}

Will return the second member.