How to Write a Custom MDX Calculations in Ranet OLAP

1. What are custom MDX calculations?
2. Few words about custom MDX calculations names’ syntax
3. Some peculiarities of custom MDX calculations’ creation
4. How to create a calculated member
5. How to create a named set
6. Example

In the previous blog post, we wrote about custom calculations editor in Ranet OLAP and the options it provides. Let’s see today how to create custom MDX calculations that can be used in pivot table report settings.

What are custom MDX calculations?

To begin with, let’s define custom MDX calculations. Custom MDX calculations are the end-user-defined expressions written with MDX syntax, which can be used to get values anywhere within an MDX query. Custom calculations are limited to the scope of the MDX query, their definitions and values are not stored as cube data, but only in memory.

For custom MDX calculation creation you need to use Ranet OLAP Custom Calculations Editor. It has cube metadata browser and the ability to customize calculations’ formatting. Developers, system administrators and business analysts who have the appropriate qualifications can create custom calculations using all the capabilities of the MDX language. Settings can be saved to the server in a single file storage web-application. End-users who do not have the technical skills can upload and use the created custom computing settings.

More information regarding custom calculation editor you can find in the article Custom Calculations in Ranet OLAP Pivot Tables.

Few words about custom MDX calculations names’ syntax

Before moving to custom calculations themself it is relevant to touch upon two more aspects: custom MDX calculations name’s syntax and some peculiarities of their creation. But one step at a time.

The name of the calculated member is defined as a multi-component identifier and must be unique. Here is a standard custom MDX calculations name’s syntax:

[Dimension].[Hierarchy].[Parent name].[Custom Member Name].

Let’s consider each element in more detail.

Element	Description
[Dimension]	Dimension’s identifier to which the member belongs.
[Hierarchy]	The identifier of the hierarchy in the dimension with which the member will be associated. Dimension and hierarchy are considered together, since the hierarchy always belongs to the dimension, and all members are considered in the context of the hierarchy.   You should specify the full name to create a member in a dimension other than the measure dimension. In other case, the member will be created in the measure dimension.
[Parent name]	The identifier of the dimension member for which the generated member is created to be a child.   In other case the member will be created as the child for the element [All].
[Custom Member Name]	Calculated member’s identifier.

There are no such strict requirements for named sets’ syntax. The name there is defined by a valid string expression, enclosed in square brackets.

Some peculiarities of custom MDX calculations’ creation

Talking about peculiarities, we would like to highlight several points:

1. To create a calculated member as a member of a specific dimension hierarchy, its name must contain the unique name of that hierarchy. Select the desired hierarchy in the cube metadata tree and drag the member into the editable field. Standard syntax for the name of a calculated member looks as following: [Dimension].[Hierarchy].[Custom Member Name];

1. Calculated members can be created at any point in the hierarchy. To create a calculated member as a child of a dimensional member, its identifier must contain the unique name of the parent member. Standard syntax for the name of a calculated member:

[Dimension].[Hierarchy].[Parent name].[Custom Member Name];

1. For indicators, the arguments of an expression describing arithmetic calculations can be any combination of members or fixed numeric values, operators and functions of the MDX language, corresponding its syntax.

How to create a calculated member

Now it is high time to talk about custom MDX calculations writing.

In order to create a custom MDX calculated member, you have to follow these steps:

1. Click Add Calculated Member button on the editor's toolbar;
2. Set the custom calculation unique name:

• [Measures].[Calculated Member Name] for a calculated measure that belongs to the measures dimension;
• [Dimension].[Hierarchy].[Calculated Member Name] for a calculated member of any other dimension;
• [Dimension].[Hierarchy].[Parent name].[Calculated Member Name] for a child calculated member created for an object in the hierarchy.

If the name of the calculated member does not contain the name of the hierarchy, it belongs to the measures dimension by default.

1. Set the expression for calculating the value of the calculated member. You can use cube members, fixed numeric values, operators, MDX functions, and other custom calculations as arguments in the expression.
2. Specify the format string for the custom calculation. Choose its configuration from the list or create it manually;
3. Optional: Set the measure or a set of measures that will be used to define calculated members behavior when processing empty cells (Non-empty Behavior);
4. Optional: Set the background and foreground colors for the cell, if required.

How to create a named set

In order to create a named set:

1. Click Add Named Set button on the toolbar;
2. Set the named set unique name;
3. Create the multidimensional expression defining the named set. It can contain OLAP cube data objects (dimensions, levels, measures, etc.), arithmetic operators, numbers, and functions.

You can create the expression:

• manually, by listing unique member names separated by commas;
• by dragging objects from the OLAP cube browser (they have to be separated by commas);
• by setting a range (the first and the last items have to be separated by a column);
• by using MDX functions that form the set by selecting the objects to include.

If the set contains groups of objects from different hierarchies, you should put the groups inside curved brackets ({}) in the expression.

Example

Let’s review a simple example when we can use custom calculations in the pivot table.

The company offers discounts to its top 50 resellers based on their sales for the past year (in our case, the year 2008). The discount is 20% of yearly sales for resellers who sold goods for more than $ 100,000, and 10% for resellers who have sold goods for less than $ 100,000, but the amount of the discount cannot be higher than $ 35,000.

For this example, we will create three custom calculations:

1.Named Set [Top 50 Resellers].

2.Two calculated members to calculate the amount of the discount: [Intermediate Discount] for calculating the discount based on the reseller’s sales and agreed discount percentage; [Final Discount] for checking if the calculated discount exceeds $ 35,000 and setting up the final discount that can be provided to the reseller.

We hope this post was useful. If you have any questions about creating and using custom calculations in Ranet OLAP feel free to ask us.

Also, we invite you to read our blog post about building and integration of ASP.NET pivot table.