Referenc Parameter to MDX Query from a Worksheet Cell

[sriharsha.bn]

Hi All,

I need help to pass a parameter to MDX query in Active Form.

=TM1RPTROW($B$9,"abc_tm1:Customer",,,,,$H$2)

Under H2 cell of the sheet i have the below MDX.

{ ORDER( {TOPCOUNT( {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 1)}, [Customer].[IncludeInTop] = "Include")}, 10.000000, [Activity].([Version].[LE],[Period].[Full Year],[Currency].[Euro],[Control_Type].[All Control Types],[Cust_Type].[All Cust Types],[Project].[All Projects],[BU].[FFW Total],[Sector].[All Sectors],[Branch].[$H$8], [Route].[All Routes],[Measures_Activity].[Gross Margin]))}, [Activity].([Version].[LE],[Period].[Full Year],[Currency].[Euro],[Control_Type].[All Control Types],[Cust_Type].[All Cust Types],[Project].[All Projects],[BU].[FFW Total],[Sector].[All Sectors],[Branch].[$H$8)],[Route].[All Routes],[Measures_Activity].[Gross Margin]), BDESC) }

In Worksheet under Cell H8 i have a reference to entity selected for Branch Dimension which is being passed to MDX as a parameter but this doesn't seem to work. Please suggest how could i accomplish passing a parameter to MDX from Worksheet cell in Active Form.

If i replace H8 with a Specific Entity say Australia or New Zealand it works but doesn't work when i reference a Worksheet cell.

A document with screenshot has been attached for reference.

Thanks all in advance!

Cheers,
Sri

[asutcliffe]

At the moment your MDX string includes the string "$H$8)" not the value of cell h8. You need to change H2 to contain a formula that concatenates the strings together. I think you have a stray bracket in there too. That is, try something like this:

Code: Select all

="{ ORDER( {TOPCOUNT( {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 1)}, [Customer].[IncludeInTop] = "Include")}, 10.000000, [Activity].([Version].[LE],[Period].[Full Year],[Currency].[Euro],[Control_Type].[All Control Types],[Cust_Type].[All Cust Types],[Project].[All Projects],[BU].[FFW Total],[Sector].[All Sectors],[Branch].[" & $H$8 & "], [Route].[All Routes],[Measures_Activity].[Gross Margin]))}, [Activity].([Version].[LE],[Period].[Full Year],[Currency].[Euro],[Control_Type].[All Control Types],[Cust_Type].[All Cust Types],[Project].[All Projects],[BU].[FFW Total],[Sector].[All Sectors],[Branch].[$H$8)],[Route].[All Routes],[Measures_Activity].[Gross Margin]), BDESC) }"

[lotsaram]

Also - I assume you are generating the MDX to use as the rowset in an active form for the MDX argument of TM1RPTROW?

If so although you can use TOPCOUNT and ORDER functions directly in the MDX you don't have to. If instead you use the TM1RPTFILTER formula then all you need to do is pass in the column tuple and your MDX for the rowset would be greatly simplified.

[sriharsha.bn]

Thanks all for your response!

The stray bracket was just a typo error while posting the request. Since a formula in excel cannot exceed 255 characters i cannot include the MDX directly in the TM1RPTROW function.

[sriharsha.bn]

Also i would not like to use TM1RPTFILTER function since the MDX which i am trying to implement is pretty complex (different from the one posted), i am just trying with simple MDX first and would replace once i get to know exactly on how i could use the parameter.