Page 1 of 1

Dynamic listing of elements using MDX

Posted: Wed Feb 21, 2018 5:48 pm
by sreesuku
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 ?

Re: Dynamic listing of elements using MDX

Posted: Wed Feb 21, 2018 6:01 pm
by tm123
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)

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 11:43 am
by sreesuku
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 ?

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 11:51 am
by tomok
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???

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 1:13 pm
by sreesuku
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

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 3:20 pm
by Mark RMBC
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

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 4:26 pm
by tm123
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] = '')})")

Re: Dynamic listing of elements using MDX

Posted: Thu Feb 22, 2018 5:01 pm
by Mark RMBC
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!

Re: Dynamic listing of elements using MDX

Posted: Sat Feb 24, 2018 12:01 pm
by Wim Gielis
Mark RMBC wrote: Thu Feb 22, 2018 5:01 pmI 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 ;-)

Re: Dynamic listing of elements using MDX

Posted: Sat Feb 24, 2018 2:50 pm
by Wim Gielis
Wim Gielis wrote: Sat Feb 24, 2018 12:01 pm
Mark RMBC wrote: Thu Feb 22, 2018 5:01 pmI 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/