KB45111: How to filter metric values according to user login while other metrics keep available to all users in MicroStrategy Developer 9.x

 
In some business scenarios, it is required to filter metric values according to user login while other metrics keep available to all users. For example, values of metric  are available to all users, but User1 and User2 can only see one of the Regions, as shown below:

• User0 can see all of the data:
  

• User1 is only allowed to view  in Region1, but can view all values of :
  

• User2 is only allowed to view  in Region2, but can view all values of :
  

 
Follow the steps below to achieve the requirement:

• Structure and values of FACT_TABLE in Data Warehouse:
  

• List of users in User Manager:
  

• Create a new lookup table LU_USER in Data Warehouse:
  

• Create a relationship table REL_USER_REGION to map available regions to users, as shown below:
  

• Import the tables into the project in Strategy Developer.
• Create a logical table FACT_JOIN_REL to join fact table and relationship table together, as shown below:
  

• Sample Code/Error
  
  select t2.user_id, t1.region_id, t1.profit
  from FACT_TABLE t1
  join REL_USER_REGION t2
  on (t1.region_id = t2.region_id)
  
  
   
  
• Update Schema.
• Create attributes , , and facts , accordingly.
• Update Schema.
• Create a filter to get current user using system prompt 'User Login', as shown below:
  

• Create metric based on fact without any condition.
• Create metric based on fact with the filter in metric condition, as shown below:
  

Login as different users, and the results are as expected.
 
 
NOTE 1:
In order to display null values in the report, Outer Joins for attributes and metrics need to be enabled. Refer to following technical notes to enable outer joins:
KB3838: How to use an outer join to retrieve data when a metric on the report has a condition that is mutually exclusive with the report filter in Strategy 9.x
KB10969: How to set the default metric Join Type to Outer Join in Strategy 9.x?
KB3904: How to perform an outer join against a lookup table in Strategy 9.x
 
NOTE 2:
As user Administrator is not in the relationship table, report will not display any value for metric when Administrator is logged in, as shown below:

 
 
 
414384