Tutorial 3 - The Monthly Returns Report

1. Set the report up to group by year

2. Add a graph to the report

Beginning this tutorial

Select Monthly Returns Report - 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=tut3_1_monthreturns.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 set of monthly counts ordered by year and month. Press the Back button and enter report design mode so that we can add a graph.

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. You will notice that the query includes the GROUP BY option to allow us to prudce a count of rentals grouped by month and year. It also makes use of the MySQL to_days and IF statements in order create a monthly count of films returned more than 2 days after they were borrowed.

Create the graph

In this report we want to show monthly film return counts graphed for each year. This means we must first identify that the data is to be grouped on the report by year. To do this click on the Output menu tab and then select Groups. Click Add to add a group. Click the Group 0 button, and change the Group On Column to loaned yr and press Ok to save the changes.

Now we want to add the graph. Click on the Graphs subtab and click on Add to add a graph. Select the Graphs button and then set the graph configuration as follows :-

	Parameter	Value	Comment
1.	Group Column	loaned_yr
2.	Title	Rentals {loaned_yr}	The title to give the report
3.	Column for X Labels	loaned_mth	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 values are 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	loan_count	The monthly rental count will be plotted above each month.
2.	Plot Style	BAR	The value will be plotted as bar chart
3.	Fill Color	yellow	iThe color of each bar plotted

Enter a Second Graph Plot Value

We want to plot both monthly count and monthly late return count so we need to add this as a second plot. Click on the Plots subtab, and again press the Add button and press the resulting Plots 1 link. This time select the following plot paramter values :-

	Parameter	Value	Comment
1.	Column To Plot	overdue	The monthly late return count will be plotted above each month
2.	Plot Style	BAR	The value will be plotted as bar chart
3.	Fill Color	orange	To contrast with the yellow of the monthly count

Check the Report

Return to report execution mode and run the report. You should see a count of film rentals per month and a graph for each year representing this data.