Finding or Excluding Rows with Null Values in Ad Hoc Tutorial

Introduction

JasperServer Professional 3.5 provides rich ad hoc query and reporting functionality. One function that is not obvious, however, is how to create queries that find (or exclude) rows that contain null values.

This tutorial shows you how to design the Domain to allow users to find or filter rows with null (blank) values in Ad Hoc. Also described is a technique that allows you to enforce a filter at the Domain level.

Product Versions / User

This tutorial works with version 3.1 and later. Version 3.5.1 was used for the descriptions and screen shots.

User

Developer

Objectives

This guide will enable you to:

Add the isNull function
Create a simple Domain
Create a field that evaluates "null"
Hard code the filter in the Domain (Administrator option)
Create an Ad Hoc Query
Set a Filter (End User option)
Create the Report

Pre-Requisites

This tutorial assumes that you have the following:

Ability to edit an xml file and restart the application server
Administrator or superuser login credentials to JasperServer Pro
Familiarity with JasperServer Domain definitions
An understanding of SQL is helpful, but not required

Add the isNull Function

The first step is to introduce a new Domain function that creates a SQL “is null” statement. Write access to the application server, and the ability to restart the server are required to complete this process.

Step	Action
1	Using a text editor, open applicationContext-semanticLayer.xml, which is located in: Windows: C:\Program Files\jasperserver-pro-3.5.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF Linux: /opt/jasperserver-pro-3.5.1/apache-tomcat/webapps/jasperserver-pro/WEB-INF
2	Locate the following section: <bean id="defaultSQLGenerator" class="com.jaspersoft.commons.semantic.dsimpl. SQLGenerator" scope="prototype">
3	Add the following entry. Make sure that the entry appears within the defaultSQLGenerator section and between the <map> and </map> tags. <entry key="isNull"> <value>sqlArgs[0] + " IS NULL "</value> </entry>
4	Save the file and restart JasperServer Professional.

Return to top

Create a Simple Domain

For the purposes of this tutorial you will create a simple Domain from the SuperMart demo data. You will use the customer table, which includes null values in the address2 column.

Step	Action
1	Login with [superuser or] jasperadmin credentials.
2	Click View > Repository.
3	For tutorial purposes, navigate to [root > Organizations > ] Organization > Domains.
4	From the toolbar, click the Add Resource icon and select Domain.
5	Under Data Source, click Browse… and select: [Organizations > ] Organization > Analysis Components > Analysis Data Sources > Foodmart Data Source JNDI. Click OK.
6	Under Design, click Launch Domain Designer.
7	Drag the customer table to the Selected tables: box.

Return to top

Create a Field that Evaluates "null"

Here you will apply your newly created "isNull" function to a calculated field.

Step	Action
1	Go to the Calculated Fields tab.
2	Click the Create Field button. Enter the following: Name: BlankAddress2 Type: Boolean Expression: isNull(customer.address2) Click OK. Result: BlankAddress2 will appear in alphabetic order in the Available Fields box. It will be preceded by a f symbol to indicate it is a calculated (function) field.
3	Go to the Display tab. Drag the customer Join tree to the Sets and Items box. Click Done. Give the Domain a name, location, and description and click Save.
4	Continue to the optional Hard Code the Filter in the Domain section below, or the following Create the Ad Hoc Report section.

Return to top

Hard Code the Filter in the Domain (Administrator Option)

Administrators can enforce filters at the Domain level, which end-users cannot override.

Skip to Create the Ad Hoc Report section if you want users to set or change their own filters.

Note: Steps 5 through 7 ensure that end-users are unable to apply a conflicting filter on the BlankAddress2. Conflicting filters could result in no rows being retrieved at all.

Step	Action
1	From the Repository view, check the check box next to your new Domain, then click the Edit icon on the toolbar.
2	In the Design section, click Launch Domain Designer. Go to the Filters tab.
3	Select BlankAddress2 from Available Fields, and then click the New Condition button.
4	Select True (to retrieve rows whose address2 fields are null) or False (to retrieve rows with values in address2). Click OK.
5	Click Next or click the Display tab.
6	In the Sets and Items: box, expand the customer folder.
7	Select the BlankAddress2 calculated field and click the Delete (trash icon) button.   Confirm that you wish to remove the item from the Domain display.
8	Click Done, then Save.

Return to top

Create an Ad Hoc Query

You can allow users to filter on "null" or "not null" themselves.

Note: If the end-user does not select a filter and no filter is defined in the Domain, all rows will be retrieved.

Step	Action
1	Log into JasperServer Professional with any valid credentials, including demo, joeuser, etc.
2	Click Create > Ad Hoc Report.
3	Click the Domains tab and select the Domain you created in the Create a Simple Domain section, above. Click Choose Data.
4	Drag the customer data island to the Selected items: box.

Return to top

Set A Filter (End User Option)

Important: If the administrator implements a filter on a field at the Domain level, then these steps are not available for that field when creating Ad Hoc queries. You can skip to "Create the Report."

Step	Action
1	Click Next or go to the Filters page.
2	Click the blue arrow next to BlankAddress2 to begin defining the filter.
3	Select True (to retrieve rows whose address2 fields are null) or False (to retrieve rows with values in address2). Click OK.
4	Check the Prompt? check box to allow yourself to switch between null and not null while working with reports.

Return to top

Create the Report

The query is complete. You can now use the Ad Hoc report designer to create charts, tables, and crosstabs. In this example you create a simple table-style report.

Step	Action
1	Click Open Report.
2	Drag address1 and address2 into the report. The address2 column will be blank (if true) or populated (if false).
3	Note: You can only change the filter if you select Prompt on the Filter page. Filters hard-coded into the Domain are not modifiable. Check the BlankAddress2 check box and click OK. Result: The address2 column changes, as shown below.

Return to top

What's Next?

You can of course make changes to the Ad Hoc queries, reports and Domain definitions.

For more information, refer to:

JasperServer Professional User Guide located in the <jasperserver-install>\docs folder
- Chapter 6: Creating Domains
- Chapter 7: Advanced Domain Features

Written By

This tutorial was written by Mary Flynn, Sales Enginer Manager, July 2009