I have a small Excel workbook with two Pivot tables, linking each to their own Analysis Services cube. The purpose of these two pivot tables is to daily compare the bookings and shipments data. This involves making a change to the date filter, making sure that both pivot tables use the same date, and formatting the tables to the content.

I finally broke down, and dove into the macro editor in Excel. Since I had no idea how the macro editor works, I decided to record the actions. When I pulled the editor, this is what I saw:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year]"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year]"). _
        CurrentPageName = _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year].&[2013].&[1].&[01].&[2013-01-03T00:00:00]"
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Transaction Date].[Calendar Year Qtr Month].[Calendar Year]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Transaction Date].[Calendar Year Qtr Month].[Calendar Year]"). _
        CurrentPageName = _
        "[Transaction Date].[Calendar Year Qtr Month].[Date].&[20130103]"

End Sub

The important parts are the reference to the date filters in the cube. These will become driven by a variable containing a date. However, the trick is to change the date into strings, and specifically parts of the date into strings. We need the year, quarter, month, and actual date in two different formats. I used two different functions to accomplish this.

  • DatePart can take a part of the date, specified by the first parameter.
  • Format changes between different data types, like integer to character.

So, to replace the hard coded quarter 4 with the quarter of the date variable, you would use Format(DatePart(“q”,vtDate),”d”). Using these functions, and a few others, I wound up with the following code:

Sub ChangeDate()
'
' ChangeDate Macro
' Update the date in both date boxes to same date (yesterday)
'
' Keyboard Shortcut: Ctrl+d
'
    Dim vcDateName As String
    Dim vcDateYear As String
    Dim vcDateQuarter As String
    Dim vcDateMonth As String
    Dim vcDateTime As String
    
    Dim vtTheDate As Date
    
' Get Yesterday's date
    vtTheDate = Date - 1
    
' Format the various necessary fields
    vcDateName = Format(vtTheDate, "yyyymmdd")
    vcDateYear = Format(vtTheDate, "yyyy")
    vcDateMonth = Format(vtTheDate, "m")
    vcDateQuarter = Format(DatePart("q", vtTheDate) + 1, "d")
    vcDateTime = Format(vtTheDate, "yyyy-mm-dd")
        
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year]"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year]"). _
        CurrentPageName = _
        "[Calendar Filter].[Calendar Year - Quarter - Month - Date].[Calendar Year].&[" + vcDateYear + "].&[" + vcDateQuarter + "].&[" + vcDateMonth + "].&[" + vcDateTime + "T00:00:00]"
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Transaction Date].[Calendar Year Qtr Month].[Calendar Year]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Transaction Date].[Calendar Year Qtr Month].[Calendar Year]"). _
        CurrentPageName = _
        "[Transaction Date].[Calendar Year Qtr Month].[Date].&[" + vcDateName + "]"
    Cells.Select
    Selection.Columns.AutoFit
End Sub

Link this macro to a keyboard shortcut and instead of editing two date fields manually, we now only have to use the shortcut to retrieve the newest data.