Use Excel Parameters to Dynamically Export SQL Server Data


By:   |   Updated: 2020-05-08   |   Comments (11)   |   Related: More > Microsoft Excel Integration


Problem

Microsoft Query allows us to retrieve data from external sources into Excel easily, which can be very handy if we want to provide an ad-hoc report to our users. All we need is to copy-paste our T-SQL query code and hand the Excel file over to our users, who can refresh the dataset at any time.

However, sometimes we are required to add parameters to our report, and we usually gravitate to a different tool like Reporting Services, Power BI, Crystal Reports, etc. Not any more, with the power of the M language, we can add parameters into our Excel report and use them for our query. In this tip, I will show you how it's done.

Solution

In our example, I’ll use the AdventureWorks2017 database, and I’ll create a simple stored procedure to query the "Product" table between the selling start and sell end dates. Our task in this example is to pass different sell dates to the stored procedure, and our report will refresh accordingly, allowing the user to manipulate the dates parameters as they wish without modifying the query.

USE [AdventureWorks2017]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
--    Example: exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24'
	
CREATE PROCEDURE [dbo].[ProductList] @SellStartDate as Date, @SellEndDate as Date 
AS
BEGIN
 
   SET NOCOUNT ON;
 
   SELECT
        PR.[Name] ProductName
       ,[StandardCost]
       ,[ListPrice]   
       ,CAST([SellStartDate] AS DATE) SellStartDate
       ,CAST([SellEndDate] AS DATE) SellEndDate
   FROM [AdventureWorks2017].[Production].[Product] PR
   WHERE SellStartDate>=@SellStartDate AND SellEndDate<=@SellEndDate
   ORDER BY SellStartDate,ProductName
 
END

We will start with creating the parameter fields.

parameter fields

Then, we will highlight them and assign them a name, in this instance: "GetValue_1"

get value

Next, we will add the query from SQL Server, as described in the screenshot below.

sql server database

And fill in the required information for server and database. In the statement box, we will copy-paste an example.

exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24'
sql server database

When ready, click, OK. And we can click the "Load" button.

adventure works

It will load the results into a new sheet.

product name

Next, we need to modify the M language to find out the line of code used to generate the database call.

Therefore, we need to edit the query.

workbook queries

Note, if the above window is missing, go to Data > and click "Show Queries".

show queries

After clicking Edit, it will open the Power Query window.

query editor

Click the "Advanced Editor" button to see the code. Note, we will use this the editor window very frequently in this tip.

transform

Below we can see the code being uses for the procedure call.

advanced editor

We will make a note of this line of code, as we will use it later.

Following the below steps, we will create the necessary M code. Unless you are an expert in M, I suggest you test each line of code until you achieve the desired M code. The best way to do that is to output the result variable of each step.

Please note the following if you are unexperienced with M.

  1. A block of code most have "LET" and "IN".
    1. The "LET" section will include all our steps in will be evaluated in sequence
    2. The "IN" section is used for outputting the result
  2. It is recommended that each operation have its own line of code, and it has to include a variable name at the beginning. And it must terminate with a comma unless it’s the last line in the "LET" block.
  3. The M language evaluation concepts:
    1. M is using "Lazy Evaluation", which means each line of code is evaluated as needed. If the line of code is not required for the output, it will not be calculated.
    2. All other expressions are using "Eager Evaluation", which means that they are evaluated immediately when encountered during the evaluation process.

In order to build the M code, we will create a Blank Query and slowly build the required code.

show queries

We will click the "Advanced Editor".

transform

In the following code, we will pull the parameter values from the spreadsheet into a variable.

let
    Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content]
in
    Source			
query
excel

Next, we need to pull the date value into a variable. If we right-click on the Date field and select "Drill Down", we will get the required code. To view the code, click on the Home tab > Advanced Editor.

advanced editor
convert
advanced editor

Now, we need to convert the date to text with the format "yyyy-mm-dd". To do so, we can apply the function "ToText" from the DateTime library, more details are available here.

current workbook
date time

Now that we have the Sell Date, we can get the End Date value the same way.

current workbook
end date

Now, let’s create the required stored procedure call as a variable called "query". We can use the same common operators like in Excel to create the required call.

query = "exec ProductList '"& SellDate &"','"& EndDate &"' "			
current workbook
product list

Now, let’s copy the query line generated by M from Query 1. We need to modify it accordingly, instead of passing the procedure call, we will pass the "query" variable.

let
    Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content],
    SellDate = DateTime.ToText(Source{0}[SellDate],"yyyy-MM-dd"),
    EndDate = DateTime.ToText(Source{0}[EndDate],"yyyy-MM-dd"),
    query = "exec ProductList '"& SellDate &"','"& EndDate &"' ",
    target = Sql.Database("ohad\dbtesting", "AdventureWorks2017", [Query=query])
in
    target			

Now, click "Done", and you will be required to approve the following screens for the Privacy level.

sql database
privacy levels
power query editor

