CODEWITHSUNDEEP
sqlreporting-services
Supermode
Supermode

Reputation: 936

SSRS Reporting with Date ranges are optional

I am currently try to build report with filter that has Date range (DateTO and DateFrom). The problem is the report need to have ability for date range as optional. THe following is my current SQL. Please advise how i can modify 

SELECT 

PG.Name AS [Project Group],
PSG.Name AS [Project Sub Group],
P.ReferenceNumber,
P.ReferenceNumber + ' / ' + P.ProjectTitle AS [Project Description],
CASE WHEN W.Removed = 1
 THEN 'Yes'
 ELSE 'No'
END AS [Removed],
W.Description,
W.CommunicationStartDate,
PR.Name as [Person Responsible],


CASE WHEN W.CommunicationStartDate IS NULL
 THEN 'Not Specified'
 ELSE CONVERT(NVARCHAR, W.CommunicationStartDate, 103)
END AS [Deadline],
ES.Name AS [Status],
CASE WHEN W.Estimate IS NOT NULL
 THEN W.Estimate
 ELSE 0
END AS [Estimate]

FROM dbo.ProjectWorkplan W
  INNER JOIN dbo.Project P
   INNER JOIN dbo.ProjectSubGroup PSG
   INNER JOIN dbo.ProjectGroup PG ON PSG.ProjectGroupId = PG.ProjectGroupId
  ON P.ProjectSubGroupId = PSG.ProjectSubGroupId
 ON W.ProjectId = P.ProjectId
 INNER JOIN dbo.PersonResponsible PR ON W.PersonResponsibleId = PR.PersonResponsibleId
 INNER JOIN dbo.ElementStatus ES ON W.ElementStatusId = ES.ElementStatusId

WHERE P.DateCompleted IS NULL AND W.Removed = 0
AND W.CommunicationStartDate BETWEEN @DateFrom AND @DateTo


AND ES.NAME in (@ActionRequired, @FollowUp, @OnHold, @UrgentAction, @Waiting,@Complete)


ORDER BY PG.Name, PSG.Name, P.ReferenceNumber, W.Removed, W.CommunicationStartDate

Upvotes: 0

Views: 1040

Answers (1)

suff trek
suff trek

Reputation: 39777

You can check the date parameters for NULL and use them if only both of them are valid:

AND (@DateFrom IS NULL OR @DateTo IS NULL OR W.CommunicationStartDate BETWEEN @DateFrom AND @DateTo)

Upvotes: 1

Related Questions