BI xPress MDX Calculation Builder

Introduction

IconDescription
BI xPress MDX Calculation Builder IconCreate 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.BI xPress MDX Calculation Builder Visual Studio Calculations tab2. Right click the Script Organizer window, and then select Calucation Builder (BI xPress) from the context menu.BI xPress MDX Calculation Builder select Calculation Builder (BI xPress) in Visual Studio

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.

BI xPress MDX Calculation Builder Wizard Choose a template
BI xPress MDX Calculation Builder Wizard Step 1

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 SelectionDescription
The Measures window allows you to select a measure. The measures are displayed in order and grouped by their Measure Group.BI xPress MDX Calcuation Builder Wizard Measures window
The Hierarchy window allows you to select a hierarchy from a dimension. The hierarchies are displayed under their dimension.BI xPress MDX Calculation Builder Wizard Choose Hierarchy window
The Level window allows you to select a level from a hierarchy. The levels are displayed under their dimension hierarchy.BI xPress MDX Calculation Builder Wizard Choose Level window
The Attribute window allows you to select an attribute from a dimension. The attributes are displayed under their dimension.BI xPress MDX Calculation Builder Wizard Attribute window

After adding your desired object(s) select Next to continue.

Step 3:  Format

BI xPress MDX Calculation Builder Wizard Step 3

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:

  1. When the calculation returns a value that is:  > 50000
  2. Set the font color to: Red
  3. Set the background to: Black
  4. 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 BI xPress MDX Calculation Builder Wizard Step 4

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.BI xPress MDX Calculation Builder Finish

Note:  If any formatting was added to the calculation, the script for the formatting will be its respective window.BI xPress MDX Calculation Builder script example