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 and col2, 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, as col2, 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.

Example

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()

Example

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.

Example

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.

Examples of Object Creation

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.