How To Create Report In Mysql

DBAs – and pretty much everyone else – tend to focus so much on data input and storage that they forget about the reason that they're hoarding all of that information in the first place, which is to do something useful with that data. One of the most overlooked uses of stored data is that of statistics. It's what converts the data into truly useful information - the type that can let us know what's really going on or uncover patterns that may not be immediately apparent to us.

The best kind of statistical reporting is one that takes truckloads of raw data and filters it down to a drillable table and/or charts that quickly relate the information we seek. For that we have to employ specialized reporting tools designed specifically for report generation. This tutorial provides an overview of reporting tool types as well as some practical experience in report building using the reporting facilities of the Navicat for MySQL database administration GUI.

Reporting Tool Types

I received much of my practical experience in reporting back when I worked for Citizenship & Immigration. As you can imagine, there was always a pressing need for timely reporting on immigration trends. I spent several years there building numerous canned and ad hoc reports. We used Crystal Reports with Visual Basic for a time and eventually switched to Birt when our department moved over to Java development.

Now, in looking for MySQL solutions, I find that most tools are geared towards leveraging application code using ODBC or JDBC drivers. Others, like dmyreports, are Web-based and run on the server in an executable file type such as PHP. The last main report generator type describe those that come packaged with a database admin tool such as dbForge Studio or Navicat. From my research on admin GUI products while writing my Top 10 MySQL GUI Tools article, reporting is only made available in commercial or enterprise editions of admin software. This is also true of MySQL's own Workbench product.

That being the case, you might as well choose an admin tool based on a broad criteria set rather than just focussing on reporting exclusively. I've been working with Navicat for MySQL as well as their flagship Navicat Premium offering and I find that it suits my needs quite nicely. Hence, Navicat for MySQL will be what I'll use in today's tutorial. The trial version of Navicat for MySQL may be downloaded from the company's website. The 30-day trial version of the software is identical to the full Enterprise Edition so you can get the full impression of all its features. Moreover, registering with PremiumSoft via the location 3links gives you free email support during the 30-day trial.

The Source Database

To make it easy for you to follow along, I'm using the Sakila sample database. It was developed by Mike Hillyer, a former member of the MySQL AB documentation team, and was created specifically for the purpose of providing a standard schema for use in books, tutorials, articles, and the like.

One of the reasons that I chose the Sakila database as the data source for this article is that it contains a number of related tables that lend themselves well to reports with multiple levels. It's also themed around the film industry and covers everything from actors and film studios to video rental stores. (Anybody remember those?) The full schema structure can be viewed on the MySQL Dev site.

Mosey on over to the Download page and click on your preferred archive format (TGZ or Zip).

MySQL Documentation

The above archives contain three files:

sakila-schema.sql: it contains all the CREATE statements required to create the structure of the Sakila database including tables, views, stored procedures, and triggers.
sakila-data.sql: it contains the INSERT statements required to populate the tables and views.
sakila.mwb: a special data model file that can be viewed in the MySQL Workbench. It contains a visual model of the database structure.

Once you've downloaded and extracted the archive files to your hard drive, launch Navicat for MySQL.

Unless you've already got a connection that you'd like to use, you'll have to create one.

Select File > New Connection > MySQL… from the main menu to open the New Connection dialog.
Give your connection a name, such as "sakila". You'd be hard pressed to mistake that for anything else!
You'll also want to enter your admin account credentials.

You can test your connection by clicking on the Test Connection button in the lower left of the dialog.

New Connection

Once you get a success message, click OK to create the connection. It will be added to the connections list on the left of the screen.
Double-click on your new connection in the left-hand pane to activate it. The icon to the left of the connection name will turn green to show that the connection is now active.

To import the sakila database:

Right-click on the connection name and select Execute SQL File… from the context menu.

On the Execute SQL File dialog:

Use the ellipsis button […] to browse to the sakila-schema.sql file.
Make sure that UTF-8 is selected for the character encoding.
Click the Start button to run the database creation queries. You can follow its progress on the Message Log tab.

Execute SQL File

