Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Need to produce regular SQL report

  • 12-09-2013 6:21pm
    #1
    Registered Users, Registered Users 2 Posts: 7,733 ✭✭✭


    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


Comments

  • Registered Users, Registered Users 2 Posts: 2,021 ✭✭✭ChRoMe


    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.


  • Registered Users, Registered Users 2 Posts: 851 ✭✭✭TonyStark


    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?


  • Registered Users, Registered Users 2 Posts: 7,733 ✭✭✭StupidLikeAFox


    TonyStark wrote: »
    What's your online app written in?

    Its php linked to a mySQL database


  • Registered Users, Registered Users 2 Posts: 1,456 ✭✭✭FSL


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


  • Closed Accounts Posts: 8,015 ✭✭✭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.


  • Advertisement
  • Moderators, Technology & Internet Moderators Posts: 1,336 Mod ✭✭✭✭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.


  • Moderators, Society & Culture Moderators Posts: 17,643 Mod ✭✭✭✭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.


Advertisement