darko.jankovic_1 Posted October 22, 2019 Share Posted October 22, 2019 Is it possible to generate the SQL query that will be executed when generating the report?I found a way to get the query with the parameters, like thisfinal JasperReport report = JasperCompileManager.compileReport(layoutStream);String query = report.getQuery().getText();[/code]What I want to achieve is to get the query with the parameter values inserted. Is there a way to achieve this?I'm using version 6.1.0 but I can probably upgrade if it's necessary. Link to comment Share on other sites More sharing options...
joseng62 Posted October 29, 2019 Share Posted October 29, 2019 So this should be possible. Define a string parameter in your jrxml. Set the given parameters as your datasource query and set the query string on run time. https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queriesHere is an example of a jrxml. I am running on a oracle db datasource, but hard seleting via dual. So in your java code via the jasper java api just figure out to set the given parameter. <?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1 --><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="report name" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="af88d3eb-c1fe-4d76-9a0c-e7394dc2ddb4"> <property name="ireport.zoom" value="3.4522712143931042"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="wbmsresourcesdatasourcesdataAdaptersDev12pdb1.xml"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <style name="style1" isPdfEmbedded="true"/> <parameter name="sqlQueryToInject" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["Select * FROM (select 10 as age , 'Green' as Name from DUAL union select 15 as age , 'Red' as Name from DUAL union select 25 as age , 'Yellow' as Name from DUAL union select 2 as age , 'Blue' as Name from DUAL )"]]></defaultValueExpression> </parameter> <queryString> <![CDATA[ $P!{sqlQueryToInject} ]]> </queryString> <field name="AGE" class="java.math.BigDecimal"/> <field name="NAME" class="java.lang.String"/> <group name="Group1"> <groupExpression><![CDATA[$F{AGE}]]></groupExpression> <groupHeader> <band height="30"/> </groupHeader> </group> <background> <band splitType="Stretch"/> </background> <detail> <band height="50"> <textField> <reportElement x="100" y="0" width="100" height="30" uuid="571c1a0b-812d-43dd-b756-b62289273805"/> <textFieldExpression><![CDATA[$F{AGE}]]></textFieldExpression> </textField> <textField> <reportElement x="0" y="0" width="100" height="30" uuid="a6870cc0-efed-46c4-98be-44100faa40b8"/> <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression> </textField> </band> </detail></jasperReport> Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now