TM1 Forum

Hi,

I am trying to populate an active form with a MDX on the tm1rptrow.
the mdx works on 2 attributes and it works on the expression window of the subset editor but in my form it gives me an empty set.

The mdx is this:

{FILTER(Descendants({[DIMENSION].[PARENT]}),(([DIMENSION].[ATTR1] ="Yes") AND ([DIMENSION].[ATTR2]="ATTRVALUE")))}

I tried both the "static" version of this and the "Dynamic" created passing the "ATTRVALUE"via Excel

It could some sort of priority on fetching the data.

Any ideas?

forgot to mention it is TM1 10.1

It would be helpful if you also provide a text and/or screenshot of your formula in Excel where you are parsing the MDX expression. When doing this myself I always find it handy for debugging to copy from the excel formula and paste as text into the expression window of the subset editor to make sure that the MDX is OK and returns the expected result. Usually when there is an error in parsing in Excel for an active form MDX rowset it is because of forgetting to include the double quotes which are needed for testing the string values like your attributes which you can do with &CHAR(34)& or with a triple quote in the Excel formula.

If there is a compile error in the MDX you will also find that you get a ERROR line in the tm1server.log for the TM1.Mdx.Interface logger. It isn't usually helpful but it may tell you the character position in the MDX query where the (1st) error is encountered.

lotsaram wrote:It would be helpful if you also provide a text and/or screenshot of your formula in Excel where you are parsing the MDX expression. When doing this myself I always find it handy for debugging to copy from the excel formula and paste as text into the expression window of the subset editor to make sure that the MDX is OK and returns the expected result. Usually when there is an error in parsing in Excel for an active form MDX rowset it is because of forgetting to include the double quotes which are needed for testing the string values like your attributes which you can do with &CHAR(34)& or with a triple quote in the Excel formula.

If there is a compile error in the MDX you will also find that you get a ERROR line in the tm1server.log for the TM1.Mdx.Interface logger. It isn't usually helpful but it may tell you the character position in the MDX query where the (1st) error is encountered.

as mentioned, the expression window works smoothly and retrieves exactly what I expect.

the formula is exactly the one I wrote in my first with the name of attributes/value changed.

The values are correct (as otherwise it will give me an error on the expression window).

The Dynamic one is the same with some char(34) in it, I didn't copy it here as i don't get result either with the HC one.

Why should anyone help you if you can't be bothered to provide a simple request for more information? It's highly likely that your problem is in the construction of the cell(s) in Excel where you've constructed the TM1RPTROW formula. The fact that an MDX statement works in the subset editor doesn't mean jack squat if you've constructed the TM1RPTROW formula incorrectly, likely due to syntax issues.

tomok wrote:Why should anyone help you if you can't be bothered to provide a simple request for more information? It's highly likely that your problem is in the construction of the cell(s) in Excel where you've constructed the TM1RPTROW formula. The fact that an MDX statement works in the subset editor doesn't mean jack squat if you've constructed the TM1RPTROW formula incorrectly, likely due to syntax issues.

sorry but I don't understand what I didn't provvide, the name of the attributes and the values? because it seems to me that a part from that everything was there....
AKA, I copy pasted the formula after a find replace the names.

BTW, it works now. the problem itself is a mistery.

I tried in another environment and it worked first try. restarted the server and all went good.

stingo wrote:sorry but I don't understand what I didn't provvide, the name of the attributes and the values? because it seems to me that a part from that everything was there....

lotsaram wrote: It would be helpful if you also provide a text and/or screenshot of your formula in Excel where you are parsing the MDX expression

Does this ring a bell?