Introduction
| Icon | Description |
|---|---|
 | Create complex MDX Calculations from several available MDX templates quickly with no manual coding. Using the BI xPress MDX Calculation Builder templates, you can add dynamic time calculated members to your cube MDX calculation script. |
Note: This option is available when you are in the SSAS Cube Designer and the Calculations tab is active.
Feature Highlights
- Quickly build calculations from pre-made templates
- 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
Opening the MDX Calculation Builder
Open the MDX Calculation Builder by completing the following steps:
1. Open an Analysis Services Cube in Visual Studio, and then select the Calculations tab.
2. Right click the Script Organizer window, and then select Calucation Builder (BI xPress) from the context menu.
Using the MDX Calculation Builder Wizard
Step 1: Choose a Template
Select a Calculation Template. Select Open Folder to select the folder where your templates are stored. Select Next to continue.
 |  |
Note: The default folder for BI xPress templates is \%Program Files%\BiXpress\SSAS Snippets\.
Step 2: Select Objects
The Calculation wizard allows you to select different types of objects, the following options are available:
| Object Selection | Description |
|---|---|
| The Measures window allows you to select a measure. The measures are displayed in order and grouped by their Measure Group. |  |
| The Hierarchy window allows you to select a hierarchy from a dimension. The hierarchies are displayed under their dimension. |  |
| The Level window allows you to select a level from a hierarchy. The levels are displayed under their dimension hierarchy. |  |
| The Attribute window allows you to select an attribute from a dimension. The attributes are displayed under their dimension. |  |
After adding your desired object(s) select Next to continue.
Step 3: Format
The Formatting step allows you to apply formatting code that can be used within the MDX calculations. The MDX Calculation builder uses conditions for formatting. For example, if you want the calculation results to be display 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 you have selected your properties, select Save Condition to move the condition into the Condition box. Select Next to Continue to the next step.
Note: You can edit your conditions by double clicking a condition to open the condition in the Add Formatting Condition section. After you make your changes, select Update to save the condition.
Step 4: Calculation Properties
Use the Properties page to name the new calculation, set the format of the result (Currency, Percent, etc.) and the measure group.
After selecting your properties, select Finish to add the calculation to the Script Organizer window and display the calculation properties in Visual Studio.
Note: If any formatting was added to the calculation, the script for the formatting will be its respective window.
