Sometimes, reports based on the data in OLAP cube’s dimensions and hierarchies are not enough for efficient data analysis. In this case, using custom calculations can come in handy. One of the core business intelligence features are custom calculations which can significantly simplify the pivot table design by taking into account specific requirements, e.g. complex data filtering algorithms.
Custom calculations are defined by users with the MDX syntax and can be used anywhere within the MDX query. They are limited by the area of the query, exist only in memory and are not stored as the OLAP cube’s data.
Custom calculations editor
Custom calculations editor in both WPF and HTML pivot table controls allows creating calculated members and named sets.
A calculated member is a member created through calculating an MDX query that returns a value. A named set is a set of dimension members or an expression of such set created for multiple use in MDX queries. Both calculated members and named sets can be created using combinations of cube data from the OLAP cube browser, arithmetic operators, numbers, and functions. Besides, calculated members can be based on other custom calculations available in the editor.
Member View tab in the editor allows setting up the custom calculation configuration, particularly:
- Custom calculation name in the format [Dimension].[Hierarchy].[Parent name].[Custom Member Name];
- The MDX expression used to calculate its value.
Member view tab in Custom Calculations editor (WPF pivot table).
Script View tab demonstrates custom calculation settings in MDX.
Script view tab in Custom Calculations editor (HTML pivot table).
The editor also provides options to configure the layout of the custom calculation value in the pivot table, particularly, to modify the format string, the background and the text color. The options are reflected in the script of the MDX expression.
How to use custom calculations
Сustom calculations can be used in the pivot table report settings, just like any other object available through the cube browser.
Named sets are used in the Rows and Columns areas.
Calculated members are used in the Data area. Besides, they can be used in the MDX query if the query is created manually.
Calculations created in the editor can be saved in the centralized file storage on the web application’s server and used later as templates. This is especially helpful if end users lack technical skills. The list of custom calculations can be seen in the Calculations node in the OLAP cube browser.
We will write how to create custom calculations in Ranet OLAP pivot tables for HTML and WPF (with examples) in our next blog post.