Adding a filter to custom datasource

Introduction

In this article, we will take a look at adding a filter to the custom data source example that JasperReports Server ships with. Unlike database-based filters, in this example we have to read the values from the custom data source itself to build out the values for the input control.

Problem

We want to add an input control to the custom datasource example that JRS ships with, and we want the values for that input control to be those from the "city" field in the custom data source.

Setting up the Custom Data Source example

Go to where you installed JasperReports Server, find the samples directory, and browse down to the customDataSource subdirectory. For example, on my system that folder is C:Program Filesjasperreports-server-4.2samplescustomDataSource.

Do these steps to deploy the example:

• Stop the JasperReports Server instance
• Edit the build.xml file in the customDataSource subdirectory, and modify the value of the webAppDir property to match where JasperReports Server is installed in the Tomcat directory

• From the command line, type this command:

  ant deploy 

• Re-start the JasperReports Server instance

When you log in to JasperReports Server, you will see that you have more choices for data source types.

Choose the type "My Custom Data Source", and fill out the form:

• Name: give it a name
• Resource ID: give it the same name as above
• Foo Foo: foo
• Bar Bar: bar
• Leave the "Save Location" in the repository's /public directory

Submit the datasource.

At this point, you can go ahead and create the report:

• Choose a directory in the repository, and add a "JasperReport" resource there. You will need to name it, add a datasource (where you will reference the datasource you saved in the above step)
• Upload the JRXML file (that's the report template file) from the samplescustomDataSourcereportssimpleCDS.jrxml directory
• Save and run the report

When you run it, you should see a list of people's names grouped by the city in which they live.

Steps to add a filter to the custom datasource-based report

We'll need to add a parameter and a filter to the report so that when we implement the input control on the server, it will know to filter the results based on the parameter that will get filled in by the input control.

In iReport, open up the simpleCDS.jrxml and do this:

• Add a parameter, call it "the_city_distinct", keep its type as java.lang.String

• Add a filter to the report. You do this by bringing up the report query editor, then clicking the "Filter expression..." button, and then adding the filter expression to filter on (for example), the city field:

  $F{the_city}.equals( $P{the_city_distinct} )

  The expression above makes sure that "the_city" field matches the value of the "the_city_distinct" parameter

• Make sure to upload the report to the server when you're done.

Build a custom query executer

We're going to have to build a custom query executer in order to get the input control properly working, since by default, we can only set up input controls that use SQL, HQL, or Domain query languages, all of which require a datasource with a REPORT_CONNECTION. Since the data is in the custom data source, which in our case is part of a custom data source class, we'll have to build our own custom query executer.

There are three items that we need to build for the custom query executer:

• An Executer Factory
• A custom query executer
• A custom data source

The Executer Factory class

The Executer Factory class implements the net.sf.jasperreports.engine.query.JRQueryExecuterFactory interface, and for our example, the key method is the "createQueryExecuter" method. Its role is to create the custom query executer object.

package example.cds;

import java.util.Map;
import net.sf.jasperreports.engine.JRDataset;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRValueParameter;
import net.sf.jasperreports.engine.query.JRQueryExecuter;
import net.sf.jasperreports.engine.query.JRQueryExecuterFactory;

public class CDSExecuterFactory implements JRQueryExecuterFactory { 

  @Override
  public JRQueryExecuter createQueryExecuter(JRDataset dataset, Map parameters) throws JRException {
    // TODO Auto-generated method stub
    return new CDSQueryExecuter(dataset, parameters);
  }

  @Override 
  public Object getBuiltinParameters() {
    // TODO Auto-generated method stub 
    return null; 
  } 

  @Override 
  public boolean supportsQueryParameterType(String arg0) { 
    // TODO Auto-generated method stub 
    return false; 
  } 
} 

The custom query executer

The custom query executer is in charge of creating the custom datasource object itself. In the example below, I'm doing a simple check, but I could just as easily call the custom datasource constructor without doing any checks. Similarly, we could also add more steps to the "createDatasource" method if we needed to extra processing.

package example.cds;

import java.util.Map;
import net.sf.jasperreports.engine.JRDataSource; 
import net.sf.jasperreports.engine.JRDataset; 
import net.sf.jasperreports.engine.JRException; 
import net.sf.jasperreports.engine.query.JRAbstractQueryExecuter; 

public class CDSQueryExecuter extends JRAbstractQueryExecuter { 

  public CDSQueryExecuter(JRDataset dataset, Map parameters) { 
    super(dataset, parameters); 
    parseQuery(); 
  } 

  @Override 
  public boolean cancelQuery() throws JRException { 
    // TODO Auto-generated method stub 
    return false; 
  } 

  @Override 
  public void close() { 
    // TODO Auto-generated method stub 
  } 

  @Override 
  public JRDataSource createDatasource() throws JRException { 
    // TODO Auto-generated method stub 
    if (getQueryString().equals("cities")) { 
      return new CustomDataSource("the_city"); 
    } else { 
      return new CustomDataSource(); 
    } 
  } 

  @Override 
  protected String getParameterReplacement(String arg0) { 
    // TODO Auto-generated method stub 
    return null; 
  } 
} 

The custom datasource class

The custom datasource class is based on the source file that ships with JasperReports Server, and you can find it under samplescustomDataSourcesrcexamplecdsCustomDataSource.java. I'm including only the bits of code that I modified to get my example working.

I modified the constructor only to show that you can pull the user's profile attributes, if necessary, to do any extra processing that may be necessary to build the custom datasource.

public CustomDataSource()
{ 
  MetadataUserDetails user = null; 
  Authentication auth = SecurityContextHolder .getContext() .getAuthentication(); 

  if (auth != null && auth.getPrincipal() != null) { 
    if (auth.getPrincipal() instanceof MetadataUserDetails) { 
      user = (MetadataUserDetails) auth.getPrincipal(); 
      List attribs = user.getAttributes(); 
      int size = attribs.size(); 
      if (size > 0) { 
        Iterator i = attribs.iterator(); 
        while (i.hasNext()) { 
          ProfileAttributeImpl attrib = (ProfileAttributeImpl) i.next(); 
          String attribName = attrib.getAttrName(); 
          String attribValue = attrib.getAttrValue(); 
        } 
      } 
    } 
  } 
} 

The getField method is where we process the individual fields for a given record, while the JasperReports library is iterating through the datasource. The extra bit I added is the last "if" statement, where I check for the existence of "the_city_distinct" as a field value request; this name has to match the parameter name we gave the report.

public Object getFieldValue(JRField field) throws JRException 
{ 
  Object value = null; 
  String fieldName = field.getName(); 

  if ("the_city".equals(fieldName)) { 
    value = data[index][0]; 
  } else if ("id".equals(fieldName)) {
    value = data[index][1]; 
  } else if ("name".equals(fieldName)) { 
    value = data[index][2]; 
  } else if ("street".equals(fieldName)) { 
    value = data[index][3]; 
  } else if ("the_city_distinct".equals(fieldName)) { 
    value = data[index][0]; 
  } 
  return value; 
} 

Setup before building the input control

Since the goal is to create the input control so that it uses the custom data source, we're going to have to enable the custom query executer to appear in the list of possible query languages when creating an input control. This is what we have to do:

• Edit WEB-INF/flows/queryBeans.xml

Find the property whose name is queryLanguages. To the list of values, create a new list item and give it the label you want for the Districts custom data source. Something like

CustomQuery

• Edit the WEB-INF/classes/jasperreports.properties file

Add this line: net.sf.jasperreports.query.executer.factory.CustomQuery=fully.qualified.package.CustomQueryExecuterFactory Make sure the custom query executer factory is properly referenced, it needs the full package name and class name. Doing this provides the link between the UI and the matching query executer factory.

• Edit WEB-INF/bundles/jasperserver_messages.properties

Add this line:

query.language.CustomQuery.label=Custom Query

You can change the Custom Query value to whatever you want, it's what will appear in the UI when creating the input control.

Creating the Input Control

Here are the steps for creating the input control.

• Log into JasperReports Server as an administrator, browse to the repository
• Create a new input control on a folder of your choice
• Type: Single-select query
• Prompt Text: a user-friendly value here will do
• On the "Locate Datatypes" screen choose: "Define a query in the next step." Hit Next.
• On the "Name the Query" screen: Enter in a name
• On the "Link to a Datasource" screen, choose: "Do not link to a data source." Hit Next.
• On the "Define the Query" screen, choose the values below, and then hit the "Save" button:
  • Query Language: the language you set up from previous attempts
  • Query String: you can leave it blank.
• On the "Provide parameters for the value column" screen:
  • In the value column: enter in the name of the field you added in the JRXML
  • In the visible columns, enter in the name of the same field you added in the value column. Make sure to click on the "Add" link afterwards.
  • Since I've been using "the_city_distinct" all along, for the sake of this example, you may wish to populate both those fields with "the_city_distinct."

Add the Input Control to the Report

Now you have to link the report to the input control you just created. You can do this by going to the repository, finding the report, and editing it. There's a button for "Controls and Resources"; once there, you'll add the input control you just created by locating it in the repository.

Process for deploying to a test system

Once you have built and compiled the custom code, you'll need to deploy it to a test server environment. I suggest doing it like this:

• Package up the classes you've developed into a separate JAR file
• With the server shut down, copy it to /jasperserver-pro/WEB-INF/lib
• Re-start the server, test