• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1819
  • Last Modified:

How to select latest date from VisibleItemList in Pivot table report that uses OLAP cube connection in excel


I think this is going to be an easy one for those you have been dealing with Excel, VB and Pivot tables for longtime. I am sort of new and pushed into MS Sql server and now dealing with a report that has a column called SnapshotDt which users use to pull up historical data and we are using a excel template and refreshing this template daily with new data hence the snapshotdt column gets new dates very day. But the issue is the template is set for a particular date because one date has to be selected. Now users want me to dynamically update this sndpshotdt to the latest date when we run the refresh step which is where my issue is. I can do this manually by recording a macro but I need this done on daily basis which is where I am getting stuck and as was wondering if some one can take a look at this Macro and help me update the .&[ piece to pick up the latest date automatically.

Sub test4()
' test4 Macro

    ActiveSheet.PivotTables("PivotTable2").PivotSelect _
        "'[Dim].[SnapshotDt].[SnapshotDt]'[All]", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Dim].[SnapshotDt].[SnapshotDt]").VisibleItemsList = Array( _
        "[Dim].[SnapshotDt].&[2009-12-13T00:00:00]")   - this 2009-12-13 needs to be replaced with a variable where this macro can get the latest date from snapshotdtand that's gets selected
End Sub

Any help on this will be a great
  • 3
1 Solution
reehalsoniAuthor Commented:
This is not Crystal Report Zone , I didn't mean to select that
Jerry PaladinoCommented:
If the data is refreshed daily via a QueryTable then calculated columns to the right of the data will automatically be added as the data increases or decreases on each update.  Assuming your date field is in column "B".  Add a new column of data called "MaxDate" that has the formula:
=IF(B2=MAX(B:B),"Current Date","") copy the length if the data and format the column as a date.  Then use MaxDate as a Page filter and and have "Current Date" as the default selection.
Jerry PaladinoCommented:
Alternate solution if you do not have access to the data from a QueryTable.  I am assuming you are using Excel 2003 but these instructions apply to 2007 as well.
Create a copy of the sheet that has the current pivot table.  On the newly created sheet, modify the Pivot Table so SnapShotDt is in the Data field and there are no Row or column fields.  Format SnapShotDt as a Date field. See attached graphic.  Open the field setting dialog for SnapShotDt from the wizard or by double clicking on the name in the pivot table and set the "Summarize By" to "MAX".  The dialog box is slightly different if you are using Excel 2007 but the same options are there too.  Since this is a copy of your production pivot report it shares the same pivot cache and will be updated automatically everytime the production pivot report is updated.  Name the new sheet "GetMaxDate" and then hide the worksheet.
Add the following statement to your VBA code:
MaxDate = Worksheets("GetMaxDate").Cells(4, "B").Value
changing the cell reference to the cell that contains the Max of SnapShotDt in the hidden worksheet.   Replace your hardcoded date reference with the new VBA field "MaxDate" which should always contain the most current date from the cube data assuming your VBA code has refreshed the data for the pivot reports before you get to this section of your code.

Jerry PaladinoCommented:
My first post will probably not work for this person since he is pulling data from a OLAP cube and does not have direct access to the data to add a column within Excel.
My second post will work in all situations where the data is available AND where the data is not directly available(as with the OLAP cube). This provides the latest date in the data and makes it available to his VBA routine.
His problem as stated was:
"Now users want me to dynamically update this sndpshotdt to the latest date when we run the refresh step which is where my issue is."

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now