Filter

Source - https://www2.microstrategy.com/producthelp/Current/manuals/en/AdvancedReporting.pdf

Filters

• Creating report filter
• Types of Report filters
  • Attribute Qualification
  • Set Qualification
  • Report as filter
  • Filter Qualification
• Set operator

Advanced filters

• Attribute to attribute filtering
• Joint element list
• Dynamic dates
• Import filter elements
• Pass through functions
• Set qualification- qualifying on metrics
• Set qualification- Relationship filters

Filter

There are two categories of filters in MicroStrategy:

• Report Filter
• View Filter

Report Filter

A report filter specifies the condition that the data must meet to be included in report results. The report filter is usually equivalent to the WHERE clause in SQL statement. You can create the report filter from within the Report Editor or with the Filter Editor.

View Filter

A view filter enables you to create a filter on the fly, based on those objects that exist in the report objects window. View filters do not appear in the report SQL rather are applied after the data is retrieved from the warehouse. You can create a view filter with the view filter window of a report.

Types of Report Filters

• Attribute qualification
• Set qualification
• Shortcut to a Report (Report qualification)
• Shortcut to a Filter (Filter qualification)

Attribute qualification

Attribute qualification filter enables you to specify the conditions that attribute elements must satisfy to be included in the report results.

There are various ways to qualify on an attribute:

• Attribute element list qualification
• Attribute Form qualification
• Date qualification

Attribute element list qualification

This type of filter enables you to qualify on a list of attribute elements. Options can be “in list” or “not in list”.

Attribute form qualification

Attribute form qualification enables you to qualify on any attribute form of an attribute.

Date qualification

This type of filter enables you to qualify on an attribute of the date data type.

Set qualification filter

Set qualification filter enable you to restrict report results based on a metric.

Shortcut to a Report (Report as Filter)

The result set of an existing report object can be used as a report filter condition for another report. Often the result set of one report is exactly what is needed to filter another report.

Shortcut to a Filter (Filter qualification)

Existing report filter objects are used to as filter condition for other report.

Set Operator

When a report filter has multiple conditions, they are always combined with set operators. Set operators govern the interaction between different filter conditions.

• AND
• OR
• OR NOT
• AND NOT

Advanced Filters

Attribute-to-Attribute Filtering

Attribute-to-Attribute comparison filters enable you to create reports that compare values of two attributes using their respective attribute forms. eg. you can create a report that retrieves only those orders that were shipped within 2 days of their order date by comparing ship date with order date + 2.

Add an Attribute qualification Select attribute form In value drop-down list, select Custom

Joint element List

Joint element List enables you to choose combinations of attribute elements from different attributes to filter a report.
(2007 AND Northeast) OR (2008 AND Central)

Dynamic Dates

Dynamic Dates are a fixed set of dates or ranges of dates that change over time. These dates are fixed offsets of the current date according to the system clock of the MicroStrategy Intelligent Server machine.

Import filter elements

You can import filter elements into the filter edition from sources other than MicroStrategy (like Excel, csv).

• Add an Attribute qualification
• Select attribute form
• In operator drop-down list, select In List or Not In List
• Click Import

Pass-through functions

Pass-through functions are intended to provide access to the special functions that are not standard in MicroStrategy but are provided by various RDBMS platforms.

There are several types of Apply functions, each of which corresponds to a basic type of MicroStrategy function and replaces that function when it is necessary to use a corresponding RDBMS function, instead. The Apply function types are listed below, along with examples of the MicroStrategy functions that they can replace:

1) ApplySimple: Simple single-value functions, that is, arithmetic operators such as + and -, date and string manipulation functions, and so forth

2) ApplyAgg: Group-value (otherwise known as aggregate) functions such as Sum and Max

3) ApplyOLAP: OLAP functions such as Rank and NTile

4) ApplyComparison: Comparison operators such as >, <, and Like

5) ApplyLogical: Logical operators such as And and Or

- Filter editor > In filtering options, select Add an Advanced qualification - In Advanced qualification editor, type the custom expression:

ApplyComparison(“#0=trunk(sysdate-1)”,Date@ID) OR ApplySimple(“Mid(#0,5,2)”,Month@ID) = 12

Set qualification: Qualifying on Metrics

Metrics qualifiers restrict the rows of data returned in a report by constraining the metrics. You can qualify on a particular function for the selected metric. The functions include:

• Metric value
• Rank
• Percent

Output Level

The output level of the metric qualifier is the set of attributes at which you want the metric to evaluate.

• None selected
• Calculate the output at the Metric level
• Calculate the output at the Report level
• Calculate the output for the list of attributes

Break By

Break by enables you to choose the attribute level at which to restart the rank or percentage for a metric.

Report Filter: (Rank of {Units Sold} Top 2 )

Metric-to-Metric Comparison

Metric-to-Metric Comparison enables you to create reports that compare the values of two metrics. For example, you can create a report that returns only those records with Revenue greater than Last Year Revenue.

Set Qualification: Relationship filters

Relationship filter is a filter that can be used to define a specific type of relationship between two or more attributes that are not directly related.