Fast Ideas, 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 up to now.

That is the fourth one within the type of Fast Ideas. Right here is the situation. One among my prospects had a requirement to get information from a Saved Process from SQL Server. She required to go the values from a Question Parameter again to SQL Server and get the leads to Energy BI.

The answer is considerably simple. I created a easy saved process in AdventureWorksDW2019 as under:

	@date int
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date

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

  • Enter Server title
  • Enter Database title
  • Choose Information Connectivity Mode
  • Broaden the Superior choices
  • Kind in a SQL assertion to name the saved process like under:
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 Information
Transform Data in Power BI Desktop

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

Creating New Query Parameter in Power BI Desktop

At this level you must modify the Energy Question expression both from the Formulation 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 information sort will not be Textual content then we’ve got to transform it to Textual content. In my pattern, the information sort is Quantity, subsequently I take advantage of Textual content.From() perform.

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 under:
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 will use this methodology to parameterise some other T-SQL statements in Energy BI with Question Parameters.

Take pleasure in!


Please enter your comment!
Please enter your name here

Share post:




More like this

CPI-weighted wage development – Financial institution Underground

Josh Martin The Financial Coverage Committee has not too...

US Pandemic Response Influence on Inequality & Progressivity

The pandemic led to...

Learn how to Align Your PR and Advertising Methods to Get Extra Out of Each

Opinions expressed by Entrepreneur...

How Synthetic Intelligence Is Impacting the Authorized Business

The authorized business presents a novel distinction: lots...