BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 30 November 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
SQL Stored Procedures and Parameters in Power BI Desktop
Following an enquiry from someone on a recent Power BI Desktop course, I thought I would explain how to pass parameters to a SQL stored procedure from a Power BI Desktop query. So this blog will look at:
- The problem I want to solve;
- How passing parameters looks in Reporting Services;
- The closest equivalent in Power BI Desktop; and finally
- An alternative approach in Power BI Desktop to give the same functionality, but more in accordance with the way the software is meant to be used.
You can generate the table and stored procedures used for this example here (although it wouldn't take too long to type them in from scratch).
The problem I want to solve
I want to be be able to import this table into a Power BI Desktop file:
Planet Earth II is a fantastic natural history program from the BBC, if you're wondering.
The results initially should look like this:
Initially you should see all of the rows in the table.
What I'd like to do then is to apply a filter to see only those animals with a given rating (10, say):
The truly excellent animals.
I can easily do this by applying a filter in the Power BI Query Editor, but can it be done by calling a SQL stored procedure and passing a parameter to it?
How passing parameters works in SSRS
To understand what I'm trying to achieve, here's how this report would work in SSRS:
In SSRS you can type in a rating, and pass this to a stored procedure which will show all animals for that rating.
How to get this to (sort of) work in Power BI Desktop
Our database contains the following stored procedure:
-- list out just animals for giving rating
CREATE PROC [dbo].[spListAnimalsByRating](
WiseOwlRating = @Rating
To get this to run in Power BI Desktop, first choose to get data from SQL Server:
Choose this option to get data from your SQL Server database.
Type in your server name, then choose Advanced options as shown:
Click on Advanced options to type in the SQL you want to run. Your server will obviously be different from the one shown here.
Type in the SQL you want to run into the box that appears:
The dynamic SQL to list out the animals with a rating of 10.
Here's the SQL in a more readable (and copyable) form:
-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'spListAnimalsByRating 10'
-- run it!
When you click on OK and load the data, this is what you should see:
The query only shows animals with a rating of 10.
What we'd REALLY like to do is to pass in the value of a parameter set in Power BI Desktop into the stored procedure, but I can't find any way to do this!
An alternative approach, more in line with the Power BI Desktop ethos
Here, I'm sure, is how Microsoft intend the product to be used!
Click on a value in the slicer, and the table will update.
Alternatively, you could use linked visualisations and set drill-down options, but this is a variation of the same thing.
I wholeheartedly agree with your comment "What we'd REALLY like to do is to pass in the value of a parameter set in Power BI Desktop into the stored procedure, but I can't find any way to do this!"... have Microsoft added the ability to do this yet?
I can't find a simple way to call SQL Stored Procedures and pass parameters to them. It really doesn't make sense to bring back millions of rows of SQL data into Power BI and then filter the results there to see the couple of hundred records you're insterested in.
I cannot find a good online resource for Power BI where this is explained. I've found a few workarounds, but nothing obvious. Is Power BI really unable to fully support Microsoft SQL Server yet?