JSP Crystal Reports

This is my working sample of running a crystal report through a jsp. It turns out to be very pretty and gives you many options. As I suggested in a comment. I would really advise on downloading Eclipse IDE + the Business Objects Plugin. This will give you most, if not all the utilities you need to put you in the right direction.

LET ME KNOW IF THIS HELPED YOU IN THE COMMENTS! Enjoy!

If you have any questions, please post in the comments.

Thanks,
Daniel

Author: Dan Folkes
Description: JSP Crystal Reports
Language: java
Pasted on: 08:44:23 Friday 18th January 2008

JSP Crystal Reports helper sample image

This is Licensed under GPLv3. Give Back.

    Download Source

  1. <%@ page language=”java”
  2. import=”logic.*,com.businessobjects.samples.JRCHelperSample,
  3. com.crystaldecisions.report.web.viewer.CrystalReportViewer,

  4. com.crystaldecisions.reports.sdk.ReportClientDocument,
  5. com.crystaldecisions.sdk.occa.report.application.OpenReportOptions,
  6. com.crystaldecisions.sdk.occa.report.lib.ReportSDKExceptionBase,
  7. com.crystaldecisions.sdk.occa.report.reportsource.IReportSource”
  8. pageEncoding=”ISO-8859-1?
  9. contentType=”text/html; charset=ISO-8859-1?
  10. %>
  11.  
  12. <!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
  13. <html>
  14. <head>
  15. <title>My JSP ‘home_sql12.jsp’ starting page</title>
  16. </head>
  17.  
  18. <body>
  19. <%
  20. /*java.util.Enumeration paramNames = request.getParameterNames();
  21. while(paramNames.hasMoreElements()) {
  22. String parm = (String)paramNames.nextElement();
  23. out.println(parm + ” = ” + request.getParameter(parm) + “<BR/>”);
  24. }*/
  25.  
  26. QueryHelper qHelper = new QueryHelper();
  27. String reportName = “rpt_SR_sqlsrt.rpt”;
  28. String repSel = “S”;
  29. if (request.getParameter(”Report”)!=null) {
  30. repSel = request.getParameter(”Report”);
  31. reportName = “/rpt/rpt_”+repSel+”.rpt”;
  32. }
  33.  
  34. //checks for null
  35. //checks if the value is NA
  36. //checks if the Report Selected should be passing this parameter
  37. //adds the string to the end of the qHelper.selectionstring
  38.  
  39. if (request.getParameter(”comboboxDistrict”)!=null)
  40. if (!request.getParameter(”comboboxDistrict”).equals(”NA”))
  41. if ( (repSel.equals(”S”))||(repSel.equals(”PERD”))||(repSel.equals(”SPR”))||(repSel.equals(”FER”))||(repSel.equals(”SML”))||(repSel.equals(”IAR”))||(repSel.equals(”IANR”))||(repSel.equals(”TR1?)) )
  42. qHelper.add(1,”ACV_STATIONS.STA_DISTRICT”,request.getParameter(”comboboxDistrict”));
  43.  
  44. if (request.getParameter(”comboboxCity”)!=null)
  45. if (!request.getParameter(”comboboxCity”).equals(”NA”))
  46. if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
  47. qHelper.add(1,”ceds_FACILITIES.FAC_L_CITY”,request.getParameter(”comboboxCity”));
  48.  
  49. if (request.getParameter(”comboboxZip”)!=null)
  50. if (!request.getParameter(”comboboxZip”).equals(”NA”))
  51. if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
  52. qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP5?,request.getParameter(”comboboxZip”));
  53.  
  54. if (request.getParameter(”comboboxZip4?)!=null)
  55. if (!request.getParameter(”comboboxZip4?).equals(”NA”))
  56. if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
  57. qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
  58.  
  59. if (request.getParameter(”textExpDate”)!=null)
  60. if (!request.getParameter(”textExpDate”).equals(”NA”))
  61. if ( (repSel.equals(”PERD”))||(repSel.equals(”PERND”))||(repSel.equals(”FER”))||(repSel.equals(”IEL”))||(repSel.equals(”IEML”))||(repSel.equals(”IER”))||(repSel.equals(”TEL”))||(repSel.equals(”TEML”))||(repSel.equals(”TER”)) )
  62. {}//qHelper.add(1,”ACV_STATIONS.STA_EXP_DATE”,request.getParameter(”textExpDate”));
  63.  
  64. if (request.getParameter(”comboboxDate1?)!=null)
  65. if (!request.getParameter(”comboboxDate1?).equals(”NA”))
  66. if ( (repSel.equals(”IANR”))||(repSel.equals(”TR1?))||(repSel.equals(”TR2?)) )
  67. {}//qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
  68. if (request.getParameter(”comboboxDate2?)!=null)
  69. if (!request.getParameter(”comboboxZip4?).equals(”NA”))
  70. if ( (repSel.equals(”IANR”))||(repSel.equals(”TR1?))||(repSel.equals(”TR2?)) )
  71. {}//qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
  72. qHelper.removeAndOr();
  73.  
  74. //START THE CHECKBOXES
  75. QueryHelper checkboxSCQH = new QueryHelper(); //will need an and before if greater than 3
  76. if (request.getParameter(”checkboxSC1?)!=null)
  77. if (request.getParameter(”checkboxSC1?).equals(”on”))
  78. checkboxSCQH.add(1,”ACV_STATIONS.STA_CODE”,”Both”);
  79. if (request.getParameter(”checkboxSC2?)!=null)
  80. if (request.getParameter(”checkboxSC2?).equals(”on”))
  81. checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”EIS”);
  82. if (request.getParameter(”checkboxSC3?)!=null)
  83. if (request.getParameter(”checkboxSC3?).equals(”on”))
  84. checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”CRF”);
  85. if (request.getParameter(”checkboxSC4?)!=null)
  86. if (request.getParameter(”checkboxSC4?).equals(”on”))
  87. checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”Neither”);
  88. checkboxSCQH.removeAndOr();
  89.  
  90. // INSPECTION CHECK BOXES
  91.  
  92. QueryHelper checkboxQH = new QueryHelper(); //will need an and before if greater than 3
  93. if (request.getParameter(”checkboxIS1?)!=null)
  94. if (request.getParameter(”checkboxIS1?).equals(”on”))
  95. checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Active”);
  96. if (request.getParameter(”checkboxIS2?)!=null)
  97. if (request.getParameter(”checkboxIS2?).equals(”on”))
  98. checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Inactive”);
  99. if (request.getParameter(”checkboxIS3?)!=null)
  100. if (request.getParameter(”checkboxIS3?).equals(”on”))
  101. checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Revoked”);
  102. if (request.getParameter(”checkboxIS4?)!=null)
  103. if (request.getParameter(”checkboxIS4?).equals(”on”))
  104. checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Suspended”);
  105.  
  106. if (request.getParameter(”checkboxRS1?)!=null)
  107. if (request.getParameter(”checkboxRS1?).equals(”on”))
  108. checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Active”);
  109. if (request.getParameter(”checkboxRS2?)!=null)
  110. if (request.getParameter(”checkboxRS2?).equals(”on”))
  111. checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Inactive”);
  112. if (request.getParameter(”checkboxRS3?)!=null)
  113. if (request.getParameter(”checkboxRS3?).equals(”on”))
  114. checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Revoked”);
  115. if (request.getParameter(”checkboxRS4?)!=null)
  116. if (request.getParameter(”checkboxRS4?).equals(”on”))
  117. checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Suspended”);
  118. checkboxQH.removeAndOr();
  119. String output = “”;
  120. if (qHelper.toString().length() > 2){
  121. output += “( ” + qHelper +” ) “;
  122. if (checkboxSCQH.toString().length() > 2){
  123. output += ” AND ( “+checkboxSCQH+” )”;
  124. if (checkboxQH.toString().length() > 2)
  125. output += ” AND ( ” +checkboxQH+” )”;
  126. }
  127. }
  128.  
  129. try {
  130. // Set Report Values
  131. ReportClientDocument clientDoc;
  132. clientDoc = new ReportClientDocument();
  133.  
  134. clientDoc.open(reportName, OpenReportOptions._openAsReadOnly);
  135. clientDoc.setRecordSelectionFormula(output);
  136.  
  137. //DB Info
  138. String connectStr = “jdbc:oracle:thin:@finland:1521:DBNAME”;
  139. String driverName = “oracle.jdbc.OracleDriver”;
  140. String userName = “TABNAME”;
  141. String password = “DBPASS”;
  142. String JNDIName = “”;
  143.  
  144. JRCHelperSample.changeDataSource(clientDoc, userName, password, connectStr, driverName, JNDIName);
  145.  
  146. if (request.getParameter(”comboboxOrderBy”)!=null)
  147. if ( (repSel.equals(”S”))||(repSel.equals(”FER”))||(repSel.equals(”SML”))||(repSel.equals(”IML”))||(repSel.equals(”TML”)) )
  148. JRCHelperSample.addDiscreteParameterValue(clientDoc, “sort”, “”, request.getParameter(”comboboxOrderBy”));
  149. // FORMAT SELECT/OUTPUT
  150. String Format = “PDF”;
  151. if (request.getParameter(”radioFormat”)!=null){
  152. Format = request.getParameter(”radioFormat”);
  153. }
  154.  
  155. if (Format.equals(”PDF”)) {
  156. IReportSource reportSource = clientDoc.getReportSource();
  157. JRCHelperSample.exportPDF(reportSource, request, response, config.getServletContext(), true);
  158. }
  159. else if (Format.equals(”CRView”)) {
  160. CrystalReportViewer crystalReportPageViewer = new CrystalReportViewer();
  161. IReportSource reportSource = clientDoc.getReportSource();
  162. crystalReportPageViewer.setReportSource(reportSource);
  163. crystalReportPageViewer.setOwnPage(true);
  164. crystalReportPageViewer.setOwnForm(true);
  165. //crystalReportPageViewer.setSelectionFormula(SelectionString);
  166. crystalReportPageViewer.processHttpRequest(request, response, application, null);
  167. }
  168. else if (Format.equals(”RTF”)) {
  169. IReportSource reportSource = clientDoc.getReportSource();
  170. JRCHelperSample.exportRTF(reportSource, request, response, config.getServletContext(), true);
  171. }
  172. else if (Format.equals(”CSV”)) {
  173. IReportSource reportSource = clientDoc.getReportSource();
  174. JRCHelperSample.exportCSV(reportSource, request, response, config.getServletContext(), true);
  175. }
  176.  
  177. } catch (ReportSDKExceptionBase e) {
  178. out.println(e);
  179. } /*catch (SQLException e) {
  180. out.println(e);
  181. }*/
  182.  
  183. %>
Facebooktwittergoogle_pluspinterest

9 thoughts on “JSP Crystal Reports”

  1. Hi Dan,

    I am trying to implement part of your code. I am using Crystal XI R2. I’ve got is working fine without the reportClientDoc.setRecordSelectionFormula(output);
    but when I add it it crashes.

    Code is

    ReportClientDocument reportClientDoc = new ReportClientDocument();
    reportClientDoc.open(incomingRPT, OpenReportOptions._openAsReadOnly);

    String output = “{sb_user.sb_login_code} = ‘Phil'”;
    reportClientDoc.setRecordSelectionFormula(output);

    Error is

    84. reportClientDoc.setRecordSelectionFormula(output);
    ^———————————————–^
    *** Semantic Error: No accessible method with signature “setRecordSelectionFormula(java.lang.String)” was found in type “com.crystaldecisions.reports.sdk.ReportClientDocument”.

    Any thoughts on what is wrong?

    Thanks,
    Phil.

  2. I can’t tell from that error information. I remember when I got this working that it took many trial and error sessions to get it working just like I wanted it.

    I would install IBM’s Eclipse IDE(http://www.eclipse.org/) and use the Business Objects plugin (http://diamond.businessobjects.com/node/440). This was essential to me getting all of this working.

    Then all you have to do is right-click on the report file and it will generate some code you can use. The code will have to be modified, but it will put you in the right direction.

    Thanks,
    Daniel Folkes

  3. Hi Daniel,

    Thankyou for your response.

    I certainly understand when you say “many trial and error sessions”. I have been through the same sort of process trying to figure out how to call crystal from coldfusion jrun.

    I suspect the error above that I reported was because the .jar files in CRXIR2 are old compared to the ones you are using. I had a quick go at using the newer ones available through http://diamond.businessobjects.com/node/440 and then got a different error. I will go back through the cycle of “many trial and error sessions” now that I have updated .jar files, and will let you know if I actually have success.

    Again, many thanks.

    Phil.

  4. i m using netbeans IDE..n want create a crystal repor.
    i m totally beginner but its urgent.Also i want 2 retrive dta from oracle 10g database.

    Thank you in advance

  5. I would download Eclipse and use the Crystal Reports Plugin to create some Crystal reports magic.

    Then try to open the files in Netbeans. I know that Eclipse has a pretty great front end for CR.

    Hope this helps Biplob.

  6. Thank u for u previos reply……………i did all successfully that i need…….i am opening the report in pdf form using CR 11 and tomcat and netbeans/jsp…now i want that user should not be able to save the pdf means i want to disable the save options in that pdf……
    any help will b accepted…
    Thank you again in advance

  8. hello buddy;

    I want to list my datas with crystal reports and jsp. I prepared my report and imported it into my jsp.

    I saw list of my datas but, I have a problem. My datas are shown like this :

    ID NAME
    1 Ali

    2 Coskun

    3 Ahmet

    and the question is HOW CAN I ORDER THE LIST? HOW CAN I PREPARE THIS SPACES? PLEASE HELP ME THANKS

    1. Usually I created a template using the plugin for Eclipse. Then, I would pull in the data using the query. You could look up how to sort with the query. Probably much like SQL.

Comments are closed.