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 withSTRATEGY
in this example.Company@DESC
sets the first argument as the description form of theCompany
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 thelike
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
- 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 .
- In the Filter Editor, drag the Month attribute from the Object Browser to the Filter definition pane.
- From the Qualify On drop-down list, select ID.
- From the Operator drop-down list, select Exactly.
- From the drop-down list, choose Custom.
- 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.
- Click OK.
- 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 theDateAdd
function is used in this example,-1
subtracts one month (indicated bym
) from the system date (returned by theDate()
function).In this example, the report has been executed on October 30, 2026, so the system date is
20261030
. TheDateAdd
function returns20260930
, the date that is one month prior to the system date. - The
DatePart
function returns a specified part of a given date. The firstDatePart
function extracts the year (as specified byyyyy
) from the result of theDateAdd
function. The secondDatePart
function extracts the month (specified bym
) from the result of theDateAdd
function.In this example, the first
DatePart
function returns2026
; the second returns09
(after the month has been decremented). - The result of the first
DatePart
function is multiplied by100
and then added to the result of the secondDatePart
function,09
, yielding202609
, using theApplySimple
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.