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!
