ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

JasperSoft - Left, Sub string, Right Functions for Adhoc Views

book

Article ID: 199366

calendar_today

Updated On:

Products

CA Service Management - Service Desk Manager CA Service Desk Manager

Issue/Introduction

There is a requirement within JasperSoft reports where a subset of a text field is required to be reported on.

For example, Left(Description, 100) will return the 100 leftmost characters.

This is a common function in databases, spreadsheets, etc.

Environment

Service Management 17.x

JasperSoft 7.x

All Supported Windows Operating Systems

Resolution

JasperSoft has its own built-in functions to be used for Adhoc Views. Although JasperSoft does not have Left, Substring, and Right Functions specifically, you can use the Mid function instead.

The description of the Mid function is below:

Mid (TextExpression,Integer1,Integer2)

Given a text string, returns the substring starting at Integer1 with length Integer2.

For example:

Mid("Phone", 1, 3) — Given an American phone number starting with a 3-digit area code, extracts the area code.

Note that "Phone" is the field name. 

For Left function, an example would be:

Mid("product_name", 1, 5)

For Right function, it is more tricky as the length of the string can be a variable. However, you can make use of the Length function together with the Mid function like this:

Mid("product_name", Length("product_name") - 3, Length("product_name"))

Built-in functions for calculated fields are documented in the TIBCO JasperReports Server User Guide:

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v71/calculated-fields-reference

In this section, you will find information about the Mid function.

Additional Information

NOTE:  Custom report writing is outside the scope of Broadcom Support.  The information provided in this KB article is provided only as general guidance.