Calculated Members
Overview
Calculations like financial percentages, nett amounts, overdue amounts etc are often needed in multiple company reports.
A Calculated member is a reusable formula whose value is calculated using other member values, mathematical calculations and logical operations. The member is dynamically calculated at the time of use.
Besides usability, a great benefit of calculated members are that if the calculation changes, all reports using the formula are automatically updated. It also makes maintaining reports much easier.
Resource properties will also indicate which reports and other resources are dependent on this particular calculated member.
Calculation order
The classic symptom of an incorrect calculation order in an analysis is a displayed column or row total of a custom formula or other calculation that doesn't agree with the correct total of the individual values. Similar issues may occur with averages and other aggregations. The detail calculations themselves are correct – they just don't add up. See the examples below for detailed illustrations of the issues.
In these situations, the problem can be fixed by explicitly specifying the calculation order for the detail calculations, as explained below.
In Data Hub, calculation precedence can be made explicit in the definition of a function, custom formula or calculated member, using the Calculation Order combo box.
The following possibilities are available as drop-down values:
Pre-Aggregation – Calculation takes place before members are aggregated (for example, a currency conversion involving a multiplication).
Aggregation – The calculation itself performs aggregation and is therefore a reference point for the other pre- and post-aggregation calculations. Simple formulas using non-precedence operators can use this type. For example, values might be aggregated using a Sum template, or members might be added together in a custom formula (for example, X + Y + Z).
Post-Aggregation – Calculation takes place after members have been aggregated (for example, ratios involving division).
Blank – If the drop-down box is blank (the default), the behavior is the same as the Post-Aggregation setting, above. This means that calculations involving division will total or average correctly; calculations involving multiplication may need to be changed to Pre-Aggregation for totaling or averaging to work correctly. To return the drop-down box to the blank setting, simply select the text or number within it and press DELETE or BACKSPACE.
Note
If finer control of calculation order is needed, a numbered calculation order value can be manually entered in the combo box. Calculations are executed in ascending order as specified by the calculation order assigned to each one. Note that the three pre-defined orders are:
1000 = Pre-Aggregation
2000 = Aggregation
3000 = Post-Aggregation
Example
a calculation assigned a calculation order value of 1500 will be executed after calculations assigned to Pre-Aggregation (1000) and before calculations assigned to Aggregation (2000).
Note
Complex calculations may sometimes result in anomalous results due to calculation order issues. In these cases, contact ZAP support for assistance.
Expandable Calculated Members
Expandable calculated members can now be used in analyses, allowing you to dynamically create a reusable hierarchy (of two or more levels) using the Data Hub calculated members and named set resources. This type of calculated member relies on a calculated member resource and a named set resource, which has to be saved within the calculated member resource.
Creating Expandable Members
Expandable calculated members are created by saving a named set under an existing calculated member. Either of the following two methods can be used:
From the Resource Explorer. Create the named set that will be used, and move it under the calculated member using the Resource Explorer's Cut and Paste right-click options (shown below).
Using the Save As feature. With the named set resource open, click the Save As button on the Homeribbon. From the Save As dialog box that appears, you can expand any calculated member entry and select the Save here option.
Using Dynamic Named Sets to Create an Expandable Calculated Member
Expandable calculated members may be created in the following non-exhaustive scenarios:
For analysts or report designers to maintain their own expandable groups of members, if they don’t have access to modify calculations in a cube
For dynamically grouping and expanding fields that may not have a parent column in the underlying source system
Example
In the example below, a dynamic named set returns all items starting with the letter C.
?image needed
The named set is then summed in a calculated member, as shown below. This calculated member will be used as the parent item for the expandable calculated member.
?image needed
You can create the expandable calculated member by cutting and pasting the named set under the calculated member (via the Resource Explorer right-click options). In this example, the Items starting with C named set is pasted onto the C family calculated member, as shown below.
?image needed
You can then add the parent calculated member to an analysis, where it appears in a "collapsed" state.
?image needed
Once it is expanded, by clicking the + icon, the contents of the named set appear.
Note
Although it is possible to have child calculated members of a parent calculated member in the Resource Explorer, you can only expand the members (that are coming from a child named set resource) of a parent calculated member resource.
This functionality is permitted because it provides a way to categorize calculations. It is also useful in a scenario where calculated members are used in expanded named sets.
You can create multi-level expandable calculated members by including calculated members in named sets, and saving those named sets under calculated members.