XLCubed has always provided a tree view selector to let users chose items from different levels in a hierarchy. Previously, however, it was only possible to do this directly from a cube-based hierarchy. With the extension of our SQL reporting capability in V7 we found a few scenarios where we wanted to create tree views from non-cube data. This can be easily achieved in Version 7 by using a slicer sourced from an Excel range. This can then be used to drive reports sourcing data from cubes, Tabular models, or SQL as required.
You can also use this method to allow users to choose items from an amended structure of a hierarchy or a limited part of a cube hierarchy and this is what our example below shows:
As you can see we’re going with a food-based theme. This Excel range needs to be in a specific format and so we have our list of slicer choices with the three required columns: key, value and depth.
Here are the slicer choices at the different levels of the hierarchy:
We’re happy with our list so from the XLCubed tab let’s select Slicer and then Excel which allows us to insert a slicer based on the data in our workbook.
At this window we need to tell the slicer where to find the data (slicer range) in our workbook and the slicer type – in our case a tree view.
In our example we are also giving the slicer a name ‘Food and Drink Slicer’ as well as instructing it to write the slicer selection to cell location $J$19.
The resulting slicer looks like this and the user’s choice can then be used to drive any report, ranging from cube-based grids to DAX and SQL tables.