We’ve been asked a few times in the last couple of months if we can build a ‘Previous / Next’ selector for date hierarchies, which allows the user to quickly navigate sequentially through months or days. The answer is of course ‘yes’, otherwise it would be a very short blog..
One of the key strengths of XLCubed is it’s tight integration with Excel, and it means that with some creative thinking the answer is very rarely ‘no you can’t’. Here we use a combination of our slicers, the xl3membernavigate function, and standard Excel formulae to produce a very effective selector for just this scenario.
A working example of this which connects to the sample bicycle sales local cube which we ship with the product is available here or you can view the online demo here.
There are a couple of key things to note with this approach:
1) Slicers are typically populated direct from the cube, which makes them very flexible and dynamic. However a less well known aspect is that slicers can be driven from an excel range, and in this case that’s what we’ll be doing.
2) XL3MemberNavigate(). A fairly new formula which allows you to traverse a hierarchy dynamically in a multitude of different ways. Here we just scratch the surface.
To begin with we need to prepare a range of cells in Excel to base the slicer on, in this case the months, and we also need to ensure it’s dynamic and can change with the underlying data structure. We need to prepare a table of similar structure to the below.
Cell B2 is the selection made by the user in the slicer, which we’ll come back to. The other columns in the table show:
Description:
Logical description of what the row is
Month:
The month available for selection, determined by whatever the user chooses in the slicer, and the Xl3MemberNavigate formula (Insert Formula – Member Navigate) .
Checked Month:
Validation checks on the month to cater for when the first and last available months are selected.
Slicer Display:
what will be displayed in the slicer dialog for user selection.
The first month uses MemberNavigate to get the first available month. This is very straightforward in the MemberNavigate dialog, and will insert a formula in this syntax: XL3MemberNavigate(1,”[Time]”,”[Time].[Month]”,”FirstMember”). Last month is achieved the same way, but using ‘lastmember’.
Previous and Next are again achieved using MemberNavigate, this time the syntax will be: XL3MemberNavigate(1,”[Time]”,SlicerData!$B$7,”Previous”).
Displayed month is simply what the user has chosen in the slicer.
Adding the slicer:
Add a slicer from the XLCubed ribbon (or insert slicer menu in 2003). On the selection tab, choose ‘slicer range’ and select C5:D9 on the table shown above. Then set the slicer Type to be buttons. Lastly, on the settings tab, set the slicer to update cell B2 on the SlicerData sheet.
Optionally, you can also name the slicer and choose to show a title bar, as we have in this example.
On inserting the slicer, you’ll need to resize the control itself, and possibly also the size of the buttons if the data member names are long.
You should now have a slicer which enables Prev/Next selections, along with first and last.
Using the slicer in a report
The slicer isn’t currently connecting to anything, or changing filters within a report. To do that, as it’s not directly connected to a hierarchy in the same way as a standard slicer, we need to go via the excel cell which it updates. So any XLCubed grids or formulae need to reference the cell which the slicer outputs its selection to, in this case in this case SlicerData!$B$2.
In our example we’ve just connected one grid, but there can be as many as required. Our example also gives some sales and costing detail for the main product categories. We also use in-grid sparklines to give a feel for the trend, and these can be drilled or sliced and diced in the same way as a standard grid.
The working example can be downloaded here, or a similar version published to XLCubedWeb used online here.