Once the process completes, close the dialog and refresh the connection details by right-clicking on the name and selecting Refresh from the context menu. The sakila schema should then appear under the connection:

sakila schema

Repeat the same process to execute the sakila-data.sql file.

It will take a little longer because it populates all of the data.

Once it completes, you can double-click on any table or view to see its contents.

Creating a New Report

To create a new report, click on the large Report button at the top of the screen to display the Report commands and then click the New Report button.

That will open the Report Editor with the report size set for a standard 8^1/2 by eleven page:

Report Editor

Building a report can be broken down into three distinct components: Selecting the data, designing the report, and previewing the finished appearance. All three components are handled by their own screen, which are each accessible via the tabs in the upper-right corner of the screen, just below the main report designer menu.

Although there will doubtless be some jumping back and forth between each tab, you will typically move through them from left to right: data, design, and then preview.

Selecting Report Data

We're going to construct a report that displays customers who have more than one movie checked out. It's easy enough to find who has movies in their possession by checking the rental table's return_date field for a value; it is only populated when a move is returned. Weeding out those customers who have more than one movie in their possession is a little more difficult and requires a subquery to fetch the customer IDs using the GROUP BY and HAVING clauses. There is also a calculated field (the due_date) that is the sum of the rental_date and rental_duration days.

Here is the complete query:

SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer,         address.phone,          film.title,          rental_date,          DATE_ADD(rental_date,INTERVAL film.rental_duration DAY) AS due_date  FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id  INNER JOIN address ON customer.address_id = address.address_id  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id  INNER JOIN film ON inventory.film_id = film.film_id  WHERE rental.return_date IS NULL          AND   customer.customer_id in        (SELECT customer.customer_id        FROM rental         INNER JOIN customer ON rental.customer_id = customer.customer_id        WHERE  rental.return_date IS NULL        GROUP BY customer.customer_id        HAVING COUNT(customer.customer_id) > 1)          ORDER BY customer, rental_date, due_date DESC

Note: if you have any reservations about the subquery, just omit the AND clause in red.

We could have left all of the calculations and row filtering up to the report, but in my experience, getting your data as complete as possible from the onset can only make things easier further down the line.

Binding Our Query to the Report

From the Data tab, there is only one menu command available: the File command.

Click it and choose the New… item from the dropdown menu.

From there, you have two options for selecting your report data: the Query Wizard and the Query Designer. The former is aimed at the more novice user who hasn't had a lot of SQL experience. It guides you through the process one step at a time. The Query Designer provides all of your query parts up front using tabs. Moreover, the last tab on the right lets you work with the SQL statement directly.

What I like to do is work on my query using the main Navicat SQL Editor to construct my query. Once I'm happy with it, I then copy it to the clipboard, go to the SQL tab of the Query Designer and paste in my statement by right-clicking the editing area and selecting Edit SQL (the only command) from the popup menu. The Query Designer will display a warning dialog informing me that once you manually edit the SQL you can no longer use the visual design tabs.

Clicking Yes activates the Editor pane and turns it white.

You can then paste in the code and give the query a name.

Query Designer

Once you click on the OK button, the dialog will close and the selected fields will be displayed on the Data screen.

Data Screen

You can also preview and edit your query from here. The other commands are disabled because we wrote our SQL statement ourselves.

Designing the Report

Moving on to the Design tab, you should now see the empty report header, detail, and footer sections, along with the query output fields on the right.

To add a field to the report, drag it over to the section that you want to place it in. Usually, you'll want to place the fields in the details section because it is the one that repeats for each row of data, while the header and footers only print once per page.

Once you let go of the mouse button, both the field name and value will be added to the report.

Go ahead and click on the Preview tab to see how the data appears in the report.

Here is the customer field:

Customer Field

Saving the Report

It's always a good idea to save your work early and often in case of hardware issues and/or sudden loss of power. The Ctrl-S shortcut works here as in most software: the first time that you save the report, you will be given the opportunity to assign a meaningful name to your report and choose where to store it. I called it "Multiple Movie Rentals Report".

