Script Editor in Formulate

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.

The Script Editor IDE is accessible from the following designers:

Access the Script Editor

To access the IDE, click Script Mode from the ribbon:

Note: This example shows the Formula Designer ribbon; the Script Mode button is also visible on the ribbon when you are editing Lists, KPIs, Custom Columns, and Custom Scripts.

Script Editor Rules

  • Any logic already added to the canvas will appear in script mode.
  • Once in script mode, you cannot go back to the canvas view.
  • You can add scripts from other locations; for example, copy and paste scripts, or drop files containing scripts, or drop metadata from Microsoft SQL Server Management Studio onto the script window.
  • You can use the Metadata panel (opened from the bottom of the page) to insert the relevant metadata into the script

Script Editor Workspace

  1. From the Script Editor, drag the function you want to use from the Functions listing (red arrow below) onto the canvas (orange highlight).

    2. Note: You can search for the function you want, and hover over each function to see a tooltip about it (green highlight).

  2. From the Metadata panel (blue arrow below), inject the required model metadata into the function.
  3. Use the Style options in the Script Editor Ribbon (blue highlight) to set the design theme and text size. The design theme determines the background color of the script editor, and the text color.
  4. As usual, click Run (green arrow) to test the function; the results are displayed in the Result Preview panel (purple highlight).

Tip: For details about the options on the overhead Ribbon when in Script Editing Mode, see Script Editor Ribbon.

Navigating Custom Formulations in the Script Editor

You can add existing custom formulations to your custom script from the Metadata panel:

Hover Help

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. This also works for date elements and masked elements.

You can also open the formula in its relevant editor by holding Ctrl and clicking on the Item ID.

Script Language

The script language is PQL, unless you are using OLAP, Tabular, or BW models, in which case the script language is MDX. To review a reference library of MDX functions, see Microsoft's MDX Function Reference.

Using AI to Generate Your PQL

You can use the Generative AI integration to generate PQL or MDX code for custom scripts.

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.

Chatbot

You can open the Chatbot in Formulate and use natural language prompts to build scripts that drive formulas, lists, or columns. For more information, see Creating calculations using the Chatbot.

LLM Function

You can use the LLM PQL function (part of the Common PQL library) as part of your script. This function sends a prompt to the LLM to generate some output and then adds the results into your PQL formula. For more information, see LLM (PQL).