Thursday, May 19, 2022
HomeBusiness IntelligenceFast Suggestions, Energy BI Desktop, Question Parameters, Half 4, Passing Energy Question...

Fast Suggestions, Energy BI Desktop, Question Parameters, Half 4, Passing Energy Question Parameter Values to SQL Server Saved Procedures


I’ve written 3 blogposts about question parameters prior to now.

That is the fourth one within the type of Fast Suggestions. Right here is the situation. One among my clients had a requirement to get knowledge from a Saved Process from SQL Server. She required to cross the values from a Question Parameter again to SQL Server and get the ends in Energy BI.

The answer is considerably straightforward. I created a easy saved process in AdventureWorksDW2019 as beneath:

CREATE PROCEDURE SP_Sales_by_Date 
	@date int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date
END
GO

In Energy BI Desktop, get knowledge from SQL Server, then:

  • Enter Server title
  • Enter Database title
  • Choose Knowledge Connectivity Mode
  • Broaden the Superior choices
  • Sort in a SQL assertion to name the saved process like beneath:
exec SP_Sales_by_Date @date = 20140101
  • Click on OK
Get Data From SQL Server using SQL Statements in Power BI Desktop
  • Click on Remodel Knowledge
Transform Data in Power BI Desktop

Now we have to create a Question Parameter. In my pattern I create a DateKey in Decimal Quantity knowledge kind:

Creating New Query Parameter in Power BI Desktop

At this level it’s important to modify the Energy Question expression both from the Components Bar or from Superior Editor. We truncate the T-SQL assertion after the @date, then we concatenate the Question string with the Question Parameter title. If the Question Parameter knowledge kind shouldn’t be Textual content then we’ve got to transform it to Textual content. In my pattern, the info kind is Quantity, subsequently I take advantage of Textual content.From() operate.

Here’s a screenshot of the Energy Question expression earlier than altering the code:

Power Query Expressions in the Formula Bar in Power Query Editor in Power BI Desktop

The Energy Question expression is:

Sql.Database(".sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date = 20140101"])
  • Change the code as beneath:
Sql.Database(".sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date " & Text.From(DateKey)])
  • Click on Edit Permission
  • Click on Run
  • That’s it. Right here is the outcomes:

We are able to use this methodology to parameterise every other T-SQL statements in Energy BI with Question Parameters.

Take pleasure in!

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments