Advanced qualification: custom expression - Microstrategy

Advanced qualifications allow you to create custom expressions to fit particular needs. For example, you can create a relationship filter using the custom expression area of the advanced qualification window.

Advanced qualification: relationship filters

The advanced qualification window allows you to use commands rather than an interface. To work with an interface, see Set qualification: relationship qualification.

The following syntax is required to create a relationship filter using an advanced qualification:

where:

  • The relation can be a fact, a table, or an empty filter. Facts and tables are relationships between attributes in Filtering Input and Output Level. Relationships determine which table is used during SQL generation.

    If a relationship is left empty, the schema is used to select the appropriate table.

  • The filter qualification defines input filtering criteria. It may consist of an attribute qualification, a filter qualification, or a metric qualification, followed by a comma and an output level.
  • The list of output attributes is a comma-separated list of the attributes to be filtered on. If your regional settings are not set to English, the list separator must be whatever is defined in your regional settings. The output level dictates the contents of the relationship filter output set.

It is easiest to simply drag an attribute from the Object Browser into the list. If you manually enter the attribute, it must be in the format

For example, if you are creating a report that shows all stores selling Nike shoes in the DC area, the relationship filter syntax looks like this:

where Fact Sales is the table name, Nike Shoes and Region form the filter qualification, and Stores is the attribute.

Advanced qualification: apply functions

Pass-through expressions, or apply functions, in MicroStrategy are intended to provide access to the special functions or syntactic constructs that are not standard in MicroStrategy, but are found in various RDBMS platforms. There are five predefined apply functions, each belonging to a different function type:

  • ApplySimple—Single-value function
  • ApplyAgg—Group-value function
  • ApplyOLAP—OLAP function
  • ApplyComparison—Comparison function
  • ApplyLogical—Logical function

Among these five functions, ApplyComparison andApplyLogical can be used to create custom expressions for filtering.

While an Apply function can be used wherever the function group it belongs to is applied, you should NOT use any Apply functions when standard MicroStrategy functions can be used to achieve the goal. This is because using Apply functions effectively bypasses the validations and other benefits of the product. Therefore, use it ONLY when support does not exist in the MicroStrategy product and submit an enhancement request so that MicroStrategy can evaluate your needs for inclusion in a future product release.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics