January 30, 2015 at 5:30 am
Hello all
I have done a search all over for this, but it's hard to find any results among all those to do with adding NULLs to multiple value parameters.:-)
I am doing a quick report with Date From and Date To parameters. I am putting an expression in the @DateTo parameter value of
=DateAdd("d",1,Parameters!DateTo.Value)
I use this in a lot of reports where I am querying on a DATETIME and want to ignore the times, it works absolutley fine. The problem with this report is that I want to be able to allow NULL values so that all records are returned.
This is the query and it behaves exactly as expected in SSMS.
SELECT
GroupedJobs.*
,d.JobName
FROM
(SELECT
RequestId
,MAX(StartTime) AS MostRecentRun
,OutputType
,COUNT(RequestId) AS CountOfRuns
FROM
EurekaServer.JobRun
WHERE
StartTime BETWEEN @DateFrom AND @DateTo OR (@DateFrom IS NULL AND @DateTo IS NULL)
GROUP BY
RequestId
,OutputType
) AS GroupedJobs
INNER JOIN EurekaServer.JobData AS d
ON d.RequestId = GroupedJobs.RequestId
ORDER BY
GroupedJobs.MostRecentRun DESC
Once in the report it works fine, with or without NULL parameters, as long as I don't have my expression in the parameter value that adds the day to @DateTo. With the expression I get the error:
SqlDateTime overflow. Must be between etc etc
Makes sense I thought, trying to add a date to NULL. So I changed the expression to check whether anything was there and if not return Nothing, otherwise add a day to the value.
=iif(isNothing(DateAdd("d",1,Parameters!DateTo.Value)),Nothing,DateAdd("d",1,Parameters!DateTo.Value))
I get the same error. Can anybody tell me please if this is even possible? It's very frustrating because I know each component part works! I have tried substituting Nothing for "NULL", NULL and System.DBNull in the last expression.
TIA
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
January 30, 2015 at 5:35 am
What a complete idiot I am.
I previewed this 4 times checking code and spelling etc. When reading it through one final time after posting I found the reason, a silly copy and paste error :rolleyes:
The Parameter value expression should of course be:
=iif(isNothing(Parameters!DateTo.Value),Nothing,DateAdd("d",1,Parameters!DateTo.Value))
I will leave the post up in case it's useful to anyone searching for similar.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply