I'm trying to figure out whether there is a way to filter the data from an SQL Server connection before it is imported into Excel. The initial import doesn't matter (when the connection is set up), but I need Excel to have little to no data when opened subsequently and import the relevant data only after a parameter is chosen. The SQL tables could have a lot of data and performance is an issue.
So if I import the data into a table, apply a filter for, say, Country to be UK, close the file and open it again next week, ideally it should only query for and import the data for UK. And when I choose another country it should import the data for that country alone. I need the choices to be enumerated from the table.
Now, I don't believe tables have that capability. But how about Slicers, PivotTables, PowerView or PowerPivot?
Appreciate any ideas.
Best Answer
You can absolutely do this with Tables, as well as Powerpivot and Data Explorer. The easiest, and my preferred method currently, is to create a parameterized query in Excel. To do this use the Query Wizard and at the end add constraints for each parameter you want to pass. Once this is done, you can edit the Connection Properties and assign the Parameter to be returned as a static value, dialog box or a spreadsheet cell.
Data>Other Sources>Microsoft Query
.Data Source
, select your SQL data source here. I'll generally leave all of these options blank until I get to theFinish
window, then I'll selectView data or edit query in MS Query.
Show/Hide Criteria button
and choose a Criteria field. This will be what calls your parameters to pass back to SQL.I'll often create a Pivot Table based upon a Table fed from a paramterized query to return values for automatically updated, specific date ranges (e.g. prior 6 months).
EDIT: To return multiple values for use in a SQL IN clause, you just need to modify the Criteria window in the MS Query box. For example, if you have a field name of Country, select it in the Criteria Field, then for Value enter IN([First],[Second],[Third]). Choose Return Data to Excel and MS Query will ask to manually input values. Once you're back in Excel you can modify the parameters again, and point them to three cells in your spreadsheet, each of which can be constrained by Data Validation. While there's probably a limit to how many parameters you can pass like this, Excel easily handles three, so a few more isn't likely out of the question.