<?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 boyy&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>    // Create temporary table to hold count of each film category
    $sql =&quot;CREATE TEMPORARY TABLE t_cattotal 
          ( 
              cat_code CHAR(5),
              cat_total INTEGER 
           ) &quot;;
    $stat = $_connection-&gt;Execute($sql); 

    if ( !$stat ) 
    {
        trigger_error(&quot;Query Failed&lt;BR&gt;&quot;.$sql.&quot;&lt;br&gt;&quot; . 
              &quot;Error &quot;.$_connection-&gt;ErrorNo(). &quot; - &quot;. 
              $_connection-&gt;ErrorMsg(), E_USER_ERROR); 
              return; 
    } 

    // Now populate table with category counts
    $sql = &quot;INSERT INTO t_cattotal 
                SELECT cat_code, count(*)
                  FROM film
                  GROUP BY cat_code&quot;; 
    $stat = $_connection-&gt;Execute($sql); 


</PreExecuteCode><bodyDisplay>show</bodyDisplay><graphDisplay>show</graphDisplay></Format><Datasource><SourceType>database</SourceType><SourceConnection><DatabaseType>none</DatabaseType><DatabaseName></DatabaseName><HostName>localhost</HostName><ServiceName></ServiceName><UserName></UserName><Password></Password></SourceConnection></Datasource><EntryForm><Query><TableSql>film INNER JOIN loan ON film.film_id = loan.film_id</TableSql><WhereSql>                                
AND film.film_id = loan.film_id</WhereSql><GroupSql>GROUP BY 1, 2, 3</GroupSql><SQL><QuerySql></QuerySql></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><OrderColumn><Name>loaned_yr</Name><OrderType>ASC</OrderType></OrderColumn><OrderColumn><Name>loaned_mthno</Name><OrderType>ASC</OrderType></OrderColumn></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>white</GraphColor><Title>Rentals {loaned_yr}</Title><GraphWidth></GraphWidth><GraphHeight></GraphHeight><GraphWidthPDF></GraphWidthPDF><GraphHeightPDF></GraphHeightPDF><XTitle>Month</XTitle><YTitle>Rented</YTitle><GridPosition>back</GridPosition><XGridDisplay>major</XGridDisplay><XGridColor>gray</XGridColor><YGridDisplay>none</YGridDisplay><YGridColor>gray</YGridColor><XLabelColumn>loaned_mth</XLabelColumn><TitleFont>Font 1</TitleFont><TitleFontStyle>Normal</TitleFontStyle><TitleFontSize>12</TitleFontSize><TitleColor>black</TitleColor><XTitleFont>Font 1</XTitleFont><XTitleFontStyle>Normal</XTitleFontStyle><XTitleFontSize>12</XTitleFontSize><XTitleColor>black</XTitleColor><YTitleFont>Font 1</YTitleFont><YTitleFontStyle>Normal</YTitleFontStyle><YTitleFontSize>12</YTitleFontSize><YTitleColor>black</YTitleColor><XAxisColor>black</XAxisColor><XAxisFont>Font 1</XAxisFont><XAxisFontStyle>Normal</XAxisFontStyle><XAxisFontSize>8</XAxisFontSize><XAxisFontColor>black</XAxisFontColor><YAxisColor>black</YAxisColor><YAxisFont>Font 1</YAxisFont><YAxisFontStyle>Normal</YAxisFontStyle><YAxisFontSize>8</YAxisFontSize><YAxisFontColor>black</YAxisFontColor><XTickInterval></XTickInterval><YTickInterval></YTickInterval><XTickLabelInterval>1</XTickLabelInterval><YTickLabelInterval>1</YTickLabelInterval><MarginColor>white</MarginColor><MarginLeft>50</MarginLeft><MarginRight>50</MarginRight><MarginTop>20</MarginTop><MarginBottom>100</MarginBottom><Plots><Plot><PlotColumn>loan_count</PlotColumn><PlotType>BAR</PlotType><LineColor>black</LineColor><DataType>number</DataType><Legend>Rented</Legend><FillColor>yellow</FillColor></Plot><Plot><PlotColumn>overdue</PlotColumn><PlotType>BAR</PlotType><LineColor>black</LineColor><DataType>number</DataType><Legend>Overdue</Legend><FillColor>orange</FillColor></Plot></Plots></Graph></Graphs></Output></EntryForm></ReportQuery></Report>