Microsoft Query is a powerful tool that allows us to retrieve data from external sources into Excel easily. It is commonly used to provide ad-hoc reports to users. However, when we need to add parameters to our reports, we often resort to different tools like Reporting Services, Power BI, or Crystal Reports. But with the power of the M language, we can now add parameters into our Excel reports and use them for our SQL Server queries.

In this article, we will explore how to add parameters to an Excel report and use them in a SQL Server query. We will use the AdventureWorks2017 database as an example and create a simple stored procedure to query the “Product” table between the selling start and end dates. Our goal is to pass different sell dates to the stored procedure, and our report will refresh accordingly, allowing the user to manipulate the date parameters without modifying the query.

Let’s start by creating the parameter fields in Excel. We will then highlight them and assign them a name, such as “GetValue_1”. Next, we will add the SQL Server query to the Excel report. We can copy-paste an example query like this:

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

After loading the results into a new sheet, we need to modify the M language code to find the line of code used to generate the database call. To do this, we will edit the query in the Power Query window. If the window is not visible, go to Data > Show Queries. Click the “Advanced Editor” button to see the code.

In the Power Query window, we will slowly build the necessary M code to create the parameterized query. Each step in the code should be evaluated in sequence using the “LET” and “IN” sections. It is recommended to have each operation on a separate line of code, with a variable name at the beginning and a comma at the end (unless it’s the last line in the “LET” block).

Here is an example of the M code to pull the parameter values from the spreadsheet into a variable:

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

We can then convert the date values to text with the desired format using the “ToText” function from the DateTime library. Finally, we can create the required stored procedure call as a variable called “query” using common operators like in Excel:

query = "exec ProductList '"& SellDate &"','"& EndDate &"' "

Next, we need to modify the M code generated by Query 1 to pass the “query” variable instead of the procedure call. We can use the “DateTime.ToText” function to convert the date values to the desired format. The final M code should look like this:

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

After clicking “Done” and approving the privacy level screens, we can load the data to the same worksheet or a new one. To refresh the data with different parameters, we can modify the dates and go to Data > Refresh All or press Ctrl + Alt + F5 on the keyboard.

By using parameters in Microsoft Query, we can create dynamic Excel reports that allow users to manipulate the data without modifying the underlying SQL Server query. This provides a flexible and user-friendly reporting solution.

For more information on the M language and its functions, you can refer to the official documentation. If you prefer to use VBA script instead of the M language, you can check out this article for a similar solution.

That’s it! Now you can start using parameters in Microsoft Query to retrieve data from SQL Server and create dynamic Excel reports. Happy querying!