Documentation for CXO-Cockpit Reporting Suite 4.2, click for CXO-Cockpit Reporting Suite 4.1 Documentation or check the CXO-Cockpit Website.
Calculated Members
From CXO Cockpit Reporting Suite Documentation
Contents |
Introduction
Cube Calculations are a powerfull way to enrich data
Cube Calculation calculations are done in CXO-Cockpit when
- The calculation is not done in the source system
- In some organizations, adding a calculation in the source system can be a very cumbersome or time consuming tasks, due to for example compliance rules.
- The calculation can’t be done in the source system
In some systems (such as HFM) the calculation options are limited (such as dynamic rules in HFM) which can’t deal with more complex or advanced rules - The calculation can be done much easier or more maintenance friendly in CXO
In CXO we have the most powerful and maintenance friendly language (MDX) available.
Cube Calculations
What are Cube Calculations Technically?
- Cube Calculations are Performed in the OLAP Cube
- Calculations are made on the Fly when a Report is opened
- Cube Calculations are written in MDX
- Calculated Members are stored:
- In the cube for Source Systems that are connected through an Integration Adapter
- In CXO-Cockpit for Free Models, the MDX calculation is applied at the moment of executing the query
Attributes
Basic Attributes
| Attribute | Type | Mandatory | Description |
|---|---|---|---|
| Dimension | Pick List | Yes | Technical Dimension Name |
| Name | Text | Yes | Technical Name of Calculated Member |
| Calculation | Text | Yes | Actual Calculation. Written in MDX (Press Button to Edit Code) |
| Language 1 | Text | No | Description of Calculated Member |
| Account Type | Pick List | No | What kind of account (Revenue? Expense? Asset? or Liability?) |
| Comment | Text | No | Textual comment on Calculated member (aimed at Captain) |
Advanced Attributes
| Attribute | Type | Mandatory | Description |
|---|---|---|---|
| Calculation Order | Number | Yes | Solve Order of calculations, lower numbers are calculated first and are available for use in later calculations |
| Language 2 | Text | No | Description of Calculated Member in second language |
| Language 3 | Text | No | Description of Calculated Member in third language |
| Visible | Check Box | Yes | Select to make the calculated member visible in the Point of View? |
| Parent | Pick List | No | Enter Parent of Calculated Member |
| Member Order | Number | No | Used for sorting within the Point of View |
| Times Used | Number | Yes | Displays how many times the calculated member is used (Press button to view where used) |
Best Practices
Examples of cube calculations that are often used
- A rolling 12 month total for profit and loss items
With cube calculations we can set this up in a very dynamic way - A profit and loss as a percentage of Sales or a profit and loss per FTE
We can calculate these percentages for all lines in the profit and loss in one formula - A Year-on-Year growth percentage
Examples of all of these calculations are available in reports in our on-line demo environment.
In addition, the cube calculations are also often used for simple calculations such as the sum of accounts or entities.
For an overview of supported MDX functions check the Microsoft website:
http://msdn.microsoft.com/en-us/library/ms145970.aspx
