I am working on a ssrs report and the requirement for the report is ; Start & End date filters and Order number filter which will be searchbox. The report will show today's data but if they want to see previous days I have to have these 3 filters on my report.
My challenge is how to add the logic in where clause. I tried below logic but didn't work bc for some cases @ordernumber or date filters might be null.
and sh.CustomerOrderNbr = (@OrderNumber)
and (Convert(date, cr.CreateDt) between @StartDate and @EndDate)
Expecting to add a logic that can return date range and ordernumber filters Or either @ordernumber or @StartDate&EndDate
and Convert(date, cr.CreateDt) between ISNULL(@StartDate,'2001-01-01') and isnull(@EndDate,'2099-01-01')
and (
(ISNULL(@OrderNumber,'') <> '' and sh.CustomerOrderNbr = @OrderNumber)
OR
(ISNULL(@ordernumber,'') = '' And sh.CustomerOrderNbr <>'')
)
Came up with the solution.