programmer blog

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.

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. %>

This entry was posted on Thursday, February 7th, 2008 at 10:24 am and is filed under Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

« Python Torrent Search and Download (TPB)
Py2Html »

3 Responses to “JSP Crystal Reports”

  1. Phil Says:
    March 7th, 2008 at 6:14 pm

    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. admin Says:
    March 10th, 2008 at 11:01 am

    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. Phil Says:
    March 13th, 2008 at 6:13 pm

    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.

Leave a Reply