Jaspersoft iReport XML datasource

In this post I will show you how to use XML as data source in Jasper Reports.First we will create the XML data from a table in MySQL database. In this example I will use Yii application to generate the XML and will integrate it with Jaspre Report. If you don’t know how to create the basic Yii application you can read the post Yii ajax form submission. Please also go through the post Yii report using Jasper Reports to understand how to integrate Jasper Report with Yii framework.

In this xml data source jaspersoft iReport sample I will use the database created in this example. First create the view file for displaying the XML data. Create the file at ‘myapp\protected\views\site\xml_data.php ‘ and enter the below code.

<?php
header ("Content-Type:text/xml");
echo $xml_data;
?>

Next we will create the action for the view in Site controller located at ‘myapp\protected\controllers\SiteController.php’

public function actionXmlData()
{


$command=Yii::app()->db->createCommand("SELECT * FROM subject");
$result=$command->queryAll();
//Create SimpleXMLElement object
$xml = new SimpleXMLElement("<?xml version=\"1.0\" encoding=\"UTF-8\"?><root></root>");

//Add each column value a node of the XML object
foreach($result as $row) {
$datarow = $xml->addChild('row');
foreach($row as $key=>$value) {
$datarow->addChild($key,$value);
}
}

$this->renderPartial('xml_data',array('xml_data'=>$xml->asXML()));

}

We have written the query to select all the subjects from the subject table. Next we have converted it into XML and rendered the view file using renderPartial. For displaying the view file we have used renderPartial so that no layout should be associated with the view. Next create the view file for displaying the report at ‘myapp\protected\views\site\subject_report.php’ and paste the below code.

$this->widget('ext.Yiijasperserver.Yiijasperserver',array(
'path'=>'/reports/myapp/subject',
'format'=>'pdf',
'out'=>'I',
'file'=>'Subjects',

)
);

Next create the action to display the view at ‘myapp\protected\controllers\SiteController.php’

public function actionXmlDataReport()
{
$this->render('subject_report');
}

Now open ‘myapp\protected\views\layouts\main.php’ and add the below code to menu section.

array('label'=>'Subject Report', 'url'=>array('/site/xmldatareport')),

By default JasperReports Server does not have the xpath2 query executer, for this we have to copy a jar file that comes with iReport. Copy jasperreports-extensions-3.5.3.jar from /ireport/modules/ext to JasperReports Server/jasperserver/WEB-INF/lib/jasperreports-extensions-3.5.3.jar

Next create the Jasper Report using iReport Designer. Select New from File menu. In the dialog that appears enter report name as subject, then click next and finish.

jaspersoft

jaspersoft

Now click on the Report Datasources button and click new. In the window that opens enter name as XML Data Adapter amd XML URL as http://localhost/myapp/index.php?r=site/xmldata. Now click on the Test button, on successful click save.

jaspersoft

jaspersoft

Now click on the Report Query button and in window that opens, select Report Query. Select Query language as xPath2. Right click on the row node and select ‘set record node (Generate xpath) Now right click on the id and name field and select ‘Add node as field’.

jaspersoft

jaspersoft

Next drag the ‘name’ field into Detail tab from the Report Inspector tab in the left side. Create the heading ‘Subject’ by dragging a static text field from the paleete in the right side. Now style as required and click the preview tab to run the report.

jaspersoft

jaspersoft

Now open query window and change query language to SQL and then right click on the parameter node in the Report Inspector tab and create three parameters XML_USERNAME,XML_PASSWORD,XML_URL with default values “”,”” and “http://localhost/myapp/index.php?r=site/xmldata”
respectively. Un check the Use as prompt option.

jaspersoft

Now create the Jasper Server connection. From the Repository Navigation tab, click the add new server button. Enter the following details ID : JasperReports Server,JasperReports Server URL : http://localhost:8080/jasperserver/services/repository, Username : jasperadmin, Password : jasperadmin

jaspersoft

Now change the Query language back to xpath2. Next from under the Reports node right click the myapp folder which we created in the previous example. Select Add->JasperServer Report. In the window that appears enter Id : subject, Name : subject and click next. Click on the ‘Get source from current opened report’ and click next. Now select ‘Don’t use any datasource’ option and click finish.

jaspersoft

jaspersoft

jaspersoft

jaspersoft

Now click on the Report link on the menu in our Yii apllication at http://localhost/myapp and see the PDF report.

jaspersoft