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 create a database view and then use the Crosstab Report Setup form in PHP Report Maker to generate a report that shows quarterly sales by product for different years.

1. Create a new View and add the Orders table, OrderDetails table, Customer table and the Product table to the Query Building Area.
   
   
2. Click and drag the OrderID field of the Orders table's Field List box to the OrderID field of the Order Details table's Field List box. This creates join criterion indicated by the join line between the OrderID fields.
   
   
3. Click and drag the CustomerID field of the Customer table's Field List box to the CustomerID field of the Orders table's Field List box. This creates join criterion indicated by the join line between the CustomerID fields. (This step is optional as customer name is not used in this example.)
   
   
4. Click and drag the ProductID field of the Products table's Field List box to the ProductID field of the Order Details table's Field List box. This creates join criterion indicated by the join line between the ProductID fields.
   
   
5. Click and drag the CategoryID field of the Products table's Field List box to the CategoryID field of the Categories table's Field List box. This creates join criterion indicated by the join line between the CatergoryID fields.
   
   
6. Drag the OrderDate field of the Orders table, the ProductName field of the Products table, the CompanyName field of the Customer table to the grid.
   
   
7. In the Columns Pane under the Query Building Area, type Amount: orderdetails.UnitPrice*orderdetails.Quantity*(1-orderdetails.Discount)) to calculate the net amount of the purchase of each line item in the OrderDetails table.
   
   
   
   
8. Click the Execute button (F9) to test the Custom View at this intermediate point of the design The View returns the number of records in the OrderDetails table.
   
   

Save the query as "Orders By Product", we'll use this query to create the Crosstab Report.

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

Click the "Reports" menu button and select "Quarterly Orders by Product" 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.