title: How to filter data in table (crosstab) by date
body: <p>I need some help in creating report using iReport for this layout. I am able to create records using crosstab, but its creating duplicate records. </p>
  
  <pre><code>                   Male                      Female
  </code></pre>
  
  <p>Date</p>
  
  <p>Total Count</p>
  
  <p>Query </p>
  
  <pre><code>SELECT table1.`gender` AS table1_gender,
       registration_details.`date_reg` AS registration_details_date_reg
  FROM
       `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`
  </code></pre>
  
  <p>where
  gender is present in table1 (candidate_id : pk)
  date_reg is present in registration_details</p>
  
  <p>The <em>jrxml</em>:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report12" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"&gt;
      &lt;style name="Crosstab Data Text" hAlign="Center"/&gt;
      &lt;style name="table"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="1.0" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table_TH" mode="Opaque" backcolor="#F0F8FF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table_CH" mode="Opaque" backcolor="#BFE1FF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table_TD" mode="Opaque" backcolor="#FFFFFF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table 1"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="1.0" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table 1_TH" mode="Opaque" backcolor="#F0F8FF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table 1_CH" mode="Opaque" backcolor="#BFE1FF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;style name="table 1_TD" mode="Opaque" backcolor="#FFFFFF"&gt;
          &lt;box&gt;
              &lt;pen lineWidth="0.5" lineColor="#000000"/&gt;
          &lt;/box&gt;
      &lt;/style&gt;
      &lt;subDataset name="New Dataset 1"&gt;
          &lt;queryString language="SQL"&gt;
              &lt;![CDATA[SELECT
       table1.`gender` AS table1_gender,
      registration_details.`date_reg` AS registration_details_date_reg
  FROM
   `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]&gt;
          &lt;/queryString&gt;
          &lt;field name="table1_gender" class="java.lang.String"/&gt;
          &lt;field name="registration_details_date_reg" class="java.sql.Date"/&gt;
      &lt;/subDataset&gt;
      &lt;subDataset name="Table Dataset 1"/&gt;
      &lt;queryString language="SQL"&gt;
          &lt;![CDATA[SELECT
       table1.`gender` AS table1_gender,
       registration_details.`date_reg` AS registration_details_date_reg
  FROM
       `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]&gt;
      &lt;/queryString&gt;
      &lt;field name="table1_gender" class="java.lang.String"/&gt;
      &lt;field name="date_reg" class="java.sql.Date"/&gt;
      &lt;detail&gt;
          &lt;band height="125" splitType="Stretch"&gt;
              &lt;crosstab&gt;
                  &lt;reportElement x="0" y="0" width="555" height="125"/&gt;
                  &lt;crosstabDataset&gt;
                      &lt;dataset&gt;
                          &lt;datasetRun subDataset="New Dataset 1"&gt;
                              &lt;connectionExpression&gt;&lt;![CDATA[$P{REPORT_CONNECTION}]]&gt;&lt;/connectionExpression&gt;
                          &lt;/datasetRun&gt;
                      &lt;/dataset&gt;
                  &lt;/crosstabDataset&gt;
                  &lt;rowGroup name="date_reg" width="70" totalPosition="End"&gt;
                      &lt;bucket&gt;
                          &lt;bucketExpression class="java.lang.String"&gt;&lt;![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($F{date_reg})]]&gt;&lt;/bucketExpression&gt;
                      &lt;/bucket&gt;
                      &lt;crosstabRowHeader&gt;
                          &lt;cellContents backcolor="#F0F8FF" mode="Opaque"&gt;
                              &lt;box&gt;
                                  &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                              &lt;/box&gt;
                              &lt;textField&gt;
                                  &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="25"/&gt;
                                  &lt;textElement/&gt;
                                  &lt;textFieldExpression class="java.lang.String"&gt;&lt;![CDATA[$V{date_reg}]]&gt;&lt;/textFieldExpression&gt;
                              &lt;/textField&gt;
                          &lt;/cellContents&gt;
                      &lt;/crosstabRowHeader&gt;
                      &lt;crosstabTotalRowHeader&gt;
                          &lt;cellContents backcolor="#BFE1FF" mode="Opaque"&gt;
                              &lt;box&gt;
                                  &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                              &lt;/box&gt;
                              &lt;staticText&gt;
                                  &lt;reportElement x="0" y="0" width="70" height="25"/&gt;
                                  &lt;textElement textAlignment="Center" verticalAlignment="Middle"/&gt;
                                  &lt;text&gt;&lt;![CDATA[Total date_reg]]&gt;&lt;/text&gt;
                              &lt;/staticText&gt;
                          &lt;/cellContents&gt;
                      &lt;/crosstabTotalRowHeader&gt;
                  &lt;/rowGroup&gt;
                  &lt;columnGroup name="table1_gender" height="30" totalPosition="End"&gt;
                      &lt;bucket&gt;
                          &lt;bucketExpression class="java.lang.String"&gt;&lt;![CDATA[$F{table1_gender}]]&gt;&lt;/bucketExpression&gt;
                      &lt;/bucket&gt;
                      &lt;crosstabColumnHeader&gt;
                          &lt;cellContents backcolor="#F0F8FF" mode="Opaque"&gt;
                              &lt;box&gt;
                                  &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                              &lt;/box&gt;
                              &lt;textField&gt;
                                  &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="30"/&gt;
                                  &lt;textElement/&gt;
                                  &lt;textFieldExpression class="java.lang.String"&gt;&lt;![CDATA[$V{student_gender}]]&gt;&lt;/textFieldExpression&gt;
                              &lt;/textField&gt;
                          &lt;/cellContents&gt;
                      &lt;/crosstabColumnHeader&gt;
                      &lt;crosstabTotalColumnHeader&gt;
                          &lt;cellContents backcolor="#BFE1FF" mode="Opaque"&gt;
                              &lt;box&gt;
                                  &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                              &lt;/box&gt;
                              &lt;staticText&gt;
                                  &lt;reportElement x="0" y="0" width="50" height="30"/&gt;
                                  &lt;textElement textAlignment="Center" verticalAlignment="Middle"/&gt;
                                  &lt;text&gt;&lt;![CDATA[Total student_gender]]&gt;&lt;/text&gt;
                              &lt;/staticText&gt;
                          &lt;/cellContents&gt;
                      &lt;/crosstabTotalColumnHeader&gt;
                  &lt;/columnGroup&gt;
                  &lt;measure name="table1_genderMeasure" class="java.lang.Integer" calculation="Count"&gt;
                      &lt;measureExpression&gt;&lt;![CDATA[$F{table1_gender}]]&gt;&lt;/measureExpression&gt;
                  &lt;/measure&gt;
                  &lt;crosstabCell width="50" height="25"&gt;
                      &lt;cellContents&gt;
                          &lt;box&gt;
                              &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                          &lt;/box&gt;
                          &lt;textField&gt;
                              &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/&gt;
                              &lt;textElement/&gt;
                              &lt;textFieldExpression class="java.lang.Integer"&gt;&lt;![CDATA[$V{table1_genderMeasure}]]&gt;&lt;/textFieldExpression&gt;
                          &lt;/textField&gt;
                      &lt;/cellContents&gt;
                  &lt;/crosstabCell&gt;
                  &lt;crosstabCell height="25" rowTotalGroup="date_reg"&gt;
                      &lt;cellContents backcolor="#BFE1FF" mode="Opaque"&gt;
                          &lt;box&gt;
                              &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                          &lt;/box&gt;
                          &lt;textField&gt;
                              &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/&gt;
                              &lt;textElement/&gt;
                              &lt;textFieldExpression class="java.lang.Integer"&gt;&lt;![CDATA[$V{table1_genderMeasure}]]&gt;&lt;/textFieldExpression&gt;
                          &lt;/textField&gt;
                      &lt;/cellContents&gt;
                  &lt;/crosstabCell&gt;
                  &lt;crosstabCell width="50" columnTotalGroup="student_gender"&gt;
                      &lt;cellContents backcolor="#BFE1FF" mode="Opaque"&gt;
                          &lt;box&gt;
                              &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                          &lt;/box&gt;
                          &lt;textField&gt;
                              &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/&gt;
                              &lt;textElement/&gt;
                              &lt;textFieldExpression class="java.lang.Integer"&gt;&lt;![CDATA[$V{table1_genderMeasure}]]&gt;&lt;/textFieldExpression&gt;
                          &lt;/textField&gt;
                      &lt;/cellContents&gt;
                  &lt;/crosstabCell&gt;
                  &lt;crosstabCell rowTotalGroup="date_reg" columnTotalGroup="table1_gender"&gt;
                      &lt;cellContents backcolor="#BFE1FF" mode="Opaque"&gt;
                          &lt;box&gt;
                              &lt;pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/&gt;
                          &lt;/box&gt;
                          &lt;textField&gt;
                              &lt;reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/&gt;
                              &lt;textElement/&gt;
                              &lt;textFieldExpression class="java.lang.Integer"&gt;&lt;![CDATA[$V{table1_genderMeasure}]]&gt;&lt;/textFieldExpression&gt;
                          &lt;/textField&gt;
                      &lt;/cellContents&gt;
                  &lt;/crosstabCell&gt;
              &lt;/crosstab&gt;
          &lt;/band&gt;
      &lt;/detail&gt;
  &lt;/jasperReport&gt;
  </code></pre>
  
  <p>Currently its showing all the records , Need suggestions/help in creating report 
  Where a User will be able to fetch records by providing date.</p>
  
  <p>also its printing single records multiple time. </p>
  
  <p>I would appreciate any help. </p>