Passing Parameters to Jasper Reports Question

Classic Servoy
1

Hi,
I would like to send just the WHERE sql details to a jasper report through the plugin, I have tried a couple of different ways with out success. See below

This is the Jasper Report Code.

     SELECT
     invoices.`invoicesid` AS invoices_invoicesid,
     invoices.`cust_name` AS invoices_cust_name,
     invoices.`invoice_date` AS invoices_invoice_date,
     invoices.`invoice_number` AS invoices_invoice_number,
     invoices.`description` AS invoices_description,
     invoices.`cust_bill_address` AS invoices_cust_bill_address,
     invoices.`cust_bill_address2` AS invoices_cust_bill_address2,
     invoices.`cust_contact` AS invoices_cust_contact,
     invoices.`cust_phone` AS invoices_cust_phone,
     invoices.`cust_fax` AS invoices_cust_fax,
     invoices.`cust_city` AS invoices_cust_city,
     invoices.`cust_postcode` AS invoices_cust_postcode,
     invoices.`cust_country` AS invoices_cust_country,
     invoices.`cust_del_address` AS invoices_cust_del_address,
     invoices.`cust_del_address2` AS invoices_cust_del_address2,
     invoices.`cust_del_city` AS invoices_cust_del_city,
     invoices.`cust_del_postcode` AS invoices_cust_del_postcode,
     invoices.`cust_del_country` AS invoices_cust_del_country,
     invoices.`invoice_sales_person` AS invoices_invoice_sales_person,
     invoices.`cust_state` AS invoices_cust_state,
     invoices.`cust_del_state` AS invoices_cust_del_state,
     invoices.`cust_del_name` AS invoices_cust_del_name,
     invoices.`cust_id` AS invoices_cust_id,
     invoices.`invoice_total` AS invoices_invoice_total,
     invoices.`invoice_gst` AS invoices_invoice_gst,
     invoices.`invoice_freight` AS invoices_invoice_freight,
     invoices.`invoice_total_inc` AS invoices_invoice_total_inc,
     invoices.`gst_amount` AS invoices_gst_amount,
     invoices.`invoice_sales_rep_state` AS invoices_invoice_sales_rep_state,
     invoices.`cust_order_number` AS invoices_cust_order_number,
     invoices.`invoice_terms` AS invoices_invoice_terms,
     invoices.`austube_jon` AS invoices_austube_jon,
     invoices.`invoice_comments` AS invoices_invoice_comments,
     invoices.`salesrepid` AS invoices_salesrepid,
     invoices.`invoice_sales_rep_int` AS invoices_invoice_sales_rep_int,
     invoices.`invoice_sales_rep_ln` AS invoices_invoice_sales_rep_ln,
     invoices.`invoice_column_names` AS invoices_invoice_column_names,
     invoices.`invoice_report_total` AS invoices_invoice_report_total,
     invoices.`invoice_type` AS invoices_invoice_type,
     invoices.`status_month` AS invoices_status_month,
     invoices.`invoice_lock` AS invoices_invoice_lock,
     invoices.`blank` AS invoices_blank,
     invoices.`protection` AS invoices_protection,
     invoices.`logged_user` AS invoices_logged_user,
     invoices.`logged_user_group` AS invoices_logged_user_group,
     invoices.`protection_colour` AS invoices_protection_colour,
     invoices.`test` AS invoices_test,
     invoices.`gst_amount2` AS invoices_gst_amount2,
     invoices.`achived` AS invoices_achived,
     invoices.`quiotesid` AS invoices_quiotesid,
     invoices.`del_address_calc` AS invoices_del_address_calc,
     invoices.`bill_address_calc` AS invoices_bill_address_calc,
     invoices.`sales_rep_calc` AS invoices_sales_rep_calc,
     invoices.`system_company_name` AS invoices_system_company_name,
     invoices.`system_company_id` AS invoices_system_company_id,
     invoices.`calc_archive_colour` AS invoices_calc_archive_colour,
     invoices.`full_inv_number` AS invoices_full_inv_number,
     invoices.`date_stamp` AS invoices_date_stamp,
     invoices.`modified_date_stamp` AS invoices_modified_date_stamp,
     invoices.`project_name` AS invoices_project_name,
     invoices.`ajustment` AS invoices_ajustment,
     invoices.`companyid` AS invoices_companyid,
     invoices.`modified_by` AS invoices_modified_by,
     invoices.`modified_date` AS invoices_modified_date,
     invoices.`created_by` AS invoices_created_by,
     invoices.`creation_date` AS invoices_creation_date,
     invoices.`invoice_destination` AS invoices_invoice_destination,
     SUM(invoices.`factory_cost`) AS invoices_factory_cost,
     SUM(invoices.`report_inv_total`) AS invoices_report_inv_total
FROM
     `invoices` invoices

WHERE $P{whereParam}

GROUP BY austube_jon

ORDER BY invoices_sales_rep_calc ASC

This is in Servoy

var vWhereParam = " invoice_date BETWEEN " + vStartDate + " AND " + vEndDate 
var $parameters = new java.util.HashMap(); //create parameters
$parameters.put("whereParam",vWhereParam );
plugins.jasperPluginRMI.jasperReport(vServerName,vReportName,null,'view',$parameters);
2

as far as I can see, it looks oke,
Did you create the parameter: $P{whereParam} also in iReport?? (under Parameter section)

you could also try to past this: invoice_date BETWEEN " + vStartDate + " AND " + vEndDate
(with the real values for vStartDate and vEndDate in the jasper SQL and see if that works??)

(how does the vStartDate format look like?)

3

Hi Harjo,
Thanks for the reply, I have sorted the problem please see code below. There were a couple of problems.

  1. I needed to include single quotes around the date variables. and the jasper param needed a !after the P eg. $P!{whereParam}, I am not sure what difference it makes but it now works.

var vReportName = globals.modRptFormName
var vStartDate = utils.dateFormat(globals.modRptStartDate,‘yyyy-MM-dd HH:mm:ss’) //date format needs to be ‘yyyy-MM-dd HH:mm:ss’
var vEndDate = utils.dateFormat(globals.modRptEndDate,‘yyyy-MM-dd HH:mm:ss’)
var vFormStartDate = utils.dateFormat(globals.modRptStartDate,‘dd-MM-yyyy’) //send formated date fields for printing
var vFormEndDate = utils.dateFormat(globals.modRptEndDate,‘dd-MM-yyyy’)

if(globals.modRptSearchRep){
var vWhereParam = vDateColumnNm + " BETWEEN ‘" + vStartDate + "’ AND ‘" + vEndDate + "’ AND " + vSearchColumnNm + " = ‘" + globals.modRptSearchRep + "’"
}

Harjo:
as far as I can see, it looks oke,
Did you create the parameter: $P{whereParam} also in iReport?? (under Parameter section)

you could also try to past this: invoice_date BETWEEN " + vStartDate + " AND " + vEndDate
(with the real values for vStartDate and vEndDate in the jasper SQL and see if that works??)

(how does the vStartDate format look like?)

4

To actually use the parameter $P{whereParam} in a jasper report you have to modify it slightly: $P!{whereParam}
The exclamation point basically will be interpreted to mean get the string properly.