Today’s blog will show you a really quick and easy way to format your grid to show different display units.
This approach is ideal for dynamic Grids where the size of the values can vary considerably based on the selected filters, or where the user has drilled down to lower levels in the data. For example, if country level numbers are in hundreds of millions, but customer level numbers are in hundreds or thousands, it can be useful to have the ability to quickly change the display units.
Using this method, you can switch quickly to using different formats. In our example we want to give the user the option to display the measure Reseller Sales Amount as Units, Thousands or Millions.
You can see we have a slicer to the right of the grid giving the user the choice of how to display the figures.
The slicer is based on an Excel range and is not directly linked to the grid.
A couple of things to note are the ‘Update range’ and ‘Activate XL3Link’ slicer settings are checked but more of that later.
‘Enabling Update Range with selection’ means that the slicer choice is written to a cell in the workbook – in our example it’s cell $M$17 (shown in red in screenshot below) – currently Millions are selected.
The other thing to note from the screenshot above are N13:N15. These are a series of IF statements like below which will determine how the format sheet is updated:
As M13 is not equal to M17 the Units row will be set to ‘NOT SELECTED’.
The row containing the ‘*’ is the format which we want to be applied to the Grid.
We now need to reflect that in the format sheet. This is done by first adding three rows for the relevant hierarchy, and setting the Excel numeric format column G to units on the first row, and then thousands and millions on the other two.
We can then set the ‘Member’ cells in column E as a simple formula referencing the table shown above. For example, the Unit’s member incell E50 in the XLCubedFormats sheet is set as =Sheet1!N13. The same process is followed for Thousands and Millions.
With the format sheet as shown above, based on the user’s slicer selection, all members will then be displayed with the predefined Millions format as ‘*’ is a wildcard and will match on all the members in the Product Categories hierarchy.
The formatting for Units and Thousands will not be applied – unless of course you have a member in your hierarchy called ‘NOT SELECTED’!
As mentioned previously, our Excel slicer is not directly linked to the grid – we need a way to tell the grid to refresh each time the slicer choice changes.
This is where XLCubed’s ActivateXL3Link and XL3RefreshObjects comes into play.
Let’s look at the XL3ActivateLink first. As you can see from our slicer screenshot above, our slicer is set to activate the XL3Link statement in cell I7.
I7 is set as =XL3Link(XL3Address($K$7),”Set Refresh”,,XL3Address($I$6),TRUE)
As you can see from the screenshot below it sets the target cell I6 to TRUE.
Cell J6 contains the formula =XL3RefreshSheetObjects(I6, “Sheet1”, TRUE).
As I6 is set to TRUE this forces a refresh of all objects in the sheet when the slicer choice changes.
It’s that simple – so the next time your Sales Team want their sales figures displayed as units but the CEO wants to see them expressed as millions impress them with this method!