• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

How to create report from MySQL

Hello - New area for me. I have a database running on a Centos (Linux) system using MySQL Workbench V 5.2.47.
What I wish to achieve is have a shortcut on the MySQL system's desktop that when launched it will prompt you to enter your search or query detail, say name, job and date range. When the OK button is clicked a report is generated on the screen showing the relevant data.

If the entry and report screens could be made pretty it it will be great. What tools are required for this please and any help would be well received?
0
Compuit1
Asked:
Compuit1
  • 7
  • 5
  • 3
  • +4
3 Solutions
 
DcpKingCommented:
You could look at either jasper or pentaho. Alternatively you could fairly easily write your own using PHP and an Apache web server.

hth

Mike
0
 
Compuit1Author Commented:
Right I have setup a test environment where and uploaded test data into MySQL.

Using the search tools that come with phpMyAdmin I can pull the query data .... How do i put that query into a script that will prompt for a date or name etc if run / launched. Sorry have little knowledge on this topic...
sample-data.png
0
 
Compuit1Author Commented:
Here is a simple query and the output... Simple query and output
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Compuit1Author Commented:
How could I achieve this from say a shortcut on the desktop instead of logging into MySQL and running the search tool? Can a few lines of code do it? If so could someone point me to an example please?
0
 
RobOwner (Aidellio)Commented:
Keeping it simple, Mysql just a database. I.e. it just stores data for you.
As for how you view that data is completely up to you. I know that doesn't help much but it's hard to give a recommendation when I don't know how the data is going to be used. As has been mentioned above you could use jasper etc but that's only relevant if you're doing reporting on such things like sales data etc (simplified analysis)
You mentioned a shortcut on your desktop and prompts for a user so what is your actual project? What are your programming skills like?
It may just be easier to use ms access with linked tables to your mysql database as it will give you an easy way to create an user friendly interface to your data
0
 
Olaf DoschkeSoftware DeveloperCommented:
MySQL workbench is a database administration tool. As tagit has said MySQL is just your data store.

You already have some good advice right from the start from DcpKing: jasper or pentaho or (unlike the workbench) reporting tools, as far as I see, if you have questions about that ask him.

Other popular reporting tools are crystal reports (on Windows). And indeed you can easily create html output from a query and a short html form for entering a name or date range. Still that's only easy to developers. It's not much code needed, but there are several concepts to comprehend from Setting up a webserver (on CentOS or any linux system apache should already be available, though), creating some HTML Form for input and an output page, eg with PHP, then PHP and it's MySQL integration with pdo, for example.

The MySQL queries you run in the workbench are already a major ingredient, but the easiest solution to make use of that query is a reporting tool. I'm not very familar with Linux tooling, but maybe just install jasper and see, if you can create the report as wanted. And then it should be easy to start the report from a browser, so your desktop link would just be a URL to a startpage with the form asking for the report variables.

Bye, Olaf
0
 
ZberteocCommented:
If you want to connect to the MySQL server from Windows client and you don't want to install any tools then you can use Excel:

http://office.microsoft.com/en-ca/excel-help/connect-to-a-mysql-database-HA104019820.aspx
0
 
RobOwner (Aidellio)Commented:
Excel is a great suggestion but unless we know exactly how your wanting to display and interact with the data these suggestions are futile.
0
 
ZberteocCommented:
"Excel is a great suggestion but unless we know exactly how your wanting to display and interact with the data these suggestions are futile."

:o) You mean "great but useless". Thanks. I could say about the same about your comment though, no offense intended.

Anyway, that's up to the asker to decide what' useless and what's not, isn't it?
0
 
RobOwner (Aidellio)Commented:
No that's not what I'm saying at all.  I was giving credit to your suggestion :)  

However, I am making the point though that it still may not be suitable for what the OP wants to do.  I could list "thousands" of ways to interface with a database but they're only useful if the asker can use it in their project.  If we just keep throwing ideas with no feedback then it's just confusing.  From their point of view they may have to test and research each idea we give them so could also end up a waste of time.  So yes it is up to the asker to decide what's useless and what's not.

As for my comments above, hardly useless as I'm also questioning the asker as to their intentions with the data not just saying "use this".
0
 
ZberteocCommented:
Agreed, but one way of solving problems here IS to make suggestions and in many cases the asker will pick whichever suits him better without the need to study the whole history and the future of the problem. :o)

Cheers!
0
 
RobOwner (Aidellio)Commented:
I agree! :)

This is in no way aimed at you but I think what's generally missing here at EE is helping the asker find the best solution.  I see so may suggestions without probing the asker to find out what the asker is trying to achieve.  You just caught a bit of my rant, that's all ;)  The futile comment is aimed at all the suggestions, not just yours ;)
0
 
Compuit1Author Commented:
Hello thanks for the feedback - just quickly the time frame in which I needed help has passed but in the end I was thinking of DataVision and Crystal reports. We were in a rush to get something in place and due to time constraints I found and put together some SQL queries for the user and saved them as snippets in the user's MySQL Workbench console. Now this met the immediate requirement to quickly query and retrieve result on the screen for given search criteria albeit not the best. Then there is a feature to saving the report data to disk in My Workbench which worked as well for their needs.

Now I will look at what has been given as a solution and allocate according but just at the moment I do not have time.... "I will be back".
0
 
RobOwner (Aidellio)Commented:
Thanks for posting back.  Given your are considering Crystal reports, I'd like to make another recommendation on a business intelligence (BI) tool that it's far easier to use. Qlikview, check it out. www.qlikview.com
0
 
mlmccCommented:
FYI - Crystal only runs in Windows.  There is a way to run a viewer in Linux but the report has to be developed and I believe saved with data on a Windows machine

mlmcc
0
 
Compuit1Author Commented:
Hello - Thank you...  Yes the solution would be either jasper or pentaho. I also like Datavision and believe it will be fit for purpose. The solution needed to be opensource based and reporting a key criteria. Qlikview really good but appears to be Windows based.

Thanks again, I spread the points.
0
 
RobOwner (Aidellio)Commented:
Thanks for the points.  The server version of Qlikview is windows based but overkill for a lot of us.  I've been able to get away with just the client version for my users.
0
 
fiboCommented:
Qlikview seems to be free only for individual use.
Would that cover several users in the same company using company data?
0
 
RobOwner (Aidellio)Commented:
The free version has too many restrictions to be useful.
The paid client version works much like excel in that it can connect to virtually any data source (via odbc) and can be saved as a file .qvw that other users can access and open
0
 
fiboCommented:
Any comment/experience about Talend Studio
http://www.talend.com/products/talend-open-studio ?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now