KPI Script Editor

Pyramid’s Formulate toolset enables non-technical users to build advanced business logic calculations, lists, KPIs, parameters, and scripts using a point-and-click interface. The code editor extends this functionality with an integrated development environment (“IDE”) for advanced users to write both PQL and MDX statements, complete with drag-and-drop function libraries and metadata models.

Access the Script Editor

To access the IDE, click the Script Mode button from the top ribbon in the KPI designer. This button is exposed from the Actual Value, Target Value, and Status Value steps.

Script Editor Workspace

From the Script Editor, navigate the Functions listing (green highlight below) and drag the required function onto the scripting window (orange highlight).

From the Metadata panel (blue highlight), inject the required model metadata into the function.

As usual, the execute button (green arrow) is used to test the function; the results are displayed in the Result Preview panel (purple highlight).

Use AI to Generate PQL

Use the OpenAI integration to generate PQL or MDX code. This is useful if you want to generate code quickly, or don't know the syntax, for instance, To do this, click Generate PQL (or Generate MDX for MS OLAP, Tabular, and BW models) from the ribbon (red highlight below). In the text field at the top of the "Generate Your PQL" dialog, enter a description of the query you want to perform.

  • Text Field: enter a description of the query you want to perform and click the arrow to enter your query description and return a it as a script. (red highlight below).
  • Schemas: the schemas panel exposes the database schema (orange highlight below); you can select the dimensions, elements, and views that are relevant to your description to simplify the query,
  • Script Window: the AI-generated script will appear in the scripting window (green highlight below).
  • Apply: apply the script to the Query node.
  • Cancel: close the dialog without applying any changes.

Warning: AI-generated assets are generated from public domain algorithms, which can produce both erroneous and inconsistent/ random results. Use at your own risk.