This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1. What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has. While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter. Example reports with this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.
Figure 1: Top 10 Users by Events using an Excel Named Set
In the second blog posting of this series, Create a #PowerPivot report filtered by the Top X Users by (Part 2 of 3), I showed how to filter by this same named set in a static fashion. In this third part of this blog series, let’s figure out how to filter by a named set in a dynamic fashion.
“Filtering” by the Named Set (Dynamic)
So the ask here is how can I “filter” by the named set that I just created. One potential idea would be to create a linked table using the PivotTable containing the Named Set which you can then join to and filter by. Alas, for SQL Server 2008 R2 PowerPivot, creating a linked table against an existing PivotTable is not supported. But while you cannot create a direct link table, you can create an indirect link table. That is, use Excel expressions to create a new regular table based on your PivotTable and then create the linked table based on the former. Thanks to Bradley Ouellette from the Analysis Services for reminding me of that trick!
Figure 2: PivotTable created based on the Top 10 Named Set with slicers and regular table (red).
As you can see in Figure 2, a PivotTable based on the Top 10 Users by Events Named Set has been created and a number of slicers have been included like year, state, database and department (server_group). Any time I click on a slicer, a different listing of UserIDs are shown in my PivotTable.
The red oval is a regular table that uses Excel expressions to copy the data from the PivotTable. The Excel expression for the first row of the UserID table with the value 112103102 is simply the Excel expression:
This is repeated for all ten rows of the PivotTable.
Figure 3: PivotTable, Regular Table, and PowerPivot window all noting the same set of UserIDs
Now I have a regular table which I can then create as a Linked Table within the PowerPivot window in Figure 3.
Figure 4: Department / Database Report filtered by Top 10 Users (of all users within the data set)
Based on the top 10 users within the entire data set (all states, all years), these ten users managed to create almost 28.6 million events for the Lab department and General database. But now I want to view the top 10 users for only Washington State. To do this, I can go back to my PivotTable created based on the Top 10 Named Set and click on the State slicer.
Figure 5: Altering the PivotTable created based on the Top 10 Named Set to include only Washington State.
Notice how clicking on the WA state code updated both the PivotTable and the linked regular table with a different set of UserIDs. Now that you have the new UserIDs in your Pivot Table, click on the Update All button
Figure 6: Click on the Update All button to refresh the linked data set within the VertiPaq database.
The linked table within the PowerPivot window will now be updated with the new values.
Figure 7: Linked table within the PowerPivot window updated based on the regular table, based on the PowerPivot table for your Top 10 users named set.
Now you can go back to your original Department / Database Report filtered by Top 10 Users (of all users within the data set). Note you may need to refresh the Top 10 users filter so that way you will only include the ten users. Note the last checkbox is not checked which represents the users that are not in the top 10 list.
Figure 8: Top 10 Users Named Set within the PivotTable Filter
As well, it may be necessary to click on PivotTable Tools > Options > Refresh to ensure any schema changes to the pivot due to the change of the Top 10 are taken into account.
Once these steps are done, you will notice the same Department / Database report has been updated for the top 10 users of Washington state.
Figure 9: Department / Database Report filtered by Top 10 Users (of Washington State users)
While not the most straightforward way of doing this by making use of named sets and regular tables linked to a PivotTable, you can dynamically change your report to filter by different set of Top 10 users by clicking on the slicers (and the extra few steps).
[…] the next blog posting Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3), I’ll go over how to setup the Top 10 named set so it is dynamic in nature so that way you can […]
[…] Read more … Possibly related posts: (automatically generated)“PowerPivot Twins” is itself a double-take! […]
Hi Denny, great stuff. Every time I think of an analysis using linked tables, I’m exasperated when I remember that in a server deployment scenario, typical PowerPivot data consumers won’t be using the add-in, and so won’t be able to use this type of solution (gimme remote linking :)). A common situation that I encounter in constructing dashboards is retrieving the last 12 months activity for the current top ten (customers, products, regions etc.). The last 12 months activity is plotted in a small chart (in Excel 2010 we can use a sparkline).
I’m glad that you’ve gone through this 3-part exercise, because the scenario you describe is not uncommon in the real world. Moreover, the exercise provides food for thought for the next version of PowerPivot/SQL Server.
The idea of creating a linked table against a PivotTable is intriguing. Now that Excel PivotTables have a “Repeat All Item Labels” option, the linked table idea makes even more sense.
Thanks a bunch Colin! By the way, look out for Rob’s next few posts on the topic as he is definitely the Excel expert and may have a better way to do this, eh?!
[…] but it doesn’t look like you can filter them by slicers. Meanwhile, the technique I showed in Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3), you will notice that you can filter by […]
[…] Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3) […]