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 :-

1. Add a date range criteria item

2. Add a LIST type criteria item

3. 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

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!