Thursday, March 8, 2012

JasperReports with PHP

Today i am going to write this article on the use of JasperReports in PHP as a reporting tool. Here i have used IReport-4.5.0 IDE for designing the JasperReport Template (known as jrxml file).In addition, you require a PHP library(third party) that generates PDF reports from the given jrxml document. this library can be downloaded from the following Git Repository
https://github.com/chathurangat/PhpJasperLibrary

or else you can downloaded through following link


In order to design the template, please follow the steps given below.

1. load the IReport Designer tool
(executable file is available inside the bin directory)


2.after loading the IReport Tool,select new report template to start the report designing.
(File -> New)
then you will get a new window with a list of available report templates. select a template out of the available templates. in my case i have selected the Blank A4 template. please refer below.








then click the Launch Report Wizard to load the selected report template.



3. then give a Report Name and browse a Location where the report file (jrxml file) should be saved.  Please refer below.






4. then click on Next to proceed with next phase. please refer below.





 As you can see in the Screen-shot, you will be displayed a list of available data source connections that can be used in your report. if you wish to create a new data source connection, you are free to do so by clicking New button.


 5. if you click the New button to create new data source, you will be shown the below screen to select the the data source type. ( the data source type should be selected based on the application requirements. ) since this is PHP based web application, i have selected Database JDBC Connection as the data source type.



6. once you select the data source, click on next to proceed with next step. next will be the below screen.



here you have to provide the JDBC connection details.

 Name : just give any name for this connection for later reference  identification purpose


JDBC Driver:  select the most suitable JDBC driver from the given list based on your database. since i am using MySQL server database, i have selected  MySQL-JDBC Driver.


JDBC URL: make sure to edit the jdbc url based on your sever and database name. the default value of the jdbc url does not change, based on the value you provided for server and database. therefore please edit it manually.

Server Address : IP Address of the database sever. if the database is in your local machine, use localhost as IP.

Database : Database name that you are going to connect.


username: Username of the database server.

password : Password of the database server.


after filling all the configuration details, you can test the database conncetion by clicking the Test button provided. sometimes it may ask you to re-enter the database password. if the connection is successful, you will get a successful message as follows. then you can Save your new data source connection.





 7. then you can use your newly created data source for designing the query for your report. select the newly created data source and press Design Query button. then you will get the below window for designing the Query.


 
you will see a list of tables available in the connected database. you can Drag and Drop these tables to the provided area for designing the Query. then customize the columns displayed in the report by using the check boxes provided for each column.


8. once the Query is designed, press OK to finish it. the click Next to proceed with next phase. then next step is to select the required fields for your report out of the available all resulted columns of the Query you designed. add and remove the required database columns for your report with the provided button. please refer the below screen shot.




 9. after completing the above operation, you can press Next button for continue with next phase. next phase is for applying the Group By clause for the report view. then phase is optional and you can skip this step by clicking just Next button. then the initial process of the report design is finished and you will be notified with the below screen. jut click on finish button.



10. then it will load and display the created .jrxml file. you are required to remember that this is the file that contains your report template. you can design the report as you wish by providing preferable title, footer and other required fileds.


11. the designing utilities are available in the Report Inspector window. please refer below screen shot.


as you can see that the selected database column fields are available under the Fields. you can drag and drop the database fields in into the Detail1 Section of your Report.Once you drag a database field, its column header section will be automatically visible under Column Header section. In addition, the data field is visible under the Detail 1 Section. you can edit the column name as you wish.

12. Once the Design is done, you can preview the design using the Preview button. 


13. The Report design elements are available in the palette window. you can get the palette window Window -> Palette


14. Once all the design is done, the it is time to integrate with your PHP application. make sure to download and import the PhpJasperLibrary in your PHP script. (copy both .jasper and .jrxml files into a same directory and give the reference in the PHP script)


report_view.php

<?php

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


//database connection details

$server="192.168.0.11";
$db="lcs_ims";
$user="web";
$pass="abc123@#";
$version="0.8b";
$pgport=5432;
$pchartfolder="./class/pchart2";


//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("report/chathuReport.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


?>



access the report_view.php file in your LAMP/WAMP server. yo will get the report in PDF format.



Hope this will helpful for you!

Thanks and Regards,
Chathuranga Tennakoon
chathuranga.t@gmail.com



