Filtering a cube based on a Dimension value

[ATD]

Hi All,

I have a cube that has a dimension that contains hundreds of values. Even though these are sorted in a logical order, the list is too long to make it usable. This dimension has two levels - Group Code and Code.

I've, therefore, created a second dimension that holds the Group Code values and created a new test cube with this included.

My intention is that when a user selects a Group Code value from the new dimension (or the "All" element at the top of that), the main Code listing is filtered to just those elements that have the same Group Code value (I've also added Group Code as an Attribute on the dimension as well as it still being part of the hierarchy) - or to display all of them if the user selects the "All" element.

However, I can not see how I can update the original dimension to apply the filter. Presumably, I need to create a subset (either dynamically or statically?) - but how do I tell the cube to use that subset instead of the Default one based on what the user has selected?

Any suggestions welcome,

Andy

[qml]

First and foremost - what user interface(s) do you need this to work in? You would do it differently in a spreadsheet/websheet, differently in Cube Viewer and differently in, say Cognos BI.

[ATD]

Hi,

For now, it will be in the cube viewer.

Thanks

Andy

[qml]

In that case check out this thread.

[ATD]

Hi,

That's the post I've been trying to follow - but with no luck so far

My subset's Expression is:

{FILTER({TM1SubsetAll([CODE_DIM])},[GroupCode]=[CUBE_NAME].([GROUP_CODE_DIM].CurrentMember))}

Where CODE_DIM is the name of the dimension containing the Code values and an Attribute called GroupCode that contains the value I would like to filter on. CUBE_NAME is the name of the cube where this is being tested. That contains the GROUP_CODE_DIM that has the main GroupCode listing. I've set the cube to use that subset but no matter what I select from the GROUP_CODE_DIM dimension, the CODE_DIM dimension doesn't change.

I'm not sure what I'm doing wrong here. My idea is that the CODE_DIM dimension will be filtered to all elements where the GroupCode attribute value matches the GROUP_CODE_DIM element selected by the user in the cube. (I'd also like to add an "OR where selected value is 'All'", but I'll leave that bit until I can get the main bit working!)

Any suggestions about what I may need to change?

Thanks

Andy

[qml]

I don't think you really understood and followed the example in that thread.

The below syntax for a dynamic subset for CODE_DIM should work.

Code: Select all

{FILTER({TM1SubsetAll([CODE_DIM])},[CODE_DIM].[GroupCode]=[GROUP_CODE_DIM].CurrentMember.Name)}

[ATD]

Hi,

I've tried that but the only thing that would work is something like:

Code: Select all

{FILTER({TM1SubsetAll([CODE_DIM])},[CODE_DIM].[GroupCode]='XX')}

Which, of course, defeats the object.

I'll leave it at that point and try to return to this at a later date

Andy

[qml]

I built a test cube and made sure this MDX worked before posting it here verbatim.

One restriction that you need to be aware of (and it is mentioned very clearly in the linked thread) is that your GROUP_CODE_DIM needs to be a title dimension - it cannot be on rows or columns as in that case the CurrentMember clause will have no context.

If you are still having issues with the provided code, please state the exact nature of these issues.

[ATD]

Hi

GROUP_CODE_DIM and CODE_DIM are both Title dimensions. I have SITES_DIM as the Row dimension and DATES_DIM as the Column dimension.

I've created a subset of CODE_DIM with your code and no matter what value I select from GROUP_CODE_DIM, the CODE_DIM list doesn't change - ie, it always shows all of the codes.

Andy

[harrytm1]

Hi,

This thread is really useful! I now know how to create a dynamic subset to use the selected element in a title dim to filter the elements in a row dim.

I need some advice on the following situation. I have three dims: Company, Cost Centre and Currency. In the Cost Centre dim, there is an attribute "Company" to map the Cost Centres to their assigned company. So it is a many CC to one Company relationship.

Using what I learn from this thread, I'm able to create a dynamic subset in Company dim so that the Company dim (sitting as row dim in a cube view) can automatically filter to the correct Company element based on the selected CC in the title dim.

What I hope to achieve additionally is to also create a dynamic subset for Currency dim so that it can also refer to the selected CC in the title dim and filter accordingly. Note that this Currency dim is also positioned as a row dim, just like the Company dim.

However, the CC dim does not have a "Currency" attribute. I tried to create a MDX expression in the Currency subset to indirectly identify the Currency of the CC through the Company attribute in the CC dim since the Company dim has a "Currency" attribute.

Is this possible, or is there a better way of doing this short of creating a "Currency" attribute in the CC dim? Look forward to your advice! Thanks!

[harrytm1]

Hi,

Anyone has any idea how to set up the MDX in a subset to handle the problem mentioned in my post above?

Many thanks!