How can I update the parameters of an SSRS report used as a PowerPivot data source? RRS feed

  • General discussion

  • I've got an SSRS report that I've used as the data source for a PowerPivot workbook and published to SharePoint (v2010 of both of those tools). This is a great feature as it should allow us to build on a significant investment in existing SSRS reports and use them in PowerPivot, PowerView etc.

    The report in question has 3 parameters, all of which have default values. One of the paramter's default values is calculated in SSRS. It specifies the reporting period timeframe. So when in SSRS and you open the report it always displays data by default for the most recent closed reporting period.

    What I can figure out is how to refresh the powerpivot to take on an updated value for that paramater. It is stuck with the value for the day that the powerpivot was created.

    How does one get the paramters for an SSRS powerpivot datasource to update when refreshed and pick up the new SSRS default value? I can set it manually but would be nice to have it sync to the SSRS defaults.

    Monday, August 12, 2013 3:11 PM

All replies

  • Any progress with this?


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

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

    Wednesday, August 21, 2013 7:33 PM
  • I haven't figured out a way for the powerpivot to automatically pivk up the newly calculated default value for the parameter. I am going in and manually re-doing the SSRS powerpivot data set parameter.
    Monday, September 16, 2013 4:50 PM
  • Hi dkackman

    There are a few options:

    a) if the PowerPivot model contains all dates but you are passing the desired date from SSAS as either MDX or DAX

    b) if you only intend to refresh the PowerPivot model with the one and only date you need. In that case, this would be a feature of the model itself, not related to what SSRS is defining as 'default' date.  In other words, you would have to configure that on the underlying SQL query the model uses to import data.

    Are any of these possible?

    Javier Guillen

    Thursday, September 19, 2013 2:25 PM