Version 2021
t MicroStrategy Functions
This topic outlines the following types of functions:
Basic Functions
MicroStrategy pushes the calculations for the following built-in basic functions down to PostgreSQL using the associated SQL patterns.
|
Function |
SQL Pattern or Example |
|---|---|
|
Add |
(#0#< + #*#>) |
|
Average |
(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) |
|
Avg |
avg(#0#< #*#>) |
|
Count |
Count(#0#< #*#>) |
|
GeoMean |
EXP(AVG(LN(#0))) |
|
Max |
Max(#0#< #*#>) |
|
Min |
Min(#0#< #*#>) |
|
Multiply |
(#0#< * #*#>) |
|
Stdev |
stddev_samp(#0#< #*#>) |
|
StdevP |
stddev_pop(#0#< #*#>) |
|
Sum |
sum(#0#<#*#>) |
|
Var |
Var_samp(#0#< #*#>) |
|
VarP |
var_pop(#0#< #*#>) |
|
Greatest |
GREATEST(#0#<, #*#>) |
|
Product |
(CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE POWER(-1,SUM(CASE WHEN #0<0 THEN 1 ELSE 0 END))*EXP(SUM(LN(ABS(CASE WHEN #0<>0 THEN #0 ELSE NULL END)))) END) |
MicroStrategy uses the analytical engine to implement the basic functions shown below.
|
Function |
Example of Analytical SQL Calculated by the Analytical Engine |
Usage |
|---|---|---|
|
First |
First(WJXBFS1) |
Returns the result of an aggregate applied over a set of rows that ranks as the first within a specified order. This is a group-value function that shares the sort by capability of the OLAP functions. |
|
HistogramMedian |
HistogramMedian(WJXBFS1, 5.0) |
Histogram Median is a distributive implementation to calculate approximation of median over large datasets. |
|
Last |
Last(WJXBFS1) |
The Last function returns the last value in a sorted set of values. It is often used to define subtotals, as shown in the example below. This is a group-value function. |
|
Least |
Least(WJXBFS1, 1800000.0) |
The Last function returns the last value in a sorted set of values. It is often used to define subtotals, as shown in the example below. This is a group-value function. |
|
Median |
Median(WJXBFS1) |
The Last function returns the last value in a sorted set of values. It is often used to define subtotals, as shown in the example below. This is a group-value function. |
|
Mode |
Mode(WJXBFS1) |
Returns the most frequently occurring value in a given list. This is a group-value function. |
String Functions
MicroStrategy pushes the calculations for the following built-in string functions down to PostgreSQL using the associated SQL patterns.
|
Function |
SQLPATTERN |
|---|---|
|
Trim |
TRIM(BOTH ' ' FROM #0) |
|
Length |
LENGTH(#0) |
|
Lower |
LOWER(#0) |
|
LTrim |
LTRIM(#0) |
|
Upper |
UPPER(#0) |
|
RTrim |
RTRIM(#0) |
|
Concat |
(#0#< || #*#>) |
|
Replace |
REPLACE (#0, #1, #2) |
|
Char |
CHR(#0) |
|
LeftStr |
SUBSTR(#0, 1, CAST(#1 AS INTEGER)) |
|
RepeatStr |
REPEAT(#0, #1) |
|
SubStr |
SUBSTRING(#0 FROM CAST(#1 AS INTEGER) FOR CAST(#2 AS INTEGER)) |
|
Position |
POSITION(#0 IN #1) |
|
RightStr |
CASE WHEN LENGTH(#0)<#1 THEN #0 ELSE SUBSTR(#0, (LENGTH(#0) - CAST(#1 AS INTEGER) + 1)) END |
|
ToString |
CASE WHEN '[$P1]' = '' THEN CAST(#0 AS TEXT) WHEN '[$P1]' = 'ddd' THEN TO_CHAR(#0,'Dy') WHEN '[$P1]' = 'mmmm' THEN TO_CHAR(#0,'Month') WHEN '[$P1]' = 'mmm yyyy' THEN TO_CHAR(#0,'Mon YYYY') ELSE TO_CHAR(#0,'[$P1]') END |
|
ToNumber |
cast (#0 as numeric) |
|
LastPosition |
CASE STRPOS(#0, #1) WHEN 0 THEN 0 ELSE LENGTH(#0)+2-STRPOS(REVERSE(#0), REVERSE(#1))-LENGTH(#1) END |
|
InitCap |
(UPPER(SUBSTR(#0, 1, 1))||LOWER(SUBSTR(#0, 2))) |
|
EndsWith |
CASE WHEN SUBSTRING (#0, LENGTH(#0)-LENGTH(#1)+1, LENGTH(#1))=#1 THEN 1 ELSE 0 END |
|
BeginsWith |
CASE WHEN SUBSTRING (#0, 1, LENGTH(#1)) = #1 THEN 1 ELSE 0 END |
|
ConcatBlank |
(#0#< || ' ' || #*#>) |
MicroStrategy uses the analytical engine to implement the string functions shown below.
|
Function |
Examples |
Usage |
|---|---|---|
|
Split |
Split(Subject, Delimiter, Index) |
Splits the subject into substrings according to a separate string (which can be either a string or a regular expression) and returns the index string occurrence |
|
ConcatAgg |
ConcatAgg(ValueList) |
Returns a concatenated string consisting of a list of values. The elements of the string are comma-separated by default, but this can be changed. |
|
TitleCap |
TitleCap(String) |
Capitalizes the first letter of every word separated by a space (space, tab, etc.) in a string |
|
Match |
Match(Subject, Regex) |
Attempts to match the string subject in its entirety against the pattern Regex and returns the first occurrence of the capture groups |
User-Defined Functions
PostgreSQL supports user-defined functions (UDFs), which allow users to extend the SQL language by writing their own functions or installing UDF packages from third parties.
Examples of Passthrough User-Defined Functions
MicroStrategy supports any UDF defined on PostgreSQL through the use of passthrough functions, such as ApplySimple. Using these functions, MicroStrategy generates SQL call UDFs just like any other functions provided by PostgreSQL. Different passthrough functions are used depending on the context used in MicroStrategy. The syntax and usage of these functions are listed as below.
| Function | Syntax | Usages |
|---|---|---|
|
ApplyLogic |
ApplyLogic(Pattern, Arg0,Arg1,...,ArgN) |
ApplyLogic is a pass-through function that builds a SQL logical expression based on the input string of database-specific SQL syntax. Arguments may be substituted into the specified SQL pattern. This is a logical function. |
|
ApplySimple |
ApplySimple(Pattern, Arg0,Arg1,...,ArgN) |
ApplySimple is a pass-through function that builds a SQL expression based on the input string of database-specific SQL syntax. Arguments may be substituted into the specified SQL pattern. This is a single-value function. |
|
ApplyOLAP |
ApplyOLAP(Pattern, Arg0,Arg1,...,ArgN) |
The ApplyOLAP function is used to define compound metrics. It is used with database-specific OLAP functions such as Rank(). This function is an OLAP function. |
|
ApplyComparison |
ApplyComparison(Pattern, Arg0,Arg1,...,ArgN) |
ApplyComparison is a pass-through function that builds a SQL comparison expression based on the input string of database-specific SQL syntax. Arguments may be substituted into the specified SQL pattern. This is a comparison function. |
|
ApplyAgg |
ApplyAgg(Pattern, Arg0,Arg1,...,ArgN) |
The ApplyAgg function is used to define simple metrics or facts by using database-specific, group-value functions. It is a group-value function. |
This function can be used in a MicroStrategy expression using the ApplySimple function as follows:
ApplySimple('Find_Text(#0, #1)', [Search String], [Pattern])