MDX Calculation Builder
This feature allows you to create complex MDX Calculations in a few clicks from several available MDX templates quickly with no manual coding. This option is available when you are in the SSAS Cube Designer and the Calculations tab is active. Using the BI xPress MDX Calculation Builder templates, you can easily add dynamic time calculated members to your cube MDX calculation script.
• Quickly build calculations from pre-made templates
• Easily leverage powerful MDX date functions and time calculations such as MTD, YTD, ParallelPeriod, Avg and percent calculations
• Use pre-made template to create dynamic or static named sets
• Eliminates the need for manual MDX scripting
1. To start the calculation builder, open an Analysis Services Cube and click on the "Calculations" tab
2.Next, click the icon on the toolbar or right click in the "Script Organizer" window and click "Calculation Builder (BI xPress)"
Calculation Wizard UI
Once the Calculation Builder window is displayed, the first step will be to choose a Calculation Template. If there isn't a Template Location selected, click on the "Open Folder" icon to select the folder where the templates are stored. The default is \%Program Files%\BiXpress\SSAS Snippets\.
After selecting a template, click the "Next" button to see the first page of the calculation builder wizard.
Calculation Wizard Object Selection
The calculation wizard allows the user to select different types of objects. The following explains each object selection control.
The measures window allows the user to select a measure. The measures are displayed in order and grouped by the Measure Group they belong to.
The hierarchy window allows the user to select a hierarchy from a dimension. The hierarchies are displayed under the dimension they are part of.
The level window allows the user to select a level from a hierarchy. The levels are displayed under the dimension and hierarchy they are part of.
The attribute window allows the user to select an attribute from a dimension. The attributes are displayed under the dimension they are part of.
The calculation builder makes it easy to apply the sometimes cryptic formatting code that can be used within calculations. After the Cube objects (measure, attributes, etc.) have been selected the second to last step in the wizard is the Formatting window. This window is optional. The way the formatting works is by using conditions. For instance, if you want the calculation results to be displayed in BOLD RED WITH BLACK BACKGROUND if the value is greater than 50,000 then you'd enter the following formatting properties:
• When the calculation returns a value that is: > 50000
• Set the font color to: Red
• Set the background to: Black
• Set the font style to: Arial, 8, Bold
After all the properties are selected click the "Save Condition" button. This will move the condition into the grid below the "Add Formatting Condition" section.
The "Formatting Preview" column gives you a preview of what the calculation result will look like if the result matches the condition.
If you need to edit any of the conditions, simply double click on the row in the grid and the condition will be populated in the "Add Formatting Condition" section. After done editing, click the "Update" button to save the condition.
The properties page is where the user will name the new calculation, set the format of the result ("Currency", "Percent", etc.) and the measure group (2008 and above only).
After the properties are set, click the "Finish" button and the calculation will be added to the "Script Organizer" window and the properties of the calculation will be shown in BIDS.
If any formatting was added to the calculation, the script for the formatting will be in their respective windows.