TM1 Forum

Hi. I am new to MDX (been trying to write new MDX code for only a week) and am struggling to find an MDX code which will return the results of a dimension which has an attribute. I have a dimension called Customer, with a subset called All Parent Customers - Sales Reporting. I usually have to filter by level zero and then using an attribute, filter the results using an attribute called New Customer and the conditon is Yes. This should return all new customers within the past 7 days.

I have managed to filter by level 0 which is the level the attribute works on but have been unable to then add into the mdx code the attribute filter. Can anyone help me with this?

The code I have, for filtering by level 0 is:

=TM1RPTROW($B$9,"CXMD_VCH:Customer","",'{AR}01'!$B$24,"Customer Number & Name",0,"{TM1FILTERBYLEVEL( {Descendants([Customer].[All Parent Customers - Sales Reporting] )}, 0)}")

Please bear with me on this as I am totally new to this.

Thanks

Sarah,

First things first, the easiest way to get your head around MDX as a starting point is to go into the subset editor, then under view you can turn on the expression window and select to record an expression, with these on you can automatically build up an MDX query to filter by level and filter on an attribute. In addition to this I recommend googling "TM1 MDX Primer" (or searching this forum for it), it is basically an everything you need to know on the TM1 MDX fundamentals, it's quite rare that people need to go beyond anything in that document and even if they do it is a very good grounding for teaching yourself that extra step.


So to your specific issue, the TM1RPTROW formula suggests that you are doing this as part of an active form, in the case that you want an active form to return only the MDX derived elements you can get rid of the {AR}.... part of the formula, that is looking at a hidden sheet of static elements and pulling those back, since you want it to bring back your MDX elements you obviously don't want that. On another note even if you do want a static list of elements do NOT user the auto generated {AR} hidden sheets to do it, they kill performance almost as extremely as anything effecting active forms can do.

If for example you were trying to pull back all level 0 elements under "All Parent Customers - Sales Reporting" that happen to have an Attribute populated as "YES" where the attribute name is "FILTER ME" the following code would do the trick:

Breaking it down:
Returns all descendants under "All Parent Customers - Sales Reporting" (so just a TM1 specific MDX function that is the equivalent of Descendants)
Returns all nodal (level 0) elements that were returned earlier (TM1DRILLDOWNMEMBER xxx, all, recursive returns ALL levels of descendants plus the filtered member itself.)
Finally you filter all your nodals to see which ones have the "FILTER ME" attribute set to "YES".


You could also have used the following (since you already have a grasp of the descendants function):

Thank you for the detailed reply. I tried to record the expression which returned the correct subset but I need the form to update each time you log in but this seemed to return a static subset.

I also tried your formula and get and error. Excel highlights the "yes" as being and error

=TM1RPTROW($B$9,"CXMD_VCH:Customer","",'{AR}01'!$B$24,"Customer Number & Name",0,"{FILTER( {TM1FILTERBYLEVEL( {DESCENDANTS( {[Customer].[All Parent Customers - Sales Reporting]})}, 0)}, [Customer].[New Customer] = "yes")}")

I dont know if I just have brackets or " in the wrong places?

The error isn't a TM1 specific thing it is just basic excel.

You have double quotes around the "YES" as such excel reads that as being the end of a string. If you want it to be part of the statement you can use:
As to it being "static", the elements returned will only change if the dimension structure (e.g. if you get more level 0 elements) and/or the population of the attribute changes.

The MDX section does take precedent over the {AR} static subset from memory but I really would not recommend keeping it in there in addition to your MDX statement.

Great thanks. All working now and have removed the AR reference too.

I wll get there slowly!