Understanding Reporting Members and Calculated Members
  • 4 Minutes to read
  • Dark
    Light
  • PDF

Understanding Reporting Members and Calculated Members

  • Dark
    Light
  • PDF

Article summary

What are Reporting Members and Why to Create Them?

Using the Segment Hierarchy page, you can create user-defined Reporting members used in the Consolidation module. 

The value of the Reporting members can be converted in multiple ways to achieve different calculations and meet reporting requirements. Independently create a Reporting member, assign a currency for values to be converted, choose currency exchange rates to be used for the conversion, and choose the calculation type to be used for the conversion. You can add a rollup child to a reporting member and then define calculation parameters by adding a Leaf Child to the New Rollup Child. An example of a Reporting hierarchy is provided below.

Notes:
  • You can create up to five reporting members. If you would like to create more than five, contact Support. The maximum number of reporting members is 12.
  • Once a reporting member is created, it cannot be deleted.
  • You can modify the settings of the reporting member and use it for another set of calculations. A reporting member can be modified any number of times.
  • If the Consolidation process is run after modifying the reporting member settings, data is automatically recalculated based on new settings.
  • A rollup child will not be saved without the creation of a Leaf child.
  • The Leaf member becomes a Reporting member, available for selection on the Currency Exception and Calculation Exception pages.


Understanding Segment Properties

The Properties pane contains Segment Properties configuration functionality, where you can define calculation parameters around the reporting member.

Use each of the fields under Segment Properties to map the reporting member to new currency parameters, used specifically to generate a Cash Flow report with a common currency, local currency, or other currency, when multiple entities are assigned to different local currencies.

Currency Type

Select a Currency Type to associate with the new reporting member:

  • AVG - Average Monthly Rate
  • PYAvg - Prior Year Average Rate
  • BOY - Beginning of Year Rate
  • PYBOY - Prior Year Beginning of Year Rate
  • EOM - End of Month
  • LYEOM - Last Year End of Month
  • PYEOM - Prior Year End of Month
  • HistAvg - Historical Average Rate
  • HistEOM - Historical End of Month
  • NT - Not Translated. This means that the value entered in Local Currency will not be converted during the Currency Conversion process. Therefore, you will have a value in Local Currency, but NOTHING in Common Currency. An example where this might be used is a statistical account, which doesn't need to be converted as it is not numerically based.
  • SAME - Same Rate for Override Accounts. Select SAME unless you want the headcount statistical accounts used in Dynamic reporting translated using the FX rate. Selecting SAME will not translate and display the same number in both Local and Common currencies.
  • YAvg - Average Year-to-Date Rate
  • Account based – Uses Account segment properties defined settings. The calculations will be performed when the Consolidation process is executed.

Note:

Currency types are defined on the Add Currency Type page.

Currency Code

Common Currency is selected by default. All values will be converted to the common currency for reporting purposes.

Entity Currency allows you post a Local Currency to the reporting member with the proper Local Currency calculation. This can be used when you need to generate cash flow reports in Local Currency.

Currency Exception

Select Yes if the currency exception rates defined apply for all currency conversions.

Select No if the currency exception rates defined do not apply for all currency conversions.

Note:

Use the Currency Exception Setup page to set currency exceptions for defined reporting members. 

 see Also: Currency Exception Page

Calculation Type

Select Flow to convert all accounts associated with the reporting member to a Cash Flow account. For Cash Flow reporting, select this option so that all accounts will be converted based on the Cash Flow calculation.

Select Account Based to convert all accounts associated with the reporting member to Balance Accounts.

Calculation Exception

Select Yes to set exceptions to the calculation type based on currency conversions for each account and entity combination.

Select No if you don’t want to set exceptions to calculation type based on currency conversions for each account and entity combination.

Do Not Consider Opening Balance

Select if you want YTD values calculated in the first period to exclude the opening balance of the previous period. This is only applicable to budget scenarios. 

Do Not Consider CC DataSelect if you do not want the common currency value to be converted and reported in local currency.
Overwrite Opening BalanceSelect if you want to overwrite the opening balance value and set a historic month’s MTD as the opening balance. This is only applicable to budget scenarios.
Overwrite default CTA

Select if you want to override the default CTA configuration and compute CTA on the selected accounts. You need to define, CTA Sets in order to “Overwrite Default CTA” for the reporting currencies.

Note:
Overwrite default CTA is available for CTA Sets.


What are Calculated Members and Why To Create Them?

Calculated members are calculations in a cube that are evaluated when a query is performed. They can be configured on a dimension in the main or alternate hierarchy. Calculated Members return a single member from a dimension. However, it is generally a combination of multiple members that results in the single member returned. For example, if you want to extrapolate the average unit price for all units in all locations within North America, you would have to include many account and product dimension members to return the one member, which is the average unit price.

Calculated members contain a formula that allows further financial analysis. The formulas for creating calculated members are created using universally accepted formula syntax for accessing multi-dimensional databases known as Rules or Multi-dimensional Expressions (MDX).

Some examples of calculated members are:

[Gross Sales] = [Sales]+[Freight Revenue]-[Sales Returns]

[Net Sales] = [Gross Sales]-[Sales Discounts]-[Sales Deductions]+[IC Sales]

[Operating Profit Margin] = ([Gross Profit]-[Operating expenses])/[Net Sales]

[Shipping Expenses] = {[Total Departmental Expenses],[Shipping Department]}

[Previous Month] = [Current Month].lag (1)

[Previous Quarter] = [Current Quarter].lag (1)

[Previous Year] = [Current Year].lag(1)


Was this article helpful?