ActiveForm: MDX is ignored
Posted: Mon Jun 25, 2012 7:04 am
Dear TM1 friends,
I have an active form which displays product groups as line headers. I need a dynamic selection of these, based on two cube queries, so in the end there will only be product groups which were either sold in the current period or planned to sell in the forthcoming period.
I use this dynamically concatenated mdx statement as a parameter of the TM1RPTROW() function, and it fails. If I use something very simple, like just one element indicated in the mdx, this works, but as soon as I switch to the syntax which I actually need at the end of the day (or even parts of it), the report behaves as if there were no mdx statement at all (i.e. it displays the product groups as shown in the view originally used when creating the report).
Here is what I checked so far:
- The syntax works, i.e. if I paste the statement into the subset editor, it shows the expected result
- The element names are all principal names, so no aliases used
- None of the single cells which are concatenated in the end contains more than 256 characters each
- Even if I have just hard coded text in all the cells, so no calculation to be performed except for the concatenation, it does not work
An clue? This is Excel 2003, TM1 9.4.1. The mdx is 751 characters long, and this is what it reads:
Holger
I have an active form which displays product groups as line headers. I need a dynamic selection of these, based on two cube queries, so in the end there will only be product groups which were either sold in the current period or planned to sell in the forthcoming period.
I use this dynamically concatenated mdx statement as a parameter of the TM1RPTROW() function, and it fails. If I use something very simple, like just one element indicated in the mdx, this works, but as soon as I switch to the syntax which I actually need at the end of the day (or even parts of it), the report behaves as if there were no mdx statement at all (i.e. it displays the product groups as shown in the view originally used when creating the report).
Here is what I checked so far:
- The syntax works, i.e. if I paste the statement into the subset editor, it shows the expected result
- The element names are all principal names, so no aliases used
- None of the single cells which are concatenated in the end contains more than 256 characters each
- Even if I have just hard coded text in all the cells, so no calculation to be performed except for the concatenation, it does not work
An clue? This is Excel 2003, TM1 9.4.1. The mdx is 751 characters long, and this is what it reads:
Thank you{FILTER({TM1DRILLDOWNMEMBER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[Dim_Productgroup].[Alle Sparten]}, ALL, RECURSIVE )}, 1)},ALL, RECURSIVE)},[C_Sales].([Dim_ValueType].[905], [Dim_Version].[Version_final], [Dim_DisplayType].[Reported],[Dim_CurrFlag].[LC],[Dim_Year_Input].[2012],[Dim_Year].[2012],[Dim_Month_Sales].[HR],[Dim_Company].[0128],[Dim_Partner_Company].[All],[Dim_Country_Sales].[International], [Dim_Measure_Sales].[SumUnit]) <> 0 OR [C_Sales].([Dim_ValueType].[021], [Dim_Version].[L_Version_1], [Dim_DisplayType].[Reported],[Dim_CurrFlag].[LC],[Dim_Year_Input].[2012],[Dim_Year].[2013],[Dim_Month_Sales].[Year],[Dim_Company].[0128],[Dim_Partner_Company].[All],[Dim_Country_Sales].[International],[Dim_Measure_Sales].[SumUnit]) <> 0)}
Holger