Making a Cascaded Filter(Filter depends on another filter) in TM1

Post Reply
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by Hector2005 »

Hello there,

Please can anyone support me in this case. I searched many but empty hands.

Consider the following cube with 6 dimensions (4 are filters in the context area). Two of them are Department and areas.

If I select "IT" for example I want the next filter to load some ares, When I select "HR" I want the next filter to load another ares, and so on.

Any try sure you will be welcomed.


Thanks a lot,
Best Regards,
Hector.
Attachments
Please consider this photo
Please consider this photo
Capture.JPG (24.08 KiB) Viewed 4883 times
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by tm123 »

Are you trying to achieve this in a CubeView or in a Websheet?

You need to have some attribute (or to build a cube that holds your dimensions relationships).

Then build MDX Statement to filter only Areas of your Selected Department. You can store the Selected Department in a cube with a DBSS. (I usually create a UserSelections cube with }Clients dimension and a simple flat measures dimension, and then in websheets I store the User's selections in that cube), or you can try to use [Department].CurrentMember.Name, but this will not always work
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by Hector2005 »

tm123 wrote: Wed May 30, 2018 5:15 pm Are you trying to achieve this in a CubeView or in a Websheet?

You need to have some attribute (or to build a cube that holds your dimensions relationships).

Then build MDX Statement to filter only Areas of your Selected Department. You can store the Selected Department in a cube with a DBSS. (I usually create a UserSelections cube with }Clients dimension and a simple flat measures dimension, and then in websheets I store the User's selections in that cube), or you can try to use [Department].CurrentMember.Name, but this will not always work
Hello expert,
I want to do this in cube view.. Is your method applicable in cube viewer or I still need a websheet??

Another question: Is there a method through it I can know the selected values of the filters by the user in a specific cube??

Please I'm new in this track if you can clarify your info more a bit.. Sure I will be grateful.

Thanks sir,
Appreciated.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by tm123 »

Hector2005 wrote: Wed May 30, 2018 7:23 pm
tm123 wrote: Wed May 30, 2018 5:15 pm Are you trying to achieve this in a CubeView or in a Websheet?

You need to have some attribute (or to build a cube that holds your dimensions relationships).

Then build MDX Statement to filter only Areas of your Selected Department. You can store the Selected Department in a cube with a DBSS. (I usually create a UserSelections cube with }Clients dimension and a simple flat measures dimension, and then in websheets I store the User's selections in that cube), or you can try to use [Department].CurrentMember.Name, but this will not always work
Hello expert,
I want to do this in cube view.. Is your method applicable in cube viewer or I still need a websheet??

Another question: Is there a method through it I can know the selected values of the filters by the user in a specific cube??

Please I'm new in this track if you can clarify your info more a bit.. Sure I will be grateful.

Thanks sir,
Appreciated.
In CubeView you don't have too many options. You can create an MDX -based subset in your Areas dimension. For example:
{FILTER({TM1SubsetAll([Dim_Area])},[Dim_Area].[Department]=[Dim_Department].CurrentMember.Name)}

But I think this works only if dim_Area is a Row DImensions (it does not work when both are Title Dimensions).

Regarding your other question, there is no method, but as I mentioned, you will store the Selected Members from the SUBNMs you want to a 2-dimensional cube, one of them being }Clients dim, so then you have everything you need
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by Mark RMBC »

I guess another possible option (depending on circumstances) would be to have one dimension for Department and areas where Department is the parent and Area is the child.

Then create a subset for each department.

cheers, Mark
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Making a Cascaded Filter(Filter depends on another filter) in TM1

Post by Hector2005 »

Mark RMBC wrote: Thu May 31, 2018 1:47 pm I guess another possible option (depending on circumstances) would be to have one dimension for Department and areas where Department is the parent and Area is the child.

Then create a subset for each department.

cheers, Mark
Thanks a lot man, But this will not Solve my problem because I have many elements each of them have a lot of elements this will be sophisticated.
Post Reply