iReport -- "column prompting" / etc ?

[runner1549]

Do you have an easier way to edit SQL
code in iReport and get the benefits
of TOAD's attached column prompting
without having to do the below steps ?

1. create report
2. six months later get a
   request to change the report
3. open JRXML in iReport
4. open "Report Query" Window
5. copy below SQL into TOAD
================================================
select fmr.FMCODE COMPONENT, fmrc.FMPERCENTAGE * 100 PERC, nlln.NLTEXT, fmr.FMCAS,
       fmppc.FMMINIMUM ALLERGEN, fmP.fmcode PARENT
from fmresource fmr
inner join gml_cas gcas on fmr.FMCAS = gcas.CAS
inner join nllabelname nlln on nlln.FMENTITYOBJECTID = fmr.FMENTITYOBJECTID
inner join fmpropertyassign fmpa on fmpa.FMENTITYOBJECTID = fmr.FMENTITYOBJECTID
inner join fmpropasschar fmppc on fmppc.FMPROPERTYASSIGNID = fmpa.FMPROPERTYASSIGNID
inner join FMRESOURCECMP fmrc on fmrc.FMRESCOMPONENTID=fmr.FMENTITYOBJECTID
inner join fmresource fmP on fmrc.fmresourceid = fmP.fmentityobjectid

where fmpa.FMPROPERTYID = 391 and fmP.fmcode LIKE $P{FMCODE}
--where fmpa.FMPROPERTYID = 391 and fmP.fmcode LIKE '%A1539%'

and fmr.FMCAS = $P{FMCAS}
--and fmr.FMCAS = 150
================================================
 6. comment out $P... lines, uncomment value lines
 7. tweak SQL in TOAD
 8. run to make sure SQL works
 9. uncomment $P... lines, comment out value lines
10. copy new SQL back into iReport
 

[svenn]

No but it would be great if someone knew a way to do this.

[mdahlman]

Excellent question! I do approximately the same thing as you. But it's slightly easier using SQuirreL instead of Toad. My steps are like this:

 

1. create report
2. six months later get a request to change the report
3. open JRXML in iReport
4. open "Report Query" Window
[There is no possible way to avoid steps 1 to 4.]

[it would be nice if we could improve steps 5 - 10.]
5. copy SQL into SQuirreL
6. The SQuirreL plugin SQLReplace lets you define substitution values for the $P{param} values. So most of my reports work immediately. Occasionally I need to an another replacement value.
7. tweak SQL in TOAD
8. run to make sure SQL works
9. [no need to un-tweak the SQL]
10. copy new SQL back into iReport

 

It's a bit shorter, but more importantly it removes the most likely source of errors. You don't need to remove and re-add the $P values.

 

Regards,
Matt