SQL with Manoj

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 ?

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:

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.