How to add parameters to an external data query in Excel which can’t be displayed graphically?


Excel’s interface for SQL Server queries will not let you have a custom parameters.  A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection’s properties.  Here are the detailed steps for Excel 2010:

  1. Open Excel
  2. Goto Data tab
  3. From the From Other Sources button choose From Microsoft Query
  4. The “Choose Data Source” window will appear.  Choose a datasource and click OK.
  5. The Query Qizard
    1. Choose Column: window will appear.  The goal is to create a generic query. I recommend choosing one column from a small table.
    2. Filter Data: Just click Next
    3. Sort Order: Just click Next
    4. Finish: Just click Finish.
  6. The “Import Data” window will appear:
    1. Click the Properties… button.
      1. Choose the Definition tab
      2. In the “Command text:” section add a WHERE clause that includes Excel parameters.  It’s important to add all the parameters that you want now.  For example, if I want two parameters I could add this:
        WHERE 1 = ? and 2 = ?
      3. Click OK to get back to the “Import Data” window
    2. Choose PivotTable Report
    3. Click OK
  7. You will be prompted to enter the parameters value for each parameter.
  8. Once you have enter the parameters you will be at your pivot table
  9. Go batck to the Data tab and click the connections Properties button
    1. Click the Definition tab
    2. In the “Command text:” section, Paste in the real SQL Query that you want with the same number of parameters that you defined earlier.
    3. Click the Parameters… button
      1. enter the Prompt values for each parameter
      2. Click OK
    4. Click OK to close the properties window
  10. Congratulations, you now have parameters.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s