Home > Excel > Create Parameterized Excel Refreshable Reports

Create Parameterized Excel Refreshable Reports

September 24, 2009 Leave a comment Go to comments

In my [previous post] I provided steps to created a simple Excel Pivot Report. This Report queries your SQL Server Table and pulls data from it and renders into the Excel Sheet.

Many of the time we don’t require whole data, and want to add conditions to restrict the data, like what if we just need to pull data for Q2 (2nd Quarter) of year 2009. This would require a Date Range to be applied to the SQL Query.

How about Start Date & End Date that will be filled by the user on Excel Sheet on particular cells? And those values will be applied to the SQL Query WHERE clause and will pull just the data lying between those Date Ranges?

>> Here I’ve defined how we can pass Parameters from the Excel-Sheet to the SQL query to filter data.

1. Please refer to my previous post, stop at step 13.

2. Now, open Connection Properties and go to Definition Tab and add the following line at the end of your query:

where [DATE_FIELD] between ? and ?

Create Parameterized Excel Refreshable Reports

Now, what does “?” means.

By applying ? (question-mark) in our query in WHERE clause Excel identifies them as 2 parameters and asks you from where to pick the data.

Now point both the parameters to the desired cell in the excel-sheet as shown below:

Create Parameterized Excel Refreshable Reports Create Parameterized Excel Refreshable Reports

And finally you are done… when you will close the “Connection Properties” window the pivot report will get refreshed itself and pull the data as per the date you have applied.

  1. kit
    February 23, 2013 at 5:55 am

    Awesome thanks

  2. Mateusz
    February 5, 2018 at 8:58 pm

    Great post, but…
    Once I retrieve the data for specific parameter in the query, I would like to save it as ‘parameter-value.xlsx’ file and remove connection, so that it will not refresh once another person will open the spreadsheet.
    Should I do this (remove connection) manually from the existing connections or is there any better way.

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: