Access to the query used to generate a report?

[hgiv]

Hello,

Is it possible to retrieve a report's query text (prepared statement and statement parameters) using a JasperDesign, JasperReport, or JaperPrint?

Here are the 3 standard lines I'm using to run the report:

   JasperDesign jasperDesign = JRXmlLoader.load (getJasperFile());
   JasperReport jasperReport = JasperCompileManager.compileReport (jasperDesign);
   JasperPrint jasperPrint = JasperFillManager.fillReport (jasperReport, lParams, lConn);

 

If this is not possible can the query be created through some other mechanism?

Long story short, I'm having issues with the CSV export where what is displayed as a single row in a PDF is being rendered across two lines when exporting to CSV. I have verified the report design is using the same Y coordinates (i.e. top property) for the fields that are being split across multiple lines.

Since generation of CSV is a requirement for our project, I'm trying to determine if I can simply retrieve the query from jasper and hack in my own CSV generation logic... With access the query this would be very simple to implement to suite our needs. The main issue is that we are using dynamic queiries where chunks of the query are defined as parameters and generated runtime. So, in order to do this properly, I need to get the query from Jasper after the parameters have been generated.

Any help would be greatly appreciated!

[svenn]

There is no such fuctionality to display the SQL.

In each band you also have to make sure that the items in each band that fall within the same column start at the same X co ordinate.

Have you tried exporting the report to a different format to verify if you are actually getting more columns than expected? Try exporting it to pdf for example.

 

 

[hgiv]

I was afraid of that, thanks for the feedback.

 

Yes, I have tried with other export methods. PDF and the internal viewer both look correct. When exporting in JRTextExporter I am seeing the same behavior seen in the CSV export.

 

I have went as far as trying to implement my own exporter based on JRTextExporter and am ocasionally seeing the Y coordinates off by 1 for the elements that are being displayed on a different lines.

 

I'm not sure if this will be useful or not but below is debug output I added to a locally modified JRTextExporter's exportText() method. This output demonstrates a row that is displayed correctly and another showing output that is split:

 

// Row of report data that is correct, notice calculated Y value is same.

exporting Text: [XXX] Calc X (3), Calc Y (32), Element X (22) Element Y (226), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (11), Calc Y (32), Element X (84) Element Y (225), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (27), Calc Y (32), Element X (191) Element Y (226), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (41), Calc Y (32), Element X (295) Element Y (226), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (82), Calc Y (32), Element X (583) Element Y (226), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (49), Calc Y (32), Element X (347) Element Y (225), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (66), Calc Y (32), Element X (466) Element Y (225), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (93), Calc Y (32), Element X (655) Element Y (226), Offset X (0), Offset Y (0)

 

// Row of data split across two lines, notice calculated Y values are different.

exporting Text: [XXX] Calc X (3), Calc Y (26), Element X (22) Element Y (182), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (11), Calc Y (25), Element X (84) Element Y (181), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (27), Calc Y (26), Element X (191) Element Y (182), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (41), Calc Y (26), Element X (295) Element Y (182), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (82), Calc Y (26), Element X (583) Element Y (182), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (49), Calc Y (25), Element X (347) Element Y (181), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (66), Calc Y (25), Element X (466) Element Y (181), Offset X (0), Offset Y (0)

exporting Text: [XXX] Calc X (93), Calc Y (26), Element X (655) Element Y (182), Offset X (0), Offset Y (0)

 

Thanks again and I'll keep digging.

[lucianc]

hgiv
Wrote:

Long story short, I'm having issues with the CSV export where what is displayed as a single row in a PDF is being rendered across two lines when exporting to CSV. I have verified the report design is using the same Y coordinates (i.e. top property) for the fields that are being split across multiple lines.

Do you have a sample report to illustrate this?

Regards,

Lucian

[hgiv]

Lucian,

 

Thanks for the question. As a result I took a closer look at all my reports and found one where CSV export was working properly and quite a few that were not.

 

I was able to resolve the CSV export issues by making sure that the report data field's left and length props matched the column heading's left and length props. Also, making sure the top props were the same for all the report data fields helped as well.

 

Another tidbit that made the CSV reports much cleaner was removing pagination (repeating page header, column header, column footer, page footer, etc). In case this may be useful to someone else out there, I did this by:

 

1. Defining system property: net.sf.jasperreports.properties=<path>/jasperreports.properties

 

2. Added the following to <path>/jasperreports.properties

 

# Exclude the following bands for CSV output. Removing columnHeader fixed issues

# were data was mis-aligned on the first page.

net.sf.jasperreports.export.csv.exclude.origin.band.1=columnHeader

net.sf.jasperreports.export.csv.exclude.origin.band.2=pageFooter

net.sf.jasperreports.export.csv.exclude.origin.band.3=columnFooter

net.sf.jasperreports.export.csv.exclude.origin.band.4=pageSummary

net.sf.jasperreports.export.csv.exclude.origin.band.5=summary

 

# Print title on first page only.

net.sf.jasperreports.export.csv.exclude.origin.keep.first.band.1=title

 

Thanks Svenn and Lucian for prodding me along. :)

 

Harry

[systemsam]

Has anyone found a way to find the query - our report designs don't allow for the alignment needed to get CSV to work and I don't want to have to create two reports for each report. The log4j can grab the query so you think that somehow you could grab this a execution time. Has anyone build a funtion to export the raw data returned into a CSV without all the grouping/sorting applied?

 

Sam