TM1RPTROW issue

[subha.cognos]

Dear All:

Immediate help is really appreciated. kindly assist me if it is correct mdx or not.

I have active form input template where I filter using MDX to show the Outlets that outlets are active, no rule applied OUTLETS using the TM1RPTROW which is perfectly working. but I am adding the one filter where attribute is NON-Dummy then the TM1RPTROW is not showing any outlets.

Working formula:
=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].{Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")}")

$C$13 is my account dimension reference. $C$16 is my OUTLETS dimension parent.

when I add another filter where my TRANE is equal to "NON-Dummy". it is not working.
=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].{Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},[OUTLETS].[TRANE]=""NON- Dummy"")}")


kindly let me know if my TM1RPTROW is correct?

[gtonkin]

Could it be as simple as the space in "NON- Dummy"?

[subha.cognos]

Thanks gtonkin. I figured out the issue. I added the "s" in TRANE (make unique Trane attribute) because I have one of the dimension name is TRANE and my attribute name is TRANE in the OUTLETS dimension. so renamed the attribute name to OUTLET dimension as TRANEs then it worked.


=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].{Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},[OUTLETS].[TRANEs]=""NON- Dummy"")}")

But another issue I came across. I need to make the dynamic selection on the TRANEs (it is selection for users on the top). based on user selection ( user can select TRANEs as NON-Dummy or Dummy or Dummy.New) then my outlets should show accordingly. so I am refereeing it to $C$17 since TRANE dimension is selection to users. but it is not showing up any outlets. can you please let me know if my formula is appropriate?

New RPT:
=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].{Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},[OUTLETS].[TRANEs]=""$C$17"")}")

[Steve Rowe]

Hi,

Its pretty challenging to debug MDX without the system in front of you.
I'd suggest the following.

1. Set up a distinct cell that holds the Excel formula that is delivering the MDX statement. i.e. This piece

="{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].{Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},[OUTLETS].[TRANEs]=""NON- Dummy"")}"

2. Refer to this cell in the TM1RptRow formula.

This way you will have an excel cell that will deliver the complete MDX statement that you can read and debug. You should always be able to paste this into the expression / MDX window of the set editor and see if it is valid (versus valid but returning 0 rows).

Hope this helps

[burnstripe]

I can see one typo here
[OUTLETS].{Active Status]

Should be
[OUTLETS].[Active Status]

[subha.cognos]

Thank you burnstripe. it was typo while I am typing it.

do you have any suggestions how to call the subnm function selection (user selection) in the mdx in below bolded syntax?

=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].[Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},
[OUTLETS].[TRANEs]=""$C$17"")}")

where $C$17 is SUBNM in the top selection for user.

[Wim Gielis]

You need to chop up the MDX string and concatenate the different pieces with the ampersand character &

This is no different compared to regular Excel formulas where you would do the same, so I guess this information should be sufficient to get it working.

[subha.cognos]

Hi Wim Gielis,

could you please do needful if this it is correct? I tired various options but didnot work. could you please some light on it.

=TM1RPTROW($B$9,"PLANNING:OUTLETS","","","Alias",0,"{FILTER({FILTER({FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[OUTLETS].["&IF($C$13="No Account selected","",$C$16)&"]},ALL,RECURSIVE)},0)},[OUTLETS].[Active Status]=""Y"")},[OUTLETS].[NO RULE]="""")},
[OUTLETS].[TRANEs]="&$C$17&")}")

[Wim Gielis]

In the bold part at the end, I don't understand why you would get rid of the double quotes around the cell contents of cell C17. Do you assume that you can just do without them ?
Did you test in the Set Editor / Subset Editor that your MDX works ?
The $ signs around cell C17 are useless.