Preparing Data for Reports

Before creating any report in PHPMaker, you must prepare your data first.

Make sure ALL data are available using Views

All data to be displayed in the report must be available in the source table. Each report is based on one single source table (or view) only. If you have foreign keys in your table and you want to display data from the referenced table, you should use view of the your database and create joins to combine data from the tables first.

Example

In the tutorial of Crosstab Report, we create a View first, you can see that the relationships are as follows:

The main data is actually stored in the OrderDetails table, but we obtain the following information using the joins between tables:

Company name - Use the OrderID field in the OrderDetails table to trace back to the Orders table, then use the CustomerID in the Orders table to trace back to the Customers table to get the CompanyName.

Product name - Use the ProductID field in the Order Details table to trace back to the Products table to get the ProductName.

Category name - Use the ProductID field in the OrderDetails table to trace back to the Products table to get the ProductName, then use the CatergoryID in the Products table to trace back to the Categories table to get the CategoryName.

With the company name, product name and category name present in one single source - the "Orders By Product" View, you can create reports and use them to group your data. Otherwise, you can only group by OrderID or ProductID which are only integers and make the report much less readable.

Notes

Make sure the data referenced by the foreign keys are unique or the grouping will be wrong. For example, if you have 2 different CustomerID in your orders table but they both have the same company name, these 2 companies will become indistinguishable in the view. If you group the data by customer name, the result will be wrong.
To enhance performance, only select the data you want to use when you create the views. Do not use asterisk(*) to select all fields (unless you really need to use all of them).

Filtering Data

Always retrieve the least amount of data from the database for best performance.

For example, if your data ranges across 3 years and you only need to see one year data in your report each time, you can create a report and select the year at runtime (when you run the generated script). Run-time filtering is well supported for users to see partial data that they are interested in), but this would mean that the PHP script will need to handle 3 times the data needed to show initially. There is performance penalty if the size of your data is large and it will take a longer time for the report page to show. You have 2 alternatives:

Use Extended Search for the field and choose a default year, or
Create view in your database to retrieve data for one year only.

then your report for a particular year will load faster.

After you prepare the data, connect to the database using PHPMaker, then right click the database pane or click Edit in the toolbar and choose Add Summary Report or Add Crosstab Report to create a report.