Question rdlc: Bar Chart Dataset query

[dualshck012]

Good Day!
Need help on SQL query for my rdlc reporting file in visual studio. I have a BAR chart which displays the total patient collections but my chart displays all the data in specific dates and not according to group dates or any relevant term to say. The screenshot below tells the problem at dates 2/25/2018 which should’ve been merged in one bar for the total patient in that date.

see attached image


Here is the script from the query builder in Visual Studio 2015:

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

I think I’m missing a query after GROUP BY labcollect . I tried GROUP BY CONVERT(labcollect) / CAST(labcollect AS DATE) but return as error.

IDE: VS2015
Database: MySQL through ODBC

 

[jmcilhinney]

The issue appears to be that you are grouping by a column that contains both date and time when you want the data grouped by date only. Instead of using `labcollect`, you need to use just the date component of `labcollect`. I don't really use MySQL so I don't know what function it uses to get just the date from a date/time but that should be fairly simple to find out. Let's say, for argument's sake, that it is `GETDATE(labcollect)`. You need to use that in both your SELECT and GROUP BY clauses.

 

[jmcilhinney]

Just did a quick search and it appears that you need to use `DATE(labcollect)`.

 

[dualshck012]

Just did a quick search and it appears that you need to use `DATE(labcollect)`.

OK sir i'll try it

 

[dualshck012]

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

I just tried this one but an error appear afterwards:

Error: sql execution error.

Error[07002][MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]SQLBindParameter not used for all parameters

BUT this one works fine but doesnt get the result I wanted:

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

 

[jmcilhinney]

I don't really use MySQL I'm afraid. That first option would work for SQL Server, which is what I use pretty much all the time. Maybe you need to do some research on the database you're using. For one thing, you have an error code. Did you search for information on that?

 

[dualshck012]

OK sir i'll try it

actually when I change the ? values to relative date value sample: '2017-12-01 00:23:59' in BETWEEN clause it actually works, but that omits the parameters in which i applied in my design. I have two datetimepickers for that.

 

[jmcilhinney]

Are your filters part of the report or are you populating a DataSet and passing that to the report?

 

[dualshck012]

Populating a dataset and passing to report sir.

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

I have parameters in dataset Labcoll:
Fill,GetData(labcollect, labcollect1)