Hello. I am facing a challenge in passing a dynamic php variable to an SQL statement i wrote in Jaspersoft iReport. I downloaded the PHP-Jasperserver-integration library and i am managing to open a jrxml file i created in Jaspersoft and then using the libraries to open it in PDF format, here is the php code:

report.php

<?php

//Import the PhpJasperLibrary
include_once('PhpJasperLibrary/tcpdf/tcpdf.php');
include_once("PhpJasperLibrary/PHPJasperXML.inc.php");
include "includes/functions.php";

//database connection details




$server="localhost";
$db="underwriting";
$user="root";
$pass="";
$pchartfolder="./class/pchart2";


 if ( $_REQUEST_METHOD = 'GET' ) {
   if ( (isset( $_GET['url'] ) && !empty( $_GET['url'] ))) {
        $file = escape_value( $_GET['url'] );


    }
} 

//display errors should be off in the php.ini file
ini_set('display_errors', 0);

//setting the path to the created jrxml file
$xml =  simplexml_load_file('reports/billing_schedule.jrxml');

$PHPJasperXML = new PHPJasperXML();
//$PHPJasperXML->debugsql=true;
//$PHPJasperXML->arrayParameter=array("parameter1"=>1);
$PHPJasperXML->xml_dismantle($xml);

$PHPJasperXML->transferDBtoArray($server,$user,$pass,$db);
$PHPJasperXML->outpage("I");    //page output method I:standard output  D:Download file


?>

And the SQL query i wrote in Jaspersoft:

SELECT
policydetails.policyStatus AS policyStatus,
policydetails.payingAuthorityName AS payingAuthority,
policydetails.policyNumber AS policyNumber,
policydetails.firstName AS firstName,
policydetails.lastName AS lastName,
policydetails.premium AS premium,
policydetails.coverDate AS coverDate,
policydetails.ECNumber AS ECNumber,
policydetails.IDNumber AS IDNumber
FROM
policydetails
WHERE
(policydetails.payingAuthorityName = 'Unifreight') AND
(policydetails.policyStatus = 'Active' OR
policydetails.policyStatus = 'Accepted' OR
policydetails.policyStatus = 'Lapsed' OR
policydetails.policyStatus = 'Underwriting')

ORDER BY
policyStatus ASC,
policyNumber ASC
;

Now the problem i am having is how to pass a php variable from report.php taken from a dropdown combo box in another script(which a value is then passed to report.php using url parameter) and then replace 'Unifreight' in the above SQL WHERE clause with the dynamic php variable so that the records listed will be dependant on the value picked from the combo box.
I need to know how to re-write report.php to accomodate the php variable to be passed to the SQL query in Jasper. Any help would be greatly appreciated!!

Member Avatar for iamthwee

Without downloading that library we can only guide you.

Best would be to first write a bit of sql+php that is independent of that library. That way you know you change the records given the combo box selection chosen. After you've done that I would try to integrate it with THAT library.

If you get stuck wack a couple of echo statements in there.

All i need to know is how to define a variable in the php script which i then can use in the Jasper Reports.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.