Prev |  | Next
## Tutorial 2 - Creating the loan history report Note that this tutorial requires you to enter assignments using PHP code. Assignment 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 learn how to :- Add a date range criteria item Add a LIST type criteria item Add a conditional asignment ### Beginning the Tutorial 2 Report Select Loan History - 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=tut2_1_films.xml). You are now ready to start this tutorial. On entry to this tutorial, the SQL data query has already been set up. Press the Execute button to see the output. You should see a history of films borrowed from the DVD library. Press the Back button to return and then enter report design mode so that we can set up reporting by date. Before configuring this report, it is a good idea to inspect the SQL statement we are using to generate report data. Press the Query Details to view the SQL statement. The SQL used in this report looks as follows :- ``
    SELECT member.member_id member_id, member.first_name first_name,
           member.last_name last_name, film.film_id film_id, film.title title,
           DATE_FORMAT(loan_date, '%d %M %Y') loaned,
           DATE_FORMAT(return_date, '%d %M %Y') returned,
           loan.loan_date loan_date,
           loan.return_date return_date,
           join_date join_date
      FROM reptut_loan loan, reptut_member member, reptut_film film
     WHERE 1 = 1
       AND loan.member_id = member.member_id
       AND loan.film_id = film.film_id
     ORDER BY member.last_name ASC, member.first_name ASC,
              loan.loan_date ASC
This query pulls out a list of all loans made from the DVD library ordered by library member and date. The ordering allows the report to group by member. Notice the use of the standard MySQL DATE_FORMAT function to format the dates into dd/mm/yyyy format before reporting. ### Set up Date Range Criteria Entry Click on the Criteria tab, and then press the Add button and press the resulting Criteria CriteriaName button. You should be presented with a set of criteria parameters. Set these as follows :-
| Parameter | Value | Comment
1. | Name | loanDate | The name of the ecriteria so it can be referred to elsewhere in the report
2. | Title | Date Range | The title of the criteria item as it appears during criteria entry.
3. | Main Query Column | >set to blank< | This option normally indicates what table/column in the main report query corresponds with the criteria item. We are going to link the date range crtieria with SQL within the main report SQL later, so leave this blank
4. | Criteria Type | Date Range | Will ask the report user for a date range when entering crtieria for the report.
5. | Criteria Display | No Entry | With a date range criteria this field is not relevant
6. | Expand Display | No Entry | Indicates that no Expand option is applicable to this criteria item
7. | Defaults | FIRSTOFLASTMONTH-TODAY | The default report period be from the first of last month until today
To link this criteria item in with the main query you need to edit the main query. To do this select the Query Details option and add the following line after within the WHERE clause - i.e. after the line beginning WHERE but before the line beginnning ORDER :- ``
    [ AND loan_date between {loanDate,RANGE1} and {loanDate,RANGE2} ]
This causes the report to replace the values between curly brackets ({}) with the lower and upper dates of your loanDate criteria range. Now test this out by returning to criteria entry mode and by running the report with different radio buttons selected. Now that your criteria is set up, return to report execution by selecting Execute from the drop down list box. You should notice that you criteria is visible and the default dates to run for are from the start of this month until today. Now experiment with the dates by choosing an earlier period and execute the report by pressing the Execute button. ### Setting up a LIST criteria Item Now we are going to set up a criteria item that will allow you to report on all films in the DVD library or just those that are unreturned. The option will in this case be presented as a radio button. Return to report design mode and again select the Criteria tab, and then press the Add button and press the resulting Criteria CriteriaName button. Set up the criteria options as follows :-
| Parameter | Value | Comment
1. | Name | returned | The name of the criteria so it can be referred to elsewhere in the report
2. | Title | Display | The title of the criteria item as it appears during criteria entry.
3. | Main Query Column | <leave blank> | This option normally indicates what table/column in the main report query corresponds with the criteria item. We are going to link the date range crtieria with SQL within the main report SQL later, so leave this blank
4. | Criteria Type | Custom List | The items the user will select from will be set up later in this criteria item within the List Values parameter
5. | Criteria Display | Radio Buttons | This user will select from radio buttons when making critera selection
6. | Expand Display | No Entry | Indicates that no Expand option is applicable to this criteria item
7. | Defaults | 1 | The default mode to run the report in will be Report on all films. The value of 1 equates with "all films" because the next parameter List Values dictates this
8. | List Values | All=1,Loaned Out=2 | Indicates the the user will be presented with 2 radio buttons labelled "All" and "Loaned Out". If the user selects "All" then the returned criteria item wil be set to 1 otherwise it will be set to 2. This value is then used within the report execution by linking it in with the main query in the next step.
To link this criteria item in with the main query you need to edit the main query. To do this select the Query Details option and add the following line after within the WHERE clause - i.e. after the line beginning WHERE but before the line beginnning ORDER :- ``
    [ AND ( return_date IS NULL OR {returned,VALUE} = 1 ) ]
