Crystal Reports Viewer with User Provided Parameters

By: Peter Fowler

Abstract: The Crystal documentation for using parameters within reports is very misleading, and at times incorrect. I've managed to get parameters working fine with Crystal reports, and would like to share how I did that with the rest of the JBuilder community.

    Overview

Crystal Reports 10 and now Crystal Reports XI have been used in my company for over 2 years. We have a single server dedicated to running the Enterprise RAS, which allowed us to quickly centralise reporting, and leverage it against the Oracle database backend without causing too many problems.

Our web applications are all J2EE and developed in JBuilder (we’ve used 9, X and 2005). Business Objects (the creators of Crystal Reports) have supplied lots of documentation regarding Crystals use with Java, and have graced the JBuilder community with a plugin/wizard to ensure faster implementation of reporting solutions.

The documentation nearly had me 100% on the road to having a complete working Crystal Reports within my Java web applications, except for one thing, providing parameters to reports just did not work! Whenever a report with parameters ran, it would to the usual ‘Prompt for Parameters’. If I tried to provide the parameters myself using the documented API, the entire report would crash with no error, no logs… just a blank web page.

The Crystal documentation for using parameters within reports is very misleading, and at times incorrect. I've managed to get parameters working fine with Crystal reports, and would like to share how I did that with the rest of the JBuilder community.

    The Nuts and Bolts… and the problem

This is going to be a tutorial for how to build a working Crystal Report and provide your own parameters. However, In this first section, I would like to show the correct way of providing parameters to a report. First off, here is how the code example says parameters should be added (There are numerous examples that use this method; I’ve chosen the shortest code example):

      Fields parameterFields = new Fields();

      ParameterField pfield1 = new ParameterField();

      pfield1.setReportName("");

      Values vals1 = new Values();

      ParameterFieldDiscreteValue pfieldDV1 = new ParameterFieldDiscreteValue();

      pfield1.setName("fname");

      pfieldDV1.setValue("Maria");

      vals1.add(pfieldDV1);

      pfield1.setCurrentValues(vals1);

      parameterFields.add(pfield1);

      viewer.setParameterFields( parameterFields );

Essentially, the code is creating a Fields object, populating it with the parameter values then adding that to the viewer. With this method, the report will crash every time. Why is this? Well, it took me quite some time to figure out. Each report actually provides its own Fields with default parameters. These Fields are tied up internally within the report. When the new Fields object is set via the setParameterFields method, the original Fields are overwritten and the report has no idea how to use the new ones. We can prove this my getting the existing Fields when a report is first initialized:

Fields parmFields = viewer.getParameterFields();

Iterator reportIter = parmFields.iterator();

while (reportIter.hasNext()) {

ParameterField field = (ParameterField)reportIter.next();

System.out.println(“Parameter name: “ + field.getName());

}

This code snippet will loop through all the parameter fields of a report, and output the name to the standard out. So if the parameters already exist within the report, why are we re-creating them? My guess is there was miscommunication between the viewer developers and the technical writers. Not so unbelievable, this was Crystal first version to include Java support, so I will forgive them for the time wasted, after all, that time has been saved with the actual report building using Crystal excellent report builder.

So how do we add the parameter then? There’s just a simple modification my little code snippet above:

Fields parmFields = viewer.getParameterFields();

Iterator reportIter = parmFields.iterator();

while (reportIter.hasNext()) {

ParameterField field = (ParameterField)reportIter.next();

if (field.getName().equals("fname")) {

Values values = new Values();

ParameterFieldDiscreteValue pfdv = new ParameterFieldDiscreteValue();

pfdv.setValue("Maria");

values.add(pfdv);

field.setCurrentValues(values);

}

}

Instead of overwriting the ParameterFields, we instead use the current parameter fields, and add our Values directly to those fields. This is all the code that needs to be done when providing parameters, though, I usually add some helpers to incorporate more then one parameter and parameter type (The example only deals with one discrete value).

    Building a Crystal Report with User Parameters

This section will teach you how to build a simple Crystal Report and add it to a web application using JBuilder. This is the primary focus of this document; I will only gloss over how to install the Crystal components. I will assume that there will be access to a working database, and will require basic administration of said database.

First off, we will need to create a simple table to report from, and populate is with some data. Run this SQL command to create the tables we will be working with.

Note* There is some Oracle syntax in the below SQL. The VARCHAR2 String data type and the TO_Date() function will need to be altered to suit your database.

CREATE TABLE EMPLOYEE

(

EMP_ID VARCHAR2(9),

FNAME VARCHAR2(20),

MINIT VARCHAR2(1),

LNAME VARCHAR2(30),

JOB_ID NUMBER,

JOB_LVL NUMBER,

PUB_ID VARCHAR2(4),

HIRE_DATE DATE

)

