I am trying to generate report with SQL Query:
SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}
It is taking the user input from JavaFX TextField. The Model.id will be replaced with single Integer, say 1 and it should output only one row! But when I am passing this parameters to the JasperReports's report, It is printing all rows available in the table.
The Java Code:
@FXML
public TextField one;
public void click(ActionEvent event) throws JRException, SQLException, ClassNotFoundException, IllegalAccessException, UnsupportedLookAndFeelException, InstantiationException {
String reportSrcFile = "/home/sample/learn.jrxml";
// First, compile jrxml file.
JasperReport jasperReport1 = JasperCompileManager.compileReport(reportSrcFile);
Connection conn = SqliConnect.getMySQLConnection();
// Parameters for report
int two = Integer.parseInt(one.getText());
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("ID",one.getText());//The Exact Parameter name is "ID" in the jrxml file.
JasperPrint print = JasperFillManager.fillReport(jasperReport1,
parameters, conn);
JasperViewer jv = new JasperViewer(print);
jv.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
jv.setTitle("Test Report");
jv.setVisible(true);
}
Report's template:
<subDataset name="Dataset1" uuid="2691431f-5c6f-403f-94cc-829c17ef1636">
<queryString>
<![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model]]>
</queryString>
<field name="id" class="java.lang.Integer"/>
<field name="brand" class="java.lang.String"/>
<field name="model" class="java.lang.String"/>
</subDataset>
<parameter name="ID" class="java.lang.String">
<defaultValueExpression><![CDATA[$P{REPORT_CONTEXT}.getId()]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}]]>
</queryString>
<field name="id" class="java.lang.Integer"/>
<field name="brand" class="java.lang.String"/>
<field name="model" class="java.lang.String"/>
<title>
<band height="79" splitType="Stretch">
<textField>
<reportElement x="11" y="16" width="100" height="30" uuid="d1485589-a4cf-4ab9-b896-0ef480beced4"/>
<textFieldExpression><![CDATA[$P{ID}]]></textFieldExpression>
</textField>
</band>
</title>
<detail>
<band height="250" splitType="Stretch">
<componentElement>
<reportElement x="160" y="50" width="200" height="200" uuid="e199bd58-8408-4711-85d5-ba76db9691b7">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
<property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
<property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
<property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
</reportElement>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="Dataset1" uuid="7fecdfe6-bed4-433a-97f8-43cc6347c660">
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<jr:column width="66" uuid="9a6b765f-b6d4-4753-909e-dd091b296654">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="66" height="30" uuid="7e644d1c-a893-49df-a18a-bf788eb6b92a"/>
<text><![CDATA[id]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="66" height="30" uuid="848a53bf-2b2e-46df-a7aa-26fc0b897c85"/>
<textFieldExpression><![CDATA[$F{id}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="66" uuid="514580a5-f09c-43d1-952e-d4769c2e4686">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="66" height="30" uuid="c01152e9-1547-472b-946e-92011c02bc94"/>
<text><![CDATA[brand]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="66" height="30" uuid="98c84ae5-b289-459a-b34b-a9becf43b9ce"/>
<textFieldExpression><![CDATA[$F{brand}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="66" uuid="5c1d2026-45bc-4ec8-8be4-c444e47d093d">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="66" height="30" uuid="81f76de4-a68b-41c3-a2e8-bbaa625b71be"/>
<text><![CDATA[model]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="66" height="30" uuid="d69c690e-05a4-453a-93ea-9061523975ad"/>
<textFieldExpression><![CDATA[$F{model}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
</jr:table>
</componentElement>
</band>
</detail>
What is the right way to do it?
Update: I have added the parameter for Datasources:
<subDataset name="Dataset1" uuid="2691431f-5c6f-403f-94cc-829c17ef1636">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="Learn"/>
<parameter name="ID" class="java.lang.String"/>
<queryString>
<![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}]]>
</queryString>
<field name="id" class="java.lang.Integer"/>
<field name="brand" class="java.lang.String"/>
<field name="model" class="java.lang.String"/>
</subDataset>
But now it is generating blank report.
The table component in your report uses the 'Dataset1' subdataset, which has no ID parameter defined and contains only a static query. You'll need to declare the ID parameter in the subdataset too, and use it in the query. I hope this helps, Sanda
PS: Please see the edited Raw Paste Data here: http://pastebin.com/ZMjn4jtP