Function Parameters

Parameters determine how functions perform calculations. Any function, whether standard or user-defined, can have parameters, which are contained within angle brackets <> in an expression. If more than one parameter is used, they are separated by commas.

Function parameter notations are only displayed in the Developer interface if the parameter settings are changed from the default and you have set your View option to Show Function Parameters.

You define parameters in the Function Name Parameters dialog box, which displays the tabs Parameters, Break By, and Sort By. For steps on how to access this dialog box and how to set function parameters with the Insert Function Wizard, see Accessing and Modifying Function Parameters.

Since every function object has parameters, the Parameters tab is always displayed. If a function has additional parameters, such as BreakBy and SortBy, the related tabs are displayed accordingly. The following subsections discuss the three tabs in more detail:

• Common Parameters
• BreakBy and SortBy Parameters

The details of each function are covered in this guide, including a listing and description of each parameter that is available for the function. All parameters are listed within angle brackets <> as part of the function syntax. To review the details on the parameters available for each function, review the functions provided in:

• Standard Functions
• Operators
• Plug-In Package 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 Geman.

Common Parameters

There are three common Parameter settings for a function object:

• Distinct: is a TRUE/FALSE parameter that allows you to use all values or only the unique values in the calculation.
• Fact ID: forces the calculation to take place on a fact table containing the Fact_ID.
• NULL: is a TRUE/FALSE parameter that determines if the NULL value can be used in the calculation.

Not all these settings apply to every function. In general, all group-value functions display the FactID Parameter setting. OLAP functions do not have any of these Parameter settings, but they may have BreakBy or SortBy parameters.

BreakBy and SortBy Parameters

In addition to the Parameters settings, many functions have BreakBy or SortBy parameters, each of which has its own individual settings:

• BreakBy: The logical level where the calculation of values for an expression restarts. To break by an attribute or hierarchy means to restart counting values for expressions that use relative functions. Examples of relative functions are RunningStdevP, Rank, NTile, and expressions that calculate rank or percent values. The break by level must at the same level of aggregation or a higher level of aggregation used for the expression itself.

  For example, in the report shown below the Rank by Value metric ranks the revenue values. The Rank function for this metric uses a BreakBy of the Customer Region attribute, which means the rank calculation is restarted for each customer region. This ensures that the revenue is ranked by customer region, rather than ranking all of the revenues together across customer regions. While ranking all the revenues across customer regions can be useful, this report uses the BreakBy parameter to focus on revenue comparisons within each customer region.

  

• SortBy: The order of the return values of an expression in relation to the order of the value or metadata object given. A sort by includes whether to sort in ascending or descending order, and which metadata object to sort by. Sort by may also be performed on the value of the subexpression, which is the input argument.

  For example, in the report shown below the FirstInRange metric returns the first profit value in a list of profit values. The FirstInRange function for this metric uses a sort by of the Customer State attribute, which means the first value for each customer state is returned.

  

OLAP functions often include BreakBy and SortBy parameters. For example, Rank has a BreakBy parameter, and MovingAvg has a SortBy parameter.

A few group-value functions (First, Last, IRR, and NPV) are also defined by the SortBy parameter. The First and Last functions are used effectively to calculate subtotals (see Subtotal Expressions).

For example, an inventory report lists the on-hand supply for each day. The report subtotals are the last day's inventory. Creating a user-defined subtotal that uses the Last function provides the last day inventory subtotal. If the SortBy parameters of the function are not set to sort by Day, the function may not provide the correct answer.

Accessing and Modifying Function Parameters

MicroStrategy Developer provides different methods for accessing and modifying function parameters. You can access and modify function parameters from the following interfaces:

• Insert Function Wizard: This interface is used to help guide you in the initial inclusion of a function in an expression for a MicroStrategy object. The Insert Function Wizard can be used to build a function and add it to an expression for metrics, attributes, facts, subtotals, and transformations. For more information, see Adding Functions to Expressions with the Insert Function Wizard.
• Function Name Parameters dialog box: This interface is used to modify the parameters of a function that has been added to an object expression and validated.

To Access and Modify Function Parameters with the Insert Function Wizard

1. Open an object editor for a MicroStrategy object that can include functions in its expression.

   For steps to access the Insert Function Wizard from the different object editors, see the MicroStrategy online help and search for "Steps to access the Insert Function Wizard". You can also see the section Using Functions in Expressions below that discusses the different types of expressions in MicroStrategy.

2. Click the Insert Function button, labeled as f(x) on the expression toolbar.

   

   The Insert Function Wizard opens.

3. Use the Next and Back buttons to step through the pages of the Insert Function Wizard. Each page allows you to modify different function parameters.

   The pages that you see depend on the function that you select.

To Access and Modify Function Parameters with the Function Name Parameters Dialog Box

This functionality is available in any editor in Developer where functions are used in expressions. For more information, see the following sections:

• Metric Expressions
• Attribute Form Expressions
• Custom Group Expressions
• Fact Expressions
• Filter Expressions
• Subtotal Expressions
• Transformation Expressions

1. Insert a function into an object expression and validate the expression.

   You can insert a function by writing the name of the function and all required parameters into the expression. You can also insert a function using the Insert Function Wizard (see the procedure To Build an Expression Using the Insert Function Wizard).

2. Highlight the function name, for example RunningSum, within a validated expression in the expression box.

3. Right-click the function name and select function name parameters, as demonstrated in the image below.

   

   The Function Name Parameters dialog box opens. All parameter tabs for the selected function are available, and settings editable, from this dialog. This example uses the RunningSum function.

   

Examples of Function Parameter Effects

The following examples briefly illustrate the impact of parameters on function execution.

Example 1: Distinct Parameter

Count<Distinct=True>(Order)

In this metric, you modify the default parameter setting to Distinct=True and retrieve a count of only the unique Orders.

Example 2: RunningSum

RunningSum<BreakBy={Quarter}, SortBy=(Region)>(Revenue)

This metric is defined to display the running sum of revenue on a quarterly basis, sorted by region in ascending order. For another RunningSum example and its report, see OLAP (Relative) Functions.