Welcome back! If you're reading this tutorial, it is assumed that you have already completed the first tutorial, and you're now an experienced FlashReport user. This tutorial will demonstrate how to create a report from two or more database tables (2 tables in this example). We're going to create an employee directory for a fictious company called "ACME Widgets".

The sample data for this tutorial is provided with this tutorial, in a file called "sample_data.sql" (i.e., it is in the same folder as this index.html file). This is a different dataset than what you used in the beginning tutorial.

To get started, add the sample_data.sql data to your MySQL database, using phpMyAdmin or phpFlashMyAdmin.

In previous versions of FlashReport (previously called MyPDF), it was required that you convert your database tables to the InnoDB table type. This is NOT a requirement for the current version of FlashReport. Table relationships are now created using the Relationships section of the Data Criteria tab.

Creating the Table Relationship

After adding the data from sample_data.sql, you will observe two tables: employees and departments. Here's the idea: each employee belongs to one department, and there are of course many employees in each department. But to make the database slightly more efficient, a "dept_id" (department ID) field is used in the employee table to reference the department.

Here's what we want: an employee roster which shows a grouping of employees by department - i.e., who is in which department. The department ID should be used to determine this grouping. What we need to do is create a relationship between these tables: employees.dept_id = departments.id. We'll do this in the "relationships" section of the Data Criteria screen of FlashReport.

Choose the "One to Many" option, as shown below, since for each department, there can be many employees.

Creating the Report

Ok, so how are these relationships used in an actual report? Let's get a basic report going to see. Using the 'Data Criteria' screen, create a sort by department name, and a second-level sort by employee name. Since the department name is the top-level sort, we will be able to use it for grouping and summary purposes. Then, within each department, we will be able to sort the results by employee name.

Here's what the Data Criteria screen should look like. For now, you can leave out any Filter Conditions.

In the report layout screen, add a summary section above the body, similar to how it was done in the beginner tutorial. This will hold the department name. Also, add a field to this summary section. The fields in the summary sections *must always* be the same as the field that we are summarizing by. In other words, if the summary section is a group by "dept_name", then the only acceptable field to place within this section is the "dept_name" field. If you think about how report grouping works, you'll see that this is the only thing that really makes any sense.

Finally, add the employee name field to the body section. I have also added the extension and email fields, too, so that we have a complete employee roster with contact information (what use would a list of names by themselves be?).

Final Layout and Result

As a final flourish, you can add headers to the top of each field. So that the headers repeat at the top of each page, I have added them to the "Header" section of the report. In addition, I've added a label for the department, and reformatted with employee data with various font options using the FlashReport tools. Of course, you can really get creative with this, and create a nice-looking report. In this screenshot, I have hidden the buttons so that you can see the full layout.

And finally, here's what the resulting PDF looks like. As you can see, the employee roster is summarized by department (the top-level sort), and then sub-sorted within each department by employee name. In this example, I've used the "Impact" font for the report title and department names.

NOTE: If the title is cut off ("Acme Widget Employee Roster") then this means that you just need to make the text field horizontally wider in the layout.

Thanks for using FlashReport... enjoy!
Darren