MDX Filter in Active Form

[missspeedy23]

I have an active form where I want to filter 2 values (where one is equal to 0 and the other is not equal to 0). I need the MDX to return the rows that this relates to but it's not working as I'd expect it to as it is looking at the consolidations for the elements and not the leaf levels for the 2 dimensions that are in the rows that I want to see leaf levels against.

I have attached what I'm trying to achieve.

The MDX code being used in the first row/column is:
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Cost Centre] )}, 0)}, [Staff Monitoring].([Measure].[FTE]) =0 and [Staff Monitoring].([Measure].[Value]) <> 0)}

I'm not sure what/if I need some code in the second column for the second dimension or whether what I want to do is even possible!

[tomok]

Your problem is that your MDX statement is not valid and when the MDX is invalid, TM1 just returns every element in the dimension, including consolidations. If you fix your MDX then it should work.

To the best of my knowledge, there is no AND operator in MDX. This is why it is failing. What you have to do is create a list of elements where FTE is equal to 0 and another where the Value is <> 0 and then do an INTERSECT of the two.

Code: Select all

{INTERSECT(
FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [Cost Centre] )}, 0)}, [Staff Monitoring].([Measure].[FTE]) =0),
FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [Cost Centre] )}, 0)}, [Staff Monitoring].([Measure].[Value]) <>0)
)}

[Wim Gielis]

To the best of my knowledge, there is no AND operator in MDX. This is why it is failing.

No Tom, it works just fine. I have an example on my website but here's a visual example.
Both AND and OR work.

[declanr]

The problem is likely that only 1 dimension is being referenced un the cube filter but the document provided makes it look like there are quite a few more.

[missspeedy23]

The problem is likely that only 1 dimension is being referenced un the cube filter but the document provided makes it look like there are quite a few more.

I think that is the problem but I'm not sure what the code should be for more than one dimension - can you help with that at all please?

[babytiger]

When applying filter in MDX using cube values, and a particular dimension is not included, then that MDX will examine all elements. For example, since Version is not included in the MDX, the MDX will check against all versions (budget/actual/forecast) that meets the criteria.

To include all dimensions in the MDX, you try the following:
1. have mdx statement sit in a cell in the websheet, which links to the values of your SUBNM values, eg.
=“{FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [Cost Centre] )}, 0)}, [Staff Monitoring].([Version].[“&B1&”], [Year].[“&B2&”], [Period].[“&B3&”], [Staff Type].[“&B4&”], [Detail Code].[“&B5&”], [Job Number].[“&B6&”],[Measure].[FTE]) =0}”
2. have your TM1RPTROW formula to refer to this mdx statement.

In this case, any change to your SUBNM values, will automatically come through into your MDX.

As you may have pickup, I have excluded "[Staff Monitoring].([Measure].[Value]) <> 0" part, as I don't think it is necessary. What you have to do to toggle suppress zero on, then should achieve your result.

Hope this helps.

[missspeedy23]

When applying filter in MDX using cube values, and a particular dimension is not included, then that MDX will examine all elements. For example, since Version is not included in the MDX, the MDX will check against all versions (budget/actual/forecast) that meets the criteria.

To include all dimensions in the MDX, you try the following:
1. have mdx statement sit in a cell in the websheet, which links to the values of your SUBNM values, eg.
=“{FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [Cost Centre] )}, 0)}, [Staff Monitoring].([Version].[“&B1&”], [Year].[“&B2&”], [Period].[“&B3&”], [Staff Type].[“&B4&”], [Detail Code].[“&B5&”], [Job Number].[“&B6&”],[Measure].[FTE]) =0}”
2. have your TM1RPTROW formula to refer to this mdx statement.

In this case, any change to your SUBNM values, will automatically come through into your MDX.

As you may have pickup, I have excluded "[Staff Monitoring].([Measure].[Value]) <> 0" part, as I don't think it is necessary. What you have to do to toggle suppress zero on, then should achieve your result.

Hope this helps.

Apologies for the delay in replying. This works OK where the elements are in the context/title area but I have multiple (3) dimensions in the rows which are based on dynamic subsets so I have no idea how to apply the MDX (or if it's even possible!). I've tried to link the MDX to the first row of the active form for the relevant dimensions but it doesn't return anything. Any ideas?