MicroStrategy ONE

Apply Functions in Metrics: Using Database-Specific Functions

Custom expressions sometimes use Apply functions, also known as Pass-through functions. Using the full power of custom expressions, therefore, may require an understanding of Apply functions. In MicroStrategy, these functions provide access to functionality and syntactic constructs that are not standard in MicroStrategy, but are found in various RDBMS platforms.

It is not advisable to use Apply functions when functions within the MicroStrategy product can accomplish your goal, because using Apply functions bypasses the validations that MicroStrategy provides. Apply functions should only be used when you require functionality that MicroStrategy does not currently provide.

Apply Function Types

There are several types of Apply functions, each of which corresponds to a basic type of MicroStrategy function and replaces that function when it is necessary to use a corresponding RDBMS function, instead. (See the Standard Functions chapter of the Functions Reference for further details about when it is necessary and appropriate to use MicroStrategy Apply functions instead of their simpler MicroStrategy function counterparts.) Apply functions act as containers that hand off data from MicroStrategy to your RDBMS. The Apply functions also hand off to your RDBMS functions that are specific to your database. MicroStrategy provides Apply functions as a means of handing off these data and RDBMS-specific functions to your database, which results in your database—not MicroStrategy—performing the computations you require.

The Apply function types are listed below, along with examples of the MicroStrategy functions that they can replace:

  • ApplySimple: Simple single-value functions, including arithmetic operators such as + and -, date and string manipulation functions, and so on
  • ApplyAgg: Group-value (otherwise known as aggregate) functions such as Sum and Max
  • ApplyOLAP: OLAP functions such as Rank and NTile
  • ApplyComparison: Comparison operators such as >, <, and Like
  • ApplyLogical: Logical operators such as And and Or

For filters, the ApplyComparison and ApplySimple functions are most useful, although you can also use Apply functions to create metrics and prompts. For examples of Apply functions in filters, see the rest of this section.

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 Apply functions ONLY when support does not exist in the MicroStrategy product for your needs, and please submit an enhancement request so that MicroStrategy can evaluate integrating the functionality you are using in a future product release.

Apply Functions: Syntax

This section provides a brief overview of the syntax for Apply functions. For more comprehensive information, consult the Standard Functions chapter of the Functions Reference.

Basic Syntax of Apply Functions

The syntax for Apply functions is as follows:

	ApplyFUNNAME("expression_with_
		placeholders", argument_0, ...,
			argument_n)

The placeholders are represented by #0, #1, and so on. The character # is a reserved character for MicroStrategy and precedes the number of the argument, which starts at 0 and increases in increments of 1. For example:

	ApplyComparison("<ComparisonFunction(#0,#1)>",
attribute0@ID, attribute1@ID)

