DATAGRIDS

A DataGrid in xpna is a pre-built dynamic query that returns results either as a spill range starting in the current cell or as an Excel table in the specified destination.

Each DataGrid may have different arguments that allow for more fine-grained control of the query results. Most will allow for Filters, which are references to other xpna.MEMBER formulas.

Manage DataGrid

Using the context-aware actions Insert DataGrid or Update DataGrid, the xpna Task Pane will display a simple interface to assist in building out the xpna.DATAGRID formula.

Users can select the DataGrid to show, its destination, required/optional arguments, and filters if applicable.

Note:

When no destination is specified, the DataGrid will show as a spill range from the current cell.

Destination

The destination field has special logic to handle how Excel treats a cell reference versus how it is needed in this case. When Excel sees a cell reference in a formula, it places the value of that reference in that argument, whereas the reference itself is required.

To address this, the special xpna.INFO formula is available, which will be substituted for the cell reference when the DataGrid arguments are updated.

For example, if C6 is selected, on saving the formula in Manage DataGrid, the argument will be updated from C6 to xpna.INFO("address", C6). This functions similarly to the in-built Excel CELL formula with the address type, with one major difference: it also returns the sheet the cell is on, i.e., Sheet1!C6 instead of just C6 as the Excel CELL formula does.

This distinction is important because if it were just C6 and the user was on another sheet in the workbook, the datagrid would appear on the current worksheet in C6, not where it was intended.

Selection Assist

For some fields in this interface, focusing on them enables cell selection mode, indicated by a dotted box icon on the right end of the field. This allows any cell in the workbook to be clicked, and its cell address will be inserted into that field. If the cell is on another worksheet, the sheet address will also be placed into the field.

Note:

The Excel F4 shortcut can also be used on the cell addresses in these fields to cycle between hard and soft referencing types.

Available DataGrids

The following DataGrids are available:

Name	Description
AP Summary	A table of business partners with AP balances. Not passing a slicer will get values for the end of the current month
AP Detail	A table of business partners and their invoices with AP balances. Not passing a slicer will get values for the end of the current month
AR Summary	A table of business partners and their invoices with AR balances. Not passing a slicer will get values for the end of the current month
AR Detail	A table of business partners and their invoices with AR balances. Not passing a slicer will get values for the end of the current month
Account Detail / Business Partner	A table of General Ledger Amounts by account for selected Business Partner
Business Partner Detail / Account	A table of General Ledger Amounts assigned to Business Partners for selected accounts