This causes the report to return all relevant when the value of the returned criteria item is 1 and just records for unreturned films (return_date is NULL) if it is 2. This sort of lateral thinking makes these LIST criteria items quite powerful. Now test this out by returning to criteria entry mode and by running the report with different radio buttons selected. ### Setting up a conditional assignment Now we are going to set up a conditional assinment that will cause the report to display the text "**UNRETURNED**" instead of the return date against films that have not been returned. To do this enter report design mode and select the Assignments tab. Then add a new Assignment and click the resulting Assignment Column button. Now set the following parameters for the new assignment :-
| Parameter | Value | Comment
1. | Assign To | returned | This column normally represents the return date of a film. It is this column that we are resetting if its value is blank - i.e. the film has not been returned.
2. | Expression | "**UNRETURNED**" | This is the text we are going to change the returned column to. Note that you must include the quotes around the text value for this to work since the value must be an expression valid within the PHP language.
3. | Condition | !{returned} | Indicates that this assignment will only be made if the value of the returned column is false - or in other words NULL.
Finally test your new assignment by running the report for a period that includes unreturned films. ### Setting text colour based on column value Now we are going to make the return date go red if it is set to "**UNRETURNED**" To do this enter report design mode and select the Assignments tab. Then add a new Assignment and click the resulting Assignment Column button. Now click on the Output Styles Wizard and set the following parameters for the new assignment :-
| Parameter | Value | Comment
1. | Assign To | returned | This is the column we wish to turn red
2. | Expression |  | Leave blank
3. | Condition | {returned} == "**UNRETURNED**" | Since we set the value to **UNRETURNED** in the previous section, this is the value we wish to test for to turn it red
4. | Text Colour | #ff0000 | HTML text colour #ff0000 is red
Finally test your new assignment by running the report for a period that includes unreturned films. Run in PDF mode too to see the colour change! ### Set your rows to appear in alternating colours We are going to create two assignments, one to set a default row background colour and then another to use a different colour on every other row. To do this enter report design mode and select the Assignments tab. Then add a new Assignment and click the resulting Assignment Column button. Now click on the Output Styles Wizard and set the following parameters for the new assignment :-
| Parameter | Value | Comment
1. | Assign To | member_id | This can be any column since this assignment relates to the row as a whole
2. | Apply Style To | Row | It is the row that we want to apply the background to
3. | Style Background Colour | #f3f3ff | A very light blue
After pressing ok, add another assignment but this time setting a different colour and with a condition as detailed below. So add another assignment and again click on the Output Styles Wizard and set the following parameters:-
| Parameter | Value | Comment
1. | Assign To | member_id | This can be any column since this assignment relates to the row as a whole
2. | Condition | lineno() % 2 == 1 | Will apply to every other line as this evaluates to true when the remainder of the line nu,ber divided by 2 = 1
3. | Apply Style To | Row | Applies background to report output rows
4. | Style Background Colour | #ddddff | A slightly darker blue
Finally test your new assignment by running the report for a period that includes unreturned films. Run in PDF mode too to see the colour change!
Prev | Up | Next
Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns | Reportico Tutorial | Tutorial 3 - The Monthly Returns Report
Documentation generated on Fri, 26 Jul 2013 20:58:43 +0100 by phpDocumentor 1.4.3