MicroStrategy ONE

Function Types

MicroStrategy functions are classified into the following types:

  • Single-value functions (also known as Non-grouping or Scalar functions): These functions operate on each individual component of an input variable or argument, resulting in an output element for each component. Examples of this category are simple mathematical operators (+, -, *, /), Abs, Accrint, Ddb, Cos, Ln, Round, Truncate, ApplySimple, and so on. For details, see Single-Value Functions.
  • Group-value functions (also known as Grouping or Aggregate functions): These functions take one or more lists of values as input and generate a single output value for each list. Examples of this category are Avg, AvgDev, Correlation, Count, HomoscedasticTTest, Intercept, Slope, StDev, Sum, ApplyAgg, and so on. For details, see Group-Value Functions.
  • OLAP functions (also known as Relative functions): These functions take multiple elements from a list and return a new list of elements. Each element is related to and dependent on one or more other elements in the list, and the relative positions of elements within the list determines how computation is performed. Examples include Rank, RunningSum, MovingAvg, NTile, ApplyOLAP, and so on. For details, see OLAP (Relative) Functions.
  • Comparison operators: These operators compare single values or lists of values, or compare a list to a threshold value. Examples of this category are Between, Like, Greater than(>), Less than (<), ApplyComparison, and so on. For details, see Comparison Operators.
  • Logical operators: These operators provide basic comparisons and return TRUE or FALSE values based on the evaluation of the formula. This type of operator includes And, Or, and Not. For details, see Logical Operators.
  • Apply functions: These functions provide access to functions and syntactic constructs that are not standard in MicroStrategy but are offered by various relational database management system (RDBMS) platforms. Each of the functions in this category substitutes for one of the function types mentioned above and can be used wherever that type is used. For example, ApplySimple can be used wherever a single-value function is used. For details, see Apply (Pass-Through) Functions.

In this guide, lists of attributes, metrics, values, parameters, etc. within a function are separated by commas. Your separator may vary depending on your number and date format locale. For example, the separator is a comma (,) in English and a semicolon (;) in German.

Single-Value Functions

A single-value function operates on each individual component of one or more arguments, resulting in an output component for each set of input components. Subtraction, addition, division, and multiplication operators (–, +, /, *) are common examples of single-value functions. Other examples include Abs, Cos, Ln, Round, Sin, Trunc, ApplySimple, and so on.

Single-value functions can be used to create facts, metrics, attribute forms, consolidation elements, and transformations.

For example, using a single-value function that operates on four arguments, where each argument is composed of a five-component list, returns five components. In other words, the number of output components is equal to the number of input components in each argument. A simple example using numbers follows:

Using the single-value "+" (addition) function, A+B=C

Where

  • A=1 (an argument containing one component whose value is 1)
  • B=2 (an argument containing a single component whose value is 2)
  • C=3 (the returned value, containing a single output component, whose value is 3)

In the example above, A, B, and C each contain one component. More generally, given variables D and E used as arguments in the addition function, where D and E each contain a five-component list, D+E results in a five-component list. Single-value functions need not take a single value as an argument or even a single argument. Rather, the basic requirement for a function to be categorized as single-value is that the number of output components equals the number of input components of the arguments.

The following two examples illustrate the use of single-value functions in the creation of a transformed fact and a compound metric. Transforming a fact and creating a compound metric are similar in that both use a single-value function to turn one or more lists of values into another list of values. They differ in that, for a transformation, the single-value function must be applied before a group-value function, while in a compound metric the single-value function is applied after the group-value function.

Example 1: Transformed Fact

Avg(Abs([Account Transactions]))

Suppose Account Transactions is a list of the following values: -300.5, -7.7, 900, -80, and 2.2. The single-value function, Absolute, is applied to the list. The result set is the absolute value of each element in the list: 300.5, 7.7, 900, 80, 2.2. It is important to note that the single-value function returns five elements of output for five elements of input. Once the single-value function has been applied, the group-value function, Avg, is applied to produce an average of those values, 258.08. For more information on the Abs and Avg functions, see Abs (Absolute Value) and Avg (Average).

Example 2: Compound Metric

Avg(Revenue){Quarter} - Avg(Cost){Quarter}

In this example, the group-value function Avg is applied to both the Revenue and Cost facts in your data warehouse. First, MicroStrategy uses the list of values for the two input variables Revenue and Cost to generate, using the Avg function twice, two new variables each containing a single value. The two resulting variables are stored as intermediate results. Next, the single-value function "-" (subtraction) is applied by MicroStrategy to subtract one intermediate result from the other, resulting in a single value for the metric. For more information on the Abs and Avg functions, see Abs (Absolute Value) and Avg (Average).

