Dynamic Filters in Cube Viewer

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

Dynamic Filters in Cube Viewer

Post by Hector2005 » Fri Mar 02, 2018 2:52 pm

Dear my friends, Really appreciated from your aid.

Sure I have a question :D :D , I use Planning Analytics 2.0 on a Windows OS.

Recently I have a problem Suppose I have dimension contains categories just (Consolidated elements), These consolidated elements will contains childs, These childs will be entered at run time through a TI process BUT they related to a another specific elements.

Suppose Dimension called Projects_Categories has the following:
+Development
+Exploration
+Design
+Digging


Suppose Dimensions called factory contains our factories names has the following:
Zalaia
Verich
Zefta


At run time I will add new projects specific to Locations under Projects_Categories. So I will insert new projects:
Zalaia --> Development --> P1
Zefta --> Development --> P2
Verich --> Design --> P3
Verich --> Exploration --> P4
Zalaia --> Exploration --> P5


When selecting Zalaia from factory filter, Cube Viewer should present the following:
-Development
P1
-Exploration
P5
+Design
+Digging

When Selecting Verich from factory filter, Cube Viewer should present the following:
+Development
-Exploration
P4
-Design
P3
+Digging

And So on...

I'm using Attribute to maintain the relationship between factory and Projects.

My Question is How can I make this view I searched and I found that we can use MDX in subset expression, I tried to do this

Code: Select all

{FILTER({TM1SubsetAll([Projects_Categories])},  [Projects_Categories].[Code] = [factory].[Code]}
Problems with the above code:
1- I can't show Parents such as Development with childs such as P1 in a right way,
I see P1
-Developemt
P1

2- When I need to rolling up or drilling down subset changes automatically.


Please I want Help..

Thanks a lot,
Best Regards,
Hector.

Wim Gielis
MVP
Posts: 1781
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Filters in Cube Viewer

Post by Wim Gielis » Sat Mar 03, 2018 10:09 pm

I would use:

Code: Select all

{Hierarchize( Union( Filter( TM1FilterByLevel( TM1SubsetAll([Projects_Categories]), 1), [Projects_Categories].CurrentMember.Parent.Name = "" ),
FILTER({TM1SubsetAll([Projects_Categories])},  [Projects_Categories].[Code] = [factory].[Code]) ) )}
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1781
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Filters in Cube Viewer

Post by Wim Gielis » Sat Mar 03, 2018 10:11 pm

The code attribute should only be filled in at level 0.
Attached a picture.
Attachments
00.png
00.png (31.26 KiB) Viewed 1454 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

Re: Dynamic Filters in Cube Viewer

Post by Hector2005 » Mon Mar 05, 2018 11:03 am

Wim Gielis wrote:
Sat Mar 03, 2018 10:11 pm
The code attribute should only be filled in at level 0.
Attached a picture.
Ohhhh my god, It's worked successfully. Actually I don't find any words present my feelings. Really Appreciated Sir ..

I have three questions:

1- Why when I drill down in Project_levels, Architect changes its subset to another subset ? If there is a solution I will be more more grateful.

2- Could you please give me your business mail, Really I want to know MVP like you? :)

3- Could you please give me a powerful paper at the same time it is simple enough to study and learn MDX queries?

thanks a lot,
My Best Regards.
Hector.

Wim Gielis
MVP
Posts: 1781
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Filters in Cube Viewer

Post by Wim Gielis » Mon Mar 05, 2018 11:35 am

Hector2005 wrote:
Mon Mar 05, 2018 11:03 am
Ohhhh my god, It's worked successfully. Actually I don't find any words present my feelings. Really Appreciated Sir ..

I have three questions:

1- Why when I drill down in Project_levels, Architect changes its subset to another subset ? If there is a solution I will be more more grateful.

2- Could you please give me your business mail, Really I want to know MVP like you? :)

3- Could you please give me a powerful paper at the same time it is simple enough to study and learn MDX queries?

thanks a lot,
My Best Regards.
Hector.
You are welcome.

1 - this is by design, no solution possible
2 - an autograph is for free, the material on my website too. For anything else you'll have to pay :-)
3 - http://www.wimgielis.com/tm1_mdxstatements_EN.htm

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

Re: Dynamic Filters in Cube Viewer

