Passing parameters to a query that provides data for PowerPivot

[RichardRayJH]

I can write a simple query for retrieving data, but what happens if the user needs to specify a date range, etc.? It can make a difference between retrieving several 10's of millions of rows of data and manipulating them in PP, or just getting the few hundred thousand the user actually wants. The difference in performance is substantial.

When using regular PivotTables I set up workbooks so the user can enter query parameters in cells. Oftentimes they get nice pulldown menus, etc. They want the same thing using PowerPivot. As a workaround, I can use a parameterized query to get the data into a tab in Excel, then link that back to PowerPivot, but that's a bit of hack. Is there a way to do it directly?

 

[Jacob Barnett]

If you are using Excel 2013 this is straightforward as the model is exposed to VBA.

If you are using Excel 2010 then its pretty tricky - an Irish guy called Tom has developed a bunch of techniques to hack his way into the PowerPivot model. I've had success with the VBA refresh but I understand you can also make changes to the underlying model in the procedure which would enable you to specify the date. VBA Code to Automate a PowerPivot Refresh | Gobán Saor

Jacob