KB4202: How to implement attribute form expression using Apply Functions in MicroStrategy Developer

Apply functions can be used in the attribute editor to provide access to functionality that is not standard in Strategy, but available from the Relational Database Management System (RDBMS), such as the special functions or syntactic constructs provided by various RDMBS platforms.
 

 
The functionality of Apply functions is provided by wrapping the expression, as a string, inside a dedicated (Apply) function call. To provide a reasonable level of generality, the feature is based on a 'pattern and arguments' scheme. The types of arguments determine how the parameter markers inside the pattern are replaced. Also, the particular Apply function used in the expression determines how the results are utilized by the system. The engine uses the information received in the pattern 'as is' to generate commands for the RDBMS, without interpreting its semantics, relying on the user knowledge of the RDBMS.
 
The syntax is as follows:
 
Grammar:

PassThroughExpr = ApplyFunName (Pattern, Arg, Arg, …, Arg)
Pattern = String (containing parameter markers:  #0, #1, …)
Arg = Any argument appropriate for regular, predefined functions with the same type as that 
represented by ApplyFunName and applied in the same context.

• ApplyFunName is a generic name used for one of the predefined Apply functions (presented below).
• Pattern is the string describing the actual expression (syntax) to be used by the engine when generating the SQL to be sent to the RDBMS.
• Arg is an argument that replaces the parameter markers in the pattern.

 
The Apply Functions:
Each type of custom sub-expression/function call is represented by a specialized Apply function. There are several predefined Apply functions that can be used where regular predefined functions of the same type are used.

• ApplySimple where simple (e.g. arithmetic operators) are used.
• ApplyAgg where aggregate functions (Sum, Max, etc.) are used.
• ApplyRelative where OLAP (ranking, etc.) functions are used.
• ApplyComparison where comparison operators (>=, <, Like, etc.) are used.
• ApplyLogic where logical operators (And, Or, etc.) are used.

 
The Pattern:
The string pattern is similar to the ones used internally by the engine for regular predefined functions. The string pattern contains parameter markers flagged by '#' characters and indexed from 0 (e.g., #0, #1, #2, etc.). The engine replaces parameter markers by actual arguments according to the following tenets:

• Constants are inserted as they are.
• Object names are handled according to their types, similarly to predefined functions.

 
The Arguments:
The number of arguments is variable and is not checked until the replacement of parameter markers. When parsing, accepted argument types are:

• Constants of the usual types.
• Names of objects of types that are valid for the predefined functions of the same type.

 
Typically, patterns represent custom function calls (possibly with a special syntax) or 'custom SQL,' but, since patterns are not analyzed, there is no limit to what they can be, as long as the result returned is compatible with what the analytical engine expects.
 
EXAMPLES:
If the engine has to generate the following to be sent to the RDBMS

 SELECT MAX({FN CONVERT(INT, {FN DATEDIFF(MYDATE,STARTDATE)})})

 MAX(ApplySimple("{FN CONVERT(INT,{FN DATEDIFF(#0, #1)})}",MYDATE,STARTDATE))

MYAGGFUN, an expression for the RDBMS such as the following:
 MYAGGFUN( FACT1 + FACT2)
can be specified by the user as:
 ApplyAgg( "MYAGGFUN( #0 + #1 )", FACT1, FACT2 )

 MYCOMPARISON( ATTRIBUTE1_ID, ATTRIBUTE2_ID )
specify as a filter expression:
 ApplyComparison("MYCOMPARISON(#0, #1)", Attribute1@ID, Attribute2@ID)

 ApplyComparison("#0 >o< #1", Attribute1@ID, Attribute2@ID )