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

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'd 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.

The problem I want to solve

I want to be be able to import this table into a Power BI Desktop file:

Table to import

Planet Earth II is a fantastic natural history program from the BBC, if you're wondering.

The results initially should look like this:

Initial table of animals

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 top-rating animals

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:

SSRS report

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

)

AS

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:

Get data 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

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:

Dynamic SQL

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:

List of rating 10 animals

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!

Slicer and table

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.

This blog has 0 threads Add post