Tutorial - Crosstab Report

Note Before reading this tutorial, please read Crosstab Report Setup first.

In this tutorial we will show you how to create a Crosstab Report in PHP Report Maker. We will use the demo database for demonstration.

As explained in Preparing Your Data for Reports, PHP Report Maker can create a report query from a single table, but an individual table may not contains data suitable as the data source for a crosstab query. If you need more than one table to get the result you want, which is almost always the case, you must design a view (MySQL 5) specifically for crosstab presentation.

In this example, we use the view "orders by products" to generate a crosstab report that shows quarterly sales by product for different years.

 

Steps to Create the Report

1. Loading PHP Report Maker

Open PHP Report Maker and connect to the demo database.

2. Adding a report

Right click [Reports] on the database pane then click [Add Report (Crosstab)], or click [Edit] in the main menu and then select [Add Report (Crosstab)].

The Crosstab Report Setup form will show up, enter the report name, for example, we use "Quarterly Orders by Product". Select the View "Orders By Products" as source table.

Click the [Row Headings] tab, since we want to group the order items by category name and then by product name, we select "CategoryName" and then "ProductName" as row headings. We also want to see the summary for each category so we check the [Show summary] checkbox for the field "CategoryName".

Click the [Column Headings] tab, since we show the order date (quarterly) as column headings, we select "OrderDate" as [Field] and select "Quarter" as [Interval].

When we prepare the data by creating the view, we can filter the orders to a particular year by using a WHERE clause, in that case, the report will be for that particular year only.

In this example, however, we did not filter the data in the view, the data includes orders for more than one years. If your group by "Quarter", orders in the same quarter but different years will be grouped together. That is usually not what you want, so we enable the [Year filter] feature, PHP Report Maker can create a combobox of the years according to the data and display data for a year at a time.

Note The [Year filter] option is only available if the row heading field is of date/time type, and the [Grouping Interval] is Quarter or Month.

Now click the [Value] tab. We want to see the total order amount by product, so we select "Amount" as the [Field] and select "SUM" as [Summary value].

Click [Include row sums] to let the report calculation sum of the each row, that is, the total amount of the quarters.

Since there are quite a few products, the report will not be displayed in one single page (depends on the [Record per page] setting), we select [Show page summary] to make the report show the summary of the orders displayed in the current page only. Moreover, we check [Show grand summary] so we'll always see the summary of all orders too.

Click [OK] to finish.

The report will be added in database pane. Field level setup will be copied from the source table.

3. PHP Script Generation

Go to the [Generate] tab, click the [Generate] button and PHP Report Maker will generate the required PHP scripts automatically.

4. Running the PHP Application

Browse the generated site, click "Quarterly Orders by Product" in the menu to run the report. Note that:

  • A column is added automatically to show the row sums.
  • The page and grand summary are also shown at the end of report.
  • The year combobox above the report. You can change it to view report for other years.

 

 

Also See

Tutorial - Drill Down Report

 

 ©2007-2017 e.World Technology Ltd. All rights reserved.