Solved

How to create report from MySQL

Posted on 2013-11-05
21
836 Views
Last Modified: 2013-11-15
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
Comment
Question by:Compuit1
  • 7
  • 5
  • 3
  • +4
21 Comments
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 167 total points
ID: 39626314
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
 

Author Comment

by:Compuit1
ID: 39626549
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
 

Author Comment

by:Compuit1
ID: 39626555
Here is a simple query and the output... Simple query and output
0
 

Author Comment

by:Compuit1
ID: 39626558
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
 
LVL 42

Assisted Solution

by:Rob Jurd, EE MVE
Rob Jurd, EE MVE earned 167 total points
ID: 39646374
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 166 total points
ID: 39647113
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39647940
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39649353
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39649487
"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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39649524
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39649540
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39649578
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
 

Author Comment

by:Compuit1
ID: 39650039
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39650079
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39650198
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
 

Author Closing Comment

by:Compuit1
ID: 39650223
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39650244
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
 
LVL 29

Expert Comment

by:fibo
ID: 39650508
Qlikview seems to be free only for individual use.
Would that cover several users in the same company using company data?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39650527
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
 
LVL 29

Expert Comment

by:fibo
ID: 39650821
Any comment/experience about Talend Studio
http://www.talend.com/products/talend-open-studio ?
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now