SSRS Date Parameter

• Briceston

  SSCrazy

  Points: 2899

  More actions

  November 7, 2017 at 9:03 pm

  #340431

  Hi guys,

  So I have SSRS report that has multiple parameters( Dates(date time) Serial# (text)  and MedicalRecord(text). All the parameters have allow null values. I want to be able to run by specific parameters only. I can run the report by Serial or Medical Record. However, the report will not run by dates and returns no records.

  I know the problem is something simple in the way I define the parameters that  I'm not catching.
  

   

  Where (VisitDate Between COALESCE(@VisitDateMin,'01/01/2001') and
  COALESCE(@VisitDateMax,'12/31/2020' )
  or  Serial# = (@Serial#)
  or MedicalRecord  = (@ MedicalRecord)

• Thom A

  SSC Guru

  Points: 99135

  More actions

  November 8, 2017 at 2:04 am

  #1967093

  You would be better writing your query in several parts using an IF, depending on what parameters have been supplied. This is only for if the dates have been, but it should give you the right idea:

  CREATE PROC YourSP @Serial varchar(10), @MedicalRecord varchar(10),

          @VisitDateMin date = NULL, @VisitDateMax date = NULL AS

    --Process for if Dates are NULL, I have assumed that both need to be supplied, or neither

    --If not, you'll need do a bbit more splitting

    IF @VisitDateMax IS NULL AND @VisitDateMin IS NULL BEGIN

    

     SELECT YourColumns

     FROM YourTable

     WHERE Serial# = @Serial

       AND MedicalRecord = @MedicalRecord;

    END

    --Now when the Dates have values

    ELSE IF @VisitDateMin IS NOT NULL AND @VisitDateMax IS NOT NULL BEGIN

    

     SELECT YourColumns

     FROM YourTable

     WHERE Serial# = @Serial

      AND MedicalRecord = @MedicalRecord

      AND VisitDate >= @VisitDateMin AND VisitDate < DATEADD(DAY, 1, @VisitDateMax);
  

    END

  GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk