Excel New Feature – INQUIRE

Excel 2013 included some great new features for BI. However, one really cool feature that flew under the radar was Inquire. For all intents and purposes, this is formula tracing at the GYM, on steroids and the only the only runner in the race. It’s a great inclusion to understand the structure of the workbook including dependencies between books (including formula references), record the formulas and document just about anything in the book.

So let’s start with a quick recap of formula auditing. This is still available and shows the how a cell is derived. You typically activate the cell and then press the Trace Precedents (if you want to see what cells are used in the cells formula) or Trace Dependents (if you want to see what other cells have a formula which is dependent on the selected cell). These are in the formulas ribbon. We can see how this works in the following image where the dependencies of cell B7 are shown as B5 and B6 (note the blue line connecting the cells).

When the formula was a linked value from another book, a grid would show to indicate and external reference. In the prior image, the cells A1 and A2 refer to cells in another workbook (called book 2) and so tracing the Precedents would show the following.

Now let’s compare this to Inquire

Firstly, Inquire must be activate as an Excel add-in. Go to File à Options, then chose add-ins from the options pane, then manage click Go from the Excel Add-Ins dropdown.

Ensure that the Inquire add-in is checked. Then an Inquire ribbon tab should be present.

The ribbon gives us many options, but let’s focus on the structure of the work book. If we click the Workbook analysis button, a new window will open which allows us to chose what we want to look at. For example, we could list formulas by selecting the All formulas from the Formulas node in the tree (as shown). Note that all formulas are listed which includes a reference to an external book.

Don’t like the window? We can even export the results for any number of selected nodes (to a new workbook) by hitting the ‘Excel Export’ button (in this window).

We can investigate the relationships (both Precedents and Dependencies) of a particular cell (just click the Cell Relationship button in the ribbon). Here a new window opens with our choices (we can chose the direction and number of expansion levels (for investigation)).

This post has just scratched the surface – there are a few other interesting options to explore (eg file comparisons and passwords), however, this feature should be very useful for tracing (and perhaps more importantly documenting) formulas in books.


Pivot Filtering with Cell Selection and VBA

This post looks at applying data filters in Excel workbooks with sheet ‘click’ functionality for filtering. The purpose of this is to provide a rich user experience for reporting within Excel. With these types of reports, you present some data in a pivot which is used to as a source filter for other parts of your report (or worksheet). Consider the situation below; when you click on one of the country names in the pivot, the chart is automatically updated to filter for that country. Clicking on any other area of the pivot removes any chart filter.

Why would you do this?

Monthly reporting workbooks are often treated as report packs and designed in lieu of an enterprise reporting system. Even where such an enterprise reporting system are in-place, the reporting environments are usually not flexible enough to provide the business what they need for monthly reporting and so the workbooks are bundled together in Excel. These workbooks can be extended to have highly interactive functionality that mimics high-end reporting tools.

While you could achieve these results with a slicer, the automation of the action may provide a nicer experience for the user because it removes the slicer clutter from the page and allows a direct association with the data being investigated.

How to Do IT

Achieving this functionality is conceptually pretty simple (and soon programmatically simple too J), all we need to do is;

  1. Listen for changes in the cell position on a worksheet.
  2. When a change is detected, we check that the change was to our source pivot
  3. Determine the filter value (ie what value was clicked in the pivot) … and
  4. Apply that value to the slicer for the chart.

These items are now discussed individually.

Work Book Setup

In this example, I am using an Adventure Works connection with a pivot table and pivot chart. The pivot table (the one you click a cell on) is called ‘country_sales’. This uses the customer.country hierarchy from adventure works. The pivot chart has a slicer linked to it (and the name of the slicer is ‘Slicer_Counrty’). Note that the same hierarchy must be used for both the slicer and pivot table.

Listening for changes in the worksheet

Each worksheet has its own code module with defined events. An event is a subroutine that is ‘inbuilt’ into Excel and fired when something happens in Excel (for example, when the user changes the active cell). You can see the worksheets module by double clicking the sheet in the project explorer or with a right-click and ‘View Code’ and see the events by selecting them from the dropdown (as shown below).

When you do this (and choose the event SelectionChange), you’ll see the following subroutine added to your module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Now, that this has been added, this procedure will fire every time you change selection cells on Sheet1. It also may be worthwhile to note that the Target parameter refers to the range of cells that’s selected (as part of the selection). Because you can select more than 1 cell, we want to check for single cell selection so we simply exit the sub if there is more than on cell in the range.

If (Target.Cells.Count <> 1) Then Exit Sub

Detecting the Source Pivot and the Active Cell Value

There are some pretty handy functions to determine if the active cell relates to a pivot table, and if it does, that the member name is and what its value is. These relate to the activecell and are the properties




Thus, in order to determine what the name of a pivot table for the active cell we would simply write;

Dim source_pivot_name As String
source_pivot_name = ActiveCell.PivotTable.Name

The name (MDX value) of a column could be determined by ActiveCell.PivotItem.Name

and the (MDX) name of the attribute
determined by  ActiveCell.PivotField.Name

For example, if I click on the France ‘country columns’ in my pivot table, I would get the following values.

ActiveCell.PivotTable.Name “country_sales”
ActiveCell.PivotField.Name “[Customer].[Country].[Country]”
ActiveCell.PivotItem.Name “[Customer].[Country].&[France]”

Note that the pivot values references are to the object model (ie the Pivot object). If the cell your referring to (the activecell) is not part of a pivot table, you’ll get an error. This is pretty easy to catch with some error trapping (see final code).

Assuming that the user clicked on a cell in the pivot (I will leave the checks for the final code) we have all the values that we need and can then set the slicer.

Applying the filter to the Slicer

I have discussed applying how to apply slicer value in VBA in this post. For brevity, I’ll just include the essential items. We simply make a reference to the slicer (SlicerCache) and set its value.

Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
sC.VisibleSlicerItemsList = Array(ActiveCell.PivotItem.Name)

If I want to remove the slicers current filter (when no ‘country is needed), I can do that with this code;


Complete Code

The following code demonstrates the complete solution. I have included updating the ‘title’ of the chart and error traps to determine if a ‘non country’ part of the pivot was chosen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If (Target.Cells.Count <> 1) Then Exit Sub
  Dim source_pivot_name As String
  source_pivot_name = “”
  Dim source_field As String
  source_field = “”
  Dim source_attribute As String
  source_attribute = “”
  Dim sC As SlicerCache
  Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
  On Error GoTo continue
  ‘try and get the active cells pivot table name
source_pivot_name = ActiveCell.PivotTable.Name
‘we can only apply a filter if we are on our ‘selection pivot’
If source_pivot_name = “country_sales” Then
  ‘note the name must go first so we can check for a ‘Row Labels’ Position
On Error GoTo continue2
source_attribute = ActiveCell.PivotField.Name
On Error GoTo continue3
source_field = ActiveCell.PivotItem.Name
‘check we have the correct source
If (Len(source_attribute) > 10 And Left(source_attribute, 10) = “[Measures]”) Or _
(source_field = “” And source_attribute = “[Customer].[Country].[Country]”) Then
‘set to all
Sheet1.Cells(11, 4) = “Category Sales for All Areas”
sC.VisibleSlicerItemsList = Array(source_field)
Sheet1.Cells(11, 4) = “Category Sales for ” & ActiveCell.Value
End If
 End If
End Sub


Adding some interactive functionality to a pivot is a pretty simple exercise which relates to the identification of a cell value and, in turn setting a slicer. This improves the end user experience for excel reporting books.

Viva Excel and black IT J. A little cheeky!

PASS Day 1 – MS Announces Hekaton, PolyBase

Today at PASS Microsoft announces three initiatives for SQL Server. These are;

  • Project Hekaton
  • Support for BIG Data
  • and the integration of self-service BI in office

For those that are actively engaged in Business Intelligence the integration of the xVelocity engine and PowerView into Excel is not surprising, it’s been on the road map for some time and has previously been available through CTP (Excel 2013). There is also the shift to less pervasive BI through additional functionality that is offered in Excel (for example chart prompting and data learning for lists). I think that a lot of analysts or users that are involved in data manipulation will also enjoy using PowerView visualisations and the ability to interact with data in real time will improve data competencies. Clearly Microsoft is delivering on its goal of bringing ‘BI to the masses’ and its flagship is Excel (with inbuilt PowerView for visualisation). Most Importantly the Excel 2013 PowerView SUPPORTS visualisations against OLAP databases so most companies investment in OLAP technologies can be utilised with PowerView in Excel.  The update to the OLAP SSAS engine will occur at some date in the future

