KB16231: How to display rank values for specified attribute elements within a report in MicroStrategy Developer

The report in this example will show the revenue for all brands as well as their rank by sales for only five selected brands. Perform the following steps to create this report in the Strategy Tutorial project:
 

• Create a filter named 'Brand filter'. This filter will be used to filter on the first five brands listed in the 'Products' hierarchy.
• Create a metric named 'Rank Revenue'. The rank function will be used with the 'Revenue' metric. The rank function may be found in 'Schema Objects\Functions' and 'Operators\Functions\Rank' and 'Ntile Functions\Rank function'. The definition is Rank ().
• Create a metric named 'Max of 1'. This will force every column in this metric to display a value of '1'. The definition is Max(1). Set the Conditionality to Brand filter. The metric definition is shown below:
   
  

• Create a metric named 'Rank times Max of 1'. This metric will display the quotient of 'Max of 1' and 'Rank Revenue' metrics. The definition is ZeroToNull(( * )). The metric definition is shown below:
   
  

•  
  NOTE: Using the 'ZeroToNull' function is optional. This is used to avoid displaying rows with null values to 0. This can be found in 'Schema Objects\Functions' and 'Operators\Functions\Null/Zero Functions\ZeroToNull' function.
   
  
• Create a report with the following attributes and metrics:
   
  

• Attributes: Brand
• Metrics: 'Revenue' , 'Rank Revenue', 'Max of 1' and 'Rank times Max of 1'
• Change the Metric join type of the Revenue metric to Outer join in the Report Data Options, as shown below:
•  
  The report should appear as shown below.
   
  

•  
  To see rankings for only the attribute elements which were filtered on (the first 5 elements), remove the 'Max of 1' and 'Rank Revenue' metrics as shown below.
   
  

NOTE: Refer to the following Strategy Knowledge Base technical note for related information regarding metrics ranking in Strategy:
 
KB4740 (KB4000-7X0-0289): How to rank a metric in Strategy Desktop 7.x