Tutorial 4 - The Late Returns Summary report

Note that this tutorial requires you to enter custom source using PHP code. Custom code may not be entered when running in SAFE mode as the user could create SQL statements here that could delete and update data in the database or perform disk operations. By default, the tutorial projects have SAFE mode turned on. To continue with this tutorial you need to turn off SAFE mode by folowing the instructions on how to turn on and off Safe Mode in the Reportico Security section.

1. Perform data manipulation prior to reporting using PHP custom code

2. Add a pie chart as a final report summary

Beginning this tutorial

Select the Late Returns Summary - Begin the Tutorial from the tutorials menu (Alternatively you can point your browser at the URL http://{SERVER_ADDRESS}/{REPORTICO_INSTALL_DIRECTORY}/run.php?project=tutorials&execute_mode=PREPARE&xmlin=tut4_1_lateness.xml). You are now ready to start this tutorial.

On entry to this tutorial, a date range criteria has already been set up to allow us to report on returns made between specified dates - the date range defaults to the period covering last month.

In the next stage you will add some PHP code and the main report query.

Add the custom PHP code

In this report we want to show how many films have been returned (within the specified date period) more than 1 day late, and provide for counts for those that are 2 days late, 3 days late, 4 days late etc. In order to do this we are going to add some PHP code that will generate a temporary table containing the number of days late each loan was taken out for, and then set up the data query to select this date by number of days late.

Enter design mode, click on the Format tab and locate the Custom Code parameter. Enter the following PHP code :-

    // Extract Users Date Range
    $fromdate =      $_criteria["date_range"]->get_criteria_value("RANGE1");
    $todate = $_criteria["date_range"]->get_criteria_value("RANGE2");

    // Create temporary table to hold number of days 
    // late a film was returned
    $sql ="CREATE TEMPORARY TABLE t_lateness 
          ( 
              days_loaned INTEGER
           ) ";
    $stat = $_pdo->query($sql); 

    if ( !$stat ) 
    {
	$err = $_pdo->errorInfo();
        trigger_error("Query Failed<BR>".$sql."<br>" . 
              "Error ".$_pdo->errorCode(). " - ". 
              $err[2], E_USER_ERROR); 
              return; 
    } 

    // Now populate table with number of days late each film was 
    // returned
    $sql = "INSERT INTO t_lateness 
            SELECT to_days(return_date) - to_days(loan_date)  
              FROM reptut_loan 
             WHERE return_date IS NOT NULL
               AND return_date BETWEEN $fromdate AND $todate
           ";                                 
    $stat = $_pdo->query($sql); 


    if ( !$stat ) 
    {
	$err = $_pdo->errorInfo();
        trigger_error("Query Failed<BR>".$sql."<br>" . 
              "Error ".$_pdo->errorCode(). " - ". 
              $err[2], E_USER_ERROR); 
              return; 
    } 

The above code firstly extracts the user's entered date range as a pair of dates. We then create a temporary table called t_lateness that will hold the number of days each loan was taken out for. We then extract into this table the number of days each loan was taken out for for loans returned between the entered date range.

Enter the report SQL

Now select the Query Details tab and enter the following SQL text against the SQL parameter.

SELECT days_loaned days_loaned, count(*) frequency 
FROM t_lateness 
WHERE 1 = 1   
AND days_loaned > 1 GROUP BY 1 
ORDER BY  days_loaned 

Now return to report execution mode. Notice that the date range criteria is defaulted to last month. Press the Execute button. You will see a count for each number of days films have been loaned out for.

Create the graph

Now we want to represent these counts as a pie chart. Return to design mode and select the Output tab. Since graphs need to be plotted against a group we need to set up a group which represents the whole of the report. This means the graph can be plotted at the end. Select the Groups button, click on the Add button and select the resulting Group link.

Now change the parameter Group On Column to REPORT_BODY.

Now add the graph by selecting the Graphs sub-tab and clicking the Add button. Select the resulting Graphs 0 button and set the following parameters :-

	Parameter	Value	Comment
1.	Group Column	REPORT_BODY
2.	Title	Late Return Summary	The title to give the report
3.	Column for X Labels	days_loaned	Indicates that the month will be used to label the X Axis

Press the Ok button to continue.

Enter the Graph Plot Values

The last graph configuration you need to do is to indicate which report column contains the value to be plotted. Click on the Plots subtab, press the Add button and press the resulting Plots link. You should be presented with a set of plot parameters.

Change these as follows :-

	Parameter	Value	Comment
1.	Column To Plot	frequency	The monthly rental count will be plotted above each month.
2.	Plot Style	PIE	The value will be plotted as bar chart

Remember to press the Ok button.

Check the Report

Return to report execution mode and run the report. You should see the pie chart at the end of the report.