For those that are engaged with SQL server, Project Hekaton is an in memory improvement to SQL server objects. The ability to hold these objects in memory will give massive improvements in performance and is targeted towards OLTP environments. The impressive thing about Hekaton is that the technology is embedded into the SQL Server engine and so will become part of core functionality. Additionally the in-memory component (load) can be customised to the database (and workload) so that there is complete control over the workload. In-fact, the engine suggests objects (tables and stored procedures) that should have in memory storage applied. There is no doubt that this will have a major impact on the performance of OLTP systems and reduce reliance on hardware based solutions (albeit the engine consumes CPU).

Finally it has been announced that, in a move to support big data Microsoft it will support its own HADOOP type instance through HDInsight. This is not really surprising given the industries direction at big data (it seems that all vendors have some big data solution). What is interesting from a Microsoft point of view is that the big data component of the engine will support SQL queries. This technology (PolyBase) allows the PDW (Parallel Data Warehouse) relational and big data components to be combined in a single SQL Query.

One noticeable exclusion from today’s keynote was the lack of clarity (or any announcement) around Microsoft’s mobile BI strategy. At last year’s summit, it was announced it would support mobile BI and demonstrated it on an IPad. A similar announcement was not given today, however there are sessions in the summit that specifically address a mobile solution (from MS personnel) and so will be interesting to see this content.

Accessing the Slicer through VBA

There may be times when we want to programmatically control slicers through VBA. For example, we may want to default a date to the current date or set a cost centre depending on who has opened the book. This post looks how a slicer can be controlled through VBA.

In this example, we have added a slicer to a worksheet that uses a date hierarchy as its source. Because, we have included all levels of the hierarchy when the slicer was setup, we get three individual slicers for each level of the hierarchy.

If we look at the settings for the slicer (right click on the slicer and select slicer settings), we can see that the slicer has a name and each level of the slicer hierarchy maintains the hierarchy level name. For example, the Slicer_Dates_Hie below has a level Year, Month and Day. Although we can change the name (for example the name Year in the picture below), the slicer retains the mdx level that the slicer belongs to.

Accessing the Slicer

We can access the slicer through the SlicerCaches object. This is as simple as declaring the slicer cache and referencing it to the name of the slicer we want to use. For example;

Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

Navigating the Structure of the Slicer

Once we have a reference to the slicer we can navigate its structure using SlicerCacheLevels. For example we can determine the number of levels of the slicer and iterate over them with the following code.

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

For Each sL In sC.SlicerCacheLevels
Debug.Print “Level ” + CStr(sL.Ordinal) + ” –> ” + sL.Name
Next sL

Naturally, the level can be accessed through the cache level ordinal to produce the same result. The highest level (year) takes the value 1 which increments for each level from the first level. There is always a level (ie level 1) even if the slicer is based on a single attribute.

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
For i = 1 To sC.SlicerCacheLevels.Count
Debug.Print “Level ” + CStr(i) + ” –> ” + sC.SlicerCacheLevels(i).Name
Next i

Slicer Data Members

We can gain access to the data items through slicer items, as mdx attributes, they have a caption, value and a key (member unique name). For example the year 2011 in this slicer has a value of 2011 and a name (MDX unique name) of [Dates].[Dates Hie].[Year].&[2011]

Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
Set SL = sC.SlicerCacheLevels(1)
Debug.Print “——————————————————————————”

For Each sI In SL.SlicerItems
Debug.Print “Caption –> ” & sI.Caption
Debug.Print “Value –> ” + CStr(sI.Value)
Debug.Print “Unique Name –> ” + sI.Name
Debug.Print “——————————————————————————”


Setting the Slicer Value

Slicer item selection must be set through the visible slicer items list and is specified using an array. For example, we could set the SlicerCache (selected items) to 2011 and 2012 with the following code;

sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]”, “[Dates].[Dates Hie].[Year].&[2012]”)

The name selected must be a data member of the level. If not a runtime error will occur (as below)

Once the values are set, connected pivots are updated immediately

Member Iteration

Members can be easily iterated using the following code;

Dim sC As SlicerCache 
Dim SL As SlicerCacheLevel 
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Dates_Hie") 
Set SL = sC.SlicerCacheLevels(2)
For Each sI In SL.SlicerItems    
 sC.VisibleSlicerItemsList = Array(sI.Name) 


The control of slicers through VBA could be used to provide some very nice personalisation to work books.

NB:  If you liked this post you might be interested in this one.  In it, I discuss setting slicers through cell association to pivot rows.