Next, click on "Close & Load" and from the drop-down menu, select "Close & Load To..."

power query editor

We can load the data to the same worksheet, by selecting "Existing worksheet" and specifying the cell where we would like the table to start.

load to

Then we should get the results, as in the screenshot as shown below.

sell date

Now, if we want to change the parameters, we can modify the dates, and go to Data > Refresh All to refresh the data. Or, hit Ctrl + Alt + F5 on the keyboard.

show queries

Note, after a hitting refresh it might ask you to approve to run the query. Just click Run.

native database query

Additional Resources

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Ohad Srur Ohad Srur is a Microsoft Solution Expert (MCSE) - Data Platform and the managing director of Data-Best, a consulting company that focuses on assisting to K-12 independent schools.

View all my tips


Article Last Updated: 2020-05-08

Comments For This Article




Thursday, September 2, 2021 - 11:40:54 AM - J.Dabr Back To Top (89200)
After few attempts I was able to adapt your solution to my scenario.
I have now downloaded the files with the example for auto refresh upon parameter change.
Would you be able to provide some more details as to how to achieve this?

Wednesday, May 26, 2021 - 8:32:26 AM - Kazım ÇETİN Back To Top (88743)
Hi.
Power Query/ External Data Source not trigger with parameter cell value changed.
Only Refresh All.
Microsoft Query refresh stored procedure when cell value changed.

Tuesday, May 11, 2021 - 8:14:48 AM - Ohad Srur Back To Top (88669)
Hey Joe,
I recommend you to download the file and check this for yourself, the file is available at the end of the article.
Re your question, I have assigned the name "GetValue_1" to the table name.
Regards,
Ohad

Monday, May 10, 2021 - 12:29:29 PM - Joe Back To Top (88665)
I've used parameters in the past where we used the ? to denote a parameter. There was a couple of simple steps to then connect the cell value to the parameter. We just upgrade to Office 2019, and that ? parameter option went away. This is my new attempt to reconnect cell values to parameters. I've followed the instructions as best I could, but i am not getting the same results as the screenshots show, and my finished query statement returns an error. In trying to troubleshoot this, i have two questions on the starting setup:

1. When you set up the parameter fields, did you convert the four fields (A1:B2) into a table? The screenshot looks like a table, but maybe you formatted the fields to look like a table.
2. When you applied the name "GetValue_1", was the name for the two date fields only, or for the two date fields and the titles above the date fields?

Friday, April 16, 2021 - 8:29:43 AM - Ohad Srur Back To Top (88552)
Hey Dave, I haven't had this requirement before, but this is an interesting and useful one.
What I would try to do is to convert the ID column to a list using M, and add it to the IN statement, i.e. WHERE ID IN (list)

Tuesday, April 13, 2021 - 11:34:43 AM - Dave Back To Top (88520)
Great work. I have a table with multiple IDs though. Any way to return results for all of them? This guide would only return for first row source{0}.

Wednesday, March 3, 2021 - 2:15:53 AM - Richard Back To Top (88326)
I have been trying to do this for months. I had been creating a variable from a table by doing that drill down but failed time and time again to get that working in my SQL script. But now, with this article, I have finally realised what I have been doing wrong all this time. Thank you so much!

Sunday, January 17, 2021 - 6:34:23 AM - Ohad Srur Back To Top (88057)
Hey Joseph,

The format for adding parameters is always the same,

1. If you need to add a number use "& parameter &"
2. If you need to add a string you'll need to wrap it in a single quote, like this '"& parameter &"'

Another suggestion, you can check/ validate your results by putting it into Excel cell, and if the result is correct copy/paste the formula back to PQ and replace the parameter values.

I hope that helps.

Regards,
Ohad

Friday, January 15, 2021 - 11:24:58 AM - Joseph Shirk Back To Top (88049)
I was able to do this with one parameter (which is not a date but an id number), but when I add one or both of the next the parameters for the start and end dates, what I get is a list of all tables in the database, which is weird. I have followed the syntax exactly, but It's hard to understand the escape rules. My query string result should be :
EXEC myproc '12345' , '2010-12-01' , '2020-12-31'

I know the single quotes and the commas between parameters need to be passed literally, but I am not clear on how this is being done. I also think it would be safe to used named parameters (so you could skip start date but include end date) but it's already a mess of single, double quotes, literal commas, and parameter commas. It's hard to make sense of.


Thursday, December 3, 2020 - 10:36:10 AM - MikeT Back To Top (87877)
Brilliant! Thank you so much. I was pretty much there. But I couldn't work out how to access the data as a cell. Your 'Drill Down' tip was a life saver!

Thank you so much!!!!!

Friday, May 8, 2020 - 3:50:42 AM - Pavel Back To Top (85600)

Hello Ohad, nice article, thanks!
Though the title is a little bit ambiguous, the more appropriate would be something like "Use Excel fields as SQL stored procedure parameters to import data"



download














get free sql tips
agree to terms