<?xml version="1.0"?>
<Report>
  <ReportQuery>
    <Format>
      <ReportTitle>Stock Report - Tutorial 1 Stage 4 - Working with Groups</ReportTitle>
      <ReportDescription>&lt;p&gt;
This is Tutorial demonstrates the following features :-
&lt;p&gt;
1. &lt;b&gt;Creating Groups&lt;/b&gt;
&lt;br&gt;2. &lt;b&gt;Creating Group Headers&lt;/b&gt;
&lt;br&gt;3. &lt;b&gt;Creating Group Column Trailers&lt;/b&gt;

&lt;p&gt;Click &lt;a style="text-decoration: underline" target="_blank" href="http://www.reportico.org/documentation/4.6/doku.php?id=tutorial_1_stage_4_-_working_with_groups"&gt;here &lt;/a&gt; for instructions on how to work through this tutorial</ReportDescription>
      <PageSize>.DEFAULT</PageSize>
      <PageOrientation>.DEFAULT</PageOrientation>
      <TopMargin/>
      <BottomMargin/>
      <RightMargin/>
      <LeftMargin/>
      <pdfFont/>
      <pdfFontSize/>
      <PreExecuteCode>NONE</PreExecuteCode>
      <formBetweenRows>solidline</formBetweenRows>
      <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/>
        <HostName/>
        <ServiceName/>
        <UserName/>
        <Password/>
      </SourceConnection>
    </Datasource>
    <EntryForm>
      <Query>
        <TableSql/>
        <WhereSql/>
        <GroupSql/>
        <RowSelection>all</RowSelection>
        <SQL>
          <QuerySql/>
          <SQLRaw>SELECT  ProductID id, ProductName product, northwind_categories.CategoryID, UnitsInStock in_stock, UnitsOnOrder on_order, ReorderLevel,  companyname Company, country, categoryname category
FROM northwind_products 
join northwind_suppliers on northwind_products.supplierid = northwind_suppliers.supplierid
join northwind_categories on northwind_products.categoryid = northwind_categories.categoryid
WHERE 1 = 1  
ORDER BY categoryname</SQLRaw>
        </SQL>
        <QueryColumns>
          <QueryColumn>
            <Name>id</Name>
            <TableName/>
            <ColumnName>ProductID</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>product</Name>
            <TableName/>
            <ColumnName>ProductName</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>CategoryID</Name>
            <TableName>northwind_categories</TableName>
            <ColumnName>CategoryID</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>in_stock</Name>
            <TableName/>
            <ColumnName>UnitsInStock</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>on_order</Name>
            <TableName/>
            <ColumnName>UnitsOnOrder</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>ReorderLevel</Name>
            <TableName/>
            <ColumnName>ReorderLevel</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title>Reorder Level</column_title>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>Company</Name>
            <TableName/>
            <ColumnName>companyname</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>country</Name>
            <TableName/>
            <ColumnName>country</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>category</Name>
            <TableName/>
            <ColumnName>categoryname</ColumnName>
            <ColumnType>char</ColumnType>
            <ColumnLength>30</ColumnLength>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>in_stock_total</Name>
            <TableName/>
            <ColumnName/>
            <ColumnType/>
            <ColumnLength/>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
          <QueryColumn>
            <Name>count</Name>
            <TableName/>
            <ColumnName/>
            <ColumnType/>
            <ColumnLength/>
            <Format>
              <column_display>show</column_display>
              <content_type>plain</content_type>
              <ColumnStartPDF/>
              <justify>left</justify>
              <ColumnWidthPDF/>
              <ColumnWidthHTML/>
              <column_title/>
              <tooltip/>
              <group_header_label>0</group_header_label>
              <group_header_label_xpos/>
              <group_header_data_xpos/>
              <group_trailer_label>0</group_trailer_label>
            </Format>
          </QueryColumn>
        </QueryColumns>
        <OrderColumns/>
        <PreSQLS/>
      </Query>
      <Assignments>
        <Assignment>
          <AssignName>id</AssignName>
          <AssignNameNew/>
          <Expression>str_pad({id},4,"0",STR_PAD_LEFT)</Expression>
          <Condition/>
        </Assignment>
        <Assignment>
          <AssignName>id</AssignName>
          <AssignNameNew/>
          <Expression>{id} . "."</Expression>
          <Condition/>
        </Assignment>
        <Assignment>
          <AssignName>in_stock_total</AssignName>
          <AssignNameNew/>
          <Expression>sum({in_stock})</Expression>
          <Condition/>
        </Assignment>
        <Assignment>
          <AssignName>count</AssignName>
          <AssignNameNew/>
          <Expression>count()</Expression>
          <Condition/>
        </Assignment>
        <Assignment>
          <AssignName>in_stock</AssignName>
          <AssignNameNew/>
          <Expression>applyStyle('CELL', 'color', '#ff0000');</Expression>
          <Condition>{in_stock} &lt; {ReorderLevel}</Condition>
        </Assignment>
      </Assignments>
      <Criteria>
        <CriteriaItem>
          <Name>country</Name>
          <Title>Country</Title>
          <QueryTableName/>
          <QueryColumnName/>
          <CriteriaType>LOOKUP</CriteriaType>
          <CriteriaHelp/>
          <CriteriaDisplay>TEXTFIELD</CriteriaDisplay>
          <ExpandDisplay>MULTI</ExpandDisplay>
          <CriteriaRequired>.DEFAULT</CriteriaRequired>
          <CriteriaHidden>.DEFAULT</CriteriaHidden>
          <ReturnColumn>Country</ReturnColumn>
          <DisplayColumn>Country</DisplayColumn>
          <OverviewColumn>Country</OverviewColumn>
          <MatchColumn>Country</MatchColumn>
          <CriteriaDefaults/>
          <CriteriaList/>
          <Query>
            <TableSql/>
            <WhereSql/>
            <GroupSql/>
            <RowSelection/>
            <SQLRaw>SELECT DISTINCT Country
FROM  northwind_suppliers
WHERE 1 = 1  </SQLRaw>
            <SQL>
              <QuerySql/>
            </SQL>
            <QueryColumns>
              <QueryColumn>
                <Name>Country</Name>
                <TableName/>
                <ColumnName>DISTINCT</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
            </QueryColumns>
            <OrderColumns/>
            <Assignments/>
          </Query>
          <CriteriaLinks/>
        </CriteriaItem>
        <CriteriaItem>
          <Name>supplier</Name>
          <Title>Supplier</Title>
          <QueryTableName/>
          <QueryColumnName/>
          <CriteriaType>LOOKUP</CriteriaType>
          <CriteriaHelp/>
          <CriteriaDisplay>SELECT2MULTIPLE</CriteriaDisplay>
          <ExpandDisplay>NOINPUT</ExpandDisplay>
          <CriteriaRequired>.DEFAULT</CriteriaRequired>
          <CriteriaHidden>.DEFAULT</CriteriaHidden>
          <ReturnColumn>supplierid</ReturnColumn>
          <DisplayColumn>companyname</DisplayColumn>
          <OverviewColumn>companyname</OverviewColumn>
          <MatchColumn>companyname</MatchColumn>
          <CriteriaDefaults/>
          <CriteriaList/>
          <Query>
            <TableSql/>
            <WhereSql/>
            <GroupSql/>
            <RowSelection/>
            <SQLRaw>SELECT supplierid, companyname
FROM  northwind_suppliers
WHERE 1 = 1  
[ AND country in ( {country} ) ]
</SQLRaw>
            <SQL>
              <QuerySql/>
            </SQL>
            <QueryColumns>
              <QueryColumn>
                <Name>supplierid</Name>
                <TableName/>
                <ColumnName>supplierid</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
              <QueryColumn>
                <Name>companyname</Name>
                <TableName/>
                <ColumnName>companyname</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
            </QueryColumns>
            <OrderColumns/>
            <Assignments>
              <Assignment>
                <AssignName>Column</AssignName>
                <AssignNameNew/>
                <Expression>Expression</Expression>
                <Condition/>
              </Assignment>
            </Assignments>
          </Query>
          <CriteriaLinks/>
        </CriteriaItem>
        <CriteriaItem>
          <Name>category</Name>
          <Title>Category</Title>
          <QueryTableName/>
          <QueryColumnName/>
          <CriteriaType>LOOKUP</CriteriaType>
          <CriteriaHelp/>
          <CriteriaDisplay>CHECKBOX</CriteriaDisplay>
          <ExpandDisplay>NOINPUT</ExpandDisplay>
          <CriteriaRequired>.DEFAULT</CriteriaRequired>
          <CriteriaHidden>.DEFAULT</CriteriaHidden>
          <ReturnColumn>CategoryID</ReturnColumn>
          <DisplayColumn>CategoryName</DisplayColumn>
          <OverviewColumn>CategoryName</OverviewColumn>
          <MatchColumn>CategoryName</MatchColumn>
          <CriteriaDefaults/>
          <CriteriaList/>
          <Query>
            <TableSql/>
            <WhereSql/>
            <GroupSql/>
            <RowSelection/>
            <SQLRaw>SELECT CategoryID, CategoryName, Description
