I have written 3 blogposts about query parameters in the past.
- Power BI Desktop Query Parameters, Part 1, Introduction
- Power BI Desktop Query Parameters, Part2, Dynamic Data Masking and Query Parameters
- Power BI Desktop Query Parameters, Part 3, List Output
This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:
CREATE PROCEDURE SP_Sales_by_Date @date int AS BEGIN SET NOCOUNT ON; SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey >= @date END GO
In Power BI Desktop, get data from SQL Server, then:
- Enter Server name
- Enter Database name
- Select Data Connectivity Mode
- Expand the Advanced options
- Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
- Click OK
- Click Transform Data
Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:
At this point you have to modify the Power Query expression either from the Formula Bar or from Advanced Editor. We truncate the T-SQL statement after the @date, then we concatenate the Query string with the Query Parameter name. If the Query Parameter data type is not Text then we have to convert it to Text. In my sample, the data type is Number, therefore I use Text.From() function.
Here is a screenshot of the Power Query expression before changing the code:
The Power Query expression is:
Sql.Database(".\sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date = 20140101"])
- Change the code as below:
Sql.Database(".\sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date " & Text.From(DateKey)])
- Click Edit Permission
- Click Run
- That’s it. Here is the results:
We can use this method to parameterise any other T-SQL statements in Power BI with Query Parameters.
2 thoughts on “Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures”
Whatif my SP paramenter consists of a string of comma-separated values…how do I create a PBI query parameter that provides a list of comma-separated values to pass to stored procedure?
It is easy from a Power Query perspective.
You only need to create a Query Parameter with type Text.
Then pass the comma-separated values to the parameter.
That said, the heavy lifting is more on SQL Server side though.
In SQL Server, you require to split the string into values.
Read here how to do so.
Hopefully that helps,