Sometimes we want to let report users modify the structure of a report but to govern exactly what they can and can’t do. While Grids can be restricted at a granular level to enable and disable functionality, that approach still requires some degree of product knowledge by the user.
XLCubed provides the XL3SetProperty() formula, which enables manipulation of many of the core objects such as Grids, Slicers and Small Multiples. It means report users can have simple slicer selections to change the structure of a report, what’s being displayed in a chart, or to vary the chart type. It gives flexibility within the report, but requires no product knowledge from the end user which can be crucial when delivering web reports on a widespread basis.
One common example of usage is where the hierarchy to be viewed in a grid needs to change based on the measure a user selects (depending on the structure of the cube some measures may not be applicable for all hierarchies). Typically that would need to be handled in two Grids, but we can use XL3SetProperty to bring this together, and also to give user choice on the associated Small Multiple Chart view.
The final published report is shown below:
If the user selects an “Internet” measure, we show Customer Geography on rows, whereas a “Reseller” measure should show Reseller Type on rows. The same logic applies to the Small Multiple chart. In the screenshot below, the user has selected Reseller Gross Profit as the measure, and ‘Stacked Column’ as the chart type. You can see that the hierarchy on rows has been switched, as has the split within the individual charts, allowing the user to easily vary their view of the data with simple button selectors.
This is implemented through the following key points:
- A lookup table in Excel to determine what hierarchy is applicable for each measure
- An Excel list showing the available chart types – this is used in the Chart Type slicer
- The chart slicer outputs its selection into cell $AG$10
- The measure slicer is linked directly to the grid and the small multiple, but also outputs its selection to an Excel cell ($A$B4)
- A vlookup determines which hierarchy to use based on the selected measure
- Three XL3SetProperty() formulae now control what is displayed based on user selections:
- $AB$7 – sets the grid rows
- $AB$8 – sets the small multiple columns
- $AB$7 – sets the chart type
The approach gives a deep level of access to the key XLCubed reporting objects, and enables controlled flexibility within web and mobile-delivered reports. No programming is needed, just a mid-level understanding of Excel itself, and XLCubed.