The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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!
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!
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
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())
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
68 | |
31 | |
24 | |
23 | |
13 |