MicroStrategy ONE

Filter Expressions

A filter specifies the conditions that data must meet to be included in the report results. In SQL, a filter is specified after the WHERE clause. A filter can be a report object, that is, a report filter, a report limit, or a view filter, that restricts the data returned or the display or view of data on a report. For detailed information on report filters, see the Advanced Reporting Help .

A filter can also be a metric qualifier created using custom expressions employing functions. It is used as the optional condition component of a metric. You can create this type of filters by using the Advanced Qualification dialog box within the Filter Editor. Only the following types of functions can be used in filter expressions:

  • Logical operators
  • Comparison operators

Single-value functions can be used at the sublevel in a filter expression, as long as the root node is a logical or comparison function (see Example 1).

Example 1: Greater Than ( > )

((Revenue - Cost) > "5000")

This example uses a simple comparison operator to create a filter to limit the display of profit to values greater than $5,000.

You can achieve the same result by using a set (metric) qualification on a compound metric (such as the one in the example defined as Revenue - Cost). The custom filter expression is used here for illustration purposes. It is up to you which method you prefer to use.

Example 2: ApplyComparison

ApplyComparison("#0 between #1 and #2", ?[Value Prompt Date], [Order Date]@ID, [Ship Date]@ID)

ApplyComparison is commonly used to create custom filters. In this example, the filter compares a user-entered date to see if it is between the Order_Date and the Ship_Date.

To Access an Advanced Filter Qualification

  1. In the MicroStrategy Developer File menu, point to New > Filter. The Filter Editor is displayed.
  2. In the Filter Definition pane, double-click Double-click here to add a qualification or drag an object from the Object Browser. The Filtering Options pane is displayed.
  3. In the Filtering Options pane, select the Add an Advanced qualification option and click OK. The Advanced Qualification pane is displayed.
  4. Select Custom Expression from the Option list. Then use the Custom Expression box to build and validate your custom filter. Access the functions in one of the following ways:
    • Expand the Functions and Operators folder using the drop-down list or folder list. Then expand the Functions, Operators, or Plug-In Packages folder to access the various categories of functions and operators.
    • Click (Insert Function) in the Definition pane. The Insert Function Wizard opens.
    • Enter the function name and all required metric syntax directly in the Enter your formula here box.