In both of the previous examples, both single-value and group-value functions were used. The next section addresses group-value functions in more detail.

Group-Value Functions

A group-value function takes one or more lists of values as input and returns a single output value for each list. The existence of a GROUP BY clause in a SQL statement indicates that you are using a group-value function.

The most common group-value functions include Avg, Count, Max, Median, Min, Stdev, Sum, Var, ApplyAgg, and so on. First, Last, IRR, and NPV functions also belong to this category, but they have an additional sort by feature (for more information, see Common Parameters). Sort by specifies the order that the values returned by an expression will appear on a report. (For more information on the SortBy parameter, see BreakBy and SortBy Parameters.)

Group-value functions can be used to create simple metrics, nested metrics, and compound metrics, as well as in the calculation of subtotals. The following examples illustrate their use.

Example 1: Average

Avg([Employee Age])

In this example, the group-value function Avg operates on the argument Employee Age, which is a list of the following elements: 27, 35, 32, 47, 43, 40, 30. The function reduces the seven elements of the input value to a single output value of 36. For more information on the Avg function, see Avg (Average).

Example 2: Median

Median([Employee Age])

The only difference between Example 2 and Example 1 above is the fact that the group-value function, Median, is used, instead of Avg. Again, the function reduces the seven elements of the input value to a single output value of 35. For more information, see Median.

OLAP (Relative) Functions

Online Analytical Processing (OLAP) functions are also referred to as Relative functions because each element in a list of values is related to and dependent on one or more other elements in the list, and the positions of the elements within the list determine how computation is performed.

An OLAP function takes multiple elements from a list and returns a new list of elements. Unlike group-value functions, though, the number of elements in the input list and the number of elements in the output list remains the same. Unlike single-value functions, the computation depends upon the conditions set by the BreakBy parameter that defines when the calculation restarts and the SortBy parameter that defines how the list of values is sorted (see Common Parameters).

OLAP functions include Rank, all the functions with Moving as the prefix of the name (for example, MovingDifference, MovingMin, MovingStdev, and so on), all the functions with Running as the prefix (for example, RunningAvg, RunningCount, RunningSum, and so on), and all the NTile functions (such as NTile, NTileSize, NTileValue, and NTileValueSize). ApplyOLAP also belongs to the OLAP category.

OLAP functions are only used in the creation of metrics. The following is an example.

Example: RunningSum

RunningSum <BreakBy={[Customer Region]}, SortBy= ([Customer State]) >(Revenue)

BreakBy refers to the attribute or hierarchy where calculations for an OLAP function restart. To break by an attribute or hierarchy means to restart calculations that use OLAP, or Relative, functions when the analytical engine reaches the next instance of the specified attribute or hierarchy. Examples of OLAP functions include RunningStdevP, Rank, NTile, and various expressions that calculate percent values. To break by an attribute or hierarchy in an expression, you set the BreakBy parameter.

The RunningSum metric computes the sum of the revenue for each Customer Region by adding the revenue for each Customer State to the revenue of the Customer States in the rows above it and displaying the incremented total. The BreakBy Customer Region condition causes calculations to begin again, however, when the next Customer Region is encountered. (Notice in the figure below that the metrics Total Revenue and the Running Sum for Arizona are equal because the calculation for Running Sum has restarted, since Arizona is categorized in Southwest, a different Customer Region than Wyoming, the previous Customer State on the report.) Because of the SortBy Customer State condition, the Customer States are listed in ascending (alphabetical) order, as shown in the report excerpt below.

Comparison Operators

Comparison operators allow you to compare values. Using these operators, you can compare single values or lists of values, or compare a list to a threshold value.

Comparison operators include < (less than), > (greater than), = (is equal to), Between, Contains, Ends with, ApplyComparison, and so on. They are only used to create filters, which limit report data to a subset based on your need.

Example: > (Greater Than)

Revenue > 500000

In this example, the filter limits the states in your yearly income report to those with accrued revenue greater than $500,000.

Logical Operators

Logical operators provide basic comparisons and return TRUE or FALSE values based on the evaluation of the formula. For numeric values, 0 is treated as FALSE, and 1 is treated as TRUE. These operators provide a means to combine data evaluations and comparison operators into complex expressions. These expressions, in turn, can answer questions such as "Which of our regions produced revenue that exceeded a 'success' threshold?"