65 comments:

  1. Awesome tut. Thanks Chathuranga so much

    ReplyDelete
    Replies
    1. when i click for report then show this message(No data found).what can i do?.please help me...

      Delete
  2. Is IReport-4.5.0 IDE is somewhat different from netbeans? Or is it a sort of plugin for netbeans. Please explain a more on how to install IReport and from to get it installed

    ReplyDelete
  3. could you show example that using parameter please?
    thank you

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. @salman farisi, do already have answer in your question ? if you have.. can u share with me.. thanks in advance.

      Delete
  4. Very Good
    Tthis is very helpfull

    I have used it and it works fine with php+Oracle (WAMP and ODBC)

    the only problem I am facing is that the php pdf output showing first page empty and it shows the data on 2nd page
    How can I remove this space from pdf output?

    ReplyDelete
    Replies
    1. please i have the same problem ,can you help me ??????

      Delete
    2. This comment has been removed by the author.

      Delete
    3. How did you get the connection to BD oracle?
      Can you help me?

      Delete
  5. Hi, I have a question, Do I need to install JVM in order to deploy mi jasper report or is not mandatory???, Thanks!!

    ReplyDelete
  6. can you explain that how to connect with sqlite database instead of Mysql?,Thanks for your help...!!!

    ReplyDelete
  7. could you show example that using parameter please?

    ReplyDelete
  8. I found a lot of troubles by using it with CodeIgniter. Using the exactly same code, one using Codeigniter and the other one using pure PHP. The one with Codeigniter fired a lot of PHP errors and the other worked perfectly. Do you know something about this ? Thanks for your reply.

    ReplyDelete
    Replies
    1. I use this thread http://ellislab.com/forums/viewthread/144305/
      but I get error on
      $xml = simplexml_load_file('report/chathuReport');

      Message: simplexml_load_file() [function.simplexml-load-file]: I/O warning : failed to load external entity "report/chathuReport"

      if you can fix that, notify me please...

      Delete
    2. can you check and verify that you have a directory called report and inside the report directory a file called chathuReport.jrxml?

      if there are such, it should work. by the way you have forgot to mention .jrxml extension and can you re-organized your code with follows.
      simplexml_load_file("report/chathuReport.jrxml");

      then it should work

      Delete
  9. why when I open the page, it always downloaded pdf though I use the parameters "I" ?

    ReplyDelete
  10. $pchartfolder="./class/pchart2"; what is the use of this instruction?? when create a graph using ireport it did not display in the pdf page. please suggest

    ReplyDelete
  11. i tried that but it outputs only log on my page what do i need to do next so as whole data will be retrived

    ReplyDelete
  12. hai master,
    i have got some error
    Warning: Invalid argument supplied for foreach() in E:\xampp\htdocs\report_test\PhpJasperLibrary\PHPJasperXML.inc.php on line 3155
    TCPDF ERROR: Some data has already been output, can't send PDF file.

    thanks for your help.. :)

    ReplyDelete
  13. K.M.Shivakumar
    Title is coming but data not coming. Pl help

    ReplyDelete
  14. My code is as follows:
    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


    ?>

    ReplyDelete
  15. $pchartfolder="./class/pchart2"; what is the use of this instruction?? when create a graph using ireport it did not display in the pdf page. please suggest

    ReplyDelete
  16. wow it's work fine...... !!! but it's not work for sub-reports... how to compile sub reports by using "PhpJasperLibrary"

    ReplyDelete
    Replies
    1. Hello , I would need to see the report and subreport together what is possible? thanks

      Delete
    2. Plz help me to run this sub_report on a Php application by using "PhpJasperLibrary"
      (Follow this url,it's having DB backup and the sample report :- http://uploaded.net/file/fsi0h79p )
      Thanks.....

      Delete
  17. Thanks,it's working fine but shapes are coming little different from jasper ireport desginer. I designed in iReport 5.6.0 but when I generate on PHP with your instruction, rectangle boxes appeared in very small difference. But it affects view of form. Do you have any idea about that? also my table design did not appear. it is maybe version mismatch...

    ReplyDelete
  18. Here is photo with comparison:
    http://s12.postimg.org/6hvk0mabx/Form_Differences_designer_PHP.jpg

    ReplyDelete
  19. Dear Sir please kindly solve my this issue here. I can't figure out what's the problem as my report is always shown empty

    http://stackoverflow.com/questions/27884749/report-is-always-empty-created-with-phpjasperlibrary

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. @Abdul, try to remove the comment(//) in PHPJasperXML.inc.php file.

      Delete
  20. This comment has been removed by the author.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. I will ask sir, how possible if i have a parameter ? how can i use the parameter ? any answer please . Thanks in advanced.

    ReplyDelete
  23. Hi this sample works fine with MySQL, then I tried it with PostgreSQL by replacing
    ($db_host,$db_user,$db_pass,$db_or_dsn_name,$cndriver="mysql") by ($db_host, $db_user, $db_pass, $db_or_dsn_name, $cndriver) and also passed the driver parameter as "psql". I get a blank page only, no report loaded. Am I missing something?

    ReplyDelete
  24. Nice artikel, thank very much, it's work for me

    ReplyDelete
  25. Thanks for the article

    I used this this is working fine. When calling from PHP application in the generated pdf file I have two issues
    1. The first page of the pdf file displayed as empty
    2. No column headers appears

    Kindly suggest

    ReplyDelete
  26. Is there away of giving the user the possibility of searching,filtering,printing,send via email without directly exporting to pdf?

    ReplyDelete
  27. TCPDF ERROR: Some data has already been output, can't send PDF file.

    ReplyDelete
    Replies
    1. i've fixed it adding this line:
      ob_end_clean();

      on file PHPJasperXML.inc.php
      at line 999 before

      return $this->pdf->Output($filename,$out_method); //send out the complete page

      So it's work but the report need always data take from the db with a query. Doesn't work only with parameters.

      Delete
  28. and what about phpjasperxmlsubreport.
    how to use it to display subreport

    ReplyDelete
  29. How to display report in a html or new window in browse, not export pdf file ? Thanks so much !

    ReplyDelete
  30. jasper report not working with my live server? can u help me to reslove this problem

    ReplyDelete
  31. can we export the report in other formats?? like MS word and Excel

    ReplyDelete
  32. Hi Getting error -TCPDF ERROR: Some data has already been output, can't send PDF file

    please help

    also in point 14 it is said that copy .jasper into the same folder -- what is .jasper here

    ReplyDelete
  33. Hii..can you help me please.jasper not showing when im showing jasper report in my server. can u help me resolve this probleme??

    ReplyDelete
  34. Hi, first of all I thank you for this tutorial. Good job! IReport is showing the right result I want to get. But if I try to output this report (in Chrome) I get an error: Page on localhost doesn't work. (HTTP 500) I use Xampp. Could you point me in the right direction?

    ReplyDelete
  35. Hi, to use a parameter you should uncomment line 27 and change "parameter1" and put the nameof your parameter instead "nameOfourParametr" =>'yourParameter'

    ReplyDelete
  36. hi,i report cannot show multiple subreport,I create tree subreport why did show once?

    ReplyDelete
  37. Another simple alternative is to use the SimpleReport.

    You find it at: github.com/andersonsouzars/simple-report

    ReplyDelete
  38. Hi, how to setup in Ubuntu 16.04?

    ReplyDelete
  39. i have crosstab report two coloumn and two row. in ireport show good result. but coding in php browser result not show. only show the summation result. Advance Thank You.

    ReplyDelete
  40. I have this problem with php 7.0.14.. someone help me?

    Deprecated: Methods with the same name as their class will not be constructors in a future version of PHP; PHPJasperXML has a deprecated constructor in PHPJasperXML.inc.php on line 4

    Please help.

    ReplyDelete
  41. Very helpful article. I tried it and everything is fine with simple reports. But when i USE SUBREPORT OR TABLES, NOTHING IS SHOWING. kINDLY HELP

    ReplyDelete
  42. Very good articles .But can you help me to connect to sql server please.

    ReplyDelete
  43. how to make connection to oracle bd?
    Can you help me?

    ReplyDelete

  44. Hello, please, how can I show sub-reports in the same report?

    ReplyDelete
  45. hello, please i am using this on a cloud server and the output is blank when i run the php. any suggestion

    ReplyDelete
  46. but Sub-reports, lists, table datasets not showing

    ReplyDelete