Forum Discussion

andyor's avatar
andyor
Resolver I
3 years ago
Solved

Importing Stored Procedure + Dynamic Date Parameter

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

Need Help
  • andyor's avatar
    andyor
    3 years ago

    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

     

1 Reply

  • andyor's avatar
    andyor
    Resolver I
    3 years ago

    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