xpna.STATIC FUNCTION
Notes:
Static Reports is currently in feature preview and as such documentation for this feature is still work in progress
Usage
The STATIC function is the in-workbook definition for an xpna Static Report. It is used to store the report layout by referencing the member ranges that act as rows, columns, and slicers.
The function is inserted and maintained automatically by xpna when a static report is created or edited. It is not intended to be typed manually.
Syntax
| Argument | Description |
|---|---|
| row | The range of MEMBER cells that defines the row axis of the report. These cells determine which members appear as row labels. |
| col | The range of MEMBER cells that defines the column axis of the report. These cells determine which members appear as column headers. |
| [slicer] | A single container range that covers all slicer member cells. Any cells inside this range that are not MEMBER cells are ignored, and only member cells contribute to the report filter context. (optional) |
Note:
The first two arguments (
row,col) are always required.The
slicerargument is optional and defaults to the anchor cell when omitted.
Anchor cell
The cell containing the =xpna.STATIC(...) formula is the report anchor cell.
- The report definition is stored in the formula arguments.
- The cell address is used by xpna to identify the report within the workbook.
- When the report layout changes, the formula in the anchor cell is rewritten with the updated ranges.
Slicer behaviour
- Each slicer is stored as a selected filter range placed outside the row and column ranges.
- When multiple filter ranges are present, the smallest rectangular range that covers all of them is stored as the
slicerargument. - Cells inside the slicer container range that are not MEMBER cells are ignored.
- Filter cells can be selected as a single cell or as a larger range, and the selected range is preserved.
- If all slicers are removed, the
slicerargument is set to reference the anchor cell as a placeholder.
Common Problems
- The function will return an error if a valid row range is not provided.
- The function will return an error if a valid column range is not provided.
- Only MEMBER cells contribute to the report context, even when other cells fall inside the slicer container range.