Manipulating string data
Often, a data source contains string or text data that is not in the right form for a report. For example, you want to sort a report by last name, but the data source contains last names only as part of a full name field. Or, conversely, you want to display full names, but the data source stores first names and last names in separate fields.
Both JavaScript and BIRT provide a wide range of functions for manipulating strings. In the expression builder, look under Native JavaScript Functions—String, and under BIRT Functions—BirtStr. The following sections describe some of the common string‑manipulation tasks and provide examples of expressions.
Substituting string values
Sometimes, you need to substitute one string value for another. Perhaps data was added to the data source inconsistently. For example, some addresses contain “Street,” and some contain “St.”. You can replace entire string values or just parts of a string by using the replace( ) function in JavaScript.
The replace( ) function searches for a specified string and replaces it with another string. It takes two arguments: the string to replace, and the new string. The following expression searches for “St.” in an address field and replaces it with “Street”:
row["address"].replace("St.", "Street")
To search for and replace multiple strings in a single field, add as many replace( ) functions as needed to the expression, as shown in the following example:
row["address"].replace("St.", "Street").replace("Ave.", "Avenue")
.replace("Blvd", "Boulevard")
As with any global search-and-replace operation, be aware of unintended string replacements. For example, the row["address"].replace("St.", "Street") expression replaces St. Mary Road with Street Mary Road. In this case, rather than just searching for “St.”, you need to search for “St.” at the end of a line. To perform this type of search, specify a string pattern to search, rather than a literal string. For more information about searching for patterns, see Matching string patterns.
To replace entire strings, rather than just a part of the string, you can use the mapping feature instead. The mapping feature is ideal for replacing known sets of values. For example, a gender field contains two values, M or F. You can map the M value to Male, and F to Female. For more information about mapping values, see Specifying alternate values for display.
Combining values from multiple fields
Each field in a database often represents a single piece of information. For example, a customer table might contain these fields: customerFirstname, customerLastname, addressLine_1, addressLine_2, city, state, zip, and country.
You can create a customer report that uses data from all these fields by dragging each field to a table cell. The generated report, however, does not look professional because the spaces between the pieces of data are uneven, as shown in Figure 11‑6.
Figure 11‑6 Report with separate field values
The solution is to combine, or concatenate, the first and last names and place the concatenated name in a single table cell. Similarly, concatenate all the address‑related fields and place the full address in a single table cell. In JavaScript, concatenate string values using the + operator.
For the name, add a literal space (" ") between the name fields so that the first and last name values do not run together. For the address, add a comma and space between all the fields, except between state and zip. For these fields, add only a space between them.
For this example, use the following expression to display the full customer name:
row["customerFirstname"] + " " + row["customerLastname"]
Use the following expression to display the full address:
row["addressLine1"] + ", " + row["addressLine2"] + ", " + row["city"] + ", " + row["state"] + " " + row["zip"] + ", " + row["country"]
The report now looks like the one shown in Figure 11‑7.
Figure 11‑7 Report with combined field values
Several addresses display the word null because the addressLine2 field contains no data. In a database, a null value means no value was supplied. In cases where you concatenate fields that might contain no data, you need to remove the word null from the returned string value. This task is described in the next section.
Removing null values from combined fields
When concatenating string values, JavaScript converts null values to the word null. The example report in Figure 11‑7 displayed addresses with the word null when the addressLine2 field did not contain a value, for example:
8490 Strong St., null, Las Vegas, NV 83030, USA
Remove the word null by using the replace( ) function. In this example, use replace( ) in the expression to search for “null, ” and replace it with an empty string. You should also search for the comma and space after null to remove the extra comma and space that is added after the addressLine2 field. If you search only for “null” you get the following results:
8490 Strong St., , Las Vegas, NV 83030, USA
Use the following expression to remove null values from a concatenated address:
(row["addressLine1"] + ", " + row["addressLine2"] + ", " + row["city"] + ", " + row["state"] + " " + row["zip"] + ", " + row["country"]).replace("null, ","")
Searching for and replacing “null, ” does not, however, take into account missing values in the state and country fields. The state value does not have a comma after it, so you need to search for “null ”. The country value does not have a comma or space after it, so you need to search for “null”.
To replace null values in the state and country fields, add two more replace( ) functions to the expression, as follows:
(row["addressLine1"] + ", " + row["addressLine2"] + ", " + row["city"] + ", " + row["state"] + " " + row["zip"] + ", " + row["country"]).replace("null, ","").replace("null ","").replace("null","")
Removing spaces from the ends of strings
When combining values from multiple fields, the resulting string can sometimes contain extra spaces, as shown in the following example:
Carine  Schmitt
The string has an extra space between the first name and the last name because the first name value contains a space after the name, and the following expression inserts the second space:
row["customerFirstname"] + " " + row["customerLastname"]
To remove spaces from strings, use the BirtStr functions, trim( ), trimLeft( ), or trimRight( ). The trim( ) function removes both leading and trailing space characters, trimLeft( ) removes leading space characters, and trimRight( ) removes trailing space characters.
The following expression uses trim( ) to remove all leading and trailing spaces from both firstName and LastName fields:
BirtStr.trim(row["firstName"]) + " " + BirtStr.trim(row["lastName"])
Getting parts of a string
Sometimes, you want to display only a portion of a string. For example:
*An address field stores a full address, but you want to display only the zip code or the state.
*A name field stores a full name, and you want only the first or last name.
*An e-mail field stores e-mail addresses, and you want only the user name that precedes the @ symbol.
Depending on the content of the string and which part of a string you need—the first part, the last part, or a part after or before a particular character—the expression that you specify varies. The JavaScript functions you are likely to use in the expression include the functions shown in Table 11‑2.
Table 11‑2 Getting information about a string
Function
Use to
charAt( )
Get the character at the specified position of a string. Note that in JavaScript, the first character starts at 0, not 1.
indexOf( )
Find the first occurrence of a specified character and return its position in the original string.
lastIndexOf( )
Find the last occurrence of a specified character and return its position in the original string.
length
Get the length of a string. Note that length is a property of a string, not a function, so do not use parentheses, ( ), after the keyword, length.
substr( )
Return a substring of a specified length, starting from a particular position in the original string.
The following examples show how to get different parts of a string. Assume a customerName field stores names in first name and last name format, such as Robert Allen.
*To get the first name:
*Use indexOf( ) to get the position of the space character that separates the first name from the last name.
*Use substr( ) to get the first name, starting from the first character and for a specified length. The first character for JavaScript starts at 0, not 1. The length to specify is equal to the position of the space character, and not the position of the space character minus 1, as you might think. Consider the name Robert Allen. Logically, the space between the first and last names is the seventh character, but JavaScript counts its position as six. To return the first name, Robert, excluding the space, you want substr( ) to return six characters.
The following expression returns the first name:
spaceCharPosition = row["customerName"].indexOf(" ");
newStringtoDisplay = row["customerName"].substr(0, spaceCharPosition);
*To get the last name, use indexOf( ) and substr( ) again. The difference is the arguments that you specify for substr( ). To get the last name, start from the character after the space, and the number of characters that you want is the length of the entire string minus the length up to the space.
The following expression returns the last name:
spaceCharPosition = row["customerName"].indexOf(" ");
newStringtoDisplay = row["customerName"].substr(spaceCharPosition + 1, row["customerName"].length - spaceCharPosition);
*To get the first name initial and the last name, for example, R. Allen, to display in the report:
*Use the expression in the previous example to get the last name.
*Add a statement that gets the first letter in the customerName field. You can use substr(0,1) to get only the first character. Alternatively, use charAt(0), which returns a character in a specified position of a string.
*Add a statement to combine the first name initial, a period, a space, and the last name.
The following expression returns the first name initial and last name:
firstNameInitial = row["customerName"].charAt(0);
spaceCharPosition = row["customerName"].indexOf(" ");
lastName = row["customerName"].substr(spaceCharPosition + 1, row["customerName"].length - spaceCharPosition);
newStringtoDisplay = firstNameInitial + ". " + lastName;
Matching string patterns
The previous section described some techniques for getting parts of a string for display. Sometimes you need to match patterns, rather than literal substrings, in string values. For example, use pattern matching to:
*Filter rows to display only customers whose last names start with a particular string pattern.
*Search for string patterns, using wildcard characters, and replace with a different string.
To perform pattern matching, use regular expressions. A regular expression, also known as regexp, is an expression that searches for a pattern within a string. Many programming languages support regular expressions for complex string manipulation. JavaScript regular expressions are based on the regular expression features of the Perl programming language with a few differences.
In JavaScript, a regular expression is represented by the RegExp object, which you create by using a special literal syntax. Just as you specify a string literal as characters within quotation marks, you specify a regular expression as characters within a pair of forward slash (/) characters, as shown in the following example:
var pattern = /smith/;
This expression creates a RegExp object and assigns it to the variable pattern. The RegExp object finds the string “smith” within strings, such as smith, blacksmith, smithers, or mark smith. It does not match Smith or Mark Smith because the search is case‑sensitive.
You can perform complex pattern matching by using any number of special characters along with the literal string to search. Table 11-3 shows a few examples of regular expressions that contain special characters. There are many more special characters that you can use in a regular expression, too many to summarize in this section.
Table 11-3 Examples of regular expressions
Regular expression
Description
/y$/
Matches any string that contains the letter “y” as its last character. The $ flag specifies that the character to search for is at the end of a string.
Matches: Carey, tommy, johnny, Fahey.
Does not match: young, gayle, faye.
/^smith/i
Matches any string that starts with “smith”. The ^ flag specifies that the string to search for is at the beginning of a string. The i flag makes the search case‑insensitive.
Matches: Smith, smithers, Smithsonian.
Does not match: blacksmith, John Smith.
/go*d/
Matches any string that contains this pattern. The asterisk (*) matches zero or any number of occurrences of the character previous to it, which is “o” in this example.
Matches: gd, god, good, goood, goodies, for goodness sake.
Does not match: ged, gored.
/go?d/
Matches any string that contains this pattern. The question mark (?) matches zero or one occurrence of the character previous to it, which is “o” in this example.
Matches: gd, god, godiva, for god and country.
Does not match: ged, gored, good, for goodness sake.
/go.*/
Matches any string that contains “go” followed by any number of characters. The period (.) matches any character, except the newline character.
Matches: go, good, gory, allegory.
/Ac[eio]r/
Matches any string that contains “Ac” followed by either e, i, or o, and r.
Matches: Acer, Acir, Acor, Acerre, National Acer Inc.
Does not match: Aceir, Acior, Aceior.
The RegExp object provides several functions for manipulating regular expressions. The following is an example of using a regular expression with the test( ) function to test for customer names that start with “national”:
var pattern = /^national/i;
var result = pattern.test(row["customerName"]);
The first statement specifies the string pattern to search. The second statement uses the test( ) function to check if the string pattern exists in the customerName field value. The test( ) function returns a value of true or false, which is stored in the result variable.
If you are familiar with regular expressions in other languages, note that some of the syntax of JavaScript regular expressions differs from the syntax of Java or Perl regular expressions. Most notably, JavaScript uses forward slashes (/ /) to delimit a regular expression, whereas Java and Perl use quotation marks (" ").
Using pattern matching in filter conditions
In BIRT Report Designer, regular expressions are particularly useful when creating filter conditions. For example, a filter condition can contain a regular expression that tests whether the value of a string field matches a specified string pattern. Only data rows that meet the filter condition are displayed. For example, you can create a filter to display only rows where a memo field contains the words “Account overdrawn”, where a customer e-mail address ends with “.org”, or where a product code starts with “S10”.
When using the filter tool in BIRT Report Designer to specify this type of filter condition, use the Match operator, and specify the regular expression, or string pattern, to match. Figure 11‑8 shows an example of specifying a filter condition that uses a regular expression.
Figure 11‑8 Example of regular expression
In this example, the filter condition is applied to a table in the report design. In the generated report, the table displays only customers whose names contain the word National. You can learn more about filtering data in the next section.
Using pattern matching to search and replace string values
So far, this chapter has described some of the syntax that is used to create regular expressions. This section discusses how regular expressions can be used in JavaScript code to search for and replace string values.
Recall that in Substituting string values, we used replace( ) to search for a specified string and replace it with another. Sometimes, you need the flexibility of searching for a string pattern rather than a specific string.
Consider the example that was discussed in that earlier section. The row["address"].replace("St.", "Street") expression replaces St. Mary Road with Street Mary Road. To avoid these types of erroneous search-and-replace actions, use the following expression to search for “St.” at the end of a line. The $ flag specifies a match at the end of a string and the backslash before the dot specifies an actual period as opposed to the “any character” metacharacter.
row["address"].replace (/St\.$/, "Street")
Consider another example: A report displays the contents of a memo field. You notice that in the content, the word JavaScript appears as javascript, Javascript, and JavaScript. You want JavaScript to appear consistently in the report. To do so, write the following expression to search for various versions of the word and replace them with JavaScript:
row["memoField"].replace("javascript", "JavaScript").replace("Javascript", "JavaScript")
This expression searches for the specified strings only. It would miss, for example, JAVASCRIPT or javaScript. You can, of course, add as many versions of the word you can think of, but this technique is not efficient.
An efficient and flexible solution is to use a regular expression to search for any and all versions of JavaScript. The following expression replaces all versions of JavaScript with the correct capitalization, no matter how the word is capitalized:
row["memoField"].replace(/javascript/gi, "JavaScript")
The g flag specifies a global search, causing all occurrences of the pattern to be replaced, not just the first. The i flag specifies a case-insensitive search.
Converting a string to a number
A data source can store numbers as strings. Telephone numbers, zip codes, user IDs, and invoice numbers are some of the numbers that might be stored as strings. To manipulate these numbers mathematically, you need to convert them to a numeric type using the parseInt( ) or parseFloat( ) JavaScript function.
The following example converts an invoice ID to an integer and adds 10 to it:
parseInt(row["invoiceID"]) + 10
If invoiceID is 1225, this expression returns 1235. If parseInt( ) is not used to convert invoiceID to a real number, the result of adding 10 to invoiceID is 122510.
Converting a string to a date
A data source sometimes stores dates as strings. Reports, however, typically need to sort, format, or manipulate these values as dates. For example, values of date type are sorted in date order, such as 2/18/2014, 2/19/2014, 2/20/2014, 2/21/2014 rather than in alphanumeric order, such as 2/18/2014, 2/19/2014, 2/2/2014, 2/20/2014. In addition, unlike date strings, you can manipulate values of date type mathematically, for example, calculate the difference between two dates.
To convert dates from string to date type, pass a supported date format to the JavaScript Date object, as shown in the following example:
var datestring = "01/15/2014"; //Variable with MM/dd/yyyy format
new Date(datestring); //Pass the date format to the Date object
JavaScript supports the following date formats for string‑to‑date conversions:
*MM/dd/yyyy (for example: 10/15/2014)
*MMMM dd, yyyy (for example: October 15, 2014)
*MMM dd, yyyy (for example: Oct 15, 2014)
*yyyy/MM/dd (for example: 2014/10/15)
If a data source stores dates as strings in one of those formats, the string‑to‑date conversion is simple. The following example converts values in the OrderDate field to the date type:
var datestring = row["OrderDate"];
new Date(datestring);