As you can see, the label is printed with each value. Sometimes that is appropriate, but often, report headers should appear at the top of the page, i.e., in the report header. Moving the label there is just a matter of selecting it with the mouse and dragging it over into the header section. You can also edit the label by double-clicking it and typing directly into the field or via the textbox in the upper-left corner of the screen. Finally, drag the value field over to the left so that it aligns with the label. Here is the report again with vertical formatting:

Report with Vertical Formatting

Follow the same procedure to place all of the fields on the report. You'll probably want to give them a bit more room by switching the page orientation to Landscape. That option can be found in the Report menu.

When you select a field or label, the Font toolbar becomes enabled. It allows you to customize your fonts by adjusting their size, weight, and style. Here is a preview of all the fields with bold headers:

Preview of Fields with Bold Headers

Any time there is a one-to-many relationship between the data entities, some data is bound to be repeated, such as the customer names and phone numbers. Repeated fields are often good candidates for Grouping.

To create a group select Report > Groups… from the main menu.

On the Groups dialog:

Make sure that the report will Break On the "multiple_rentals_report_query.customer" Data Field and click the Add button to append it to the Groups list:

Groups
Groups

Click OK to add the group.

That will add the Group Header[0] customer and Group Header[0] customer sections to the report. Expand the Header section to make room for the customer field, and drag it into the new section.

You may also want to drag the detail fields to the top of the details section so that you can make it more compact vertically. That will give the report a tighter look:

Drag the Detail Fields to the Top of the Details Section

Formatting Fields

Data is often stored in the database in a raw format in order to preserve disk space. For instance, the phone numbers are stored without parentheses or dashes. We can tell the report to display them as phone numbers by right-clicking the phone value field and selecting DisplayFormat… from the popup menu.

The Format dialog contains a number of pre-defined formats to choose from, including one for Phone numbers.

The popup menu also contains an AutoSize command. Clicking it will allow the field to expand automatically for longer values.

Another useful command is SuppressRepeatedValues. By selecting it, fields in the details section will not repeat.

Adding Color to Sections

Although certainly not mandatory, adding some color to a section such as a group header can help identify each group.

To add color to the customer group header:

Click it in the Report Tree on the left to bring up the section properties in the pane below it.
Expand the Background and Brush properties under Appearance.
Select the color "dSkyBlue" from the dropdown list.

Add Color to Sections

Adding Group Footer Information

The Group Footer is a good place to display summary information such as counts and sums.

Let's add a field that presents the number of movies rented per customer.

Click the DBCalc button in the main toolbar and click on the right side of the customer group footer.
Position the field so that it is about one grid line away from the right edge of the page and right-align the text using the Right Justify Font Toolbar command.
Locate the DBCalcType property under Calculations in the Properties pane on the left and choose dcCount from the dropdown list.
Locate the DataField property under Data in the Properties pane and choose "title" from the dropdown list.
Click the Label button in the main toolbar and click to the left of the DBCalc field in the customer group footer.
Enter "Number of Movies Rented:" in the Set Field Value textbox to the left of the Font toolbar.
Make the font of both fields Bold.
Click the Line button in the main toolbar and click on the customer group footer directly under the phone number field.
Grab the right edge of the line box and drag it to the right edge of the section.

Assigning a Title to the Report

Every report should have a title. Here's how to assign it:

Click the Title item in the Report menu to display the title section in the report designer.
Click the Label button in the main toolbar and click on the title section to create it.
Enter "Customers with Multiple Rentals" for the report title.

Here are a few tips to make the title look like a title:

Center Justify the text. For best results, stretch the field so that it spans the entire width of the page. That will guarantee that the text is centered.
Give it a font size and weight of 14 bold.

Here is the first page of the finished report in preview mode:

Finished Report in Preview Mode

Conclusion

In today's tutorial we built a simple report using the Navicat Admin GUI for MySQL. Though basic, the report gave us an opportunity to get accustomed to designing reports against MySQL data. In an up-coming article, I hope to discuss crosstab reports and the use of subreports and charts.

See all articles by Rob Gravelle