MDX Expression in Active Form question

[ChrisF79]

I have a customers dimension and there are two attributes--one for days since they last paid and one for days since they last had a sale. What I'm trying to do is make an active form that says, "Give me all customers where they haven't paid for 90 days but have had a sale in the last 30."

How can I make the tm1rptrow formula show me that? The default is showing

Code: Select all

=TM1RPTROW($B$9,"applix-inc backup:Customers","",'{AR}01'!$B$20:$B$21,"Name",0)

Is it the {AR} part I'm supposed to replace? I used the expression recorder to make a new expression and I pasted it in there but that didn't work. Any advice would be greatly appreciated!

[ChrisF79]

I see now that it's definitely not the {AR} part of the formula so now my questions become, how do I generate the MDX that I want to limit on two attributes and then where do I implement it in the formula?

[ajain86]

The MDX statement would be the next arguement in the TM1RPTRow function, so after the "0". Look up the function TM1RptRow in the help guide and it can give you an explanation on the proper syntax.

You can generate a MDX statement using the subset editor record expression functionality.

[ChrisF79]

That's not working out unfortunately. The expression recorder is giving me this as an MDX Statement:
{FILTER({TM1DRILLDOWNMEMBER({TM1SubsetBasis()},ALL,RECURSIVE)},[Customers].[Last Pay Days]=1007.000000)}

There should be 2 customers. Unfortunately, pasting that into Excel as the last argument in TM1RPTROW is giving me an excel error saying my formula is invalid.

[tomok]

Unfortunately, pasting that into Excel as the last argument in TM1RPTROW is giving me an excel error saying my formula is invalid.

Did you bother to look at the proper syntax and sequencing of the arguments in the TM1RPTROW formula before trying or did you just blindly paste it in as the last argument?

[ChrisF79]

Unfortunately, pasting that into Excel as the last argument in TM1RPTROW is giving me an excel error saying my formula is invalid.

Did you bother to look at the proper syntax and sequencing of the arguments in the TM1RPTROW formula before trying or did you just blindly paste it in as the last argument?

I did and it's the 7th argument, just like the docs say. My error was the single quotes. However, it still doesn't update the worksheet automatically when I change the number. What I have now is:

Code: Select all

=TM1RPTROW($B$9,"applix-inc backup:Customers","","","Name",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, 0)}, [Customers].[Last Sale Days] = 14.000000)}")

It's in the proper form but if I change the 14.000000 to a different number, it doesn't update the list of accounts. I thought that was the whole point!

[lotsaram]

Chris

You can't use the TM1SubsetBasis() function in an active form! You need to specify the member of the Customer dimension that you wish to drill down on. For example:

Code: Select all

{FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Customer].[All Customers]}, ALL, RECURSIVE )}, 0)}, [Customers].[Last Sale Days] = 14)}

The TM1SubsetBasis function only has relevance in the MDX expression recorder in the subset editor. Rather than showing the tuple in the expression window the list of members selected prior to the action being performed (the "subset basis" or starting point) is actually stored as a list of elements in the .sub file. The subset basis function merely retrieves that list. Without being attached to a physical subset which has been created using record expression in the subset editor the TM1SubsetBasis function has no validity.

IMO this function is useless and if a dynamic subset is created with the recorder then you should always replace {TM1SubsetBasis()} with the actual tuple. It makes the expression much more transparent and much easier to edit and transport between dimensions also.

[ChrisF79]

Chris

You can't use the TM1SubsetBasis() function in an active form! You need to specify the member of the Customer dimension that you wish to drill down on.

I just got it working with the TM1SubsetBasis. I'm not doing any drilling, just refreshing the list of customers based on criteria the user of the report sets. THey're able to put in the number of days since last pay and the number of days since last sale and hit the rebuild button. From there, the list of customers that meet their criteria are updated and the new numbers are pulled in. It's working perfectly as far as I can tell.

[tomok]

It's working perfectly as far as I can tell.

It "works", as in the code excecutes without an error, but if you leave the TM1SubsetBasis in your code you may get unpredictable results in the future because it is based on the starting point of the subset used to create it. This starting point may or may not be updated properly in the future as your dimension changes because it is not dynamic. You would be wise to head lotsaram's suggested code.