Active Form - TM1RPTROW MDX issue

[jamesbennett]

Hi there,

I've set up an Active Form that I have published through TM1 Web. I have the original TM1RPTROW formula as follows:

Code: Select all

=TM1RPTROW($B$9,"server:Global Orgstructure","n-level","","Code and Name",0)

I inserted an MDX formula into cell C14:

Code: Select all

="{TM1FILTERBYLEVEL({DESCENDANTS([Global Orgstructure].[4D])},0)}"

and updated the TM1RPTROW formula to be as follows:

Code: Select all

=TM1RPTROW($B$9,"server:Global Orgstructure","n-level","","Code and Name",0,$C$14)

This works exactly as intended, to give me all leaf-level elements within the 4D consolidation.

However, when I try to make the MDX dynamic, so instead of hard-coding 4D I reference another cell that evaluates to 4D, the TM1RPTROW formula breaks and I get no data appearing.

Dynamic MDX:

Code: Select all

="{TM1FILTERBYLEVEL({DESCENDANTS([Global Orgstructure].["&$C$13&"])},0)}"

So you see the only thing I have changed is replacing "4D" with a reference to cell $C$13, which evaluates in Excel as "4D". Yet the hard-coded version works and the cell-referenced version does not. Am I missing something obvious?

I got it working earlier with the dynamic reference, and I'm fairly sure I haven't changed anything in the meantime that would cause it to break...

[orlando]

Hi,

i always build the MDX Query in a Excel cell (with reference etc...)
In TM1RPTROW i only refer to the cell.
My TM1RPTRow looks like that:

Code: Select all

=TM1RPTROW($B$9;"MyServer:TM1User";;;"Name";;$G$14)

Maybe that helps
Best regards
orlando

[jamesbennett]

Hi,

i always build the MDX Query in a Excel cell (with reference etc...)
In TM1RPTROW i only refer to the cell.
My TM1RPTRow looks like that:

Code: Select all

=TM1RPTROW($B$9;"MyServer:TM1User";;;"Name";;$G$14)

Maybe that helps
Best regards
orlando

Thanks, that is what I've been doing - apologies I missed that step from the OP. Any idea why it might not be working with the cell reference in the MDX?

[tomok]

It looks like you have both a subset parameter AND an MDX formula parameter in your TM1RPTROW formula. This is really an either/or thing. TM1 either uses the subset or the MDX, it can't use both. Perhaps it uses the MDX parameter over the subset one as long as it can be evaluated easily but once you add another level, by using a cell reference, it breaks. Just a guess. I always make sure I use EITHER a subset or MDX parameter. Never both.

[jamesbennett]

It looks like you have both a subset parameter AND an MDX formula parameter in your TM1RPTROW formula. This is really an either/or thing. TM1 either uses the subset or the MDX, it can't use both. Perhaps it uses the MDX parameter over the subset one as long as it can be evaluated easily but once you add another level, by using a cell reference, it breaks. Just a guess. I always make sure I use EITHER a subset or MDX parameter. Never both.

Thanks, that's a valid guess and makes sense. I've taken out the subset so my formula is just:

Code: Select all

=TM1RPTROW($B$9,"server:Global Orgstructure","","","Code and Name",0,$C$14)

i.e. just referencing the MDX formula

However nothing changes - still it doesn't update the active form unless I replace the cell reference in the MDX formula with hard-coding.

Edit: Updating my MDX formula to remove all cell references from it (but including the formulae it draws on) still produces no data:

Code: Select all

="{TM1FILTERBYLEVEL({DESCENDANTS([Global Orgstructure].["&DBRW("server:}ElementAttributes_}Clients",TM1USER("server"),"OrgStructureSubset")&"])},0)}"

[orlando]

Code: Select all

=TM1RPTROW($B$9,"server:Global Orgstructure","","","Code and Name",0,$C$14)

i.e. just referencing the MDX formula

However nothing changes - still it doesn't update the active form unless I replace the cell reference in the MDX formula with hard-coding.

Edit: Updating my MDX formula to remove all cell references from it (but including the formulae it draws on) still produces no data:

Code: Select all

="{TM1FILTERBYLEVEL({DESCENDANTS([Global Orgstructure].["&DBRW("server:}ElementAttributes_}Clients",TM1USER("server"),"OrgStructureSubset")&"])},0)}"

Dows yor MDX work in SubsetEditor?

[Wim Gielis]

And try DBR instead of DBRW.

[jamesbennett]

And try DBR instead of DBRW.

Changing the DBRW to DBR seems to work. So my next question is why would that be? I understood DBRW was generally higher-performance than DBR.

Could that be why (using DBRW) the active form is evaluating in TM1 Web, but not in Perspectives? That the data can still be drawn through (as the formulae are valid) but Perspectives can't handle it, whilst TM1 Web can?

[Wim Gielis]

Please use the search function and Google, this topic has been discussed many times and is perceived as basic knowledge for somewhat more advanced TM1 users.

[tomok]

I HIGHLY recommend you Google and read about the difference between DBR and DBRW. It's must have knowledge if you work with TM1.