Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to create report from MySQL

Posted on 2013-11-05
21
Medium Priority
?
921 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 501 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 43

Assisted Solution

by:Rob
Rob earned 501 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 30

Accepted Solution

by:
Olaf Doschke earned 498 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 27

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 43

Expert Comment

by:Rob
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 27

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 43

Expert Comment

by:Rob
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
 
LVL 27

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 43

Expert Comment

by:Rob
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 43

Expert Comment

by:Rob
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 101

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 43

Expert Comment

by:Rob
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 43

Expert Comment

by:Rob
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

916 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