Custom date range parameter OR pre-defined date from drop down in SSRS

Question

I am developing a SSRS report in MSvs which allows 2 different date selections.

One option is to select a custom date range using the StartDate and EndDate parameters (allow NULL is selected).

The other option is to select from a pre-defined set of dates listed in a drop down which can also be NULL by selecting 'Select Custom'.

I have 2 data sets both of which are stored procedures in SQL server. One is my main dataset and the other is CalendarRanges where I have defined the pre-defined date ranges as follows:

CurrentWeek
PreviousWeek
CurrentMonth
Custom

I have fully stripped my code to provide an example.

Main data set stored procedure:

ALTER PROCEDURE MainDataset
(
    @StartDate DATETIME, 
    @EndDate DATETIME 
) 
WITH RECOMPILE  
AS
BEGIN

DECLARE @StartDateKey INT, @EndDateKey INT

SELECT
    @StartDateKey = MIN(DateKey),
    @StartDate = MIN(CalendarDate),
    @EndDateKey = MAX(DateKey),
    @EndDate = MAX(CalendarDate)

FROM
    Dim.Calendar
WHERE
    CalendarDate >= @StartDate
      AND CalendarDate <= @EndDate

;WITH Sales AS
(
    SELECT
        Sales.CurrencyKey,
        OrderId,
    FROM
        Fact.Sales
    WHERE   
        Sales.OrderDateKey = @StartDateKey
),

Payments AS
(
    SELECT
        Payments.PaymentCurrencyKey,
        PaymentID
    FROM    
        Payments
    WHERE
        AND Payment.DateKey >= @StartDateKey
        AND Payment.DateKey <= @EndDateKey
)
SELECT
    Sales.*,
    Payments.*,
    @StartDate AS 'StartDate',
    @EndDate AS 'EndDate'
FROM
    Sales
    LEFT JOIN Payments
        ON Payments.PaymentCurrencyKey = Sales.CurrencyKey

The Start and End Dates are linked with my @StartDate and @EndDate parameters in SSRS.

Dataset 2:

ALTER PROCEDURE CalendarRanges 
AS
SELECT 
1 AS [Order],
Value,
Label
FROM 
(SELECT 
CurrentWeekOfYear AS Value,
'CurrentWeek' AS Label
FROM Calendar
WHERE CalendarDate =  CONVERT(varchar, getdate(), 23)) CurrentWeek

UNION 

 -- Previous Week
SELECT 
2 AS [Order],
Value,
Label
FROM 
(SELECT 
  DISTINCT CurrentWeekOfYear -1 AS Value,
 'PreviousCurrentWeek' AS Label 
  FROM Calendar 
 WHERE CalendarDate =  CONVERT(varchar, getdate(), 23)) PreviousWeek

UNION

-- Current Month
SELECT 
  4 AS [Order],
  Value,
  Label
 FROM
 (SELECT 
 DISTINCT CurrentMonth AS Value,
 'CurrentMonth' AS Label
 FROM Calendar
 WHERE CurrentMonth = MONTH(GETDATE())) CurrentMonth

UNION

-- Custom 
 SELECT 
 5 AS [Order],
 value,
 Label
 FROM (
 SELECT 
 NULL AS Value,
'Select Custom' AS Label
 ) Custom

If I choose the 'Select Custom' option from the drop down and then select a custom start and end date the report runs as expected.

However, if i leave the custom start and end dates NULL and choose a pre-defined date from the drop down, the report returns no data. I have tested by pre-defining a date that displays data when selecting custom so I know that the data is there.

I would like to manipulate the report so that if the custom start and end date parameters are NULL, then use the drop down parameter instead but I am struggling to understand how to implement this?

Akhilesh Singh Akhilesh Singh · Accepted Answer · 2019-09-23T12:30:59

You can change some logic in your proecure...it may be help you out...

ALTER PROCEDURE MainDataset
(
    @StartDate VARCHAR(20)=NULL, 
    @EndDate VARCHAR(20)=NULL
) 
WITH RECOMPILE  
AS
BEGIN

DECLARE @StartDateKey INT, @EndDateKey INT

SELECT
    @StartDateKey = MIN(DateKey),
    --@StartDate = MIN(CalendarDate),
    @EndDateKey = MAX(DateKey)
   -- @EndDate = MAX(CalendarDate)

FROM
    Dim.Calendar
WHERE

convert(DATETIME,CalendarDate)  
BETWEEN Convert(DATETIME,CASE WHEN isnull(@StartDate,'')='' THEN CalendarDate
ELSE isnull(@StartDate,'') END)                     
AND Convert(DATETIME, CASE WHEN isnull(@EndDate,'')='' THEN CalendarDate 
ELSE isnull(@EndDate,'') END)  

/* this Line is commented....
CalendarDate >= @StartDate
AND CalendarDate <= @EndDate
*/

;WITH Sales AS
(
    SELECT
        Sales.CurrencyKey,
        OrderId,
    FROM
        Fact.Sales
    WHERE   
        Sales.OrderDateKey = @StartDateKey
),

Payments AS
(
    SELECT
        Payments.PaymentCurrencyKey,
        PaymentID
    FROM    
        Payments
    WHERE
        AND Payment.DateKey >= @StartDateKey
        AND Payment.DateKey <= @EndDateKey
)
SELECT
    Sales.*,
    Payments.*,
    @StartDate AS 'StartDate',
    @EndDate AS 'EndDate'
FROM
    Sales
    LEFT JOIN Payments
        ON Payments.PaymentCurrencyKey = Sales.CurrencyKey

In your First Procedure...Do not assign default value.... For E.g. if you enter only @FromDate only then it will automatically pick last date from your table....

Ex. Your table contain date range from 10 to 30 ... then if you enter 15 then..it will automatically select 15 to 30 date range data...and vise-versa For @ToDate also...

Note:--You can try this all Query...null part is also Handle in Custom Date Range.....

Custom date range parameter OR pre-defined date from drop down in SSRS

1 Answers