Cascading Time Granularities
When the client is not happy with the pivot report layout options on cubes in Excel, my usual reaction is “OK no problem, then we’re going to use cubefunctions instead”. This went well until recently: My client told me that he wanted a report like above: Years totals first, followed by quarters & months totals, but with growing number of years! All nicely close together, no gaps. Normally I don’t mind cubereports with dynamic table length (will blog about this later), but this is basically 3 dynamic reports under each other (yes they are still alive and will probably stay 😉 ):
So it was time to test what Chris Webb has talked about in this blogpost: Using Power Query as a report authoring tool in Excel. The idea was to use Power Query’s append-function to attaching 3 different report: Year, Quarter, Month. They could then have different lengths, the append-operation would seamlessly stitch them together. The connection to the cubedata was easy enough, thanks to his brilliant step-by-step guide. But then there were some challenges to solve:
- How to pivot on multiple measures/columns: As I’ve imported all data into one flattened table, my 4 measure columns stand side by side. As my target is to split the columns by Productmanager I would pivot on that column. But then I can only choose one column for the values-section. How to overcome this is described in this post .
- How to connect these Power Queries with the slicer in the workbook that centrally control the rest of the pivot reports in there?
- How to format the report in a way that the formatting will sensibly adjust to increasing number of years. I’m using conditional formatting and need to make sure that this will stick.
Solution to 2)
I thought that this would be easy going by using the named-range functionality that works fine in Excel: Harvest your slicer selection like by using CUBERANKEDMEMBER. But instead of writing this formular in a cell/a table, write it into a named range that could be addressed by Power Query. Seamless integration without having to duplicate values. But unfortunately I couldn’t get it working: The named ranges that contain cubefunctions don’t show up in the Power Query workbook selection.
Workaround: Create pivot tables that contain the slicer attributes and connect them to the slicers. So the pivot table returns the same values than the slicers. But as these cannot directly feed into Power Query, you need to create an additional table that collects these values:
If you think of using the slicer selections as input for a filter in PQ (Table.SelectRows) – possible, but not the quickest way from my experience. I’m always using the table in a merge-operation that only returns matching values. This is quickly done even by using the UI and you don’t have to bother about Boolean logic in order to combine multiple filter arguments.
Solution to 3)
Target is to make the format adjust once you select more of less years (reduced to 2 years here):
Conditional formatting on fixed elements like the symbols is quite straightforward, but I’m always fighting with the “this cell relates to another one in a certain way”-things like this one: Put a line above the cells when the value in column B changes in relation to the previous row (Granularity change). I’ve used this technique that worked fine so far in this case: Selecting the cell in which the formatting should apply (B2). Then check conditional formatting – new… – use a formula…. – and type in this string:
=$B2<>$B1
In this case a top-line will be applied if B1 is different.
Apply the format and save. Now the conditional formatting should show on this cell. Next step is to apply this to the whole table. Enter the cf-menu again, select – adjust, select the rule and change the cell in “will be applied to” to the whole table:
The settings of the Result-table have to be adjusted like this: Table Tools – Design – External Table Data – Data Range Properties:
I’m attaching the file: MusterPQImportSSASTabularV6.xlsx
But you need to connect to your own cube first before you can slice and dice.
Enjoy & stay queryious 🙂
Not used to cubefunctions? Stay tuned then for my next blogpost with an introduction of them.