PQL Editor

The PQL Editor is used to construct PQL expressions which can then be used to drive a range of behaviors. For instance, you can use the PQL Editor to build scripts to create custom columns from a data source in the Data Flow, create conditional processes in the Master Flow, define member level security on data models, and create dynamic formulations and lists than can be used in queries.

Editor Interface

The PQL Editor features a point-and-click interface, emphasizing usability. The editor exposes the model's metadata and a range of PQL functions.

Note that the PQL Integrated Development Environment (IDE) available from Formulate, and used to create custom members and lists, features a slightly different interface to the PQL Editor.

• Click here to learn about the PQL IDE in the Formulate module.

Panels

The PQL Editor features 4 components, from which the expression is constructed:

Script Window: this is where your script is written.

Functions: exposes function libraries that can be easily navigated. The function libraries are organized into a tree structure, with a parent folder which can be expanded to expose the categories of functions within the folder. Each category can then be expanded to expose the actual functions.

The libraries are exposed contextually, depending on the specific capability from which the editor was opened. In the image below, both the Semantic and Common libraries are displayed, but when creating calculated columns in Model, only the Common library will be shown.

Columns: exposes the meta structure of the current data model or table. Depending on the function from which the editor was opened, this may display the data model as a tree structure, with a list of the model's tables which can be expanded in order to expose the columns within. Alternatively, when creating a calculated column for instance, only the columns in the given table will be displayed.

• When working in Model, the columns panel also displays variables that have been constructed in the model.
• When setting Member Security in the Admin Console, the Columns panel also displays any custom columns that have been built in the model.

Elements: exposes the elements within the selected column.

Build an Expression

Expressions are built using drag and drop or double click functionality, as well as intellisense autocompletion (see below).

To select an expression, open the relevant group; either drag the expression onto the scripting window, or double click on it. In the image below, the AddMonths function was added from the Date-Time category.

Drag and Drop

Functions, columns, elements, and variables can be added to the script window by dragging and dropping them onto the window. The dropped function or item will be placed in the current cursor location in the expression.

Double Click

Alternatively, you can add functions, columns, elements, and variables to the script by double clicking on them. Again, the added function or item will be placed in the current cursor location.

Tooltips and Help

To help you find the required functions, tooltips can be exposed for each function in the libraries, and for the library's parent folder and function categories. To see a tooltip, simply hover over the folder, function category, or function. Folder and category tooltips present a short description of the function library or category.

Function tooltips expose the following:

• The function name.
• A function mask showing you what is expected as input arguments.
• The type of value the function will return.
• A brief description of the function.

Right clicking on a folder, category, or function will expose a help menu. Clicking on it will launch the given help topic in online help.

Syntax

Placeholders

In the PQL editor, any required arguments are denoted with a placeholder. For example, the AddMonths function is written:

AddMonths(, )

The placeholders in this function are and , which must both be replaced with the required argument to create expression. For example:

AddMonths([data].[dateKey month name].[March], 6)

String Values

When inserting string values into an expression, the string must be contained within double inverted commas " ". For example, the string function 'Contains' is written like this:

Contains(, )

When inserting your arguments, each argument is placed within double inverted commas:

Contains("Apple", "pp")

• Click here to review the 'Common' PQL functions, including syntax.

Intellisense

Intelligent Code Completion

Many users prefer to type out an expression, rather than using drag and drop or double click functionality. However, the possibility of typos, the complexity of syntax, and knowledge of the available functions can be a challenge when writing expressions. In this case, you can leverage intelligent code completion to assist in finding and writing the required functions.

The script editor features intelligent code completion functionality to help you to write expressions quickly, while avoiding typos and syntax errors. Intelligent code completion is achieved using the auto completion functionality, which is used to auto complete functions directly from the script window.

To auto complete a function, begin writing it and then hit CTRL + Space on your keyboard; this will open a pop-up list of functions that begin with the given text, arranged in alphabetical order. Select the required function from the list.

Color Coding

Pyramid employs color coding in the script editor to distinguish between different components of the expression. For example, function names are written in green, text/ string values are written in orange, numeric values are written in purple, variables are written in blue, formatting is written in orange, and incorrect syntax is displayed in red.