MicroStrategy ONE
Apply (Pass-Through) Functions
MicroStrategy Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various Relational Database Management System (RDBMS) platforms.
MicroStrategy strongly advises against using Apply functions when standard MicroStrategy functions can be used to achieve the same goal, because using RDBMS functions effectively bypasses the validations and other benefits of MicroStrategy products. Using Apply functions is recommended only when corresponding functionality does not exist in MicroStrategy. When you need to use an Apply function, MicroStrategy encourages you to submit an enhancement request for inclusion of the desired feature in a future product release.
To use Apply functions, you must have the Use FreeForm SQL Editor privilege.
Syntax Common to Apply Functions
While each Apply function has its own syntax, the Apply functions share several syntactic features:
-
The "#n" code in Apply function syntax serves as placeholders for the MicroStrategy objects being passed to your database. The index for referencing these objects begins with 0 and increases by 1 for each successive object passed.
For example,
ApplySimple("#0 * #1",[col1],[col2])
indicates that two items,col1
andcol2
, referenced as#0
and#1
, respectively, are being passed to your database to be multiplied together (by the database). If the number of references in an Apply function (for example,#0,
#1,
and so forth) exceeds the number of objects passed in that function, MicroStrategy passes the last available object in place of the extra reference(s).For example,
ApplySimple("#0 * #1 * #2 * #3",[col1],[col2])
uses two more references than there are arguments to pass, so MicroStrategy passes#2
and#3
, the extra references, ascol2
, the last available object in the list. -
To use # as a character rather than a placeholder, use four # characters in a row. See the syntax below for an example.
ApplyComparison(UPPER(#0) like
'Z####%', Country@DESC)
The SQL for the function is:
Select a.11[COUNTRY_ID] AS COUNTRY_ID
from [LU_COUNTRY] a11
where upper(a11.[COUNTRY_NAME])
like 'Z#%'
- Do not use form groups for the attribute form expression when using Apply functions, because form groups are ignored by the Analytical Engine. Instead, use a single form. For example, instead of using Customer@Name, where Name is defined to contain Customer's first name, middle name, and last name, use ID or any other single form.
For more general information on Apply functions as well as an example, see Apply (Pass-Through) Functions.
ApplyAgg
The ApplyAgg
function is used to define simple metrics or facts by using database-specific, group-value functions. The ApplyAgg
function itself is a group-value function and accepts facts, attributes, and metrics as input.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
ApplyAgg()
Accepts facts, attributes, and metrics as input.
All placeholders must begin with #0 and increase in increments of 1.
ApplyAgg("Regrsxx(#0,#1)", [Argument 1], [Argument 2]
{~+}
ApplyComparison
ApplyComparison
is used to define a filter by populating the WHERE
clause in the SQL passed to your RDBMS, and can take facts, attributes, and metrics as input.
The ApplyComparison
function is used with RDBMS comparison operators such as >
, like
, and In
.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
ApplyComparison()
ApplyComparison ("#0>#1", Store@ID,2)
For another example of the ApplyComparison
function, see Example: ApplyComparison Used to Check a Prompted Date.
ApplyLogic
The ApplyLogic
function is used to define custom filters. It is used with logical operators such as AND
and OR
. ApplyLogic
is a logical function.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
ApplyLogic()
Accepts logic (Boolean) values as input.
ApplyLogic("#0 and #1", Year@ID>2003, Month@ID>200301)
ApplyOLAP
OLAP functions are group-value functions that take a set of data as input and generate a set of data as output, usually reordering the set according to some criteria.
ApplyOLAP
is the MicroStrategy Apply function tool used for OLAP functionality when you wish to use the native capabilities of your RDBMS. It is used to define compound metrics via database-specific functions such as Rank()
. ApplyOLAP
, like ApplySimple
, is used to define metrics but differs in that it only accepts metrics as input.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
ApplyOLAP()
Accepts only metrics as input.
Example
ApplyOLAP("RunningSlope(#0,#1)", [Metric 1], [Metric 2])
ApplySimple
The ApplySimple
function is a single-value function. It is used to insert any single-value, database-specific functions and simple operators directly into SQL.
In general, ApplySimple
can be used to create the following objects:
- Attribute form
For any Apply function, the attribute form in the arguments should be a single form—not a form group. The engine ignores any definitions based on attribute forms.
- Consolidation
- Custom group
- Fact
- Metric
- Subtotal
- Transformation
For information about consolidations, custom groups, metrics, and subtotals, see the Advanced Reporting Help.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
ApplySimple()
Accepts facts, attributes, and metrics as input.
Expression type | Examples |
Attribute |
ApplySimple("Datediff(YY,#0,getdate())", [BIRTH_DATE]), in which [BIRTH_DATE] is an attribute |
ApplySimple("Months_between(sysdate,#0)",[CURRENT_DT]), in which [CURRENT_DT] is an attribute |
|
Compound Metric |
ApplySimple("Greatest(#0,#1,#2)", Metric_1, Metric_2,Metric_3) |
ApplySimple(" CASE WHEN #0 between 0 and 100000 THEN 'Low' WHEN #0 between 100001 and 200000 THEN 'Med' ELSE 'High' END ", Sum(Revenue){~}) |
Examples in Custom Expressions
ApplySimple("Datediff(YY,#0,getdate())", [BIRTH_DATE])
ApplySimple("Months_between(sysdate,#0)", [CURRENT_DT])
Examples: Incorrect Usage
ApplySimple("Sum(#0)",[Column 1])
ApplySimple("Count(#0)",[Column 2])
The two examples immediately above are incorrect and should not be used in your application because of the following two reasons:
- ApplySimple is a single-value function and therefore can only be used with single-value functions. Sum and Count are both group-value functions and therefore should not be used with ApplySimple.
- Sum and Count are both MicroStrategy functions and are not database-specific; therefore, they should not be used with ApplySimple or any other Apply functions.