KB47171: How to use a selector to limit the date range between the first month of year and the selected month in MicroStrategy 9.x?

Users may want to select a month in the selector and have the grid/graph return the date range between the first month of year and the selected month. For example, if June 2010 is chosen in the selector, then the grid/graph returns data from January 2010 to June 2010. The following procedure describes how to implement such selector in Strategy Tutorial project.
 
 
 
 
Month(ID) Less than or equal to Month Selection (ID)
 

• Create a logical view to serve as the lookup table for Month Selection. Use the following SQL statement to define the lookup table:
   
  
  Sql
  
  select a11.Year_ID,
            a12.Month_ID as Month_Selection_ID,
  from   Lu_Year a11,
            Lu_Month a12
  where a11.Year_ID=a12.Year_ID
  
  
    
  Insert the following Column Objects:
   
  YEAR_ID           Integer  
  MONTH_SELECTION_ID      Integer 
   
  Make sure that YEAR_ID is added by dragging the existing column YEAR_ID from table LU_YEAR. Column MONTH_SELECTION_ID is a manually added column.
   
  

• Edit Year attribute and check LU_MONTH_SELECTION as the source table for the ID form.
   
  

• Create a new attribute called 'Month Selection' based on the lookup table LU_MONTH_SELECTION. The attribute has only ID form which is based on column Month_Selection_ID.
  

• Update Schema.
• Create a new report with attribute Month and Month Selection.
• Add report filter and define it to qualify on ID of Month attribute with condition:
• Create a new dashboard and add the report as dataset. Insert a grid with attribute Month.
• Create an element selector based on attribute Month Selection and target on the grid.
   
  

• Run dashboard. When any month is selected, the grid will show months between the first month of current year and the selected month.