I’ve written 3 blogposts about question parameters previously.
- Energy BI Desktop Question Parameters, Half 1, Introduction
- Energy BI Desktop Question Parameters, Part2, Dynamic Information Masking and Question Parameters
- Energy BI Desktop Question Parameters, Half 3, Checklist Output
That is the fourth one within the type of Fast Suggestions. Right here is the situation. Considered one of my clients had a requirement to get knowledge from a Saved Process from SQL Server. She required to move the values from a Question Parameter again to SQL Server and get the leads to 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 identify
- Enter Database identify
- Choose Information Connectivity Mode
- Develop 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
- Click on Remodel Information
Now we have to create a Question Parameter. In my pattern I create a DateKey in Decimal Quantity knowledge kind:
At this level you need to 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 identify. If the Question Parameter knowledge kind is just not Textual content then we’ve got to transform it to Textual content. In my pattern, the information kind is Quantity, subsequently I exploit Textual content.From() operate.
Here’s a screenshot of the Energy Question expression earlier than altering the code:
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 technique to parameterise some other T-SQL statements in Energy BI with Question Parameters.
Take pleasure in!