Power BI Report Builder not passing NULL date parameter value

[MarkPalmberg]

Hi.

I have a stored proc that pulls transactions for a selected date range. Here's what the variable declarations bit looks like:

ALTER PROCEDURE [MySchema].[usp_MyProc]
(
@FromDate DATETIME NULL,
@ToDate DATETIME NULL,
@College NVARCHAR(MAX),
@Department NVARCHAR(MAX),
@Area NVARCHAR(MAX),
@Account NVARCHAR(MAX), 
@GiftAmtFrom MONEY,	
@GiftAmtTo MONEY,
@Country NVARCHAR(MAX),
@State NVARCHAR(MAX),
@Administrator NVARCHAR(MAX),
@Transactiontype NVARCHAR(MAX),
@Organization BIT,
@Degreearea INT,
@Fundraiser NVARCHAR(MAX)
)

AS
BEGIN
SET NOCOUNT ON;

SET @FromDate =	ISNULL (@FromDate,DATEADD(dd,-8,GETDATE()))
SET	@ToDate =	ISNULL (@ToDate, DATEADD(dd,-1,GETDATE()))

 When I exec this proc in SSMS, the @FromDate and @ToDate settings perform correctly. In other words, this:

EXEC [MySchema].[usp_MyProc] @FromDate = NULL,
											 @ToDate = NULL, 
											 @College = 'All', 
											 @Department = 'All',
											 @Area = 'All',
											 @Account = 'All',
											 @GiftAmtFrom = 0.01,
											 @GiftAmtTo = 999999999.99,
											 @Country = 'All',
											 @State = 'All',
											 @Organization = null,
											 <li-user uid="170347" login="administrator"></li-user> = 'All',
											 @Transactiontype = null,
											 @Degreearea = null,
											 @Fundraiser = 'All'

Returns only transactions from the prior week (-1 day to account for DW refresh).

When I execute this stored proc from an rdl in Power BI Report Builder (where I have @FromDate and @ToDate parameters set up to pass to the proc), the two date parameter values are being ignored. Both date parameters in the rdl are set up as Date/Time, Allow null value. Default value is (Null). Removing the default value doesn't have any effect.

Thanks for any thoughts you may have!

[MarkPalmberg · Accepted solution]

I tracked this down. Sorry to clutter the forum before I did an exhaustive review on my end! Turns out there was a data sync issue in our DW. 😵 Happy Monday!

[v-kkf-msft]

Hi @MarkPalmberg ,

Has your problem been solved? If it is solved, please accept your reply as solution, which will help others find answers quickly.

Best Regards,
Winniz

[MarkPalmberg]

I also tried removing the @FromDate and @ToDate variable settings in the stored proc and then setting the default values in Report Builder as below, but no love there, either.

FromDate:

=DateAdd("d",-8,Now())

ToDate:
=DateAdd("d",-1,Now())