Five Version 2.6: Learn More About Our Latest Release

How To Generate A MySQL PDF Report in 3 Steps

Avatar photo
Dominik Keller
Oct 5th, 2023
Blog

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 because of its ability to connect to almost any data source, as well as its new report writer, which enables developers to rapidly generate PDF reports on an SQL database.

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

To better understand which databases Five supports, read our blog post on external databases here. The short version is this: Five gives you a built-in MySQL database, or it can be connected to an external PostgreSQL, MS SQL Server, SQLite, or MySQL database. PDF reports can be created on any of these data sources.

In this walkthrough, we will create a PDF report on Five’s built-in MySQL database.


Using Five to Generate PDF Data Reports on MySQL

Generating a SQL report is a common development task. But traditional SQL admin tools are not really fit for this purpose. Five, on the other hand, is an application builder that helps developers rapidly build and deploy database-driven web apps that can contain PDF reports (or forms, charts, dashboards, etc.).

So, let’s say here’s a simple scenario: your business needs a new monthly inventory report (yet again). Let’s check out how quickly we could build this in Five.


1. Creating the MySQL Database

First up, Five gives developers a dedicated MySQL database for every application that’s developed in Five.

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, but of course, a report can also be generated on any external, pre-existing relational database (note that a paid subscription is required to set this up with an external database, but with Five’s free download, you can already test the functionality locally free of charge).

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

Five.Co - Visual MySQL Database Modeler

To create this database, follow these steps:

  1. Download Five for free and launch it on your computer.
  2. Once you have Five open, 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
  5. Follow the video below to learn how to use the Table Wizard to create MySQL database tables in Five.

2. Creating the PDF Report on a SQL Database

Next, click on Visual > Reports. This is where you can generate, adjust, customize, and manage all your MySQL reports. Five lets you create reports on any data source, including its built-in MySQL database (as described here), any query, or any external database connected to Five.

Five.Co - Visual > Reports

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 way you would design a usual 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, I can add a table to my report that will contain each field that is stored inside my 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
Five.Co - Inserting Fields from MySQL to the Report

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:

Five.Co - Inserting Fields from MySQL to the Report Layout

If you don’t enjoy working with the rich text editor, simply 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.

  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.

Five.Co - Previewing the MySQL Report

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

To connect Five to an existing external database, a paid subscription to Five is required. However, developers can explore Five’s features at no cost by signing up for a free download and using Five’s built-in MySQL database as a test database. Building applications Five on an external database is no different from using Five’s built-in MySQL database.


Sign Up For a Free Download 
Download Five Today and Start Developing




Start developing your first application!

Get Started For Free Today

Sign Up Free Book a demo
Develop your first application with Five now. Start Free

Thank you for your message!

Our friendly staff will contact you shortly.

CLOSE