When authoring a report, there are three different categories of calculation elements that can be added to the Matrix Report:
You can drag a Subtotal element below or to the right of elements in the report builder and the Subtotal element will calculate a mathematical sum of the preceding elements. Blank spacers and Custom Headers can be used to isolate specific accounts to subtotal.
As an example, you have four accounts in the rows axis of the report, A, B, C, and D and you want to subtotal C and D only. To get the expected output, place a Blank Spacer between B and C and the Subtotal element after D as displayed below.
The subtotal does not respect the time and level/dimension/attribute rollups that are set at the account level. Go to Modeling > Model Management and click an account type. The subtotal element always produces a mathematical sum of the preceding elements.
You can drag a Difference element below or to the right of two elements in a tier to calculate a difference. By default, the calculation takes the top or left most element and subtracts it from the bottom or right most element and the difference is displayed as a value. You can right-click a Difference element and select Rename to re-label the element for reporting display. You can also change the default properties of a Difference element by right-clicking the element in the report design area and selecting Properties. Here is a description of the properties associated with the Difference element:
- Show difference as a: in this field you can chose how you want the difference calculation to display, as a Value or as a Percent, by selecting the radio button. If you want to display the difference as both a Value and a Percent, drag two Difference elements into the report design area and set one to display as a Percent.
- Reverse sign of difference for Expenses, Liabilities, and Equities- the Difference element by default calculates a mathematical difference of two elements. The mathematical difference does not make sense in specific contexts and this option allows you to change the sign for the result in the Difference element. For example, if you are displaying Fiscal Year 2010 and Fiscal Year 2011, and you place a Difference element after Fiscal Year 2011, the Difference element will display the value for Fiscal Year 2011 minus the value for Fiscal Year 2010. If the expenses decreased from 2010 to 2011, the difference will show as a negative number. Since this is a favorable variance, you can check the box next to this option to show the difference as a positive or favorable variance. This option only applies to the Expenses, Liabilities, and Equities accounts from the General Ledger account tree.
- Subtract: and From: in these sections, you can change the default difference calculation by selecting which elements represent the number subtracted and the number to be subtracted from in the equation. You can include a difference calculation in a formula that calculates a difference on elements that are not included in the report. For example, you could display Fiscal Year 2010 and Fiscal Year 2011 and add a preceding difference element that calculates the difference between Fiscal Year 2009 and Fiscal Year 2008, which are present in the version being referenced in the report, but not displayed on the report itself.
- Connected to Parameter: If a Difference element calculation refers to elements that are tied to parameters, then the Difference element should be connected to the parameter. This will ensure that the Difference elements update as expected. To connect an element in the Difference Properties to a parameter, check the Connected to Parameter check box for the element or elements.
You can drag a custom calculation element into a tier to create an arbitrary formula that refers to elements of the dimension/level/attribute/account that is on that tier. You can use the custom calculation element to add formulas on any row, column, spreadsheet tab or filter on a matrix report. In the custom report formulas, you can use the full set of functions and operators available in the formula syntax.
Once you have added a custom calculation element into the report design area, you can create the formula logic for the element by right-clicking the element and selecting Formula Assistant. See the List of Formula References to learn more about creating ad hoc formulas within your reports.
Just as with the Difference element, you can right-click the custom calculation element and select Rename to customize the label of the element in the report. Unlike the Difference element, you have the ability to specify a code for the in tier calculation element. Here are the options in the Rename dialog:
- Label: You can update the label of the calculation element.
- Code: You can specify a code in a Custom calculation element that you can use to reference this calculation element in another Custom calculation element of the same element type within the report. For example, you could create an in tier calculation for Gross Margin that is referenced in another in tier calculation that represents EBITA.
- You can set the Custom calculation element to display by right-clicking the element and selecting Properties. Here are the properties associated with the Custom calculation element:
- Display As: You can display the calculation in an account tier as a Value or as a Percent. When you place a Custom calculation element in a tier with reporting elements other than accounts, such as dimensions, attributes, time, or levels, you will see the option to display the calculation as a Value, Percent, or the Account's display type.
Be aware that the Display As option is different from the Display As Report Element.
- Reverse sign of calculation for Expenses, Liabilities, and Equities: This check box is only available for Custom calculation elements in non-account element tiers. Selecting this option will reverse the signs of your Expenses, Liabilities, and Equities accounts within the calculation.
This option only applies to the Expenses, Liabilities, and Equities accounts from the General Ledger account tree.