Create dynamic subset with another dimension subset

Post Reply
conray
Posts: 41
Joined: Thu Jul 07, 2011 7:50 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2010

Create dynamic subset with another dimension subset

Post by conray »

Hello everyone,

i am a fairly new developer in using Cognos TM1.

I would like to know if it is possible to create a dynamic subset for a dimension, based on another selected dimension.

For example:
Dimension A [Year]
2011
2012
2013

DImension B [Period]
201104
201105
201204
201205

In the cube viewer, when i choose [Year] as the title, i would like the following to be displayed:
[Year]: 2011
[Period]
|201104 |201105
Rows 0 0

[Year]: 2012
[Period]
|201204 |201205
Rows 0 0

I am not sure if this is the correct way to do it (using mdx expressions?), so please point me to any references if you have any.

Thanks,
ray
Server OS: Windows Server 2008 R2 (64-bit)
TM1: v9.5.2 FP1
MS Office: Microsoft Excel 2003, 2010
lotsaram
MVP
Posts: 3667
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Create dynamic subset with another dimension subset

Post by lotsaram »

You obviously didn't try searching too hard, this was discussed about a week back ....
http://www.tm1forum.com/viewtopic.php?f=3&t=5584#p23794
conray
Posts: 41
Joined: Thu Jul 07, 2011 7:50 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2010

Re: Create dynamic subset with another dimension subset

Post by conray »

i did see that topic before i posted but i am still unsure how does the expression actually works.

Say for example, i have 2 dimension, 1 is Product and 1 is Relation.
In Product dimension, i have:
P123
P456

in Relation dimension, i have:
P123 | A
P123 | B
P123 | C
P456 | D
P456 | E
P456 | F

So suppose i have selected P123 in the product dimension, i should only be able to see P123 | A, P123 | B and P123 | C.

Now, the expression should be written in the Relation dimension?
i should have something like {FILTER({TM1SubsetAll([Relation])},[Relation]..... ( i am not sure how the syntax should be like)
Server OS: Windows Server 2008 R2 (64-bit)
TM1: v9.5.2 FP1
MS Office: Microsoft Excel 2003, 2010
tomok
MVP
Posts: 2832
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: Create dynamic subset with another dimension subset

Post by tomok »

You can't do what you are asking. The problem is how is the Relation dimension supposed to know what you have "selected" in the Product dimension? To write an MDX statement to filter the Relation dimension in the way you want you would have to store your Product dimension selection in a cube and then reference that in the MDX. You can create a cube that utilizes the }Clients dimenson so you cand store the Product selection but you would have to deal with creating the DBSW fornula in your input template or report to write the selected product in the selection cube before the MDX will return the list from the Relation dimension. If you are using an active form report you can also assemble the MDX in the report itself and reference it in the TM1RPTROW formula. Either will work but the point is you have to be referencing a specific Product before the subset in the Relation dimension will evaluate properly.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Create dynamic subset with another dimension subset

Post by jstrygner »

conray wrote:i did see that topic before i posted but i am still unsure how does the expression actually works.
Found some time to try to achieve what (I think) you need. The solution that worked for me is very similar to the case lotsaram gave you a link to, just requires a small trick with attributes you don't have but can easily create.

Here is the step by step instruction and below you have corresponding picture:
1. Create ProductCode text attribute for both dimensions and fill it with values like on the picture. I did it manually, but you can also do it e.g. via rules (if the algorithm is as simple as in your example).
2. Create a subset for the Relation dimension ({FILTER( {TM1SubsetAll( [Relation] )}, [Relation].[ProductCode] = [Product].[ProductCode])}).
3. Create a cube and attach the created in step 2. Relation subset either to rows or columns while your Product remains as title dimension. Switch between elements in Product dimension and recalculate to see the result.

HTH
Attachments
SubsetOnSelection.JPG
SubsetOnSelection.JPG (162.77 KiB) Viewed 14967 times
conray
Posts: 41
Joined: Thu Jul 07, 2011 7:50 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2010

Re: Create dynamic subset with another dimension subset

Post by conray »

Hi jstrygner,

That is exactly what i required!

Thanks so much for the advice.
Server OS: Windows Server 2008 R2 (64-bit)
TM1: v9.5.2 FP1
MS Office: Microsoft Excel 2003, 2010
Pulpa Chamoy
Posts: 1
Joined: Tue Apr 08, 2014 6:57 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 14.0.7

Re: Create dynamic subset with another dimension subset

Post by Pulpa Chamoy »

Hello,
Your example works perfect, but when I add a consolidated element to dimension product is not selecting anything.
I tried filtering level 0 first with no luck. Can you please help?

Thank you.
dharav
Regular Participant
Posts: 193
Joined: Wed Apr 02, 2014 6:43 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Create dynamic subset with another dimension subset

Post by dharav »

Pulpa Chamoy wrote:Hello,
Your example works perfect, but when I add a consolidated element to dimension product is not selecting anything.
I tried filtering level 0 first with no luck. Can you please help?

Thank you.
Did You resolve the issue? I would like to know how?

I achieved same using MDX Statement where i utilised comparision of 2 dimension attributes and than drilldown the member.
Post Reply