0

In Jasper Reports, how do I use a SQL result in the title block and use it for subreporting?

I have previously successfully used a SQL result in the title block when there is only one result, and am not depending on it to spawn multiple subreport sections. In this instance I would like to do both use the SQL result and have multiple subreports.

My current simplified query (Oracle SQL)

SELECT DISTINCT
  LOC."NAME" AS LOCATION,
  coalesce($P{input_date},($P!{default_date})) as ACTUAL_TIME
FROM
  "CF"."LOCATION" LOC
WHERE
  (LOWER(LOC."NAME") LIKE '%'||LOWER($P{input_location})||'%')

There are three locations, and each generates two subreports grouped by location.

default_date is defined as the result of an oracle function

"SELECT START_TIME FROM CF.LAST_OPERATIONAL_DAY"

Operational days do not run 24 hours, from midnight to midnight.

1) I have been using this default_date technique with most of the reports, hopefully easing maintainability. However, evaluating the actual-time 3 times could cause the reports to run for different days if run at the wrong moment.

2) If I put $F{ACTUAL_TIME} in my Title, it shows as null. (In our simpler reports, the primary query returns one value, and this field is usable in the Title.)

3) My searching has lead me to believe that subqueries are not permitted without a list, table, or subreport.

There is the possibility of reimplementing the Oracle function in Java to set a new parameter. If input_date is null, use the new variable, but I fear the maintenance headache as we proceed.

There is the possibility of creating another layer of sub-subreport. That would make the few most complicated reports more complicated.

Is there a simpler option? Can queries be done at the parameter level? In the header? Is there some setting I'm missing?

---We are using Jasper Server 6.1.1 and Jaspersoft Studio 6.1.1 and have the flexibility to upgrade if necessary. The database is Oracle 12c.

C Frayda
  • 9
  • 1
  • Can queries be done at the parameter level?, if you mean executed No, If you mean have sql as parameter Yes. Remeber you can have default variables for parameters and for sub queries you need to use subreport or subdataset. http://stackoverflow.com/questions/7482412/multiple-queries-in-a-single-jasper-document – Petter Friberg Mar 30 '16 at 07:31
  • I can't understand exactly what you are trying to achieve, put I think the solution is in java, pre-processing data and then pass parameters to jasper report to simplify sql – Petter Friberg Mar 30 '16 at 07:34
  • Thanks Petter! That's what I feared. I would prefer to do the calculation in SQL to simplify changes across reports in the defaults and operational day range. I don't see a way either. – C Frayda Mar 30 '16 at 14:50

0 Answers0