DATE problem in SSRS with MySQL Database query fix
-
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 labcollectI 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 - clai_shock008 - Thursday, March 8, 2018 2:07 AMHi 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 labcollectI 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 queryI'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 useCAST(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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
ok tried it, but returns an error again
-
It might be that we need to format the date to remove the time component?
- clai_shock008 - Thursday, March 8, 2018 3:21 AM
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 PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
- Neil Burton - Thursday, March 8, 2018 3:29 AM
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) -
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, March 8, 2018 8:06 AM
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) 🙂 - clai_shock008 - Thursday, March 8, 2018 7:09 PMsgmunson - Thursday, March 8, 2018 8:06 AM
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) 🙂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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Friday, March 9, 2018 12:31 PMclai_shock008 - Thursday, March 8, 2018 7:09 PMsgmunson - Thursday, March 8, 2018 8:06 AM
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) 🙂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?
- clai_shock008 - Friday, March 9, 2018 11:09 PMsgmunson - Friday, March 9, 2018 12:31 PMclai_shock008 - Thursday, March 8, 2018 7:09 PMsgmunson - Thursday, March 8, 2018 8:06 AM
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) 🙂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?
The usual reason to use OPENQUERY or OPENROWSET is to ensure that you don't end up sending an entire table of data across the network to satisfy a JOIN to a Linked Server table. It's not the only one, though. Just being able to use the native SQL language for a Linked Server is the other. That way, you can write the query using mySQL syntax, and OPENQUERY or OPENROWSET then passes that along to the linked server and receives the results as a recordset that you can SELECT from, including using T-SQL functions to format data or otherwise transform any given column in that recordset. Just being able to do that allows you to combine some great features to SQL Server's T-SQL with the query results of your mySQL data. As to performance, that really shouldn't be affected, and unless you need to join that recordset to data on SQL Server, should be the minimum necessary to get that data over to SQL Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply