This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

Posted by Andy Brown on 30 November 2016

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).

This blog assumes that you're comfortable with the basics of Power BI Desktop and also of SQL stored procedures. If you're not, you might want to consider our Power BI courses or SQL courses.

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](

@Rating int

)

SELECT

AnimalName,

Notes,

WiseOwlRating

FROM

tblAnimal

WHERE

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!

EXEC (@sqlCommand)

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.

Some other pages relevant to the above blog include:

This blog has 1 thread

Add a new post

From:	garkbit
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?

Regards, Ulen

From:	Andy B
When:	08 Apr 19 at 15:14

It seems we're both wondering the same thing! Can anyone out there help?

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

SQL Stored Procedures and Parameters in Power BI Desktop

The problem I want to solve

How passing parameters works in SSRS

How to get this to (sort of) work in Power BI Desktop

An alternative approach, more in line with the Power BI Desktop ethos

Head office

London

Manchester