How to Use Stored Procedure in Power BI?
In this article, I will show you how to use stored procedure in Power BI. I will use Northwnd database. Enjoyable reading.
In the image below, you can see sample data from the Orders and Order Details tables.
First let’s create a stored procedure in SQL.
This query shows us for a country that we enter as a parameter in the procedure, the total number of orders for that country, the total product quantity in these orders and the total quantity of these orders.
Let’s call the procedure in SQL for France as an example:
The output is as follows:
Let’s call stored procedure in Power BI.
In Power Query, SQL Server is selected from the Get data section and connection information is entered as in the picture below. Let’s write the procedure we wrote in the Advanced Options section. In the first entry, we need to give a value to the variable. So we entered France as an example.
After entering the information, we called the procedure we wrote for France as you can see in the picture below.
Next step we have to create a parameter. However, in order to select countries as a parameter, we need to give as a list the countries in this parameter. For this, let’s connect to SQL Server again and call the following query as a table.
This query returns us a single column table with countries. After importing into Power BI, we right-click on the column as in the picture below. Then click ‘Add as New Query’ and it will convert to a list as a different query.
The list converted is as follows.
Now we will assign this list to a parameter. For this, there is the Manage Parameters section under the Home tab. We’re going to click on it and call it New Parameters. We will name the parameter and select the data type. Then we will select the Query option from the Suggested Value section and select the country list we have created from the field just below. Finally, we will enter a sample value and create our parameter. The process I’m talking about is as follows:
We wrote France as an example in the stored procedure we wrote as a query before. We will add this parameter we created to the procedure. Here are the old and new versions of the query:
Since we gave the France value while creating the parameter, it also brought the information about France. Let’s load the tables and see on the visualization side.
As an example, I created a table and a graph as follows.
The data I have prepared now contains information about France. You can prepare your reports and then change the value in the parameter and run your report according to that parameter value.
As seen in the picture below, there is the Transform Data option under the Home tab to change the parameter. Click the down arrow next to it and click Edit Parameters.
Then the parameter we created will appear. Let’s choose the country we want from there.
Then click OK. Our report will be immediately updated according to that parameter. The updated version for Argentina is as follows:
That’s all I’m going to tell you in this article. Hope to see you in the next article.
