Javier Guillen posted on September 10, 2011 21:03
PowerPivot currently has no programmatic interface, so there is really no automated way to leverage its capabilities (other than those provided by PowerPivot for SharePoint). However, we must remember that one way to consume PowerPivot data is through an Excel pivot table (there are others, like Excel Cube functions). Pivot tables do have an API, which allows for a certain degree of programmatic interactivity with PowerPivot data.
A client recently asked me if a PowerPivot report could behave in this way: upon expansion of a row lable, a graph should display only the ‘child’ members of the selected row. A screenshot will probably help in explaining the desired effect:
One may be inclined to think this functionality is now possible but keeping in mind that pivot tables do have an API, it was worth giving it a try. As you may have seen on my prior blog entries, PowerPivot automatically generates an interface of measures and dimensions. We can use this interface to dynamically generate the desired effect.
The first thing we must do is generate a second pivot table with countries in the row labels along with a slicer for the ‘Sales Territory’ (the one the user will be clicking on). I call this second pivot table a utility pivot table as it is not meant to be shown to the user, but will allow us to get the job done. It will not only be sliced by the same ‘Calendar Year’ as the original one, but will serve as the source for chart data.
In order to have both tables react to the slicer, we must configure the ‘Calendar Year’ slicer to connect to both. This can be easily done in the slicer ribbon tab:
Now all we have to do is generate a VBA script to take the user selection of ‘SalesTerritoryGroup’ (in the slicer) and filter the utility table as well. First we must pick the right event handler to capture a user click. For this we can use the Worksheet_PivotTableUpdate event handler. Once the event is triggered, we can capture the text on the active cell, and pass that to the slicer on the utility pivot table.
|
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
ActiveWorkbook.SlicerCaches("Slicer_SalesTerritoryGroup").VisibleSlicerItemsList _
= Array( _
"[DimSalesTerritory].[SalesTerritoryGroup].&[" & ActiveCell.Text & "]")
End Sub
|
“Slicer_SalesTerritoryGroup” is the name of the slicer of the utility pivot table. Upon pivot table update, we pass to that slicer a reference to the fully qualified dimension member which the user has just clicked on. The result is a chart that dynamically shows the child countries of the territory the user just double clicked on. This happens at the same time the row expands to show those children in the pivot table itself:
Though the solution may not be perfect (the effect happens not only on expansion but also when collapsing a row), it is still an interesting way to add functionality to a PowerPivot report.