New to these specific reporting controls, and that "ah ah" moment just hasn't arrived. Creating the class for a report, generating the datasource, setting up the parameters makes sense but getting it to work with parameters in designer is eluding me. I want to pass in a "begin" and "end" date for the data I pull, so I've set up the parameters in the datasource as @dtstrDate and @dtendDate, with DbType as Date and the Value as "=Parameters.startDate.Value" and "= Parameters.endDate.Value" respectively.
SELECT w.Firstname, w.Lastname, wf.Login, wf.Logout, DATEDIFF(hour, wf.Login, wf.Logout) AS Hours
FROM Workers AS w INNER JOIN
Workflow AS wf ON wf.LoggedInWorkerid = w.ID
WHERE (wf.Login >= @dtstrtDate) AND (wf.Logout <= @dtendDate)
I've tested it in SQL Server with
DECLARE @dtstrtDate AS DateTime
SET @dtstrtDate = '02/13/2012'
DECLARE @dtendDate AS DateTime
SET @dtendDate = '02/17/2012'
SELECT w.Firstname, w.Lastname, SUM(DATEDIFF(hour, wf.Login, wf.Logout)) AS Hours
FROM Workers AS w JOIN Workflow AS wf ON wf.LoggedInWorkerid = w.ID
WHERE wf.Login >= @dtstrtDate AND wf.Logout <= @dtendDate
GROUP BY w.Lastname, w.FirstName
... and it works just fine.
In the Configure DataSource Parameters
Name DbType Value
@dtstrtDate Date =Parameters.startDate.Value
@dtendDate Date =Parameters.endDate.Value
Design Time Parameters
@dtstrtDate =02/13/2012 (and I've tried putting these values in single quotes as well)
I've tried setting the DbType to string, as well on the designer, setting the startDate and endDate members in the ReportParameter collection to DateTime and String, and even using the CDate function to wrap the Parameters.startDate.Value i.e = CDate(Parameters.startDate.Value)...When I try to execute the query in the designer I get "Conversion failed when converting date/and or time from character string." What am I doing incorrectly?