<?xml version="1.0"?><Report><ReportQuery><Format><ReportTitle>Monthly Film Returns</ReportTitle><ReportDescription>The Monthly Film Returns Report provides a count of the number of titles rented from the DVD library per month grouped by year. It also provides a monthly count of films returned late. The report includes a graph that represents this data.
&lt;p&gt;
This tutorial demonstrates the following features :-
&lt;p&gt;
1. &lt;b&gt;Generating a graph within the report body&lt;/b&gt;
&lt;br&gt;2. &lt;b&gt;Use of a complex SQL to generate the report&lt;/b&gt;
&lt;br&gt;3. &lt;b&gt;Use of a criteria item with the &lt;/b&gt;


</ReportDescription><PageSize>A4</PageSize><PageOrientation>Portrait</PageOrientation><TopMargin></TopMargin><BottomMargin></BottomMargin><RightMargin></RightMargin><LeftMargin></LeftMargin><pdfFont></pdfFont><pdfFontSize></pdfFontSize><PreExecuteCode>NONE</PreExecuteCode><formBetweenRows>solidline</formBetweenRows><bodyDisplay>show</bodyDisplay><graphDisplay>show</graphDisplay><gridDisplay>.DEFAULT</gridDisplay><gridSortable>.DEFAULT</gridSortable><gridSearchable>.DEFAULT</gridSearchable><gridPageable>.DEFAULT</gridPageable><gridPageSize>.DEFAULT</gridPageSize></Format><Datasource><SourceType>database</SourceType><SourceConnection><DatabaseType>mysql</DatabaseType><DatabaseName></DatabaseName><HostName></HostName><ServiceName></ServiceName><UserName></UserName><Password></Password></SourceConnection></Datasource><EntryForm><Query><TableSql></TableSql><WhereSql></WhereSql><GroupSql></GroupSql><RowSelection>all</RowSelection><SQL><QuerySql></QuerySql><SQLRaw>SELECT DATE_FORMAT(loan_date, '%m') loaned_mthno, DATE_FORMAT(loan_date, '%M') loaned_mth, DATE_FORMAT(loan_date, '%Y') loaned_yr, count(*) loan_count, SUM(IF(to_days(return_date) - to_days(loan_date) &gt; 2 OR return_date IS NULL, 1,0)) overdue 
FROM reptut_film film INNER JOIN reptut_loan loan ON film.film_id = loan.film_id 
WHERE 1 = 1                                  
AND film.film_id = loan.film_id GROUP BY 1, 2, 3 
ORDER BY  loaned_yr ASC, loaned_mthno ASC</SQLRaw></SQL><QueryColumns><QueryColumn><Name>loaned_mthno</Name><TableName></TableName><ColumnName>DATE_FORMAT(loan_date, '%m')</ColumnName><ColumnType>char</ColumnType><ColumnLength>30</ColumnLength><Format><column_display>hide</column_display><content_type>plain</content_type><ColumnStartPDF></ColumnStartPDF><justify>left</justify><ColumnWidthPDF></ColumnWidthPDF><ColumnWidthHTML></ColumnWidthHTML><column_title></column_title><tooltip></tooltip><group_header_label></group_header_label><group_header_label_xpos></group_header_label_xpos><group_header_data_xpos></group_header_data_xpos><group_trailer_label></group_trailer_label></Format></QueryColumn><QueryColumn><Name>loaned_mth</Name><TableName></TableName><ColumnName>DATE_FORMAT(loan_date, '%M')</ColumnName><ColumnType>char</ColumnType><ColumnLength>30</ColumnLength><Format><column_display>show</column_display><content_type>plain</content_type><ColumnStartPDF></ColumnStartPDF><justify>left</justify><ColumnWidthPDF></ColumnWidthPDF><ColumnWidthHTML></ColumnWidthHTML><column_title></column_title><tooltip></tooltip><group_header_label></group_header_label><group_header_label_xpos></group_header_label_xpos><group_header_data_xpos></group_header_data_xpos><group_trailer_label></group_trailer_label></Format></QueryColumn><QueryColumn><Name>loaned_yr</Name><TableName></TableName><ColumnName>DATE_FORMAT(loan_date, '%Y')</ColumnName><ColumnType>char</ColumnType><ColumnLength>30</ColumnLength><Format><column_display>hide</column_display><content_type>plain</content_type><ColumnStartPDF></ColumnStartPDF><justify>left</justify><ColumnWidthPDF></ColumnWidthPDF><ColumnWidthHTML></ColumnWidthHTML><column_title>Year</column_title><tooltip></tooltip><group_header_label></group_header_label><group_header_label_xpos></group_header_label_xpos><group_header_data_xpos></group_header_data_xpos><group_trailer_label></group_trailer_label></Format></QueryColumn><QueryColumn><Name>loan_count</Name><TableName></TableName><ColumnName>count(*)</ColumnName><ColumnType>char</ColumnType><ColumnLength>30</ColumnLength><Format><column_display>show</column_display><content_type>plain</content_type><ColumnStartPDF></ColumnStartPDF><justify>left</justify><ColumnWidthPDF></ColumnWidthPDF><ColumnWidthHTML></ColumnWidthHTML><column_title></column_title><tooltip></tooltip><group_header_label></group_header_label><group_header_label_xpos></group_header_label_xpos><group_header_data_xpos></group_header_data_xpos><group_trailer_label></group_trailer_label></Format></QueryColumn><QueryColumn><Name>overdue</Name><TableName></TableName><ColumnName>SUM(IF(to_days(return_date) - to_days(loan_date) &gt; 2 OR return_date IS NULL, 1,0))</ColumnName><ColumnType>char</ColumnType><ColumnLength>30</ColumnLength><Format><column_display>show</column_display><content_type>plain</content_type><ColumnStartPDF></ColumnStartPDF><justify>left</justify><ColumnWidthPDF></ColumnWidthPDF><ColumnWidthHTML></ColumnWidthHTML><column_title></column_title><tooltip></tooltip><group_header_label></group_header_label><group_header_label_xpos></group_header_label_xpos><group_header_data_xpos></group_header_data_xpos><group_trailer_label></group_trailer_label></Format></QueryColumn></QueryColumns><OrderColumns></OrderColumns><PreSQLS></PreSQLS></Query><Assignments></Assignments><Criteria></Criteria><Output><PageHeaders><PageHeader><LineNumber>1</LineNumber><HeaderText>Rentals for {loaned_yr}</HeaderText><Format><ColumnStartPDF>5%</ColumnStartPDF><justify>center</justify><ColumnWidthPDF>90%</ColumnWidthPDF></Format></PageHeader></PageHeaders><PageFooters></PageFooters><DisplayOrders><DisplayOrder><ColumnName>loaned_yr</ColumnName><OrderNumber>1</OrderNumber></DisplayOrder><DisplayOrder><ColumnName>loaned_mth</ColumnName><OrderNumber>2</OrderNumber></DisplayOrder><DisplayOrder><ColumnName>loan_count</ColumnName><OrderNumber>3</OrderNumber></DisplayOrder><DisplayOrder><ColumnName>loaned_mthno</ColumnName><OrderNumber>4</OrderNumber></DisplayOrder><DisplayOrder><ColumnName>overdue</ColumnName><OrderNumber>5</OrderNumber></DisplayOrder></DisplayOrders><Groups><Group><GroupName>loaned_yr</GroupName><BeforeGroupHeader>newpage</BeforeGroupHeader><AfterGroupHeader>blankline</AfterGroupHeader><BeforeGroupTrailer>blankline</BeforeGroupTrailer><AfterGroupTrailer>blankline</AfterGroupTrailer><GroupHeaders><GroupHeader><GroupHeaderColumn>loaned_yr</GroupHeaderColumn></GroupHeader></GroupHeaders><GroupTrailers></GroupTrailers></Group></Groups><Graphs><Graph><GraphColumn>loaned_yr</GraphColumn><GraphColor>.DEFAULT</GraphColor><Title>Rentals {loaned_yr}</Title><GraphWidth>.DEFAULT</GraphWidth><GraphHeight>.DEFAULT</GraphHeight><GraphWidthPDF>.DEFAULT</GraphWidthPDF><GraphHeightPDF>.DEFAULT</GraphHeightPDF><XTitle>Month</XTitle><YTitle>Rented</YTitle><GridPosition>.DEFAULT</GridPosition><XGridDisplay>.DEFAULT</XGridDisplay><XGridColor>.DEFAULT</XGridColor><YGridDisplay>.DEFAULT</YGridDisplay><YGridColor>.DEFAULT</YGridColor><XLabelColumn>loaned_mth</XLabelColumn><TitleFont>.DEFAULT</TitleFont><TitleFontStyle>.DEFAULT</TitleFontStyle><TitleFontSize>.DEFAULT</TitleFontSize><TitleColor>.DEFAULT</TitleColor><XTitleFont>.DEFAULT</XTitleFont><XTitleFontStyle>.DEFAULT</XTitleFontStyle><XTitleFontSize>.DEFAULT</XTitleFontSize><XTitleColor>.DEFAULT</XTitleColor><YTitleFont>.DEFAULT</YTitleFont><YTitleFontStyle>.DEFAULT</YTitleFontStyle><YTitleFontSize>.DEFAULT</YTitleFontSize><YTitleColor>.DEFAULT</YTitleColor><XAxisColor>.DEFAULT</XAxisColor><XAxisFont>.DEFAULT</XAxisFont><XAxisFontStyle>.DEFAULT</XAxisFontStyle><XAxisFontSize>.DEFAULT</XAxisFontSize><XAxisFontColor>.DEFAULT</XAxisFontColor><YAxisColor>.DEFAULT</YAxisColor><YAxisFont>.DEFAULT</YAxisFont><YAxisFontStyle>.DEFAULT</YAxisFontStyle><YAxisFontSize>.DEFAULT</YAxisFontSize><YAxisFontColor>.DEFAULT</YAxisFontColor><XTickInterval>.DEFAULT</XTickInterval><YTickInterval>.DEFAULT</YTickInterval><XTickLabelInterval>.DEFAULT</XTickLabelInterval><YTickLabelInterval>.DEFAULT</YTickLabelInterval><MarginColor>.DEFAULT</MarginColor><MarginLeft>.DEFAULT</MarginLeft><MarginRight>.DEFAULT</MarginRight><MarginTop>.DEFAULT</MarginTop><MarginBottom>.DEFAULT</MarginBottom><Plots><Plot><PlotColumn>loan_count</PlotColumn><PlotType>BAR</PlotType><LineColor></LineColor><DataType>number</DataType><Legend>Rented</Legend><FillColor>yellow</FillColor></Plot><Plot><PlotColumn>overdue</PlotColumn><PlotType>BAR</PlotType><LineColor></LineColor><DataType>number</DataType><Legend>Overdue</Legend><FillColor>orange</FillColor></Plot></Plots></Graph></Graphs></Output></EntryForm></ReportQuery></Report>