Wednesday, February 6, 2013

Excel 2010 Querying(parameterised) SQL

Simple reports for end user may require you to run some query on an excel spreasheet, so that end user can refresh and get live data as they want and when they want.

Couple ways to do:
Create a View in SQL server, save it, then Open up Excel 2010

Choose 'From Microsoft Query' (You can, if you wish, to choose SQL server, what i noticed was you cannot change your query or put dynamic parameters if you choose that)

Point to the database server of your choice, Click 'Ok' then Click Cancel, it'll pop up asking whether you want to enter query editing window, click 'Yes'.
Then choose the table you wish to display in SQL Query edit window, you can then add criteria and modify the query and look of the output. even add parameters.

After you done with your query editing, click 'File --> Return Data to Excel' to go back to Excel main window.(Note: if you added parameters in your query, you'll need to fresh the connection to be able to set the parameter look up value, i.e. a cell or range of cells.)

User will be able to simply change the parameter to get the data refreshed. Easy as~