Logical operators include And, Or, Not, and ApplyLogic, all of which can only be used to build filters where criteria are provided for the inclusion and exclusion of data from a report display or metric calculation.

Example: And

((Revenue - Cost) > 50000) And [Sell-through Percentage] > 25

Built for the attribute State, this filter limits report data to those states where Profit (defined as Revenue - Cost) is greater than $50,000 and the Sell-through Percentage is greater than 25%.

Apply (Pass-Through) Functions

The terms Apply functions and Pass-through functions are interchangeable. They both denote functions in MicroStrategy that provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various Relational Database Management System (RDBMS) platforms. The name "Pass-through" derives from the fact that MicroStrategy passes information to a database which then uses its own functions. (Using the native functionality of your RDBMS via Pass-through functions requires that you know the syntax of your particular RDBMS. That syntax is beyond the scope of this book and will vary from RDBMS to RDBMS.) RDBMS functions, while necessary, must be used with care, since they always bypass MicroStrategy's parsers and validators.

There are five predefined Apply functions that can be used to replace regular or predefined functions of the same type. The functions are as follows:

  • ApplySimple: These functions are used where simple (for example, arithmetic) operators can be used.
  • ApplyAgg: These functions are used where aggregate functions (for example, Sum) can be used.
  • ApplyRelative: These functions are used where Online Analytical Processing (OLAP) functions (for example, Rank) can be used.
  • ApplyComparison: These functions are used where comparison operators (for example, >, =, Like and In) can be used.
  • ApplyLogic: These functions are used where logical operators (for example, AND, OR, and NOT) can be used.

With Apply functions, project designers can customize expressions in the Attribute, Filter and Metric editors to utilize RDBMS functions that are not provided by MicroStrategy.

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.

Example: ApplyComparison Used to Check a Prompted Date

In this example, a table in your data warehouse contains the columns Item, Effec Date, and Term Date (as well as Revenue), as shown below:

Item Effec Date Term Date Revenue

Blouse

06/01/2007

07/30/2007

1000

Jeans

05/30/2007

06/17/2007

500

Gloves

10/01/2007

10/25/2007

150

Leather Shoes

06/15/2007

06/22/2007

750

Winter Hat

11/01/2007

11/08/2007

900

Winter Boots

12/01/2007

12/15/2007

2200

Each row in the table corresponds to an item that was on sale during the time between Effec Date and Term Date. Your objective is to generate a report that lists all items (and an associated metric that you choose) that were on sale on a particular date your user chooses at run time. To generate this report, first create a value prompt named Test Date that allows the user to input a date. Next, using that prompt, create a report filter using the Custom expression box located in the Advanced Qualification pane of the Filter Editor, as shown below.

Even though the filter is validated when you click Validate, MicroStrategy returns an error when the report is executed. The error results from the fact that you are supplying the SQL engine with two attributes and a value prompt, while MicroStrategy is expecting to compare an attribute to the attributes Effec Date and Term Date. In effect, you have a "type mismatch" problem.

In this case, you can use an Apply function. Instead of having MicroStrategy test the date value prompt, you instruct your database to perform the test. It is important to remember that you have chosen to use an Apply function only because MicroStrategy does not have a built-in function to accomplish your task. If an appropriate MicroStrategy function existed, you would have used it instead of an RDBMS function, because the latter does not offer the validating features that MicroStrategy does. (To use Apply functions, you must know the syntax of the corresponding function or operation in the RDBMS you are using.)

To test the date prompt, use a custom expression to pass three values to the database for comparison: the value prompt Test Date, the attribute Effec Date, and the attribute Term Date. All three of these values are passed to the database using placeholders in the form of #n, where n is a positive integer that increases by 1 for each successive item being passed, starting with 0. The first value passed is referred to as #0, the second is #1, the third #2, and so on. The Custom expression in the Advanced Qualification pane of the Filter Editor depicted below shows the syntax needed for this example:

Notice that the syntax is nested. The outer portion of the expression contains the MicroStrategy function ApplyComparison, as well as the MicroStrategy prompt Test Date and the attributes Effec Date and Term Date.

The inner portion of the syntax, which is contained within double quotes, is the database operation #0 between #1 and #2. Code that is passed to the database using an Apply function is always enclosed in quotes, and the arguments that are passed with that code are written as placeholders in the form of #n, with the specific forms of the passed attributes specified by the characters after the "@" sign. In this example, [Effec Date]@ID specifies that MicroStrategy pass the ID form of the Effec Attribute instead of the DESC or any other form that may exist in the database. At run time, #0, #1, and #2 are replaced by Test Date, Effec Date, and Term Date, respectively, so that the database effectively receives the following syntax:

Test Date between Effec Date and Term Date

If the user chooses 06/16/07 as the value of Test Date at run time, the RDBMS reads the table row by row to see if the date falls between Effec Date and Term Date. Whenever 06/16/07 falls between Effec Date and Term Date on a particular row, the item on that row is returned in the result set. In this example, Blouse, Jeans, and Leather Shoes are returned. (You can verify this result by looking at the data warehouse table shown in the beginning of this section.) If your report is set up with Item as a row attribute, those three items appear on your report, indicating that they (and only they) were on sale on 06/16/07.

For additional information about Apply functions, see Apply (Pass-Through) Functions. The syntax of each Apply function as well as examples appear in the sections that immediately follow.

Example: Test Whether Hire Date is in the Current Year

Your HR department requires a list of employees that have been hired during the current calendar year. The following custom expression uses the ApplySimple function to test whether the year of Hire Date is the same as the current year:

ApplySimple ( "datepart(yy, #0)", [Hire Date]@ID) = ApplySimple ( "datepart(yy, getdate())", [Hire Date]@ID)

Each piece of the custom expression is explained below. More detailed information on Apply functions in general can be found in Apply (Pass-Through) Functions. More information on ApplySimple functions, specifically, is found in ApplySimple.

  • The datepart function extracts a specified part of a given date. The first datepart function extracts the year (as directed by yy) from the ID attribute form of the Hire Date attribute. The ID attribute form—as opposed to the DESC or any other attribute form—is specified by @ID.
  • The placeholder, #0, stands for the argument [Hire Date]@ID that is passed to your RDBMS. (Apply functions use your database's computational capabilities instead of those of MicroStrategy.)
  • The second datepart function extracts the year (as instructed by yy) from the current, or system, date. The system date is obtained via the RDBMS function getdate().
  • Your RDBMS extracts the year from both the Hire Date and the system date, with MicroStrategy passing information to it. The container that hands the necessary function to your RDBMS is an Apply function, ApplySimple. In other words, ApplySimple acts as an interface between you and the database, and when the RDBMS returns both year values, they are compared with the = operator. If the year of a particular Hire Date element is the same as the year of the system date, the custom expression statement evaluates as true and that Hire Date attribute element is returned in the result set of your report. If the year of a particular Hire Date element is different than the year of the system date, the custom expression statement evaluates as false, and that Hire Date attribute element is filtered out of the report.

The attribute Hire Date is enclosed in brackets. Any time you type an attribute whose name contains one or more spaces, the attribute must be enclosed in brackets. (The use of brackets around compound object names is standard for many objects in MicroStrategy and is not restricted to custom expressions and Apply functions.)

The above example used an Apply function, ApplySimple. The next example uses ApplyComparison.

Example: Customer City = Call Center Using ApplyComparison

You need a list of customers who live in the same city as one of your call centers. While it is possible to generate this report with a custom expression that does not use an Apply function, this example uses an ApplyComparison function to demonstrate Apply functionality within the custom expression. (For steps to create this report without the use of an Apply function, see the Attribute-to-attribute qualifications section of the Advanced Filters chapter of the Advanced Reporting Guide.)

The custom expression used here evaluates whether one attribute is exactly the same as another:

ApplyComparison ("#0 like #1",
[Customer City]@DESC, [Call Center]@DESC)

Each piece of the custom expression is explained below:

  • The ApplyComparison function is used with RDBMS comparison operators, such as the like operator used in this example.
  • #0 like #1 is the actual comparison, comparing the first argument, #0, with the second argument, #1. Remember that this comparison is done by your RDBMS—not by MicroStrategy.
  • [Customer City]@DESC sets the first argument passed to your RDBMS as the description form of the Customer City attribute, while [Call Center]@DESC sets the second argument passed to your RDBMS as the description form of the Call Center attribute.

The attributes Customer City and Call Center are enclosed in brackets. Any time you type an attribute whose name contains one or more spaces, the attribute must be enclosed in square brackets. (The use of brackets around compound object names is standard for many objects in MicroStrategy and is not restricted to custom expressions and Apply functions.)

Placing a filter that uses the custom expression above on a report that lists the Customer City, Customer, and Call Center attributes yields the results below. (Only a portion of the report is shown. Also, the Revenue metric has been added.)

Notice that the custom expression qualification filter accomplished the goal of returning only data that satisfies the criterion that the Customer City attribute is the same as the Call Center attribute.