Prev |  | Next
## 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. In this tutorial you will Perform data manipulation prior to reporting using PHP custom code 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
On pressing Ok, you will see an error message complaining that the table t_lateness does not exist. This is because this table will have been created when the custom PHP code entered above executes before this main query. Therefore you can ignore this warning. 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.
Prev | Up | Next
Tutorial 3 - The Monthly Returns Report | Reportico Tutorial | Appendix 2 - Reportico URL request parameters
Documentation generated on Fri, 26 Jul 2013 20:58:43 +0100 by phpDocumentor 1.4.3