The expression editor
Whenever you want to edit complex expressions like the iteration or formula of a header or the options of a selector, you must use the expression editor dialog. You can open the editor for all editable elements by opening their context menu and using its edit items. In the current version 2.6 header and their iterations and formulas are editable and selectors and their options and default values are editable.
After you opened the editor for a expression, a new and modal dialog will appear. The dialog shows the current expression (e.g. when you edit an existing iteration of a header, the editor will show the current iteration of the header) and a number of elements and buttons.
The editor is divided into 4 main parts: The toolbar at its top, the model tree at the left side, the preview at the right side and the text field which contains the current expression at the bottom.
Adding or removing elements to / from the expression
There are three basic ways to add elements to your expression: You can use the model tree at the left side of the dialog for a fast access on the model items or you can use the dialogs "Find dimension elements" and "Select elements from model".
Adding elements from the model tree
The tree in the editor dialog is similar to the model tree visible in the model explorer when using the analyzer. It shows all dimensions, their levels and their keys.
By double clicking a level or key you can add it to the current expression. As an alternative you can also select an element and use the "Add" button from the toolbar between the tree and the preview list.
Searching keys
Use the "Find dimension elements" dialog to search keys by their ID, display text or attribute values.
The first option box defines in which dimension you want to search for keys. Note the dialog will always search in the entire dimension, not in a specific level only.
The second box lets you choose whether you want to find keys by their IDs, display text or a text attribute. If you choose "Attribute" here, a third option box appears and offers all text attributes of the dimension. Other attributes, like links to other dimensions, will not be visible.
The following input box holds the search pattern. Depending on the check box "Enable wildcards", you can use the wildcards * (any characters) and ? (and single character) here.
You can use the "Preview" button to create a preview of the result at any time. Note that the preview also depends on the select mode in the toolbar.
Like a couple of other dialogs in the expression editor, this dialog contains a select mode option in its toolbar. It lets you choose to replace your current expression by this, new expression or to modify your current expression with the new one:
- Select: This option replaces the current expression by the new expression defined by this dialog.
- Add: The Add option adds the new expression to the current expression using the JOIN operator
- Keep: Use this option to intersect your current expression with the result of this dialog.
- Remove: This option removes the result of this dialog from your current expression.
Adding elements from the model
The model dialog is similar to the initial panel of the expression editor. It shows the model tree again and allows to add a single level or key from a dimension to you expression. Simply select an element in the tree and the dialog will create a preview for the expression instantly.
In difference to the basic panel and like the find dialog, this dialog also offers a "Select" / "Add" / "Keep" / "Remove" option and therefore also allows to e.g. remove model elements from your current expression.
Loading and saving expressions
Users can store expressions on the server and reuse them at any time. If you need similar expression often, you can use this feature to save time by only defining the same expressions once.
Loading expressions
Use the "Load expression" button from the editor toolbar to show the list of the expression you saved in the past.
The list at the left side of the dialog contains the names of all stored expression. You can load the expression by selecting its name and pressing the "OK" button.
You can also create a preview for the expression before loading it. To execute the expression, select its name in the expression list and use the "Preview" button.
Like the dialogs "Find dimension elements" or "Select model elements", the load dialog also to specify the usage of the expression with the drop down list in its toolbar.
Saving expressions
To save your current expression use the "Save" button from the editor toolbar. This will open the save dialog.
The body of the dialog shows the list of all expressions you saved in the past. Below the list, there is a text box where you can input the name of your new expression.
To save the expression, enter the new name in the text box and use the "Save" button from the dialog.
You can also use this dialog to remove existing expression. Simply select the expression and use the "Delete" button from the toolbar. If you want to replace an existing expression, you must select it first, then delete it and finally save your new expression.
Expression tools
Checking the expression
Use the "Check expression" button from the editor toolbar to check your expression for its correct syntax and return type. If any error is found in the expression, the dialog will show an error after using this button.
The return type of the expression is often limited by the usage of the expression. E.g. a header iteration can only contain expression which return dimension keys.
Optimizing the expression
instantOLAP contains an internal expression optimizer which is used every time an expression is executed. You can also execute the optimizer manually here with the "Optimize" button.
The optimized expression is often easier to read than the original expression because the optimizer eliminates unneccesary code.
Freezing the expression
When build an expression with the expression editor, the result is usually a dynamic expression which can return different results depending on its point of usage and on the database content. E.g. if you create a top 10 expression, which returns the best performing products, the result will change whenever the turnaround of the product changes.
If you want the expression to always return the same result in the future, you can freeze the result with the "Freeze result" button from the toolbar. This button executes the expression and converts into a list of constant values, independent on any database content.
Clearing the expression
Use the "Clear" button to clear the current expression. If you cleared the expression by accident you can recover it using the "Undo" button from the toolbar.
Modifying the result
The toolbar right to the preview list contains buttons for a number of modifying tools. The tools allow to modify the current result by e.g. limiting or filtering it.
Limiting the result
The limit dialog allows to limit the result of the current expression to the first or last X elements.
The option box allows to select whether you want to limit the result to the first ("Select first X elements") or last ("Select last X elements") elements.
In both cases, the following number field allows you to define the number of element you want the result limit to. If the result contains less elements than you specify here, it will remain unchanged.
Use the "Preview" button to perform a preview on the result and the "OK" button to confirm your changes and to manipulate your expression.
Navigating within the result
The navigate dialog allows to navigate within the dimension hierarchy of the result keys. Here you can e.g. display the children or parents of your current result or use attributes.
The first option box allows to choose the direction of your navigation. There are several options:
- Select attribute values: Keys can own attributes which are simple values (like texts or numbers) or relations to other keys. Use this option to show the key attributes.
- Select children of elements: Show the children of all your current result keys. Each key may have no, one or more than one child.
- Select family of elements: The family of a key is the key itself, its children, the children of its children and so on.
- Select leafs of elements: The leafs of a key are only its family members without children.
- Select parents of elements: This operation returns the parent of the result keys. Except the root key of a dimension, every key has exactly one parent.
- Select ancestors of elements: The ancestors of a key are its parent, the parent of the parent and so one.
- Select following elements: Selects the following elements of the result keys.
- Select previous elements: Selects the previous elements of the result keys.
Only if you select the "Attribute values" option, a second option box will appear and let you choose the attribute.
Filtering the result
Filtering the result means to limit it to list number of keys which match the criteria you define in this dialog.
At first you have to decide, which elements of the keys you want to match. There are four different elements you can use for your filter:
- ID: Allows to filter keys by their IDs.
- Display text: Allows to filter keys by their display text. The display text is the text which is shown whenever a key is displayed in a table and can be different to the key ID.
- Key attribute: Keys can own attributes which are simple values (like texts or numbers) or relations to other keys. If you want to filter keys by one of their attributes, use this option.
- Fact: Allows to filter keys by the value a fact returns in the context with the keys.
Depending on the selection you make in this option box, a second option box may appear which allows to select the attribute or fact you want to filter the keys with.
Now you have to select the operator you want to use in your filter criteria. There is list of possible operations:
- EXISTS: This operator only returns keys which contain the selected element (ID, text, attribute or fact value). The element value must exist and must not be NULL.
- IS NULL: This operator tests the element value and only matches for keys where the element value is NULL. This is the opposite operator EXISTS.
- IS EMPTY: This operator only returns keys which contain no value for the selected element, even not NULL.
- =, <, <=, >, >=, <>, LIKE: This operators allow to compare the element value with a second value. The LIKE operator is used for text comparison and allows to use wildcards (* and ?).
If you have selected one of the operators =, <, <=, >, >=, <> or LIKE, a new option box will appear and let you choose the second value for the comparison. Like the first option box, you can choose between ID, display text, key attribute and fact here. Additionally, the box contains the option "constant value".
If you choose the "Constant value" option, a text box will appear and you can input the value here. If you choose "key attribute" or "fact", a option box will appear and let you choose the attribute or fact.
The last option you have for your match is to negate the result. If you check the checkbox "Negate result", the dialog will only return the keys which not match your defined criteria.
You can use the "Preview" button at the button to preview your expression and its result at any time.
Sorting the result
Use the "Sort" button from the preview toolbar to open the sort dialog. This dialog allows to sort the result by a couple of different attributes, e.g. by fact values, key attributes or texts.
The first option box allows to select the element you want to sort the keys by. There are four possible sort elements:
- ID: Sorts the keys by their ID.
- Display text: Sorts the keys by their display text. The display text is the text which is shown whenever a key is displayed in a table and can be different to the key ID.
- Key attribute: Keys can own attributes which are simple values (like texts or numbers) or relations to other keys. If you sort by an attribute which is an relation, the keys are sorted by the display text of the referenced key.
- Fact: Sorts the keys by the result value of a fact.
Depending on the selection you make in this option box, a second option box may appear which allows to select the attribute or fact you want to sort the keys by.
By default, the keys are sorted ascending. Use the checkbox "Descending" to change the sort order from ascending to descending.
The last input field allows to limit the number of result elements when sorting keys. By default, this field contains "0" which means there is no limitation for the result size. If you set this field to a value greater than zero, you limit the result to the first n elements.
The combination of the descending checkbox and the limit field allows to generate top 10 queries. E.g. if you sort a list of keys by a fact, set the sort order to "descending" and limit the number of result keys to 10, you will get a top 10 list of the keys for the selected fact.
Like all sub dialogs, the sort dialog allows to preview your expression with the "Preview" button.
Reversing the result
This is the simplest modification on the result: By using the "Reverse result" button of the preview toolbar, you can reverse the result of the expression.