INSERT INTO EMPLOYEE ( EMP_ID, FNAME, MINIT, LNAME, JOB_ID, JOB_LVL, PUB_ID,

HIRE_DATE ) VALUES (

'1000', 'peter', 'm', 'fowler', 1, 1, '1', TO_Date( '01/01/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO EMPLOYEE ( EMP_ID, FNAME, MINIT, LNAME, JOB_ID, JOB_LVL, PUB_ID,

HIRE_DATE ) VALUES (

'1001', 'adam', 'j', 'clay', 2, 2, '2', TO_Date( '02/02/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

COMMIT;

    Creating the Report

Before we can begin on the development of the application, we must build a report to use. The below steps will create a very basic report, with one parameter. It will closely resemble the report used in the Crystal examples. In fact, you can actually use the Crystal example report, just make sure you change the location using ‘Database Expert’.

Create a new connection. This example shows using an Oracle connection. Select your database system accordingly. Hide image

Select the ‘EMPLOYEE’ table from the tables listing.

Hide image

Select the fields to display in the report.

Hide image

Select the default options, followed by finish.

When the report is generated, we need to create the parameter field. Open the ‘Field Explorer’, right click on ‘Parameter Fields’ and click ‘New’. Enter ‘fname’ for the name of the parameter field, and ensure the data type is ‘String’. Click Ok. Hide image
Click to see full-sized image

Click on the ‘Select Expert’, select the ‘FNAME’ field and click ok. Complete the dialog box to look like the below:

Hide image

Preview the report and enter a value for the FNAME parameter field (I used ‘peter’. You should get something similar to the below:

Hide image

If everything went well, you should now have a working report that we can use to create our parameters example. If your report doesn’t look vaguely similar to the above, then follow the steps again. The most important part is when you click on ‘Preview’, Crystal Reports should ask you to enter a parameter for FNAME, this is essential to the operation of the report.

    Creating the Web Application

In this section, we need to create the web application that will access the report. For teaching the principles behind using parameters, I’ll use a locally stored report, but please refer to the ‘Enterprise RAS’ section for how to get a report source from the Enterprise server.

Before beginning this section, ensure that the ‘Java Reporting Component with JBuilder support’ as been installed from your Crystal Reports CD. This installation will ask for your JBuiler directory (C:\JBuilderX for X and C:\Borland\JBuilder2005 for 2005), and also for your Crystal Reports key code. I will assume that we are all efficient in the use of JBuilder, so I will not include and images in this section.

First off, fire up JBuilder and create a new project. To this project, add a new Web Module. When configuring the web module select the ‘Crystal Reports’ framework to ensure all the libraries are copied to the new projects. It should also create a /crystalreportsviewer10 directory in the web module.

If the crystalreportsviewer10 directory is NOT in the web module, there is another way to have JBuilder automatically add it: Create a new JSP, and click in between the body tags. Click on EditàWizardsàInsert Crystal Reports Viewer. Select the report we just made, and click on ok. This will copy the report into the module directory as well as setup the environment.

If the crystalreportsviewer10 directory DOES already exist (i.e. it was created in the WebModule creation sections, then just manually copy the report created above into the web module directory.

Next step is to create a new JSP file, and start typing some code. Start by adding the includes to the top of the JSP:

<%@ page import = "com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory" %>

<%@ page import = "com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2" %>

<%@ page import = "com.crystaldecisions.sdk.occa.report.reportsource.IReportSource" %>

<%@ page import = "com.crystaldecisions.sdk.occa.report.data.*" %>

<%@ page import = "com.crystaldecisions.report.web.viewer.*" %>

<%@ page import = "com.crystaldecisions.sdk.occa.report.*" %>

<%@ page import = "com.crystaldecisions.sdk.occa.report.lib.PropertyBag" %>

<%@ page import = "java.util.*" %>

That’s all pretty straight forward, so lets jump right into setting up our report source:

      Object reportSource = session.getAttribute("reportSource");

      //if the report source has not been opened

      if (reportSource == null)

      {

            //you will need to modify report variable to point to your report

            String report = "/report.rpt";

            IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();

            reportSource = rptSrcFactory.createReportSource(report,request.getLocale());

            session.setAttribute("reportSource", reportSource);

      }

      CrystalReportViewer viewer = new CrystalReportViewer();

      viewer.setReportSource(reportSource);

      viewer.setOwnPage(true);

      viewer.setOwnForm(true);

      viewer.setPrintMode(CrPrintMode.PDF);

This will load the report we made and add it to the session. Make sure to change the “/report.rpt” to the filename that you saved your report as. As, if you havn’t done so already, copy the report into your Web Module directory.

The last part of that code snippet will create the viewer object. The viewer object will take care of most functions for the report. It’s a piece of Crystal Reports brilliance in my books…

We now need to set the parameters for the viewer object. Copy’ing and Pasting the code snippet in the ‘The Nuts and Bolts… and the problem’ section:

Fields parmFields = viewer.getParameterFields();

Iterator reportIter = parmFields.iterator();

while (reportIter.hasNext()) {

ParameterField field = (ParameterField)reportIter.next();

if (field.getName().equals("fname")) {

Values values = new Values();

ParameterFieldDiscreteValue pfdv = new ParameterFieldDiscreteValue();

pfdv.setValue("peter");

values.add(pfdv);

field.setCurrentValues(values);

}

}

This will search through all the parameter fields in the report. When it finds the “fname” parameter field, it will create a new Values object, containing a ParameterFieldDiscreteValue object, which in turn has had its value set to “peter”. Use the setCurrentValues method of the field object to add all this back into the report.

Now all that is left for us to do is to have the viewer render itself into the JSP, and we are done!

      viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);

Run the web module, and point your browser toward the JSP file. If your database requires a login, a log in screen will be presented to the user. After that, if all has gone according to plan, the report will be displayed with only records matching the parameter as hard coded into the JSP.

    Using the Enterprise Server

The above code is useful for demonstrating how to use parameters within a Crystal Report. It isn’t however very useful for deploying reports on a large scale across your company. For this, Crystal have a solution: Enterprise Server. A copy of Enterprise server is included with Crystal Report Developer addition, along with a very generous 5 named user developer license.

The server has been the most useful implementation my company has provided. Not only will it store your Crystal Reports, but it will also store nearly any document, including Word Documents, Excel Documents, PDF’s etc.

Further more, the server will take care of all the database connections from your reports, and the report processing thereby reducing the load on your application servers (especially useful for running large on demand reports).

It is beyond the scope of this document to detail how to install and setup Enterprise server, but I would like to share to you’re some code that can be used to access these reports via a web application, and also parse parameters to the report. The code will create a standard servlet. It takes all its setting from the request.

Please note that this code will only parse discrete values to the report. For ranges and dates, you will need to modify the “parameter parsing from request” section of the code.

First off, here is a sample form that can be used to access the servlet:

<html>

<head>

<title>

test

</title>

</head>

<body bgcolor="#ffffff">

<h1>

Crystal Reports Testing Form

</h1>

<form action="http://localhost:8080/reporting/report.cr" method="POST">

<b>cmsReport</b><input type="text" name="cmsReport" value="TestReport" /><br>

<b>cmsUser</b><input type="text" name="cmsUser" value="scott" /><br>

<b>cmsPass</b><input type="text" name="cmsPass" value="" /><br>

<b>parm0</b><input type="text" name="parm0" value="fname" /><br>

<b>value0</b><input type="text" name="value0" value="peter" /><br>

<b>type0</b><input type="text" name="type0" value="dvString" /><br>

<input type="submit"/>

</form>

</body>

</html>

Items of note here are the cmsReport, cmsUser and CmsPass. The cmsReport it the report name in the Enterprise server. You set this when you first add a report to the server. The user and password are also setup within Enterprise manager. The default ‘admin’ user with no password will work here in a development environment.

The parm0 indicated the parameter name, in this case ‘fname’. The value0 indicates the value for this parameter (‘peter’) and the type0 indicates the data type of the parameter, which is unused.

More parameters can be added by incrementing the 0. i.e. parm1 value1 type1, parm2 value2 type 2, …

Below is the code for the servlet. This can be mapped to anything you like. The form above will provide the details to this servlet, which in turn will get the report source from the Enterprise server, add the parameters to in and render the viewer. Its pretty much an all in one reporting gateway between your Java application and Crystal Reports.

package reporting;

import javax.servlet.*;

import javax.servlet.http.*;

import java.io.*;

import java.util.*;

import com.crystaldecisions.sdk.occa.infostore.*;

import com.crystaldecisions.sdk.occa.security.*;

import com.crystaldecisions.sdk.occa.infostore.*;

import com.crystaldecisions.sdk.occa.report.*;

import com.crystaldecisions.sdk.occa.report.data.*;

import com.crystaldecisions.sdk.occa.report.reportsource.IReportSource;

import com.crystaldecisions.sdk.occa.managedreports.IReportSourceFactory;

import com.crystaldecisions.sdk.plugin.desktop.common.*;

import com.crystaldecisions.sdk.framework.*;

import com.crystaldecisions.sdk.exception.SDKException;

import com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory;

import com.crystaldecisions.report.web.viewer.*;

public class CrystalReport extends HttpServlet {

private static final String CONTENT_TYPE = "text/html";

//Initialize global variables

public void init() throws ServletException {

}

//Process the HTTP Get request

public void doGet(HttpServletRequest request, HttpServletResponse response) throws

ServletException, IOException {

response.setContentType(CONTENT_TYPE);

HttpSession session = request.getSession();

IInfoStore iStore = null;

IInfoObjects oInfoObjects = null;

IInfoObject oInfoObject = null;

IEnterpriseSession es = null;

IReportSource reportSource = null;

//Mandatory settings

String cmsReport = request.getParameter("cmsReport");

if (cmsReport==null) return;

String cmsUser = request.getParameter("cmsUser");

if (cmsUser==null) return;

//Settings with defaults

String cmsPass = request.getParameter("cmsPassword");

if (cmsPass==null) cmsPass = "";

String cmsAuth = request.getParameter("cmsAuth");

if (cmsAuth==null) cmsAuth = "secEnterprise";

String cmsHost = request.getParameter("cmsServer");

if (cmsHost==null) cmsHost = "crystal";

//Parameters

Enumeration names = request.getParameterNames();

Map parameters = new HashMap();

while (names.hasMoreElements()) {

String key = (String)names.nextElement();

if (key.startsWith("parm")) {

String ident = key.substring("parm".length(), key.length());

String name = request.getParameter(key);

String value = request.getParameter("value" + ident);

String type = request.getParameter("type" + ident);

//Only support discrete values at the moment

Values cvalue = new Values();

ParameterFieldDiscreteValue pfdv = new ParameterFieldDiscreteValue();

pfdv.setValue(value);

cvalue.add(pfdv);

parameters.put(name, cvalue);

}

}

// Logon and obtain an Enterprise Session

try {

es = CrystalEnterprise.getSessionMgr().logon("govdb", "", "tmpo-sql01", "secEnterprise");

session.setAttribute("enterpriseSession", es);

} catch (SDKException e) {

e.printStackTrace();

return;

}

try {

iStore = (IInfoStore)es.getService("", "InfoStore");

oInfoObjects = (IInfoObjects)iStore.query("Select * from CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report' And SI_INSTANCE=0 And SI_NAME='" +

cmsReport + "'");

} catch (Exception e) {

e.printStackTrace();

return;

}

//retrieve the latest instance of the report

oInfoObject = (IInfoObject)oInfoObjects.get(0);

try {

IReportSourceFactory factoryPS = (IReportSourceFactory)es.getService(

"PSReportFactory");

reportSource = factoryPS.openReportSource((oInfoObject), Locale.ENGLISH);

} catch (Exception e) {

e.printStackTrace();

}

//Create a viewer, set the source to the Enterprise report

CrystalReportViewer viewer = new CrystalReportViewer();

try {

viewer.setReportSource(reportSource);

} catch (Exception e) {

e.printStackTrace();

}

//Get the parameter fields

Fields parmFields = null;

try {

parmFields = viewer.getParameterFields();

} catch (Exception e) {

e.printStackTrace();

return;

}

/**

* Loop through the user parameters and find is they

* match the report parameters. If so, add the values

* to the field. Do not use the Crystal documented method

* of viewer.setParameterFields, it will not work. The

* values must be added directly to the existing object.

*/

Set keySet = parameters.keySet();

Iterator parmIter = keySet.iterator();

while (parmIter.hasNext()) {

String name = (String)parmIter.next();

Iterator reportIter = parmFields.iterator();

while (reportIter.hasNext()) {

ParameterField field = (ParameterField)reportIter.next();

if (field.getName().toUpperCase().equals(name.toUpperCase()))

field.setCurrentValues((Values)parameters.get(name));

}

}

viewer.setOwnPage(true);

viewer.setOwnForm(true);

viewer.setPrintMode(CrPrintMode.PDF);

//Log the user

System.out.println("**CRYSTAL: " + request.getRemoteAddr() + " cmsReport: " + cmsReport + " cmsUser: " + cmsUser + " ParmCount: " + parameters.size());

//Now redirect to the report

try {

viewer.processHttpRequest(request, response,

getServletConfig().getServletContext(), null);

} catch (Exception e) {

e.printStackTrace();

}

}

//Process the HTTP Put request

public void doPost(HttpServletRequest request, HttpServletResponse response) throws

ServletException, IOException {

doGet(request, response);

}

//Clean up resources

public void destroy() {

}

}

    Conclusion

I hope you have enjoyed reading this article and have taken away some useful imformation. I have found great enjoyment when working with Crystal Reports, especially when paired up with java web applications.

When it comes to parameters, I usually go two ways. If there is only a single parameter to parse to the report, I will use the above message of passing the parameter through to the report. If the report however is more detailed, involving date ranges and/or lookup values, I will let the user enter their own parameters using the Crystal Reports parameter prompt, which do quite a good job.

Either way, the main purpose of this article was to set right the documentation provided by the Crystal makers. I do know that a lot of people have run into this problem, and sadly, a lot of given up on this product. I for one will be sticking with it all the way.

Regards

Peter Fowler

Server Response from: ETNASC03