Workbook Complexity

To ensure optimal performance and usability, it is recommended that reports be started at higher levels of the chart of accounts. This approach provides high-level details and enables drill-down on xpna.MEMBER or drill to transactions on xpna.DATA for more specific information.

Complexity within a workbook is tracked by xpna based on the number, type, and usage of xpna formulas. Warnings are displayed if complexity becomes excessive, which may impact performance. If a threshold is reached, an error message will be shown.

The objective is to maintain lightweight, fast reports and to use complexity warnings to help prevent performance issues in Excel. Excel has hard limits that, if exceeded, may cause custom functions to break or Excel to become unresponsive.

Note:

Calculations that exceed Excel's limits cannot be stopped by xpna. Care should be taken to avoid reaching these limits.

Performance

By default, the xpna.DATA formula is written by referencing xpna.MEMBER cells for each argument. This method is preferred for most workbooks. However, as workbooks increase in size and Excel performance is affected, it is recommended to pass only the member key to each argument, rather than the entire xpna.MEMBER cell.

This can be accomplished in the following ways:

• Passing .key:

  • Rich entity cards for xpna.MEMBER cells provide additional properties using dot notation.
  • Passing <CellRef>.key instead of <CellRef> can significantly improve performance.
  • For example:
    • =xpna.DATA(D$5,$C6,$D$1,$D$2)
    • becomes
    • =xpna.DATA(D$5.key,$C6.key,$D$1.key,$D$2.key)

• Direct Key Reference:

  • The greatest performance gain is achieved by referencing a cell that contains only the key value, rather than the xpna.MEMBER cell itself.
  • For each member cell in accounts, a cell to the left can contain =B5.key. For columns, a cell above can contain the key, and for selector cells, a cell to the left can be used.
  • The xpna.DATA formula should reference these key cells instead of the xpna.MEMBER cells.
  • For example:
    • =xpna.DATA(D$5,$C6,$D$1,$D$2)
    • becomes
    • =xpna.DATA(D$4,$B6,$C$1,$C$2)

Note:

The direct key reference method should be used only if the .key method does not resolve performance issues in very large workbooks. The .key method typically provides the greatest net performance increase.

Reporting Tips

• Templates:

  • Reviewing the built-in xpna templates is recommended to understand effective report layouts and navigation.
  • The Discover Templates button on the Home page of the xpna Task Pane can be used to insert templates into the current workbook.
  • Templates typically start at higher levels of the chart of accounts, enabling easy navigation and drill-down.

• Member Selectors:

  • Duplicate worksheets for each department, entity, or region are not required, as data is securely stored outside Excel.
  • An xpna.MEMBER can be used as a selector at the top of the report and connected to xpna.DATA cell formulas.
  • Changing the selector updates the report to reflect the selected department, entity, or custom hierarchy.

• Chart of Account Levels:

  • Most xpna reports utilize the chart of accounts. Maintaining a concise and well-layered chart is beneficial for effective reporting.
  • In the expenses section, source systems may not group accounts beyond major categories, resulting in a single level under Expenses.
  • Hierarchy Management can be used to create new groupings under Expenses, collating similar accounts.
  • This enables users to start with a summary expense line item and drill-down or roll-up through new groupings for further analysis.