Formulas (Members and Measures)

The Formula designer is for building custom members and measures calculations. Custom measures and 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 built on the fly in Discover using "Quick" Calculations. However, the Formula designer offers these major capabilities:

  • Sophistication: The option to design more sophisticated and formulaic logic for designing the calculation
  • Re-usability: The ability to save the calculation as a standalone entity that can then be re-used across multiple reports, dashboards and publications, by multiple users.
  • Click here to review examples of how to build custom members.

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.

Building a Formula

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

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 must be defined; 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.