Need help on MDX statements

Post Reply
TM1user_pri
Posts: 9
Joined: Fri Aug 26, 2016 11:27 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Need help on MDX statements

Post by TM1user_pri » Wed Dec 20, 2017 8:15 am

Hi TM1 champions,

Need your guidance here! I believe something is wrong with my MDX query syntax or maybe I am missing something.

Scenario:

Step1: User is selecting Version and Unit on the landing page. We are storing the selected version and Unit against the username in a lookup cube- "z_control_web_portal_by_user".

Step2: We have another Lookup cube which is used to map the countries to unit by flagging version-wise.
Lookup cube: "z_security_country_map_Unit" . Dimensions are-

1. Country
2. Unit
3. Version
4. Flag

Lookup cube sample:

Version1
Unit1 Unit2 Unit3
NL 1 1 0
SE 0 1 0
CN 0 0 1

Step3- We have to filter the "Location" dimension according to the "unit" selected on Landing page.

Dimension- Location (3 level hierarchy, Total Location Unit - > Total Location -> Location) below is the sample structure only showing level1 and 0.

Elements ........................ Country Code (Attribute)
Total NL
Location1 .............. NL
Location2 .............. NL
Total SE
Location3 .............. SE
Location4 .............. SE
Location5 .............. SE
Total CN
Location6 .............. CN
Location7 .............. CN
Location8 .............. CN
Location9 ............. CN

WHAT WE HAVE APPLIED:
We have written MDX on Location subset but something is not correct. It is showing STRTOMEMBER error expression or no results sometimes.

{Filter( {[}ElementAttributes_Location].[Country Code]},

[z_security_country_map_Unit].(
StrToMember( "[Versions].["+
[z_control_web_portal_by_user].( StrToMember("[}Clients].[" + UserName + "]" ),
[z_control_web_portal_by_user_measures].[Version Selected])+"]"),
StrToMember( "[Unit].["+
[z_control_web_portal_by_user].( StrToMember("[}Clients].[" + UserName + "]" )
,[z_control_web_portal_by_user_measures].[Unit Selected])+"]"),
StrToMember( "[Country].["+
[}ElementAttributes_Location].(StrToMember( [}ElementAttributes_Location].[Country Code]))+"]"),
[z_security_country_map_Unit_measures].[Flag] )>0 )}


ACTUAL RESULT SHOULD BE:

Now, If User selects Version1 and Unit2 on landing page, Location subset should show the below result set-

Total NL
Location1
Location2
Total SE
Location3
Location4
Location5

Please try to replicate this scenario and provide your expert advice here. Thanks.

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

Re: Need help on MDX statements

Post by tm123 » Thu Dec 21, 2017 3:45 pm

You are applying your filter in the ElementAttributes cube (Your Base MDX is {[}ElementAttributes_Location].[Country Code]} ). I don't think this makes sense

Can you provide the MDX expression with Hardcoded Version and Unit, and then we can help you modify it so it uses the Version and Unit selected in Landing Page?

You can use the Expression Editor to generate an MDX.

The following MDX is an Example you can use and adjust to your needs

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Your_Location_Dimension] )}, 0)}, [Your_Mapping_Cube].(
StrToMember("[Your_Version_Dim].["+User_Params_Cube.(StrToMember("[}Clients].["+USERNAME+"]"),[User_Params_Measures].[Stored Version])+"]"),
StrToMember("[Your_Unit_Dim].["+User_Params_Cube.(StrToMember("[}Clients].["+USERNAME+"]"),[User_Params_Measures].[Stored_Unit])+"]"),
[Your_Mapping_Cube_Measures].[Mapping_Flag]) = 1 )}

Post Reply