MicroStrategy ONE

About Apply, Banding, and Case functions

Internal functions include the following types of functions:

For additional information on Internal functions, refer to the MicroStrategy Functions Reference.

Apply or Pass-through functions

Apply functions provide access to functionality that is not standard in MicroStrategy, but is available in the relational database. The apply functions act as containers for non-standard SQL expressions being passed to the database. You can use them in many different capacities, which include, creating or modifying both schema and user objects.

Do not use any Apply function when other standard MicroStrategy functions can be used to create an expression. Apply functions effectively bypass the validations and other benefits of the MicroStrategy product. Therefore, you should use Apply functions 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.

The syntax for Apply functions is as follows:

ApplyFunctionName ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN)

where:

  • ApplyFunctionName – is a generic name used for the predefined pass-through functions described above

  • expression with placeholders – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy.

  • Arg – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on.

Example

ApplyComparison ("ComparisonFunction (#0, #1)", attribute1@ID, attribute2@ID)

You can use five pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, click the link corresponding to each function.

  • ApplySimple – Uses simple arithmetic operators.

  • ApplyAgg– Uses aggregate functions such as Sum, Max, and so on.

  • ApplyOLAP– Uses OLAP functions such as Rank, Ntile, and so on.

  • Apply Comparison– Uses comparison operators such as greater than (>), between, equal (=) and so on.

  • ApplyLogic – Uses logical operators such as AND, OR, and NOT.

The examples given for each of the functions are specific to the databases. Refer to your database documentation for the correct SQL syntax.

Banding functions

Banding functions are used to differentiate the data displayed on a report. You can divide data into bands in the following ways:

  • Banding - distributes the data into bands of equal size.

  • BandingC - distributes the data into a specified number of bands.

  • BandingP- distributes values the data into bands based on specific intervals of values.

Case functions

Case functions return specified data based on the evaluation of user-defined conditions. They are of the following types: