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
- In the MicroStrategy Developer File menu, point to New > Filter. The Filter Editor is displayed.
- 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.
- In the Filtering Options pane, select the Add an Advanced qualification option and click OK. The Advanced Qualification pane is displayed.
- 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.