Post by Hector2005 » Mon Mar 05, 2018 11:48 am

Wim Gielis wrote:
Mon Mar 05, 2018 11:35 am
Hector2005 wrote:
Mon Mar 05, 2018 11:03 am
Ohhhh my god, It's worked successfully. Actually I don't find any words present my feelings. Really Appreciated Sir ..

I have three questions:

1- Why when I drill down in Project_levels, Architect changes its subset to another subset ? If there is a solution I will be more more grateful.

2- Could you please give me your business mail, Really I want to know MVP like you? :)

3- Could you please give me a powerful paper at the same time it is simple enough to study and learn MDX queries?

thanks a lot,
My Best Regards.
Hector.
You are welcome.

1 - this is by design, no solution possible
2 - an autograph is for free, the material on my website too. For anything else you'll have to pay :-)
3 - http://www.wimgielis.com/tm1_mdxstatements_EN.htm

Wim
Appreciated Sir, I will see it seems interested.
Please I have a question: Sorry I know that I did many but I still junior.

I need to add parent to all project levels and its appear with each factory too Ex: Zalaia factory.

-Total Projects
-Development
P1
-Exploration
P5
+Design
+Digging

thanks a lot,
Appreciated Sir.

Wim Gielis
MVP
Posts: 1781
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Filters in Cube Viewer

Post by Wim Gielis » Mon Mar 05, 2018 12:24 pm

Then the first part of the MDX needs to be changed.
Use DrillDownLevel, an example is listed on my website on that page.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

Re: Dynamic Filters in Cube Viewer

Post by Hector2005 » Mon Mar 05, 2018 1:10 pm

Wim Gielis wrote:
Mon Mar 05, 2018 12:24 pm
Then the first part of the MDX needs to be changed.
Use DrillDownLevel, an example is listed on my website on that page.
Thanks Sir, But It seems that I want to read all instructions to solve that, If you can help me speed up this by solving it. I will be appreciated.

Sorry Sir, I know that this makes you nervous. :(

User avatar
tomok
MVP
Posts: 2474
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Dynamic Filters in Cube Viewer

Post by tomok » Mon Mar 05, 2018 1:11 pm

Hector2005 wrote:
Mon Mar 05, 2018 11:03 am
1- Why when I drill down in Project_levels, Architect changes its subset to another subset ?
In the subset editor, when you click on the plus or minus sign to drill up or down, TM1 accesses the entire dimension to determine what the resulting set should be after the drill down or collapse, not just what was in the subset when you started. This question gets asked usually once a month. Unfortunately, that's just the way it works. To accomplish what you want you would need to change the actual dimension via custom hierarchies.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 1781
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Filters in Cube Viewer

Post by Wim Gielis » Mon Mar 05, 2018 8:41 pm

Hector2005 wrote:
Mon Mar 05, 2018 1:10 pm
Sorry Sir, I know that this makes you nervous. :(
Correct.

This is a matter of copy/paste, I already directed you to the solution on a web page with the expression that you need to use.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

Re: Dynamic Filters in Cube Viewer

Post by Hector2005 » Wed Mar 07, 2018 8:17 am

tomok wrote:
Mon Mar 05, 2018 1:11 pm
Hector2005 wrote:
Mon Mar 05, 2018 11:03 am
1- Why when I drill down in Project_levels, Architect changes its subset to another subset ?
In the subset editor, when you click on the plus or minus sign to drill up or down, TM1 accesses the entire dimension to determine what the resulting set should be after the drill down or collapse, not just what was in the subset when you started. This question gets asked usually once a month. Unfortunately, that's just the way it works. To accomplish what you want you would need to change the actual dimension via custom hierarchies.
Thanks a lot, Appreciated sir for this info.

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

Re: Dynamic Filters in Cube Viewer

Post by Hector2005 » Wed Mar 07, 2018 8:19 am

Wim Gielis wrote:
Mon Mar 05, 2018 8:41 pm
Hector2005 wrote:
Mon Mar 05, 2018 1:10 pm
Sorry Sir, I know that this makes you nervous. :(
Correct.

This is a matter of copy/paste, I already directed you to the solution on a web page with the expression that you need to use.
any way I want to thank you sir, you are right, I got it.

Post Reply