MDX query issue

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

MDX query issue

Post by TM1user_pri » Fri Dec 15, 2017 3:17 pm

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.

Post Reply