Dynamically MDX for PAW view

Post Reply
Palczan
Posts: 23
Joined: Tue Apr 14, 2020 8:57 am
OLAP Product: TM1 Cognos
Version: 11.3.0.27
Excel Version: Office 365 MSO 64bit

Dynamically MDX for PAW view

Post by Palczan » Tue Oct 12, 2021 8:10 am

Hello Everyone,

today I have problem with view mdx. I have a 5 Cubes like 001_Cube,002_Cube,003_Cube and every cubes has the same number of dimensions. The differences on structure is between name of first dimension, because the every first dimension has the number prefix like 001_Dim, 002_Dim.
The first view is creating for below statement

SELECT NON EMPTY {[Dim_2].[Dim_2].[Export Date]} ON 0, NON EMPTY {[001_Dim].[001_Dim].[Total]} ON 1 FROM [001_Cube]

Next to I'm using the information about choosing cube on admin Cube and the view mdx looks like:

SELECT {[Meas].[Meas].[Chosen Cube]} ON 0, {STRTOMEMBER("[}Clients].[" + USERNAME + "]")} ON 1 FROM [Admin_Cube]

The main problem is between connection on this two mdx because I need to change first mdx that when i change chosen cube the view automatically changes the cube and first dim .

I'm trying to connect this statement like this

SELECT NON EMPTY {[Dim_2].[Dim_2].[Export Date]} ON 0, NON EMPTY {[SELECT {[Meas].[Meas].[Chosen Dim]} ON 0, {STRTOMEMBER("[}Clients].[" + USERNAME + "]")} ON 1 FROM [Admin_Cube].[SELECT {[Meas].[Meas].[Chosen Cube]} ON 0, {STRTOMEMBER("[}Clients].[" + USERNAME + "]")} ON 1 FROM [Admin_Cube].[Total]} ON 1 FROM SELECT {[Meas].[Meas].[Chosen Cube]} ON 0, {STRTOMEMBER("[}Clients].[" + USERNAME + "]")} ON 1 FROM [Admin_Cube]

But this statement is uncorrect.

Can you tell me how to connect this two information and create the dynamic view in PAW?

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Dynamically MDX for PAW view

Post by Steve Rowe » Tue Oct 12, 2021 11:42 am

I think what you are saying is that you want to have an MDX view that dynamically changes depending on what the user selects via dropdowns in a reference cube.

This MDX expression is then going to be used to present an MDX view in PAW.

Doing this dimension elements shouldn't be an issue.

I think you may struggle as in theory you could make the cube name dynamic but (I assume) that once you save the view object to the server the view will be attached to a specific cube. It may work anyway I guess, worth a try.

I've posted my approach to this here.

If what you are trying to do is possible it may help you resolve syntax issues
Technical Director
www.infocat.co.uk

Post Reply