TM1 Forum

Good afternoon everyone,
As lotsaram advised me in my previous thread, I've created an MDX - based filtered Active Form.

What I have in TM1RPTROW cell is:
=TM1RPTROW($M$32;"servername:Task";;;;;mdx)
where $M$32 is a cell with TM1RPTVIEW and TM1RPTTITLEs - all mappings is checked and ok.

In mdx cell I have an MDX expression, so I can filter the rows by departments, so the user displays only rows that apply to his department:
="{FILTER({TM1SubsetBasis()}[Task].[Department] = '"&$Q$35&"')}"

I'm aware that "{TM1SubsetBasis()}" chunk is needed only when a subset name is given as a TM1RPTROW argument - be patient, I'm about to describe my issue just few lines later.
Of course, "$Q$35" is a reference to a cell where a department name is generated by a DBRA formula according to logged in user (if the department name is hardcoded into a cell, the issue still exists).

And now the best part. All works fine in Excel, rows are generated correctly according to given filtering, but, when uploaded to TM1 Web, Active Form is not showing up - only one, empty row is visible, like none of elements in Task dimension matches filtering criteria.
As I said before, erasing "{TM1SubsetBasis()}" chunk or adding a subset name into a MDX statement still doesn't resolve a problem.
I also tried erasing single quotes in '"&$Q$35&"' - still no luck.

Any ideas?

Regards,
Łukasz M.

Well, first of all you should never create an MDX subset using TM1SubsetBasis as this actually means nothing. If your intent is to filter the entire dimension, looking for a match, use TM1SubSetAll. However, i don't think this is your problem. It's worth a shot though. What you are running into here is just some of the little gotchas you'll uncover with TM1Web versus Perspectives. Many times all you can do is modify things until you can get it to work, making notes along the way. I'm guessing it doesn't like the two-step resolution of the Filter argument (using a DBRA in one cell and then pointing to that cell in the TM1RPTROW formula). Try replacing the $Q$35 in the TM1RPTROW formula with the actual DBRA formula so that it is not a two-step process. Beyond that I have no idea.

I don't think you need 2 steps but I agree wholeheartedly (and have said many times previously) that TM1SubsetBasis() should never be used outside of the subset editor as it has no context outside of the subset it refers to.

Rather than TM1SubsetBasis() you need something within the set brackets that resolves to a set of members, this could be something like any of the examples below ...
{ TM1SUBSETALL( [Task] ) }
{ TM1FILTERBYLEVEL( { TM1SubsetAll([Task]) }, 0 ) }
{ TM1DRILLDOWNMEMBER( { [Task].[All Tasks] }, ALL, RECURSIVE ) }
{ TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [Task].[All Tasks] }, ALL, RECURSIVE ) }, 0 ) }

You get the picture?

The other thing that is wrong with your MDX is you are missing a comma between the set and filter condition arguments. For example:
="{FILTER( { TM1SUBSETALL( [Task] ) }, [Task].[Department] = '"&$Q$35&"')}"

also I don't think the TM1 MDX engine cares about single or double (or no) quotes around a string value , but I usually use double quotes (for no other reason than this is what you get natively if you record an expression) in which case the parsed formula would look like
="{FILTER( { TM1SUBSETALL( [Task] ) }, [Task].[Department] = "&CHAR(34)&$Q$35&CHAR(34)&")}"