Solved

How to create report from MySQL

Posted on 2013-11-05
21
825 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
Comment Utility
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
Comment Utility
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
Comment Utility
Here is a simple query and the output... Simple query and output
0
 

Author Comment

by:Compuit1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Any comment/experience about Talend Studio
http://www.talend.com/products/talend-open-studio ?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

7 Experts available now in Live!

Get 1:1 Help Now