Dynamic listing of elements using MDX

Post Reply
sreesuku
Posts: 35
Joined: Wed Sep 14, 2011 7:45 am
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2003

Dynamic listing of elements using MDX

Post by sreesuku » Wed Feb 21, 2018 5:48 pm

Hi All,

I have a cube ABC with Emp,Dept,Measure dimensions. My requirement is to dynamically list out Employee Numbers from Emp dimension based on some Emp attribute filter and also based on a specific Dept number in Dept dimension in an Active from. Design is to have Dept number as Drop down in the form.

Hierarchies
Emp -- Locations --> Emp numbers
Dept -- Dep Numbers

I am trying to do this without creating a TI process, with MDX and TM1RPTROW function.

I am kind of new to MDX. Can I write MDX to create an Emp Number subset which can filter out Emp Numbers based on Emp Attribute filter and also based on a specific Dept ? If yes, what would be the code ?
And assigning this subset MDX to the TM1RPTROW can help me to list of Emp numbers ?

tm123
Posts: 113
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Dynamic listing of elements using MDX

Post by tm123 » Wed Feb 21, 2018 6:01 pm

You can include both dimensions (Department and Employee) - 2 nested TM1RPTRow columns, and build MDX statements for both Dimensions to be used in the TM1RPTRow formulas.

MDX for Employee:

="{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [employee] )}, 0)}, [employee].[attrib1] = '" & attrib1_value & "' AND [employee].[attrib2] = '" & attrib2_value & "' )}

MDX for Department
="{[Department].[" & SelectedDepartment & "]}"

The other option is to use one single Dimension (EMployee dim ) in your Active Form, but in the MDX Expression to combine the Filter by Attributes and Filter by Cube Cell Calues (you should use one of the measures maybe Employee FTE or Employee Salary)

sreesuku
Posts: 35
Joined: Wed Sep 14, 2011 7:45 am
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2003

Re: Dynamic listing of elements using MDX

Post by sreesuku » Thu Feb 22, 2018 11:43 am

I have written MDX to create a subset based on the attribute filters. I have tested the same in a TI process using SubsetcreatebyMDX.

Sample MDX is given below
"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)},(' | '[ CubeABC].([Dept].[DeptXYZ],[Measure_CubeABC].[BU]) = 'PQR') ' | ' AND [Emp].[Status] <> 'yes' ' | ')}"

Now to list the Emp numbers dynamically in Active form, I just need to edit the existing TM1RPTRow formula to substitute the above MDX query in the MDX Argument section right ? It doesn't seems to be working ?

Any other changes to be done ?

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 listing of elements using MDX

Post by tomok » Thu Feb 22, 2018 11:51 am

sreesuku wrote:
Thu Feb 22, 2018 11:43 am
Any other changes to be done ?
Excel doesn't concatenate strings using the pipe symbol (|). Change your MDX code to not concatenate those strings together. For that matter, I don't understand why you can't make the MDX just one long string anyway???
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

sreesuku
Posts: 35
Joined: Wed Sep 14, 2011 7:45 am
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2003

Re: Dynamic listing of elements using MDX

Post by sreesuku » Thu Feb 22, 2018 1:13 pm

tomok wrote:
Thu Feb 22, 2018 11:51 am
sreesuku wrote:
Thu Feb 22, 2018 11:43 am
Any other changes to be done ?
Excel doesn't concatenate strings using the pipe symbol (|). Change your MDX code to not concatenate those strings together. For that matter, I don't understand why you can't make the MDX just one long string anyway???
Sorry thats my bad. I had written a code in TI with pipe for better understanding and then copy pasted the same excel. I have changed it. However its still not working.

I have replaced the MDX expression with a much simpler one as below to test. 'Delete' is an emp attribute
'{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = "")}';

and my TM1RPTROW function will be
TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = "")})")

TestView is something which I created and it was converted to active form initially. When I change the TM1RPTRow with the above MDX I get resultant value as blank. However I have checked the subset which got created using the above MDX expression using a TI process, its having correct values. I have uploaded the sheet in Tm1 Web and checked, there also it not showing

Mark RMBC
Regular Participant
Posts: 158
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dynamic listing of elements using MDX

Post by Mark RMBC » Thu Feb 22, 2018 3:20 pm

Hi,

Have you tried to reference a cell in the mdx part of the TM1RPTROW rather than write the mdx in the actual TM1RPTROW?

So TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0, $A$10)

If you do want to write the mdx within the TM1RPTROW formula then I think you will need to wrap extra double quotes round the double quotes in the mdx statement, so i think you could try:

TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] =" &""""""&")})")

cheers, Mark

tm123
Posts: 113
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Dynamic listing of elements using MDX

Post by tm123 » Thu Feb 22, 2018 4:26 pm

sreesuku wrote:
Thu Feb 22, 2018 1:13 pm
tomok wrote:
Thu Feb 22, 2018 11:51 am
sreesuku wrote:
Thu Feb 22, 2018 11:43 am
Any other changes to be done ?
Excel doesn't concatenate strings using the pipe symbol (|). Change your MDX code to not concatenate those strings together. For that matter, I don't understand why you can't make the MDX just one long string anyway???
Sorry thats my bad. I had written a code in TI with pipe for better understanding and then copy pasted the same excel. I have changed it. However its still not working.

I have replaced the MDX expression with a much simpler one as below to test. 'Delete' is an emp attribute
'{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = "")}';

and my TM1RPTROW function will be
TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = "")})")

TestView is something which I created and it was converted to active form initially. When I change the TM1RPTRow with the above MDX I get resultant value as blank. However I have checked the subset which got created using the above MDX expression using a TI process, its having correct values. I have uploaded the sheet in Tm1 Web and checked, there also it not showing
Can you try to replace your double quotes with single quotes?

TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = '')})")

Mark RMBC
Regular Participant
Posts: 158
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dynamic listing of elements using MDX

Post by Mark RMBC » Thu Feb 22, 2018 5:01 pm

Can you try to replace your double quotes with single quotes?
I always forget you can use the single quotes in the excel sheets (force of habit is my excuse!)! A far more elegant solution!

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

Re: Dynamic listing of elements using MDX

Post by Wim Gielis » Sat Feb 24, 2018 12:01 pm

Mark RMBC wrote:
Thu Feb 22, 2018 5:01 pm
I always forget you can use the single quotes in the excel sheets (force of habit is my excuse!)! A far more elegant solution!
Better to forget that one, than using =+ at the start of Every ! God ! damn ! formula ;-)
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: 1785
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic listing of elements using MDX

Post by Wim Gielis » Sat Feb 24, 2018 2:50 pm

Wim Gielis wrote:
Sat Feb 24, 2018 12:01 pm
Mark RMBC wrote:
Thu Feb 22, 2018 5:01 pm
I always forget you can use the single quotes in the excel sheets (force of habit is my excuse!)! A far more elegant solution!
Better to forget that one, than using =+ at the start of Every ! God ! damn ! formula ;-)
What a coincidence, MrExcel Bill Jelen published this article just today :-)
https://www.mrexcel.com/excel-tips/i-st ... -formulas/
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

Post Reply