| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Grid Reporting Overview
Grid reports (i.e. those generated using Design Grid option) are treated as a single object by XLCubed. The report shape will always be rectangular, and some aspects will be familiar to users of Excel's PivotTables. However, much of the power of Grid-based reporting is in the additional functionality not available through native PivotTables. Grids are well-suited to analytical work, but can also be used in more formal reporting, depending on the formatting required. For more flexible layout options, formula reports are also possible.
Grid reports can be modified or designed using the Report Designer, or by simply dragging the hierarchy labels to the axis where you require them.
More detail about your data can be obtained by double-clicking on the members (known as drilling), and many other functions, for example Subtotals and Grid Calculations, can be accessed via right-click menus on the Grid.
Multiple Grids can exist in one worksheet. There are shown in the XLCubed ribbon or toolbar.
Formatting, modification and deletion of Grids should be handled through XLCubed rather than native Excel. Changes will otherwise not be retained when the Grid is next refreshed.
Contents
[hide]Video demonstration
Click here for a video introduction to grids
Grid Components
The main components of the Grid are listed below:
- Header/Filter Area (C3:D5)
- Dimension Labels (C3:C5, D8, C9)
- Selected Dimension Members (B10:C20, D9:H9)
- Data Area (D10:G20)
The XLCubed Grid basic navigation operates in a similar way to Pivot Tables in Excel.
Drilling applies to Members located on either Rows or Columns.
- Drill up / down by double clicking on a Member.
- Drill all currently selected members by right clicking on a Member and selecting the ‘Drill All’ option from the XLCubed menu.
- Keep only the selected Member(s) by right clicking on them and selecting the Keep Only option from the XLCubed menu.
- Exclude only the selected Member(s) by right clicking on them and selecting the Keep All Except option on the XLCubed menu.
- Drag and Drop applies to any Dimension Labels
- Swap Dimensions in the Grid by dragging one Dimension with the cursor to hover over another Dimension.
- Add/Remove Dimensions from Rows and Columns by dragging a Dimension with the cursor to the desired location.
Edit Grid
- At any point it is possible to return to the Report Designer for the current grid by right clicking on the grid, and choosing XLCubed –> Design Grid.
Grid Task Pane (2007/2010 only)
The grid Task Pane can be toggled on and off via the Task Pane menu item in the XLCubed ribbon.
- The task pane enables grid selections and the re-positioning / adding / removing of hierarchies within the grid. Changes are optionally applied instantly or on the Apply button.
- The Task pane displays the content of the currently active grid, and will update accordingly as each different grid is selected.
Saved Grid Reports
When saved Grid Reports are re-opened in Excel, they are not initially connected to their data source and are effectively static data, unless the Refresh Grid on open option is selected in the Grid Properties form. This ensures the load time is kept to a minimum, and only those Grids required for the current session need be activated.
To activate a Grid on a pre-saved spreadsheet you must do one of the following: Double click on any part of the Grid Right-click on any part of the Grid Select the Grid from the Grid ribbon/toolbar
The Grid is now active, and can be used as normal.
Refresh Grids
- Right-clicking on an inactive grid will force a refresh.
- Inserting or deleting rows in a grid will force a refresh.
- Setting the check-boxes in the Refresh panel below will force a refresh. You can uncheck the Refresh grid on open and Refresh when driving cells change boxes if you do not want this behaviour.
- Use the Exclude from display, Keep all except, Keep only functionality of XLCubed to remove rows if necessary.
- If you want to refresh manually you can right-click, XLCubed, refresh gird or choose refresh grid from XLCubed ribbon.
- If the active cell is in a grid you can press Ctrl+Shift+R to refresh that grid.
