Excel 2010 analysis services multiple reports

Posted on 2012-09-05
Last Modified: 2012-09-05
I developed a series of monthly reports for a user using Excel 2010 connected to an analysis services cube. There are 16 tabs with 16 different reports, all connected to the same cube. Each report is filtered to show data through the end of "last month" by the user clicking on the Date and changing the filter by bumping it up a month. So, to get a new set of his monthly reports, the user has go to into each of the 16 tabs and change the month filter 16 times.

I can't just use "current" data because the cube is updated every day through "yesterday". For example, he would not want to see data for Sept 1- Sept 4 (yesterday) included in the numbers for his August reports.

Is there a way to automate the refresh of the Date filter across all the tabs?

Thanks in advance!
Question by:notawahoo2
    LVL 13

    Accepted Solution

    You should be able to simply record a macro to do it, and then attach the macro to a button.

    You'll probably need to customize the ribbon to find it. Go to File, Options, Customize Ribbon, Main Tabs - click Developer and OK.

    On the Developer Tab the Record Macro button will then appear. Start recording, click the refresh button on every tab, stop recording. Test the new macro and use it where you need it.

    Author Closing Comment

    You're totally right, I can't believe VBA didn't occur to me! The recorded macro generated this, so all I have to do is figure out why 1139 means July 2012 and 1140 means August 2012 so that I can make it self-maintaining across years. Thanks !!        

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
            "[Date Admit].[By Fiscal Year].[Fiscal Mth]").VisibleItemsList = Array( _
            "[Date Admit].[By Fiscal Year].[Fiscal Mth].&[1139]", _
            "[Date Admit].[By Fiscal Year].[Fiscal Mth].&[1140]")
    LVL 13

    Expert Comment

    by:Norm Dickinson
    Good old date codes. You're welcome - we are here to help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now