562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|Passing parameters to SQL stored procedures in Power BI Desktop|
|This blog summarises how to run SQL statements in a Power BI Desktop query, using dynamic SQL to pass in parameter values|
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).
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?
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.
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!
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.
|When:||08 Apr 19 at 14:57|
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?
|When:||08 Apr 19 at 15:14|
It seems we're both wondering the same thing! Can anyone out there help?
25 Aytoun Street