SSRS with a parameter @startdate-CodePudding

I currently have an SSRS report that runs daily with the below SQL code as it should. But, when replication goes down and we miss a day, our Court people would like to be able to manually run a missed date by entering a date #1.

I need some help with setting up the parameter @StartDate, that will run the code below.

I have this SQL:

Select *

from Court

WHERE

case_filed_dt =  
CASE WHEN datepart(weekday,getdate())= 2 THEN  
   datediff(day,3,getdate())
  ELSE
   datediff(day,1,getdate()) 
  END

Order by court asc

Simple case statement that looks at the date the report is run, if it runs on Monday's, it get Friday's data otherwise previous day's data.

I would like to add a parameter @startdate for my "case_filed_dt" field, to run manually, in case a report is missed.

Example:

If I run for @startdate = '06-06-2022' it will do as my case statement code does, and get data for '06-03-2022'. If I run for @startdate ='06-07-2022', data is for 6-06-2022'.

Thanks, jer

CodePudding user response：

I would keep this simple.

Change your existing dataset query to accept a parameter (pStartDate) like this..

Select *
from Court
WHERE
case_filed_dt =  
CASE WHEN datepart(weekday, @pStartDate)= 2 THEN  
   datediff(day,3, @pStartDate)
  ELSE
   datediff(day,1, @pStartDate) 
  END
Order by court asc

Then in your report, set the parameter's default value to be an expression

=Today()

Then if the report is run as normal with no parameters passed, it will use Today() as the start date or if the report is run manually, any date can be selected by the user.

CodePudding user response：

a few versions on the fiddle, which should get you started, this one using a variable to simulate the effects of the Case statement. Change the date and see what happens

Declare @DateNow datetime = '2022-06-03'
SELECT @DateNow as YourDate, DATENAME(WEEKDAY, @DateNow) AS DayNow, '',
CASE 
WHEN DATENAME(WEEKDAY, @DateNow) = 'Monday' THEN 
   DateAdd(day,-3,@DateNow)
   ELSE
     DateAdd(day,-1,@DateNow)
    END AS ReportDate

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b3ae703d3be9ace930822f7e20230018