Need to produce regular SQL report

StupidLikeAFox

Hi guys,

I have an online app which records time to a mySQL database that has table entries like this:

ID Employee Hrs Project Date
1 John 5 SampleProj1 10/9/2013
2 Paddy 5 SampleProj2 10/9/2013
3 John 5 SampleProj2 12/9/2013
4 John 5 SampleProj2 13/9/2013
5 Paddy 5 SampleProj1 13/9/2013
6 Paddy 5 SampleProj1 18/9/2013

The online app can produce a basic report. I can select a date range and it dumps the data into a CSV file in the format above . I then have to sort the data in excel so it outputs as follows:

Employee: John
1 John 5 SampleProj1 10/9/2013
3 John 5 SampleProj2 12/9/2013
4 John 5 SampleProj2 13/9/2013
Total for Period: 15


Employee: Paddy
2 Paddy 5 SampleProj2 10/9/2013
5 Paddy 5 SampleProj1 13/9/2013
6 Paddy 10 SampleProj1 18/9/2013
Total for Period: 20

I want to something that would connect to the database, download the data, format it in the correct way and then produce something that can be printed out.

If I could produce the report broken down by projects as well as employees that would be great also.

If there is a program that already exists that would be ideal.

I have very basic programming skills but I am guessing this is a straightforward enough thing to do, so if there was some tutorial in VB or something I would be willing to have a stab at it

ChRoMe

StupidLikeAFox wrote: »

Hi guys,

I have an online app which records time to a mySQL database that has table entries like this:



The online app can produce a basic report. I can select a date range and it dumps the data into a CSV file in the format above . I then have to sort the data in excel so it outputs as follows:




I want to something that would connect to the database, download the data, format it in the correct way and then produce something that can be printed out.

If I could produce the report broken down by projects as well as employees that would be great also.

If there is a program that already exists that would be ideal.

I have very basic programming skills but I am guessing this is a straightforward enough thing to do, so if there was some tutorial in VB or something I would be willing to have a stab at it

ODBC in excel might be what you are looking for.

TonyStark

StupidLikeAFox wrote: »

Hi guys,

I have an online app which records time to a mySQL database that has table entries like this:



The online app can produce a basic report. I can select a date range and it dumps the data into a CSV file in the format above . I then have to sort the data in excel so it outputs as follows:




I want to something that would connect to the database, download the data, format it in the correct way and then produce something that can be printed out.

If I could produce the report broken down by projects as well as employees that would be great also.

If there is a program that already exists that would be ideal.

I have very basic programming skills but I am guessing this is a straightforward enough thing to do, so if there was some tutorial in VB or something I would be willing to have a stab at it

What's your online app written in?

StupidLikeAFox

TonyStark wrote: »

What's your online app written in?

Its php linked to a mySQL database

FSL

Modify the mysql query to include order by clauses. The CSV file will then be in the required sequence.

CreepingDeath

FSL wrote: »

Modify the mysql query to include order by clauses. The CSV file will then be in the required sequence.

You might even be able to tweak the MIME type of the HTTP Response, and set it to "Content-type: application/vnd.ms-excel".
Which might get Excel to open up CSV directly too.

croo

You could try jasper reports with their iReport app to design reports
http://community.jaspersoft.com/project/ireport-designer
But you would need remote access to the database itself which is not typically allowed on a website host for security reasons.

Graham

Would it not be possible to add an additional PHP page to present the data in the required format? Personally I'd much prefer that to having bits of functionality stuck all over the place with a duct-tape solution.