The Script Editor is an interface for writing PQL, MDX and SQL scripts. It is available in multiple venues throughout the application for advanced operations. This includes:
- The formulate tools which have a script mode
- Custom Calculations in Model
- Member Security in the Data Source Manager
The language presented to the user depends on the data source, context and functionality.
The Editor is made up of a scripting window, Functions, Columns, and Elements.
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.
Hovering over the function will show you
- A brief description of the function
- A function mask showing you what is expected as input arguments
- The type of value the function will return.
Right clicking on the function will expose a help menu. Clicking on it will launch the specific help topic in online help.
Any required arguments are with placeholder text "<.............>".
Insert the arguments by selecting the required columns , elements or values then click Apply:
The script is then usually added back into the window. and you can continue from there.
In the example below, the BottomPercent function is used to build a custom set of the bottom 10% of Products by Sales. First, the BottomPercent function is added to the scripting window:
A list of all members in the Product hierarchy is inserted in the List argument; the integer specified is 10; and the Sales measure is given as the third optional argument:
Once you've finished writing your script, click Apply.