FROM northwind_categories
WHERE 1 = 1  
ORDER BY CategoryName</SQLRaw>
            <SQL>
              <QuerySql/>
            </SQL>
            <QueryColumns>
              <QueryColumn>
                <Name>CategoryID</Name>
                <TableName/>
                <ColumnName>CategoryID</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
              <QueryColumn>
                <Name>CategoryName</Name>
                <TableName/>
                <ColumnName>CategoryName</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
              <QueryColumn>
                <Name>Description</Name>
                <TableName/>
                <ColumnName>Description</ColumnName>
                <ColumnType>char</ColumnType>
                <ColumnLength>30</ColumnLength>
                <Format>
                  <column_display>show</column_display>
                  <content_type>plain</content_type>
                  <justify>left</justify>
                </Format>
              </QueryColumn>
            </QueryColumns>
            <OrderColumns/>
            <Assignments/>
          </Query>
          <CriteriaLinks/>
        </CriteriaItem>
        <CriteriaItem>
          <Name>lowstock</Name>
          <Title>Show Only Low Stock</Title>
          <QueryTableName/>
          <QueryColumnName/>
          <CriteriaType>LIST</CriteriaType>
          <CriteriaHelp/>
          <CriteriaDisplay>CHECKBOX</CriteriaDisplay>
          <ExpandDisplay>NOINPUT</ExpandDisplay>
          <CriteriaRequired>.DEFAULT</CriteriaRequired>
          <CriteriaHidden>.DEFAULT</CriteriaHidden>
          <ReturnColumn/>
          <DisplayColumn/>
          <OverviewColumn/>
          <MatchColumn/>
          <CriteriaDefaults/>
          <CriteriaList>Yes=1</CriteriaList>
          <Query>
            <TableSql/>
            <WhereSql/>
            <GroupSql/>
            <RowSelection/>
            <SQLRaw/>
            <SQL>
              <QuerySql/>
            </SQL>
            <QueryColumns/>
            <OrderColumns/>
            <Assignments/>
          </Query>
          <CriteriaLinks/>
        </CriteriaItem>
      </Criteria>
      <Output>
        <PageHeaders>
          <PageHeader>
            <LineNumber>1</LineNumber>
            <HeaderText>{REPORT_TITLE}{STYLE border-width: 1 0 1 0; margin: 15px 0px 0px 0px; border-color: #000000; font-size: 18; border-style: solid;padding:5px 0px 5px 0px; height:1cm; background-color: #000000; color: #ffffff; text-align: center}</HeaderText>
            <Format>
              <ColumnStartPDF/>
              <justify>center</justify>
              <ColumnWidthPDF/>
              <ShowInPDF>yes</ShowInPDF>
              <ShowInHTML>no</ShowInHTML>
            </Format>
          </PageHeader>
          <PageHeader>
            <LineNumber>1</LineNumber>
            <HeaderText>Time: date('Y-m-d H:i:s'){STYLE font-size: 10; text-align: right; font-style: italic;}</HeaderText>
            <Format>
              <ColumnStartPDF/>
              <justify>right</justify>
              <ColumnWidthPDF/>
              <ShowInPDF>yes</ShowInPDF>
              <ShowInHTML>no</ShowInHTML>
            </Format>
          </PageHeader>
        </PageHeaders>
        <PageFooters>
          <PageFooter>
            <LineNumber>2</LineNumber>
            <FooterText>Page: {PAGE}{STYLE border-width: 1 0 0 0; margin: 40 0 0 0; font-style: italic; }</FooterText>
            <Format>
              <ColumnStartPDF/>
              <justify>center</justify>
              <ColumnWidthPDF/>
              <ShowInPDF>yes</ShowInPDF>
              <ShowInHTML>no</ShowInHTML>
            </Format>
          </PageFooter>
        </PageFooters>
        <DisplayOrders>
          <DisplayOrder>
            <ColumnName>id</ColumnName>
            <OrderNumber>1</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>product</ColumnName>
            <OrderNumber>2</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>CategoryID</ColumnName>
            <OrderNumber>3</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>in_stock</ColumnName>
            <OrderNumber>4</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>on_order</ColumnName>
            <OrderNumber>5</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>ReorderLevel</ColumnName>
            <OrderNumber>6</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>Company</ColumnName>
            <OrderNumber>7</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>country</ColumnName>
            <OrderNumber>8</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>category</ColumnName>
            <OrderNumber>9</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>in_stock_total</ColumnName>
            <OrderNumber>10</OrderNumber>
          </DisplayOrder>
          <DisplayOrder>
            <ColumnName>count</ColumnName>
            <OrderNumber>11</OrderNumber>
          </DisplayOrder>
        </DisplayOrders>
        <Groups/>
        <Graphs/>
      </Output>
    </EntryForm>
  </ReportQuery>
</Report>
