Version 2021

Using advanced functions in metrics

Steps to use an advanced function in a metric

The basic functions in the Metric Editor constitute only a small portion of the functions available for metric definition. The MicroStrategy Analytical Engine can handle a large number of advanced statistical, mathematical, financial, and OLAP calculations, all of which are available in the Metric Editor. This extensive library of functions allows you to answer the most sophisticated analytical questions about your data. For details on the syntax and usage of a particular function, look up the function in the Index tab of this Help window or see the Functions Reference.

You can also define your own customized functions using the MicroStrategy Function Plug-in Wizard and import them into MicroStrategy Developer. Plug-in functions are indistinguishable from any other functions or operators. For more information on creating and importing custom functions, see About custom functions. See the Functions Reference for more information about functions available out of the box.

Advanced functions contain:

  • Arguments, which are the input values of a function. For example, the arguments of the Average function can be the metric to be averaged.

  • Parameters, which are the properties that determine a function's behavior. For example, you can set whether the Count function is Count distinct by using the appropriate parameter. For details on the parameters of a particular function, look up the function in the Index tab of this Help window or see the Functions Reference.

You can select the advanced function to use in the metric formula and manually add its arguments and parameters. Alternatively, you can use the Insert Function Wizard to guide you through the process.

To use an advanced function in a metric

This procedure includes instructions to create a function manually. Alternatively, you can create the function using the Insert Function Wizard, then continue this procedure at step 8.

  1. Open the Metric Editor. (How?)

  2. If the Object Browser (the list of folders and objects) is not displayed, select Object Browser from the View menu.

  3. In the Object Browser, expand the Functions and Operators folder, and then expand the Functions folder. Expand the appropriate folder, such as OLAP Functions or String Functions.

    For descriptions of the various function folders, see Metric Editor Formula tab: Functions and Operators.

  4. Locate the function to use to define the metric.

    For information on a particular function, look up the function in the Index tab of this Help window or see the Functions Reference.

  5. Drag the function you have selected and drop it into the Definition window.

  6. Add any necessary arguments (the input values such as metrics and facts) of the function between the parentheses. You can type in the arguments or use the Object Browser to find and select them.

    For example, if you are using the NTile function, you must specify which metric to use. The formula looks like NTile(Revenue).

  7. Many advanced functions have unique parameters, which allow you to determine the behavior of the function. The parameters are listed at the bottom of the Definition window. To change the parameters from the defaults, complete the following steps:

    • In the metric formula, click the function name to highlight it.

    • Right-click the function and choose Function NameParameters from the menu. For example, if you were using the NTile function, the menu option is NTile Parameters.

    • Make the necessary changes to the parameters.

    • Click OK. You are returned to the Metric Editor.

    For more information on the parameters for the selected function, look up the function in the Index tab of this Help window or see the Functions Reference.

  8. When you have completed the metric definition, select it under Metric New Metric is defined as, then click Validate. The Metric Editor displays a status message at the bottom of the Definition area, showing whether the new metric definition is correct (Valid expression).

  9. Click Save and Close on the toolbar. The Save As dialog box opens.

  10. Select the folder in which to save the new metric.

  11. Enter the name of the new metric and click OK. You are returned to MicroStrategy Developer.

When naming a MicroStrategy object, you must follow the naming convention rules for your particular database platform. Using a word reserved by your database platform can result in an error. Refer to your database documentation for a list of these database-reserved words.

Related Topic