KB220144: How the Criteria field in an Advanced Sorting definition is designed to behave with regards to multiple report designs in MicroStrategy Developer 9.x.

SUMMARY
The Advanced Sorting feature is used to define a custom way in which the returned data is sorted and presented to the end user.  Within the Advanced Sorting definition the Criteria field exists that needs to be set in some scenarios, and shows up when an object exists above or below the metrics.  There may exist some scenarios in which the Criteria behavior may not behave as expected or desired.  This tech note aims to explain this feature in further detail.
 
DEFINITIONS AND DESCRIPTION
The Criteria Field:

• Defined in a sort when an object exists above or below the metrics
• Typically, the scenario to define the Criteria is when an attribute is placed above the metrics to categorize the values
  
• Can also be custom groups, consolidations, hierarchies, or other objects
• Analytical Engine controls the population of the elements in the drop down for this field, and populates the possible selection options post-execution
  
• The Analytical Engine will not have the full populate of elements prior to execution because they are retrieved when the SQL hits the warehouse
• Pre-execution shows the {empty criteria} option, and the previous saved selection from a prior execution and definition
  
• {empty criteria} is essentially selecting an empty element, and should result in no metrics being sorted based on its selection
• Previous element that was defined in the sort is stored in the report definition by the Analytical Engine by default

 
Users may not experience the Criteria field or its behaviors if no objects are ever above or below the metrics.  This type of report design results in a sort that is dynamic in nature based on the data on the grid.  This type of definition and design of the sort is dynamic in nature because the metrics are not categorized by elements of an object, so any filter or slice of the data will be affected by the sort definition for objects on the grid.
 

 
When objects are used above or below metrics to provide additional context, comparison, or difference in time, then the Criteria field will come into play if a sort is defined.  Whenever a sort is defined in this type of report design, whether explicitly in the Advanced Sorting window or via right click, then this manually creates a sorting definition based on the current Criteria above or below the metric that was sorted.  As a result, if a new filter or slice is created, the new display of data may not be affected by the sort definition because the criteria defined in the sort definition may not match or apply to the current report display elements of the objects above or below the metrics being sorted.
 

 
STEPS TO REPRODUCE
 (1) Report with Advanced Sort using Criteria field

1. Create a report with Region attribute in rows, Year attribute in columns, and Cost metric in columns
2. Execute the report, and notice that there are multiple cost metrics per each element of Year
   
• 2010, Cost; 2011, Cost; 2012, Cost; etc.
3. Navigate to Data, Advanced Sorting menu
4. Define a sort on the Rows for the Cost metric, Criteria=2011, Ascending and hit OK
5. Notice the values sort properly only for the Cost metric under the Year element 2011
6. To sort the other values, you would have to make additional sort definitions for each Criteria element
   
• This is why the Criteria field can result in a report display that does not dynamically sort with the report filter and/or view filter

 
(2) Report with Advanced Sort not using Criteria field

1. Create a report with Region and Year attributes in rows, and Cost metric in columns
2. Execute the report, and notice that elements of year are output per each element of Region with their Cost values displayed, as well as the existence of only 1 column for the Cost metric
   
• This differs from the design above where there would exist multiple instances of the Cost metric column per each element of Year
3. Navigate to Data, Advanced Sorting menu
4. Define a sort on the Rows for the Cost metric, Ascending and hit OK
5. Notice the values sort properly for the Cost metric
6. Define a report filter for Year=2011, and notice that the sort still works properly
7. Change the report filter for Year=2012, and notice that the sort still works properly for the single sort definition
   
• This is helpful if the end user expects a dynamically sorted grid for each execution, and can be utilized with prompts to help display their specific amount or category of data

 
CAUSE
The main cause to be aware of is when elements exist above or below the metrics, and a Criteria setting is necessary to sort the data.  It is expected that the elements defined in the sort do not dynamically update or change based on filters, slices, or prompts in the report.  This is because a sort definition is set manually post-execution – either via Advanced Sorting menu, or via right clicking on specific objects to sort in the grid.  There is no way to populate the Criteria setting in the Advanced Sorting window with a dynamic placeholder for a specific object that would operate like dynamic text would in a report services document.
 
This is by design because when an object exists above or below the metrics, it creates multiple instances of that metric per each element of the object that categorizes them.  When you then set a sort based on that metric, you have to tell the engine which instance of that metric to sort.
 
When no objects exist above or below the metrics, multiple instances of that metric are not created, thus the sort becomes dynamic since it is only applying to the single column that the metric outputs.
 
ACTION
Identify how your sort needs to be defined, and how the user expects the data to be sorted or displayed.

• If dynamic sort is expected where a user will be filtering or slicing the data a lot, then do not design a report with objects above or below the metrics.
  
• If this is unavoidable due to reporting requirements, then you may have to create a sort instance for every possible permutation of Criteria elements and metric objects.
  
• This can quickly become unmanageable based on the size and number of objects in the report.
• If the metrics require categorization by elements of an object, then realize the sort will not be dynamic, and that it will need to be manually set by the developer or the end user.
  
• They can set this via the Advanced Sorting GUI if the end user has access, or they can right click the grid for the desired object to be sorted, and select the appropriate sort option.

 
TO LEARN MORE
More information about Advanced Sorting can be found in the Advanced Reporting Guide product manual in Chapter 7: Designing Reports.