Execute SQL Server Stored Procedure With User Parameter In Power BI

Overview

Sometimes, there is a situation when we need to get the data by consuming SQL Server Stored Procedure. SQL Server Stored Procedures has parameters which we need to pass dynamically.

Power BI provides a functionality to execute Stored Procedure using Managed Parameters.

In this article, we will talk about the following.

• How to create Manage Parameter

• How to pass manage parameter to a Stored Procedure

• Invoke Query Result and load data to Power BI Desktop

Limitation

This feature will work only for Import Mode.

Example

I have one procedure in SQL Server named “sp_getEmpshiftDetails” which has two parameters named “vStartDate” and “vEndDate”. I want to use this procedure and load the data into Power BI Desktop. I have attached the file with this article for practice purposes.

So, now let’s get started!

Step 1 - Create Manage Parameter in Power BI Desktop

• Open Power BI Desktop and from Home tab, select “Edit Queries”.
  

• Click on “Manage Parameters” and select “New Parameter”.
  

• It will open a popup to create a new parameter. Select “New”.

It will ask for the following information.

1. Name = Name of the parameter

2. Description = Description of Parameter

3. Required checkbox

4. Type = Datatype of a parameter

5. Suggested value

6. Current value

I created parameters “vStartDate” and “vEndDate”, as shown in the screenshot.

Step 2 - Load (Execute Stored Procedure)

• Now, from “Home”, select “New source”.
  

• Select “Databases”, select “SQL Server Database”.
  

• Fill in the required fields and in command window use the below line to execute the procedure.
  

EXEC sp_getEmpshiftDetails '2015-06-23','2015-06-25'

• It will preview data. Click on “Load”.

Step 3 - Change Query in Advance Editor

1. Select Query and click on “Advanced Editor”.

Replace the existing query with new query.

• Declare ParameterPass

• Parameter to Procedure

let  

SQLSource = (vStartDate as date, vEndDate as date) =>  

let   Source = Sql.Database("DHRUVIN\SQLEXPRESS", "WMS_201", [Query="EXEC sp_getEmpshiftDetails '"& Date.ToText(vStartDate) & "','" & Date.ToText(vEndDate)&"' #(lf)#(lf)#(lf) #(lf)"])  

in Source   in SQLSource  

2. The below screenshot shows a comparison of both the queries.

Step 4 - Invoke Result

1. Select Query and enter start date and end date. Click “Invoke”.

2. It will show a warning like this. Click “Edit Permission”.

3. It will show a warning to run the query in Native Mode. Click “Run”.

4. It will load all the data.

5. Click “Close and Apply”.

Our data has been added to a Model.

Conclusion

Now, I hope you have got a better idea of “Managed Parameters” in Power BI. We can pass the dynamic parameters to SQL Server Stored procedures using this feature. Try this on your own and share your opinion with me.