How To Generate A MySQL PDF Report in 3 Steps

Learn How to Generate a PDF Report On a MySQL Database

Last week, we announced the release of Five Version 2.5, a major new release of our rapid application development environment. Our latest release comes with a report writer, which lets developers generate PDF reports on an SQL database.

This makes Five a great SQL reporting tool that covers the entire process from database creation to PDF report generation.

Five gives you a built-in MySQL database, or it can be connected to an external MS SQL Server or MySQL database. PDF reports can be created on any of these data sources in just a few clicks and with a lot of useful helper functions that make report generation quick and efficient.

In this walkthrough, we will create a PDF report on Five’s built-in MySQL database. Make sure to sign up for free to follow along.

Using Five to Generate PDF Data Reports on MySQL

Generating a SQL report is a common development task. Traditional SQL admin tools or GUIs, however, do not support PDF report generation.

With Five, a rapid application development environment, developers can rapidly build and deploy database-driven web apps that contain PDF reports (or forms, charts, dashboards, etc.).

Let’s explore how we can go from database to PDF reports in a few simple steps. Here’s a simple scenario: your business needs a new monthly inventory report (yet again). Let’s check out how quickly we can build this in Five.

1. Creating the MySQL Database

First up, Five gives developers a dedicated MySQL database. This built-in MySQL can be created using Five’s Table Wizard or its visual Database Modeler.

We will generate our report on top of this integrated MySQL database. Of course, a report can also be generated on any external, pre-existing relational database.

Here’s what our database looks like for this example. We have a single table that stores information about Warehouses.

To create this database, follow these steps:

1. Sign up for Five for free.
2. Once you have Five up and running, create a new application by clicking on Applications, and then the yellow Plus button. Give your application a Title, and click on the Tick mark in the top right corner to save.
3. Now click on the blue Manage button in the top right corner.
4. Next, click on Data > Table Wizard.

If you are struggling with any of these steps, watch this tutorial, beginning at 9 min:

2. Creating the PDF Report on a SQL Database

Next, click on Visual > Reports. You can now generate, adjust, customize, and manage MySQL PDF reports. Five lets you create reports on any data source, including its built-in MySQL database, any query, or any external database connected to Five.

Inside Reports,

1. Click the yellow Plus icon to generate a new report.
2. Give your report a Title, such as Warehouse Report.
3. Click on Data Sources, and select the Warehouse Table.
4. Go back to General, and click on Click to add in the Template field.

You’re now inside a rich text editor that allows you to design PDF reports the same way you would design a Word document. On top of the text and document design features, the report generator also lets you select any data field from your data source.

For example, you can add a table to a report that contains each field that is stored inside the warehouse table. To do so,

1. Add a table to my rich text editor by clicking the Table icon. Choose a 3 x 2, because my warehouse table only has two data fields: Location and ID.
2. Next open up the Insert Fields functionality shown here:

You can use the Insert Fields functionality to add the captions of your database fields into the top row of your table, and each record into all subsequent records. Here’s how to set this up:

If you don’t enjoy working with the rich text editor, copy this piece of HTML into the code view of the report generator. Open the code view by clicking on </> in the top left corner of the report generator. Paste this code into it:

<div><strong>Warehouse Report</strong></div>
<div><strong><br></strong></div>
<div><strong><br></strong></div>
<table style="width: 100%;">
    <tbody>
        <tr>
            <td>Location</td>
            <td>ID</td>
        </tr>
        <tr class="fiveReportHelper" contenteditable="false">
            <td class="fiveReportHelper">{{#each @root.[Warehouse].[Records] as | Warehouse |}}</td>
        </tr>
        <tr>
            <td>{{Warehouse.[Location]}}</td>
            <td>{{Warehouse.[ID]}}</td>
        </tr>
        <tr>
            <td>{{/each}}</td>
            <td><br></td>
        </tr>
    </tbody>
</table>
<div><strong><br></strong></div>
<div><strong><br></strong></div>

Save everything by clicking the Tick marks.

IMPORTANT: Don’t forget to add your report to the menu of your application.

1. Click on Visual > Menus > Yellow Plus Icon
2. Fill in the Caption of your menu item with Warehouse Report.
3. Select the WarehouseReport (Report) in the Action drop-down field.
4. Save.

3. Preview your PDF Report

To preview your SQL report that now turns the data stored in your MySQL database into an easy-to-use, printable, and shareable PDF document, launch your application by clicking the ▶️ button in the top right corner of your development environment.

After a few seconds, the application will launch, and you’ll see your PDF report right in front of you!

You might be wondering why this report is empty. Well, we didn’t add any data to our database table. But of course, we could have done so by importing a CSV, or by creating a form in our application for users to add data.

Last, we could have worked on the formatting of our SQL report (and the look of the application surrounding it) by using the rich text editor or by customizing Five’s application themes.

4. Generating More Complex PDF Reports on a SQL Database

The above report turns existing MySQL database records into a table shown on a PDF document. This is the easiest example of an SQL report. What if you need more complex reports?

Five lets you add code to your report to manipulate data or add logic to your template. You could, for example, add a calculated field (such as a monthly total). Moreover, you can add custom screen fields to a report. A screen field lets users add data to their report from inside the end-user application rather than the database. In this way, the end-user of your report can make adjustments inside the report before generating it.

Generate PDF from a MySQL Database – Webinar

Watch our Developer Advocate Pranoy explain the PDF report generator in more detail in our full product demo here (to jump straight to the SQL report writer start watching from 21m and 40s):

How to Get Started

Sign up and get started with Five at no cost.

Sign Up Today
Create a PDF Report on a SQL Database