Tm1 10.2 Active Form - Filter MDX Issue

[spiderwallet]

HI All,

Im finding some weird behaviour in active forms when using a TM1filter and it cant find any elements matching the wildcard search.

example:
Perspectives
="{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)}, '" & CONCATENATE("*",$M$29, "*") & "')}"
Executed MDX
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)},*zzz*')}"I

If the filter Is blank, it retuns level 0: =IF(filter="", "{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)}", CellReferencetoMDX)

In Operations Console the session/user is running a ViewArrayContruct on a different dimension? and it never finishes. Seems that the Tm1 server fails to return 0 rows back from the webserver and gets mixed up. I am currently on 10.2.1 FP2.

One thing to note is that this doesn't happen to all dimensions, I have another filter (based on a different dimension) using the same logic and it works fine..

Something to also note, If I use both filters at the same time it works..

Any suggestions?

Cheers!

[gtonkin]

Executed MDX
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)},*zzz*')}

Looks like you are missing a quote around the *zzz* - should be something like:

Code: Select all

{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)},"*zzz*")}

Note that I am using double-quotes too - you will probably need to change your excel formula:

Code: Select all

="{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)},""" & CONCATENATE("*",$M$29, "*") & """)}"

edit: There appeared to be one extra double-quote which I incorrectly pasted but have now fixed. Should have been easy to spot though.

[spiderwallet]

HI Gtonkin,

Thanks for replying..

I've tried your recommendation and it still doesn't work..

I've also created another web sheet from scratch and it doesn't work.. Just to complicate the issue, I have replicated the multiple filters on another instance based on a different cube and it works....

[BrianL]

In Operations Console the session/user is running a ViewArrayContruct on a different dimension? and it never finishes.

ViewArrayConstruct is actually calculating your view, so it's not unexpected that Operations Console is reporting a different dimension in the cube. TM1 has to look at all the dimensions to calculate the view. If it doesn't appear to ever finish, it's because you created a view that's very time consuming to calculate.

[Karthik1710]

HI Gtonkin,

Thanks for replying..

I've tried your recommendation and it still doesn't work..

I've also created another web sheet from scratch and it doesn't work.. Just to complicate the issue, I have replicated the multiple filters on another instance based on a different cube and it works....

Gtonkin's MDX looks perfect. If it still does not work, first of all are you sure there is an element that satisfies the filter? Also make sure the text you are filtering isn't in the Alias name? Coz I think that doesn't work.

You could also try replacing """ from the code with .. & CHAR(34) & ..

[spiderwallet]

Hi Karthik1710,

Yes, the query works perfectly when it can find an element in the dimension that satisfies the filter. Its when it doesn't satisfy the filter is when the query takes forever.
When testing the MDX in the subset it works perfectly and when it cant find an element/s that satisfies the filter it returns no elements.

Ive seen this on earlier versions of TM1 (10.1) but was hoping this wouldn't be an issue on 10.2 FP2.

Cheers!

[spiderwallet]

Workaround:

We resolved this issue by wrapping the expression in a union and joining It to an element that will always be found in the dimension.
As the active form view is zero supressed, it will rarely show rows that match the second expression.

="UNION({TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Staff] )}, 0)},""" & CONCATENATE("*",$M$29, "*") & """)},{[Staff].[X]})""

Thanks all.

SW.