KB218766: How to create a report which shows the top 3 Suppliers offering the best price per Item in the Tutorial Project in MicroStrategy Developer 9.x?

The following report shows the top 3 Suppliers which offer the best price per Item:

 
The following example shows how to create the report using the Tutorial Project:

• In the Tutorial project, the relationship between Supplier and Item attribute is one to many. Create the data for using another Item attribute which has a many to many relationship with the Supplier attribute, as shown below:
  

  
  Create table Demo_LU_ITEM
  as
  SELECT a11.ITEM_ID ITEM_ID_DEMO,
    a11.ITEM_NAME ITEM_NAME_DEMO,
    a11.SUBCAT_ID,
    a12.SUPPLIER_ID,
    FLOOR(DBMS_RANDOM.VALUE(0, 10000)) PRICE_DEMO
  FROM LU_ITEM a11
  cross join lu_supplier a12
  

  
   
  
• Import the table created in Step_1, and create the Item_Demo attribute and Price fact/metric.
   
• Define the relationship on the attribute as follows:
  

• Create a metric to calculate the rank which will be used for the metric qualification filter because there is no setting to change the ranking order from descending to ascending as described in the following technical note:
  KB11298: How to explicitly set Ascending order when qualifying the metric value using a Rank function in MicroStrategy Desktop
   
• Create three metric qualification filters to show the “Top 1” to “Top 3” suppliers and their prices, as shown in the image below:
  

• Output level : Supplier
  Function: Metric Value
  Operateor: Exactly
  Value: 1 – 3
  Uncheck ‘When resolving this qualification in a report, other qualifications will be taken into consideration and merged’ option in Advanced setting.
   
• Create three metrics to show the top 3 prices:
  

  
  Sum([Price Demo]) {~+} <[top1]; @2; ->

  
   
  
• Create three metrics to show the top 3 suppliers:
  

  
  Max(Supplier@DESC) {~+} <[top1]; @2; ->

  
   
  
• Create a report and add the Item_Demo attribute, the three metrics created in Step_6, the three metrics created in Step_7, and an attribute element prompt based off the Subcategory attribute.