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 Script Mode from the top ribbon in the KPI designer (green highlight below):
Note: This button is shown from the Actual Value, Target Value, and Status Value steps (yellow highlight above).
Script Editor Workspace
From the Script Editor, navigate the Functions list (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, click Run (green arrow) to test the function; the results are displayed in the Result Preview panel (purple highlight):
When you add custom members or global parameters from the Metadata panel to the script editor, you can hover over the Item ID to see the item name, its folder location, who created it, the create date, modification date, item ID, and formula type.
You can also open the formula in its relevant editor by holding Ctrl and clicking on the Item ID.
Use AI to Generate PQL
Use the Generative AI 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 AI Formula from the ribbon. In the text field at the top of the Generate Your PQL dialog (or Generate Your MDX dialog for MS OLAP, Tabular, and BW models), 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 it as a script.
- Schemas: the schemas panel exposes the database schema; 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.
- Apply: apply the script to the Query node.
- Cancel: close the dialog without applying any changes.
Warning: When using LLMs, your assets are generated using public domain algorithms. This can produce erroneous and inconsistent or random results. Use at your own risk.