locked
Pivots based on linked table parameters are not refreshed in Excel RRS feed

  • Question

  • Hi,

    In Power Pivot for Excel I use a parameter table where users can set some parameters for calculation without having to go into Power Pivot and change hard coded parameters in measures. This parameter table is linked to the Power Pivot data model and the values from there are used by some measures.

    The problem is that pivot tables built with these measures can't be easily refreshed when the user is changing the parameters in Excel. The change in theory should "go back" to the Power Pivot data model, then to the measure and appear in the pivot table. I attach a sample Excel file to demonstrate the situation.

    Is there any reasonable solutions either with or without VBA scripts to refresh the pivot table to reflect the changes in the parameters?

    I use Excel 2016 64Bit on Windows 10.

    Sample:

    http://work.hrasko.com/Parameters_linked_table.xlsx

    Gábor

    Wednesday, June 22, 2016 7:00 AM

Answers

All replies

  • Changes in linked tables are not automatically processed in the Power Pivot model. Changes are reflected when you open or select the Power Pivot window.

    An alternative is to click the 'Update All' button in the Power Pivot ribbon in Excel. And yes, you can write a VBA script that fires an update of the linked table.

    Wednesday, June 22, 2016 1:02 PM
    Answerer
  • Thanks,

    'Update All' won't work as in this situation the main data is not available. I used to write a VBA script and attach it either to the tab change (switching from the Parameters Excel tab to the report tabs) or to a button. Anyhhow, it stopped working for me after one of the Excel updates. As far as I remember I used this:

    Private Sub Worksheet_Deactivate()
        ' Refreshing the parameters table in the data model when the user leaves the Parameters tab.
        ActiveWorkbook.Connections("LinkedTable_Parameters").Refresh
    End Sub

    This seems not to be working any more. What is the correct method to refresh the linked tables from VBA?

    Wednesday, June 22, 2016 2:18 PM
  • Excel 2016 Pro Plus.
    If you just want to jack up the prices
    without loading new external data,
    you can use this PowerPivot Slicer method.
    It requires no "Update All" or "Refresh All".
    http://www.mediafire.com/download/st1vqa0qa365ctf/06_22_16.xlsx

    Wednesday, June 22, 2016 6:25 PM
  • Hi,

    I don't think this is an answer for my question. Slicers do work of course. But I was speaking about situations when slicers won't fit, as it would be impractical to list all possible parameter values. In fact the parameter could be an arbitrary text as well (for example a keyword that is to be matched with some data fields).

    The parameters would be listed in an Excel table that is linked to the data model. From there measures (calculated fields) are using them in the calculations. The results are displayed in Excel pivot tables and pivot charts.

    The only requirement is that the linked table should be refreshed from Excel into the data model without having to open the Power Pivot window and the change should be reflected in the Excel pivot tables. The first steps can be achieved by refreshing the linked table connection from the Connection menu or from VBA, but that will not be reflected in the pivot tables. If you refresh external tables (not Excel linked tables) the pivot tables will change immediately. In case of linked tables the change is reflected in the pivot tables only if you "force" refresh by changing back and force an independent slicer on the same pivot table. At that point the effect of the changed parameter (from the linked table) will be displayed.

    Gabor

    Sunday, July 10, 2016 4:30 PM
  • Hrasko, have you made any progress with this?

    You can send a Frown to the Power Pivot team if you run into any more troubles. Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 10:54 AM
  • I could not proceed with this. I will check what "Frown" means and how to send it :)

    Gábor
    Wednesday, November 2, 2016 8:50 AM