MicroStrategy ONE

Creating a custom expression qualification

Steps to create a custom expression qualification

In a filter, a custom expression qualification allows you to create customized filter expressions. You can use Apply functions and you can create relationship filters.

Custom expressions allow you to create highly customized filter expressions. By typing syntax instead of using point-and-click functionality within MicroStrategy, you are able to dictate filter criteria that is more specific and sophisticated than the MicroStrategy interface allows.

You can use custom expressions in the following situations:

  • Custom expression qualifications, which evaluate the custom expression you enter.

    Custom expression qualification filters are quite flexible and allow you to create highly customized filters that can test data based on text content, very specific date specifications, or other criteria. These tests are based on the structure of a custom expression qualification, since the two sides of the expression are separated by a comparison operator such as =, <, >, like, and so on.

  • Attribute-to-attribute qualifications, which compare an attribute and a custom expression.

  • Relationship (metric) qualifications, which compare metric results to a selected value (in this case a custom expression).

Basics of custom expressions

Custom expressions are written so that data can be tested against them, and whatever data qualifies as true when it is inserted into the custom expression is returned in the data result set.

All custom expressions must evaluate as either true or false. For example, if you enter 5 = 6 as the custom expression, the expression never evaluates as true, and a filter based on this custom expression excludes all data from a report that uses it. On the other hand, a custom expression consisting of 5 = 5 always evaluates as true, so the filter includes everything. No data is filtered out of a report containing this filter.

As an example of a scenario that uses a custom expression, you notice that your mailing campaigns have been impaired because longer last names do not print correctly on the envelopes you have been using. Therefore, your marketing manager needs a list of customers with last names greater than 15 characters long. This report requires a filter that uses a custom expression and a string function to count the number of characters in a name.

In another scenario, your shipping company is trying to save freight costs by optimizing shipments, and it offers you a discount for all freight that you ship between 4 A.M. and 6:59 A.M. To determine if you can save substantially on shipping by taking advantage of this offer, you generate a report showing orders that shipped in the three-hour period specified. This report uses a filter that employs a custom expression and a date-manipulation function to test for orders that are shipped between 4 A.M. and 6:59 A.M.

For more detailed descriptions of these examples, see Advanced Filters: Filtering Data on Reports in the Advanced Reporting Help.

Anything that you can specify in a filter can be specified using a custom expression, though if you can accomplish your goal using the standard point-and-click functionality of MicroStrategy, that will usually be the most efficient method to create your filter. In other words, when point-and-click functionality is not enough for your purpose, use a custom expression to create your filter.

There are certain reports that require a filter with capabilities outside of what MicroStrategy currently offers. These reports, too, use filters with custom expressions, but instead of using built-in MicroStrategy functions, these filters use the functionality of your particular Relational Database Management System (RDBMS). This functionality is discussed in Advanced Filters: Filtering Data on Reports in the Advanced Reporting Help.

Prerequisite

By default, the Add an Advanced qualification option is disabled in the Filter Editor. You must activate the advanced qualification option to create a custom expression qualification.

To create a custom expression qualification

  1. Open the Filtering Options pane of the Filter Editor, if it is not already open. How?

  2. Select Add an Advancedqualification and click OK. The Advanced Qualification pane opens.

  3. Select Custom expression from the Option drop-down list.

  4. Add a custom expression in the Custom expression box by one of the following methods:

    • Drag objects from the Object Browser into the Custom expression box. Select the operator buttons to place in your expression.

    • Manually type the custom expression in the Custom expression box.

  5. Click Validate to validate the expression.

  6. Click OK to close the Advanced Qualification pane.

  7. Do one of the following:

    • Add more qualifications to the filter. Double-click in the Filter definition pane to open the Filtering Options pane again. These can be attribute qualifications or other types of qualifications.

      If you have multiple qualifications, you can change the operator connecting them. To do this, right-click the operator in the Filter definition pane, point to Toggle operator, then select the new operator from the list.

    • Save the filter. How?

Related Topics