How to use SQL Server Stored procedures in Microsoft Power BI?

The aim of this blog is to take you through one example of executing SQL stored procedures in Power BI. Similar to SQL Server Reporting Services (SSRS) we can create parameters that will be used to return a result set based on the output parameters that were selected.  The parameter selection will be available on the Power BI home page. The data used in his example is production line data. The production line is inside a factory where products are being packaged during a certain shift and at a certain production run date. It is literally a factory line with products being packed.

(Step 1) Navigate to the Power BI Query Editor to import the data to be used.

Before we jump into this step, I want us to take a look at what the query to execute the SQL stored procedure within SQL would look like:

From the above, it is evident that we have 3 primary fields that we will use, it is ReportingPL (ReportingPackLine), Shift, and Run date. (The EndHour and Shifthours are not important for this exercise.)

From Power BI, let us navigate to the query editor to write our select statement which will import the data. And select the Server and Database from which the stored procedure will be called.

In this step, I will hardcode the output Parameter values. So, for now,  I have typed in a value for the 3 fields to be used. I made the shift a Dayshift and I typed a specific date.

Then once you’ve clicked OK, this is what the code would look like in the Advanced Editor.

The data table that we are importing based on the selection above looks like this:

(Step 2) Create lists to be linked to parameters

In this step, I created 3 lists that contain the data to be passed through the 3 fields. So, the lists are for Reporting Packline, shifts, and dates.

The lists are created by converting a table with unique values pertaining to each field into a list.

(Step 3) Add New Parameters for your 3 fields

Once you have your lists, you can go ahead and create parameters where these lists will be pulled into.

When you are in the Query Editor, you can navigate to the home tab, and select New Parameter, from the Manage Parameter drop-down list.

Now create a name for your parameter. In this instance, I will link the value of my parameter to a Query, then from the dropdown Query list choose the list pertaining to the specific parameter.

I have also typed in a Value for the ReportingPL and the date.

(Step 4) Adjust your Query from the advanced editor to make use of parameters instead of hard code

So, remember at the top, I used hard code to obtain values pertaining to each field? Well now, I will link my parameters to these fields. Then the parameter will make use of the lists that were created to obtain a value based on the selection made.

(Step 5) Close and Apply, and select values for your parameter

In the final step, once you’ve adjusted the code in the advanced editor. You can close and apply. Then from the home tab in the desktop view, navigate to transform data. Click on Edit Parameters, and select values for the parameters to be used in your visuals.

Limitations and Conclusion

One key difference between Power BI and SSRS is that an end-user cannot select parameters by means of a filter within Power BI. The parameters have to be selected from the home tab. Alternatively, the report can be set up in such a way that the end-users can select the parameters on opening the Power BI report. Which will then push the selection back to the SQL server to fetch the data based on the selection made. So if you’d like to give an end-user the ability to filter through data using parameters. Then unfortunately you won’t be able to do it for T-SQL-based data sources.

If you would like more detail pertaining to Parameter in Power Bi, you can take a look at  the official Microsoft documentation: Dynamic M query parameters in Power BI Desktop

For other interesting and informative blogs, you can also visit our blog site at: Power BI Blog – Data Bear

Training options:  https://databear.com/power-bi-training/