Formulas: Custom Members and Measures

Build custom members and measures in the Custom Member Designer. Custom members are user-defined calculations, which are written in either PQL or MDX and can be used to derive values anywhere in the query.

To enable users to configure custom formulas, the custom member designer features a functions library and data model metadata structures.

The custom member designer is accessible from within the Formulate module. Custom members and measures can also be build in the custom member IDE, and on the fly in Discover.

• Click here to review examples of how to build custom members.

Video

• Creating a formula

Language

Custom formulas are written in PQL, unless working with OLAP, Tabular, or BW models, in which case the language is MDX. To review the PQL functions, click here.

Configure a Custom Formula

Custom formulas may be configured from the Custom Member Designer, the Custom Member IDE, or from Discover (as quick formulas).

Custom Member Designer

The custom member designer is an intuitive point-and-click interface, where users can drag and drop the required functions onto the canvas. Here, users are able to build sophisticated formulas without writing any code.

To access the custom member designer, open the Formulate module and select Formula, then choose the required server, database, and data model.

Workspace

The custom member designer workspace features these areas:

1. Tool panel, where a range of functions are accessed and added to the formula.
2. Canvas, where the custom formula is built.
3. Formula ribbon, which exposes a number of useful functions.
4. Properties panel, where the selected block's properties are configured.
5. Problems panel, where any problems with the formula are displayed. If the custom formula fails when tested, the reason(s) will be listed here.
6. Context panel, which indicates where the custom member will be saved.
7. Format panel, where users can change the custom member's format.
8. Execution panel, where users can set the solve order and scope isolation
9. Result Preview panel, where a preview result will appear when testing the formula.
10. Syntax Preview panel, where a preview of the PQL or MDX syntax will appear when testing the formula.

Workflow

To build a custom formula, combine the required blocks and functions to write an expression. For instance, in the example below the Last Element member function has been added.

The <set definition> must be defined; <set definition> must be removed from the expression and replaced with a list block. With the list block selected, the required hierarchy must be selected, followed by the required elements from within that hierarchy. For more information about adding elements to formulas, click here.

Click the Pyramid run button to test the custom formula before saving.

Custom formula expressions are written in PQL, unless working with OLAP, Tabular, or BW models, in which case the language is MDX. To review the PQL functions, click here.

Custom Member IDE

The custom formula Integrated Development Environment (IDE) offers advanced users the option to manually write complex custom member and measure formulations using PQL or MDX, rather than adding blocks and functions to the custom member designer.

To access the IDE, click the Script Mode button (red highlight) from the ribbon; once in script mode, you cannot go back to the designer interface.

From the IDE, drag the required functions onto the code editor, and then inject the required data model metadata and integers into the expression.

Quick Formulas

"Quick calculations" enable users to build "quick formulas" on the fly in Discover. Click here to learn more.

Using and Maintaining Custom Members

Once saved, the custom formula can be opened in Discover immediately, by clicking Quick Discovery in the ribbon. Alternatively, add the custom formula to a data discovery - click here to learn how.

To edit a custom formula, simply open it from the CMS.