Importing Stored Procedure + Dynamic Date Parameter

andyor — Mon, 12 Sep 2022 05:06:38 GMT

I'm new to to Power BI and I've been researching how to import Stored Procedure's from Get Data query in PBI and someone said that the easiest way is to Execute in SQL Server, then copy in the execution code, as below.

DECLARE @return_value int EXEC @return_value = [Reporting].[WallerNumbers] @TimeZoneID = 1, @TZStartDate = @start, @TZEndDate = @end, @DateType = 3 SELECT 'Return Value' = @return_value EXEC @return_value = [Reporting].[XXXXX] @TimeZoneID = 1, @TZStartDate = N'2022-09-01', @TZEndDate = N'2022-09-12', @DateType = 3 SELECT 'Return Value' = @return_value

This works fine but I have 2 questions from here:

1) Is this the best way to import into Power BI from a Stored Procedure?

2) How can I manipulate this to have the TZEndDate paramater be dynamic i.e.., getdate()? If I was using SSRS I would hard code this in the report parameters

Re: Importing Stored Procedure + Dynamic Date Parameter

andyor — Mon, 12 Sep 2022 05:06:54 GMT

The solution is to declare and set these in the import statement E.g.,

DECLARE @return_value int DECLARE @start datetime DECLARE @end datetime SET @start = DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) SET @end = getdate() EXEC @return_value = [Reporting].[XXXXX] @TimeZoneID = 1, @TZStartDate = @start, @TZEndDate = @end, @DateType = 3 SELECT 'Return Value' = @return_value

topic Importing Stored Procedure + Dynamic Date Parameter in Developer

Importing Stored Procedure + Dynamic Date Parameter

Re: Importing Stored Procedure + Dynamic Date Parameter