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

Posted on 2009-12-17
Last Modified: 2012-05-08

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
Question by:reehalsoni

    Author Comment

    This is not Crystal Report Zone , I didn't mean to select that
    LVL 16

    Expert Comment

    by:Jerry Paladino
    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.
    LVL 16

    Accepted Solution

    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.

    LVL 16

    Expert Comment

    by:Jerry Paladino
    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."

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now