Or

	ApplyComparison("#0<ComparisonOperator>
		#1",attribute0@ID, attribute1@ID)

In these examples, the code marked with angled brackets (<>) can be replaced with database-specific functions or operators such as between, +, and DateAdd. See Custom Expressions and Custom Expressions for examples using these RDBMS functions. Argument placeholders must be placed in the correct positions to match the syntax of the database-specific functions.

Argument Types in Apply Functions

The number of allowable arguments in an Apply function depends on which particular Apply function you are using, among other considerations. In some instances, the number of arguments varies from case to case within the same Apply function. This is common among the logical Apply functions, in particular.

MicroStrategy does not verify arguments until the parameter markers (for instance #0, #1, and so forth) are replaced at parsing, which occurs when you click OK or Validate in an expression editor. At parsing time, the engine searches for acceptable argument types for the specific Apply function used in your custom expression. An acceptable argument type is a MicroStrategy object type or an argument that contains the name of a MicroStrategy object.

MicroStrategy does not check whether the arguments used in the Apply functions are correct data types for your specific database functions. MicroStrategy does, however, verify that any objects included as arguments actually exist in MicroStrategy.

Below are examples of Apply functions. More examples can be found in the Standard Functions chapter of the Functions Reference.

Example: Qualifying on Case-Sensitive Elements

Your company name has been entered into your data warehouse without regard to the correct case. For example, MicroStrategy has been entered as MicroStrategy, Inc.; MicroStrategy; Microstrategy; MICROSTRATEGY; and micro strategy. Rather than creating filters for all the possible combinations, you can create one custom expression qualification filter using the ApplyComparison function. The custom expression used in that filter is shown below:

ApplyComparison ("Ucase ( #0 ) like #1",
Company@DESC, "STRATEGY")

Each piece of the custom expression is explained below:

  • The Ucase command changes the case of the argument to one that is in all capitals—for instance, MicroStrategy to MICROSTRATEGY.
  • ( #0 ) like #1 is the actual comparison, comparing the first argument, #0, with the second argument, #1. This comparison evaluates as true if the left side of the expression, Ucase ( #0 ), contains the right side of the expression, STRATEGY.

    The right side of the expression is actually #1, but that argument is replaced with STRATEGY in this example.

  • Company@DESC sets the first argument as the description form of the Company attribute—instead of the ID form or any other attribute form that exists.
  • "STRATEGY" is the second argument, a string that does not vary.

Using the filter on a report containing Company and Department generates the report below. Note that no matter how the Company attribute element has been spelled or capitalized, the report includes the element as long as it contains the string "strategy".

Company

Department

micro strategy

Tech. Support

MicroStrategy

Sales

Microstrategy

Finance

MICROSTRATEGY

ADVERTISING

MicroStrategy, Inc.

Legal

This report serves simply as an example; you cannot re-create it in MicroStrategy Tutorial.

Example: Qualifying on an Attribute Element Containing a Wildcard Symbol

Your objective is to generate a report that contains all Store elements that have an * in their names. If you attempt to do this using the standard Add an Attribute qualification option in the Filter Editor, no filtering will take place, because MicroStrategy assumes that you wish to include all results when you use the * wildcard. Instead, you can use an ApplyComparison function in a custom expression qualification filter to search for this symbol.

Use the steps in Custom Expressions along with the custom expression below, to create the filter you need:

ApplyComparison ("#0 like '*'", Store@DESC)

Each piece of the custom expression is explained below:

  • The ApplyComparison function is used for comparison operators, such as the like operator used in this example.
  • The actual comparison is placed in double quotes, for example, "#0 like '*'". The first argument, #0, is compared with the wildcard symbol you are searching for. Note that the asterisk is placed in single quotes since the whole comparison expression itself is in double quotes.
  • Store@DESC sets the first argument as the description form of the Store attribute.

Using the filter described above in a report containing Store and Revenue, results in the data below, which contains only Store attribute elements with asterisks in their names. Had you used an attribute qualification instead of the ApplyComparison function, all stores would have been returned in the report data.

Store

Revenue

B*E*S*T

$123,456

Marcy*s

$456,789

Q*Mart

$78,901

Wall*Mart

$234,567,890

This report serves simply as an example; you cannot re-create it in MicroStrategy Tutorial since Tutorial does not contain a Store attribute.

Example: Filtering for Last Month's Data Using ApplySimple

Example: Filtering for Last Month, Without Transformations

An attribute-to-attribute qualification filter can be used to restrict your result set on a report to last month's data. Follow the steps below to create this filter.

Even though this example can be duplicated using a time-based transformation (information for which can be found in Transformation Metrics: Time-Based and Other Comparisons), this example uses an Apply function to demonstrate the functionality of custom expressions and Apply functions. In general, MicroStrategy strongly advises against using Apply functions except when you are using them for functionality which does not currently exist in the software. See Apply Functions in Metrics: Using Database-Specific Functions for more information.

To Re-Create This Attribute-To-Attribute Qualification Filter Example

  1. In MicroStrategy Developer, choose File > New > Filter.

    If the New Filter dialog box is displayed, click the Empty Filter icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

  2. In the Filter Editor, drag the Month attribute from the Object Browser to the Filter definition pane.
  3. From the Qualify On drop-down list, select ID.
  4. From the Operator drop-down list, select Exactly.
  5. From the drop-down list, choose Custom.
  6. Type the following custom expression in the text box:

    ApplySimple("DatePart('yyyy',
    DateAdd('m', -1, date())) * 100 + DatePart('m', DateAdd('m', -1, date()))",0)

    Your Relational Database Management System (RDBMS) might require slightly different syntax than the above. Any time you use an Apply function, it must be customized to the syntax of your particular RDBMS. The functions in this ApplySimple expression are specific to an Access database, which is recommended only for certain pre-production environments but never for an actual production environment.

  7. Click OK.
  8. Save the filter.

A filter which uses the custom expression above, compares the dates of data in your data warehouse to the system date, returning only data from last month. Therefore, whenever you execute the report, you always see results for the previous month.

Place this filter on a report with the Month and Region attributes and the Revenue metric to display regional revenue data for last month. The report shown below was executed in Oct 2026, so report generation yielded only data from Sep 2026. If you regenerate the report in Dec 2027, the new report will return data only from Nov 2027.

Analyzing the Custom Expression's Syntax

The report is executed on Oct 30, 2026. The custom expression takes the current, or system, date and decrements it by one month, changing it from 20261030 to 20260930. This is actually done twice but separately in the expression so that first, the year, 2026, can be extracted, and then the decremented month, 09, can be extracted. (Even though the date is decremented twice, it is decremented in separate places, yielding the same result in both places, 20260930, instead of a "double decremented" result of 20260830.) The year is then multiplied by 100, yielding 202600. Finally, 202600 is added to the month, 09, resulting in 202609. This is the date of the previous month, in YYYYMM format and is used to filter out all data except that from Sep 2026.

Each piece of the custom expression is explained below:

  • The DateAdd function allows you to shift a date forward or backward. In both instances that the DateAdd function is used in this example, -1 subtracts one month (indicated by m) from the system date (returned by the Date() function).

    In this example, the report has been executed on October 30, 2026, so the system date is 20261030. The DateAdd function returns 20260930, the date that is one month prior to the system date.

  • The DatePart function returns a specified part of a given date. The first DatePart function extracts the year (as specified by yyyy) from the result of the DateAdd function. The second DatePart function extracts the month (specified by m) from the result of the DateAdd function.

    In this example, the first DatePart function returns 2026; the second returns 09 (after the month has been decremented).

  • The result of the first DatePart function is multiplied by 100 and then added to the result of the second DatePart function, 09, yielding 202609, using the ApplySimple function. ApplySimple functions are used for simple single-value functions such as the arithmetic operators + and -. More information on ApplySimple and other functions can be found in Apply Function Types, as well as the Standard Functions chapter of the Functions Reference.