Parameters problem when connecting to MySQL db

[Andrew Wingate]

Hi,

I'm having trouble getting parameters to work in SSRS reports with a MySQL datasource.

Here is my Select (pulling comments out of a vtiger db)

---
SELECT `vtiger_modcomments`.`modcommentsid`

FROM `cl23-vtiger`.`vtiger_modcomments` join
`vtiger_crmentity` as `crm2` on `vtiger_modcomments`.`modcommentsid` = crm2.crmid join
`vtiger_users` on `vtiger_modcomments`.`userid` = `vtiger_users`.`id` join
vtiger_crmentity on vtiger_modcomments.related_to = vtiger_crmentity.crmid

WHERE crm2.modifiedtime between '2014-01-01' and '2014-04-07'

ORDER BY modcommentsid
---

This works fine, but when trying to use parameters for the start and end date I'm stuck. Have tried various combinations of

WHERE crm2.modifiedtime between '?' and '?'

and

WHERE crm2.modifiedtime between ? and ?

neither of these adds any parameters to my report, I can add these manually, but keep getting index out of range.

Has anyone solved this problem? or could point me in the right direction?



I am using MS-SQL 2008R2 and the MySql Connector/net method described here http://bit.ly/1oIJG5C


Kind regards
Andy

[Andrew Wingate]

Sorted it:

Manually added two parameters
@start (date/time)
@end (date/time)

Dataset Properties dialog

updated the select statement with:
where date between @start and @end

The system gives an error warning about defining the parameters

Then define parameters in dataset properties

Post by Andrew Wingate
Hi,
I'm having trouble getting parameters to work in SSRS reports with a MySQL datasource.
Here is my Select (pulling comments out of a vtiger db)
---
SELECT `vtiger_modcomments`.`modcommentsid`
FROM `cl23-vtiger`.`vtiger_modcomments` join
`vtiger_crmentity` as `crm2` on `vtiger_modcomments`.`modcommentsid` = crm2.crmid join
`vtiger_users` on `vtiger_modcomments`.`userid` = `vtiger_users`.`id` join
vtiger_crmentity on vtiger_modcomments.related_to = vtiger_crmentity.crmid
WHERE crm2.modifiedtime between '2014-01-01' and '2014-04-07'
ORDER BY modcommentsid
---
This works fine, but when trying to use parameters for the start and end date I'm stuck. Have tried various combinations of
WHERE crm2.modifiedtime between '?' and '?'
and
WHERE crm2.modifiedtime between ? and ?
neither of these adds any parameters to my report, I can add these manually, but keep getting index out of range.
Has anyone solved this problem? or could point me in the right direction?
I am using MS-SQL 2008R2 and the MySql Connector/net method described here http://bit.ly/1oIJG5C
Kind regards
Andy