Background

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:

=D12

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)

 

Discussion

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). 

 

Enjoy!