Hi,
Excuse me if this is a existing topic - I searched through the forum but couldn’t find anything - i don’t know the nomenclature enough to search for some keyword. As a matter of fact I am going through the examples in core\modules\core\test\sample
I just wanted to find out how I could pass a user parameter for the query in the report.
I get that I can either use the xml document with the query as shown below
<band name="Data" orientation="H">
<queries>
<query name="Data" type="sql">
<script>
select person_type as "person_type"
from PERSON_TYPE
where email_id=${email_id}
</script>
</query>
</queries>
</band>
and run it from java using the below code
Report report = new DefaultXmlReader().parseXml(readFileToString("working.xml")); Map<String, Object> params = new HashMap<String, Object>(); params.put("email_id", new String("xxxxx@yyy.com")); Reporting reporting = new Reporting(); reporting.setFormatterFactory(new DefaultFormatterFactory()); reporting.setLoaderFactory( new DefaultLoaderFactory().setSqlDataLoader(new SqlDataLoader(getOracleDataSource()).loadData(ReportQuery, BandData, params))); ReportOutputDocument output = reporting.runReport(new RunParams(report), new FileOutputStream("working.xlsx"));
Here I believe the SqlDataLoader.loadData(ReportQuery, BandData, params) is used to pass the parameter. In that case how would I get the BandData (parentBand) object and the ReportQuery object for the given Report?
I believe I can do the same thing exclusively in Java as shown below -
ReportBuilder reportBuilder = new ReportBuilder(); ReportTemplateBuilder reportTemplateBuilder = new ReportTemplateBuilder() .documentPath("working.xlsx") .documentName("working.xlsx") .outputType(ReportOutputType.xlsx) .readFileFromPath(); reportBuilder.template(reportTemplateBuilder.build()); BandBuilder bandBuilder = new BandBuilder(); ReportBand date= bandBuilder.name("Date") .query("Date", "select TO_CHAR(sysdate, 'MM/DD/YYYY') as \"my_date\" from dual", "sql") .build(); reportBuilder.band(date); ReportBand header= bandBuilder.name("Header").build(); reportBuilder.band(header); ReportBand data= bandBuilder.name("Data") .query("Data", "select person_type as \"person_type\" from PERSON_TYPE where email_id = ${email_id}", "sql") .build(); reportBuilder.band(data); Report report = reportBuilder.build(); Reporting reporting = new Reporting(); reporting.setFormatterFactory(new DefaultFormatterFactory()); reporting.setLoaderFactory( new DefaultLoaderFactory().setSqlDataLoader(new SqlDataLoader(getOracleDataSource()))); ReportOutputDocument reportOutputDocument = reporting.runReport(new RunParams(report), new FileOutputStream("working.xlsx"));
Again how will I pass the email_id query parameter to the query?