SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATE problem in SSRS with MySQL Database query fix


DATE problem in SSRS with MySQL Database query fix

Author
Message
clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
Hi I need some fix here to get the proper result for grouping dates in my BAR chart. I need to group them according to dates, not in date and time.
I used this query in mysql and it works, however applying to SSRS Query Builder it returns an sql error.
Error[07002][MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]SQLBindParameter not used for all parameters
SELECT COUNT(*) AS total_exam, DATE(labcollect) AS collecteddate 
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY DATE(labcollect)


BUT this one works fine but doesn't get the result I wanted:

SELECT COUNT(*) AS total_exam, labcollect AS collecteddate 
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY labcollect


I tried to change the query to this:
SELECT    COUNT(*) AS total_exam, CONVERT(labcollect, DATETIME) AS collecteddate
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY CONVERT(labcollect, DATETIME)

but still result is the same in the screenshot.

i want the 12/25/2017 dates be merged as one group using my query

Neil Burton
Neil Burton
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19539 Visits: 12714
clai_shock008 - Thursday, March 8, 2018 2:07 AM
Hi I need some fix here to get the proper result for grouping dates in my BAR chart. I need to group them according to dates, not in date and time.
I used this query in mysql and it works, however applying to SSRS Query Builder it returns an sql error.
Error[07002][MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]SQLBindParameter not used for all parameters
SELECT COUNT(*) AS total_exam, DATE(labcollect) AS collecteddate 
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY DATE(labcollect)


BUT this one works fine but doesn't get the result I wanted:

SELECT COUNT(*) AS total_exam, labcollect AS collecteddate 
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY labcollect


I tried to change the query to this:
SELECT    COUNT(*) AS total_exam, CONVERT(labcollect, DATETIME) AS collecteddate
FROM labinpat
WHERE (labcollect BETWEEN ? AND ?)
GROUP BY CONVERT(labcollect, DATETIME)

but still result is the same in the screenshot.

i want the 12/25/2017 dates be merged as one group using my query

I'm not hugely familiar with MySQL and I only rarely use Query Builder in SSRS but the problem is possibly:
GROUP BY CONVERT(labcollect, DATETIME)

which is not T-SQL syntax.
In T-SQL you'll need to use
CAST(labcollect AS DATE)

in your SELECT list and GROUP BY clause to remove the time component.



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
ok tried it, but returns an error again


clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
It might be that we need to format the date to remove the time component?
Neil Burton
Neil Burton
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19539 Visits: 12714
clai_shock008 - Thursday, March 8, 2018 3:21 AM
It might be that we need to format the date to remove the time component?

To group by date you'll definitely need to remove the time.
After reading your question properly, I'm not sure the error you're getting is actually because of the grouping. Have you declared any parameters to map to '?'?



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
Neil Burton - Thursday, March 8, 2018 3:29 AM
[quote]
clai_shock008 - Thursday, March 8, 2018 3:21 AM
It might be that we need to format the date to remove the time component?

To group by date you'll definitely need to remove the time.
After reading your question properly, I'm not sure the error you're getting is actually because of the grouping. Have you declared any parameters to map to '?'?


I havr this in my code:
labcolltableadapter.Fill(lablogsheet.Labcoll, _fromdtp1.value, _todtp1.value);
reportviewer2.RefreshReport();

I have parameters in dataset Labcoll:
Fill,GetData(labcollect, labcollect1)
sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98423 Visits: 7265
Seems likely that you have a data type issue. What data types does MySQL support? If they only support datetime as opposed to just a date (without time), then you may have no alternative but to use either OPENQUERY or OPENROWSET as part of your overall query. That portion would specify the Linked Server (existing or temporary) and the native MySQL query, which would become a table you could use as part of a T-SQL query that could then CONVERT the relevant column to date as opposed to datetime. However, we don't have any detail here as to exactly how you connect to the MySQL instance, so let us know...

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
sgmunson - Thursday, March 8, 2018 8:06 AM
Seems likely that you have a data type issue. What data types does MySQL support? If they only support datetime as opposed to just a date (without time), then you may have no alternative but to use either OPENQUERY or OPENROWSET as part of your overall query. That portion would specify the Linked Server (existing or temporary) and the native MySQL query, which would become a table you could use as part of a T-SQL query that could then CONVERT the relevant column to date as opposed to datetime. However, we don't have any detail here as to exactly how you connect to the MySQL instance, so let us know...
mySQL does support DATETIME but I think it's all about the incompatibility of the mysql syntax using SSRS.

I finally found a way to fix the issue in this grouping of dates. I just managed to create a group expression in my chart report.
under Category Group Properties i added:
=Year(Fields!collecteddate.Value)
=Month(Fields!collecteddate.Value)
=Day(Fields!collecteddate.Value)
Smile

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98423 Visits: 7265
clai_shock008 - Thursday, March 8, 2018 7:09 PM
sgmunson - Thursday, March 8, 2018 8:06 AM
Seems likely that you have a data type issue. What data types does MySQL support? If they only support datetime as opposed to just a date (without time), then you may have no alternative but to use either OPENQUERY or OPENROWSET as part of your overall query. That portion would specify the Linked Server (existing or temporary) and the native MySQL query, which would become a table you could use as part of a T-SQL query that could then CONVERT the relevant column to date as opposed to datetime. However, we don't have any detail here as to exactly how you connect to the MySQL instance, so let us know...
mySQL does support DATETIME but I think it's all about the incompatibility of the mysql syntax using SSRS.

I finally found a way to fix the issue in this grouping of dates. I just managed to create a group expression in my chart report.
under Category Group Properties i added:
=Year(Fields!collecteddate.Value)
=Month(Fields!collecteddate.Value)
=Day(Fields!collecteddate.Value)
Smile

SSRS doesn't support mySQL syntax. SQL Server can if you use either OPENQUERY or OPENROWSET as part of the query, although at that point, you would access the mySQL instance via a Linked Server as opposed to connecting directly to it.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
clai_shock008
clai_shock008
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 33
sgmunson - Friday, March 9, 2018 12:31 PM
clai_shock008 - Thursday, March 8, 2018 7:09 PM
sgmunson - Thursday, March 8, 2018 8:06 AM
Seems likely that you have a data type issue. What data types does MySQL support? If they only support datetime as opposed to just a date (without time), then you may have no alternative but to use either OPENQUERY or OPENROWSET as part of your overall query. That portion would specify the Linked Server (existing or temporary) and the native MySQL query, which would become a table you could use as part of a T-SQL query that could then CONVERT the relevant column to date as opposed to datetime. However, we don't have any detail here as to exactly how you connect to the MySQL instance, so let us know...
mySQL does support DATETIME but I think it's all about the incompatibility of the mysql syntax using SSRS.

I finally found a way to fix the issue in this grouping of dates. I just managed to create a group expression in my chart report.
under Category Group Properties i added:
=Year(Fields!collecteddate.Value)
=Month(Fields!collecteddate.Value)
=Day(Fields!collecteddate.Value)
Smile

SSRS doesn't support mySQL syntax. SQL Server can if you use either OPENQUERY or OPENROWSET as part of the query, although at that point, you would access the mySQL instance via a Linked Server as opposed to connecting directly to it.

will it minimize the performance using OPENQUERY/OPENROWSET?

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search