Unlike MS Access, which has the TRANSFORM...PIVOT statement, most relational databases – MySQL included – do not offer a mechanism for easily generating crosstab queries. I wrote back in 2010 that, left to their own devices, DBAs have had to be quite creative in designing crosstab queries.
Since crosstab queries are usually meant for displaying tabular data in reports, why not let the reporting tool assist you in the generation of crosstab reports? As we’ll see in today’s follow up to the Generating Reports on MySQL Data article, there really is no need to spend a lot of time on the SQL statement. To prove it, we’ll take the same report that we built in that tutorial and convert it to a crosstab report using the reporting facilities of the Navicat for MySQL database administration GUI. While we’re at it, we’ll learn about another neat feature called a drill-down that allows you to click on report data to bring up a subreport with more detailed information.
Crosstab Report Structure
The purpose of a crosstab (or "cross tabulation") report is to summarize information between related entities. Fields for each entity run along the X and Y axes. For instance, you might have salespeople on the Y axis and regions along the X axis. Each cell represents an aggregation of each entity, such as a count or sum. In a salespeople by region crosstab report, each cell could show the total sales (sum) for each salesperson divvied up by region.
Figure 1: Sales Figures by Region Crosstab
Customers with Multiple Movie Rentals Report as a Crosstab
In the Generating Reports on MySQL Data tutorial, we built a report that listed customers who had more than one movie rented at that time.
Figure 2: Customers with Multiple Movie Rentals Crosstab
While that report certainly does provide a high level of detail about each movie rental, including title, rental and due dates, and summary data, it does not offer a good overview of how many movies the average customer rents at a time. We could scan through all of the totals, but that still wouldn’t reveal much by way of patterns.
For that, we could build a crosstab report that replaces the X axis fields above with the number of movies rented. Hence, there would be a column for 1 movie, 2 movies, 3 movies, and so on…
Figure 3: Number of Movies Rented by Customer Crosstab Sample
In the above report, the Grand Total column doesn’t tell us much until the last row where all of the numbers are tallied up by number of movie rentals. We can instantly see that out of 159 movies currently rented, there are 136 customers with one movie, 22 customers with two movies, and only one person with three movies. Moreover, the absence of additional rows tells us that no one has more than three movies in their possession.
Constructing the Report
As with the Generating Reports on MySQL Data article, we’ll be fetching our data from the Sakila sample database and employing Navicat for MySQL to build the report. The trial version of Navicat for MySQL may be downloaded from the company’s website. Licenses range from $159 USD for the Non-Commercial Edition, $149 USD for the Standard Edition, and $199 USD for the Enterprise Edition. I’ve never been able to find a good free stand-alone reporting tool for MySQL. For the purposes of following along with this tutorial, there is a 14-day trial version which includes all of the features of the full Enterprise Edition. Moreover, registering with PremiumSoft via the location 3 links gives you free email support during the 14-day trial.
Writing the Query
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.
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 eight-and-a-half by eleven page.
Last time we pretty much wrote the query from scratch. This time, we’ll use the Report Editor’s Query Designer tool to compose the query.
To launch the Query Designer tool,
- switch to the “Data” tab
- select File > New… to bring up the New Items dialog.
You’ll see two icons there: the Query Wizard and the Query Designer. The Query Wizard guides you through each step of the process and is best suited for simple queries whereas the Query Designer presents a number of tabs that affect various parts of the query.
- Click the Query Designer icon to select it and click the OK button to launch it.
Figure 4: The Query Designer Dialog
Starting with the Tables tab:
- Select the rental table either by double-clicking it in the Available Tables list or by dragging it into the Selected Tables list.
- Do the same for the customer table.
At that point the Join Table dialog will appear so that we may define the relationship between the two tables.
- You’ll see that there are already a couple of relationships in the Joined Fields list. That’s because Navicat assumes that identically named fields are related. In this case the customer_id is indeed the linking field. However, the last_update fields are not related, so you can delete it by selecting it in the Joined Fields list and clicking the Remove button.
Figure 5: Joining Tables using the Query Designer
- Click the OK button to accept the join relationship.
We can skip the Fields tab because we aren’t going to be referencing any of the fields directly.
We do however need to visit the Calcs tab, because we will be performing a count of movies rented.
- Select the rental_id field either by double-clicking it in the Available Fields list or by dragging it into the Calculations list.
- You’ll see that the SUM() function is selected by default. Change the function to “Count(*)” by choosing it in the Function dropdown.
- The Field Alias is what will appear as the X Axis Heading in the report, so we should change it to something more descriptive. Right-click on the Field Alias value of “COUNT_<n>” and click on the Rename popup command. In the field, enter “# of Movies Rented”.
Functions that do not appear in the functions dropdown may be typed in manually, such as in the case of our calculated field. It employs the if() function to only display a count when it is greater or equal to one. Hence, zero values are suppressed.
- Once again, select the rental_id field either by double-clicking it in the Available Fields list or by dragging it into the Calculations list. (Any field will do.)
- Enter “if(count(*)>=1, 1, '' )” in the functions field and “Movies Per Customer” for the Field Alias.
- Next, select the last_name field either by double-clicking it in the Available Fields list or by dragging it into the Calculations list.
- This time, we want to pass the last_name and first_name fields to the CONCAT() function to format the names as “last_name, first_name”, just like we did before.
- There is no CONCAT() in the functions dropdown so again we will enter it manually. Double-click on the dropdown text to select it and then type in “CONCAT(customer.last_name, ', ', customer.first_name)”.
- This will change the Field Alias – and Y Axis Header - to “CONCAT_customer_last_name”. Replace it with “Customer”.
The Search tab is where you define the WHERE criteria. We have one criterion that the movie’s return_date is NULL. To enter that information:
- Select the return_date field either by double-clicking it in the Available Fields list or by dragging it into the Criteria list.
- By default, the equals (=) sign is selected in the Operator list. Change it to “Blank”, which is the second-last item in the list.
Click on the Group tab next, because we want to group the results by customer_id.
- Drag the customer_id field down into the Group Fields list.
Last but not least, click on the SQL tab. You’ll see the SQL statement there. It may not be up-to-date because some of the information gets updated once you save the query, which is exactly what we’re going to do now.
- In the Name field, enter “number_of_movies_rented_crosstab” and click OK to save our query.
Once saved, the query should be:
SELECT COUNT(*) AS '# of Movies Rented', if(count(*)>=1, 1, '') AS 'Movies Per Customer', CONCAT(customer.last_name, ', ', customer.first_name) AS Customer FROM `rental` `rental` INNER JOIN `customer` `customer` ON (`customer`.`customer_id` = `rental`.`customer_id`) WHERE ( `rental`.`return_date` IS NULL ) GROUP BY `rental`.`customer_id`
Designing and Viewing the Report
Click on the Design tab and you’ll see the empty report header, detail, and footer sections, along with the query output fields on the right.
Click on Files > New… from the main menu to bring up the New Items dialog. Select the CrossTab Wizard and click OK.
On the first screen of the Wizard, select the number_of_movies_rented_crosstab query that we just created and click Next >.
The next screen is where it all comes together. On the right there is a list of fields produced by the query and a large pane displays the field layout.
- Grab the Customer field and drag it onto the new row box.
- Next, grab the “Movies Per Customer” field and drag it to the new value box.
- Lastly, grab the “# of Movies Rented” field again and drag it to the new column box to set it as the Column (X axis) Header.
Figure 6: The CrossTab Wizard Dialog
- Click the Next > button.
- On the last screen of the wizard, make sure that the Preview the report radio button is selected and click Finish to view it.
The completed report should look just like Figure 3.
Working with Subreports
A subreport is a report that displays within the body of a main report. You can think of a subreport as being comparable to a frame in a Web page. Any report can be used as a subreport…
- to display a summary report that displays summary data from several datasets or based on different selection criteria,
- to display data from two tables where there are no fields that can be linked,
- to allow the sequential display of all records from one table, followed by all records from another, or
- to allow drill-down on each of several values in the same row.
We will modify our Multiple Movie Rentals Report to incorporate a drill-down that will display additional customer details.
Importing the Report Data
Linking the subreport query to the main one requires that both queries be built using the Query Designer rather than in “Edit SQL” mode. Instead of rebuilding the entire customers_with_more_than_one_movie_rented query, we’ll import the queries using the Data tab’s Import feature.
- Download the multiple_movie_rentals_report_with_customer_details_subreport.dtm file.
- Click the Report button on the main Navicat toolbar and then click New Report. That will open the Report Designer in a separate window.
- Click on the Data tab.
- Select the File > Import… command from the main menu and browse to your saved multiple_movie_rentals_report_with_customer_details_subreport.dtm file.
Linking the Subreport to the Main Query
To fetch the customer details, we need a common and unique field between the two queries. Since we are fetching customer details, the most logical choice is the customer_id. To link the queries,
- select the customer_id field of the “customer_details” query and drag it to the customer_id of the “multiple_rentals_report_query” query. A one-to-many link should then appear:
Figure 7: Linked Queries on the Data Screen
(Re)Designing the Main Report
For instructions on designing the Customers with Multiple Movie Rentals report, please refer to the “Designing the Report” section of the Generating Reports on MySQL Data tutorial. Be sure to omit the phone number field and adjust the remaining fields accordingly. That will leave the report with four fields: Customer, Movie Title, Rental Date, and Due Date:
Figure 8: Updated Customers with Multiple Movie Rentals Report
Designing the Subreport
- Click the SubReport icon on the Advanced Components toolbar and place it in the Group Header, directly under the Customer name field.
- That will add a tab at the bottom of the screen named “SubReport1: No Data Pipeline assigned.” Click it to build the SubReport.
- To assign the data pipeline, choose the customer_details query from the DataPipeline dropdown under the Data category in the Object Inspector.
- The available fields will be located on the right-hand side of the screen, but make sure that the customer_details query is selected in the Data Tree. Arrange the fields as follows:
Figure 9: Customer Details SubReport Design
- Back on the main report, select “SubReport1” (the only item in the list) as the DrillDownComponent under the Generation section of the Object Inspector. That will cause the subreport to display when the customer name is clicked.
Previewing the Report
- Click the Preview tab to view the report.
- Hover the mousepointer over the Customer name field and it should change into a hyperlink pointer.
- Click on the Customer name to display the subreport:
Figure 10: Customers with Multiple Movie Rentals Report with Drill-down
In today’s tutorial we took the report that we built in the Generating Reports on MySQL Data tutorial and converted it to a crosstab style using the reporting facilities of the Navicat for MySQL database administration GUI. We also learned about how to utilize drill-downs to view additional information within a subreport.