Active Form MDX Filter Query

[MSidat]

Hi All,

Currently banging my head against a brick wall trying to give the business a filtering mechanism in a Active Form.

I have a 3 Dimensional Cube:

Sales Channel: A, B, C
Product Prod1, prod2, Prod3...
Measures, DSR, SR, CV

Dependant on the drop down value that the user selects on the Active Form an appropriate MDX Expression is used for the Active Form. What I am struggling with at the moment is getting a certain MDX expression working.

For Example. The below expression will give me all Products which have a DSR Value of 10 or more in the cube. As the Sales Channel is not defined is seems to total the sales channel up and only return products with a total DSR value of 10 or mroe against the sum of all Channels.

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},[MyCube].([Measures].[DSR]) >= 10 )}

What I actually need to show is all Products with a DSR value of 10 or more against any channel. I know I can have multiple statements hard coding the channels into them and then having a union and a distinct wrapper but is there a better way of doing it?

[lotsaram]

I think for what you want to work you need to bring channel as a row dimension.

[tomok]

Create an MDX query for the Sales Channel, just like the one you have for the Products, and then put them together using INTERSECT:

{
INTERSECT(
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [MyCube].([Measuress].[DSR]) > 10 ) ,
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Sales Channel] )}, 0)}, [MyCube].([Measures].[DSR]) > 10 )
)
}

[MSidat]

Thanks Tomok:

Here is what I tried:

{INTERSECT({FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)},
[Stock Commitment Cube].([Stock_Commitment_Version].[Current],[Stock_Commitment_Measure].[Stock Remaining]) >= 500)},
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Sales_Channels_Dim] )}, 0)},
[Stock Commitment Cube].([Stock_Commitment_Version].[Current],[Stock_Commitment_Measure].[Stock Remaining]) >= 500)}
)}

This was created in the Product_Dim Dimension. I got an error message saying "INTERSECT: Sets have incompatible dimensionality".

I can definitely see your logic but can understand the error message as the second statement is retrieving sales channels back and not products. Is there possibly a way to use the GENERATE function to pass in each channel into the following:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)},
[Stock Commitment Cube].([Stock_Commitment_Version].[Current],[Stock_Commitment_Measure].[Stock Remaining]) >= 500)}

to get what i need.

Thanks

[tomok]

This was created in the Product_Dim Dimension. I got an error message saying "INTERSECT: Sets have incompatible dimensionality".

Well, you said this was related to an Active Form so I thought you were using this in the report itself, not creating a dynamic subset in the dimension. You can't save that code in the Product dimension because it references Sales Channel. Suggest you try lotsaram's suggestion.

[MSidat]

Hi Tomok,

I am indeed using the MDX Expression as my last argument in the TM1RPTROW formula, but though I will try it in the subset first. Nevertheless it does not appear to work in the Formula as well, brings back no data.

Appreciate your comments on how this could be applied on the Active Form.

Understand the suggestion from Lotsaram but the data does not really lend it self to being in that format.

[rozef]

Hi Sidat,

I'm afraid you can't achieve your subset like that.
If you skip a dimension in a cube reference, the MDX query take the first element of this dimension.

But you can easely do it with an extra cube and some rules.
A copy of your cube with some rule like

Code: Select all

[Stock Remaining]=IF( DB( 'Stock Commitment Cube' , ... 'Stock Remaining' ) >= 500 , 1 , 0 )

Then your filter can stay on sum of channels in this extra cube.

[MSidat]

Thanks for the replies.

I managed to get a suitable MDX Query working by using the following Generate Statement:

{Generate(
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Sales_Channels_Dim])}, 0)},
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)},
[Stock Commitment Cube].([Stock_Commitment_Version].[Current],[Stock_Commitment_Measure].[Stock Remaining]) >= 500)}
)}

In effect the Generate Statement is running the following MDX:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product_Dim] )}, 0)},
[Stock Commitment Cube].([Stock_Commitment_Version].[Current],[Stock_Commitment_Measure].[Stock Remaining]) >= 500)}

Against each nodal element of the Sales_Channel_Dim. Furthermore I have wrapped the whole statement in a Distinct and TM1Sort and as its not a massive cube the results are back within a second or 2.

This Statement on the active form combined with drop down selections for the user to select various filtering measures is allowing a dynamic filtering ability in TM1Web where Excel Auto Filters do not work.

[vins]

Hello,

I have similar scenario and i am using above MDX query mentioned by you in active form in the MDX subset section. It is not giving me any data.


Thanks