Use Excel Parameters to Dynamically Export SQL Server Data

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.

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

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

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'

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

It will load the results into a new sheet.

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.

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

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

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

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

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.

We will click the "Advanced Editor".

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

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.

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.

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

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 &"' "

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.

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

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.

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

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.

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

Additional Resources

• Find out more about the different M functions – https://docs.microsoft.com/en-us/powerquery-m
• A similar solution without using the M language, and using VBA script instead – https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/
• A list of all supported external data sources –https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a

Next Steps

• You can add automatic refresh every time the parameters are changed, using a VBA script.
• Download the example here.

Ohad Srur

Ohad Srur is the Managing Director of Data-Best based in Sydney, Australia, which is a consulting company that focuses on assisting to K-12 independent schools to improve their use of their technology systems. Ohad loves to develop database solutions, and wrangle with code and data to automate processes as much as possible. Ohad is a Microsoft Solution Expert (MCSE) Data Platform since 2016 and has been working with the SQL Server stack since 2011. Other than SQL Server stack, he likes to work with PowerShell and Python.