xpna Custom Functions
xpna adds a set of custom functions to Excel, enabling the retrieval and writing of data and metadata to and from the xpna database.
The functions reside in the xpna. namespace and are listed here:
Along with the individual functionality detailed in the links above, using these functions in tandem with the context aware actions in the Home page of the xpna task pane truly begins to unleash their capability.
Example usage
A common workflow with these functions and context-aware actions might begin by laying out the high-level member names for a report with a member of the Accounts hierarchy on rows, and members from the Period hierarchy on columns, along with members from the Scenario hierarchy and Region hierarchy as slicers/filters, like this:
With that layout, the following actions can be performed:
- Select the range
- Use the Convert selection to report action to resolve the members to xpna.MEMBER functions and insert xpna.DATA functions
- Select the xpna.MEMBER cell on rows from the Accounts hierarchy,
Net Profit - Use the Drilldown member action, in an upwards direction, to reveal the children, perhaps several times to go lower in the Accounts hierarchy
- Select the xpna.MEMBER cell on columns from the Period hierarchy,
FY 2023 - Use the Drilldown member action, to the right, to reveal the months
- Select an xpna.DATA cell for a lower-level Account and Month
- Use the Drill to transactions action to investigate the transactions that comprise that number and potentially link through to the original data source to view the journals related to that transaction
- Select the xpna.MEMBER cell on slicer from the Region hierarchy,
Australia - Use the Choose a different member action to select another region to examine