Dynamic listing of elements using MDX
-
- Posts: 47
- Joined: Wed Sep 14, 2011 7:45 am
- OLAP Product: Cognos TM1
- Version: PAW
- Excel Version: 2013
Dynamic listing of elements using MDX
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 ?
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 ?
-
- Posts: 132
- 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
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)
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)
-
- Posts: 47
- Joined: Wed Sep 14, 2011 7:45 am
- OLAP Product: Cognos TM1
- Version: PAW
- Excel Version: 2013
Re: Dynamic listing of elements using MDX
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 ?
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 ?
-
- 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: Dynamic listing of elements using MDX
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???
-
- Posts: 47
- Joined: Wed Sep 14, 2011 7:45 am
- OLAP Product: Cognos TM1
- Version: PAW
- Excel Version: 2013
Re: Dynamic listing of elements using MDX
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
-
- Community Contributor
- Posts: 292
- 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
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
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
-
- Posts: 132
- 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
Can you try to replace your double quotes with single quotes?sreesuku wrote: ↑Thu Feb 22, 2018 1:13 pmSorry 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
TM1RPTROW(TM1_Server:TM1Cube:TestView,"TM1_Server:Emp","",,"",0,"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Emp] )}, 0)}, [Emp].[Delete] = '')})")
-
- Community Contributor
- Posts: 292
- 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
I always forget you can use the single quotes in the excel sheets (force of habit is my excuse!)! A far more elegant solution!Can you try to replace your double quotes with single quotes?
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Dynamic listing of elements using MDX
Better to forget that one, than using =+ at the start of Every ! God ! damn ! formula
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Dynamic listing of elements using MDX
What a coincidence, MrExcel Bill Jelen published this article just todayWim Gielis wrote: ↑Sat Feb 24, 2018 12:01 pmBetter to forget that one, than using =+ at the start of Every ! God ! damn ! formula
https://www.mrexcel.com/excel-tips/i-st ... -formulas/
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly