SQL Server Store Procedure With Parameter In Power BI

            SQL Server Store 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 have parameters that we need to pass dynamically.

 

Power BI provides functionality to execute a Store Procedure using Manage Parameters.

 

In this blog, we have look about the following.

1. How to create Manage Parameter
2. How to pass manage parameter to a Stored Procedure
3. Invoke Query Result and load data to Power BI Desktop

Limitation 

• This feature will work only for Import Mode in Power BI

Example

 

               I have a procedure in SQL Server its named “spGetEmpbyGenderandDepartment” it  has two parameters named “Gender” and “Depart”. I want to use this procedure and load the data into Power BI Desktop. 

 

So, now let’s get started!

Step 1 - Create Manage Parameter in Power BI Desktop

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

1. Click on “Manage Parameters” and select “New Parameter”.
    
   
    
2. It will open a popup to create a new parameter. Select “New”.

          It will ask for the following information.

• Name = Name of the parameter
• Description = Description of Parameter
• Required checkbox
• Type = Datatype of a parameter
• Suggested value
• Current value

I created parameters “GenderParam” and “DepartParam”, as shown in the screenshot.

Step 2 - Load (Execute Store Procedure)

1. Now, from “Home”, select “New source”.
    
   
    
2. Select “Databases”, select “SQL Server Database”.
    
   
    
3. Fill in the required fields and in command window use the below line to execute the procedure.

      

        1. EXECUTE spGetEmpbyGenderandDepartment 'Male',3

4    It will preview the data. Click on “Load”.

Step 3 - Change Query in Advance Editor

 

1. Select Query / (DatawithNameGenderDepart) and click on “Advanced Editor”.

 

 

Update the existing query with as below code.

• Pass Parameter to Procedure

     let

 Gender= Text.From(GenderParam),

 Depart= Text.From(DepartParam),

    Source = Sql.Database("BORN2WIN\PRAKASH", "SQLPROB", 

    [Query="EXECUTE spGetEmpbyGenderandDepartment @Gender='" &Gender& "', @Depart= '" &Depart&"'"])

in

    Source

      Select GenderParam and enter the Current Value as Male or Female

     Select DepartParam and enter the Current Value as 1,2 or 3 

      

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

 

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

 

4. We can see the load all the data. as below

 

Conclusion

 

                     Hope you have got an idea of “Manage Parameters” in Power BI. We can pass the dynamic parameters to SQL Server Store procedures using this feature. Try on your own and share your opinion and question here,

Cheers!!!

Happy Learning