Problem binding parameters in ZuckerReports

[jlabuelo]

Good Morning all

Lets see if you can give me a hand here. I am trying to design a new IReport for SugarCRM 5.2. I have configured Sugar 5.2 as it is described with the version 3.1.4 of iReport.

What I am trying to do is getting a pdf list of all the cases from a date1 to a date2 belonging to an account "Account_Name"

I have cereated t new JasperReport template and wanted to create the SQL that would run it. To do so, I have included three parameters in the report

"Account_Name", "DateStart" and "DateEnd".

The datasource is the sugarcrm database.

So my SQL for the report is

SELECT
     accounts.`name` AS Account,
     
     cases_cstm.`totalhours_c` AS cases_cstm_totalhours_c,
     cases_cstm.`totalmins_c` AS cases_cstm_totalmins_c,
     cases_cstm.`fechacerrado_c` AS cases_cstm_fechacerrado_c,
     cases_cstm.`reason_c` AS cases_cstm_reason_c,
     cases.`id` AS cases_id
FROM
     `accounts` accounts INNER JOIN `cases` cases ON accounts.`id` = cases.`account_id`
     INNER JOIN `cases_cstm` cases_cstm ON cases.`id` = cases_cstm.`id_c`
WHERE
     accounts.`name` = "$P{Account_Name}"
 AND  (cases_cstm.`fechacerrado_c`between "$P{DateStart}"
    "$P{DateEnd}")
 AND cases.`status` = "Closed"
 AND cases.`type` = "Action"
ORDER BY
     cases_cstm.`fechacerrado_c` ASC

When I try to run the iReport, I introduce manually and under request these three values for each parameter, (not yet in SugarCRM), but I get an error message saying

Error.filling.print....Error.preparing.statement.for.executing.the.report.query.:.  SELECT .....accounts.`name`.AS.Account, ..... .....cases_cstm.`totalhours_c`.AS.cases_cstm_totalhours_c, .....cases_cstm.`totalmins_c`.AS.cases_cstm_totalmins_c, .....cases_cstm.`fechacerrado_c`.AS.cases_cstm_fechacerrado_c, .....cases_cstm.`reason_c`.AS.cases_cstm_reason_c, .....cases.`id`.AS.cases_id FROM .....`accounts`.accounts.INNER.JOIN.`cases`.cases.ON.accounts.`id`.=.cases.`account_id` .....INNER.JOIN.`cases_cstm`.cases_cstm.ON.cases.`id`.=.cases_cstm.`id_c` WHERE .....accounts.`name`.=."?" .AND..(cases_cstm.`fechacerrado_c`between."?" ...."?") .AND.cases.`status`.=."Closed" .AND.cases.`type`.=."Action" ORDER.BY .....cases_cstm.`fechacerrado_c`.ASC

as you can see the SQL did not get the values of the parameters I have introduced. If I introduce the values in the SQL directly the report works fine.

I have imported this report to SugarCRM and Binded the Parameters I have created in ZuckerReports, however I get the same error.

Any ideas of what I am doing wrong??

Thanks a lot in advance

[jlabuelo]

Hi there

For all that are having the same problem I had, here is the solution I found. The problem was in the way I called the parameter in the iReport-SQL

instead of $P{Account_Name} we should use $P!{Account_Name}.

Found the "$P!{}" solution in a forum as in the documentation I had of iReport the right way to call the parameter is still documented as "$P{}"

Hope it helps so you dont lose a couple of days like me searching for the root cause!!

Cheers