Crystal Reports – Add True Bullets to a List in a Text Object with the CHRW Function
Business Objects, Crystal Reports
Suppose you want to display a list of items in a text object and you want that list to appear as a bulleted list. If your list is hard coded (i.e. USA, Canada, Mexico), then you could simply type the character that represents a bullet, like an asterisk, and have your list in no time flat.
Our example will have a bit of static text at the beginning followed by the bullet list. The static text will read as follows:
“Last Year’s Sales and Suppliers for”
We will follow up the text with a carriage return to ensure the bullet list begins on a fresh row in the text object. This is where you could type something like the following:
* USA
* Canada
* Mexico
The finished product would look like:
Suppose your list if items is the result of selections made within a parameter and you want to display that user-defined list with bullets. The first thing you have to realize is that you can’t just place the parameter in the text box and get the list.
Text Object Formula
Text Object Result
The reason being is that if a parameter contains multiple selections, those selections are stored in an array and the text object will only display the first entry in the array.
To solve this problem, we will create a Formula Field and use the JOIN function to concatenate all of the array entries into a single displayable string. To get the effect of a column-styled list, we will use the CHR function and the ASCII code 13 to invoke a carriage return after each entry.
JOIN ({?Country}, Chr(13))
When we update the formula, the new dynamic list will appear as follows:
Text Object Formula
Text Object Result
The issue here is that we have a wonderful column-style list, but it does not have the bullet icons that precede each entry. A quick modification to the JOIN formula will solve that problem.
“* ” & Join ({?Country}, Chr(13) + “* “)
The JOIN function will concatenate all of the array entries together followed by a carriage return and an asterisk with two spaces. Because the first entry in the array does not get an asterisk and two spaces, we have added the asterisk and spaces to the beginning of the list.
Text Object Result
Asterisks are okay, but true bullets are better. Let’s see how we can improve upon this idea.
One of the limitations of the CHR function is that it can only address up to ASCII code 255 on most systems. If you look in the Character Map, there are far more characters available to us beyond the ASCII number 255 entry. In fact, when using the Arial font, we can avail ourselves to 65,276 possible characters.
By using the CHRW function, we can address characters up to and including character code 65,535.
If you scroll to about the 85% point in the Character Map, there is a nice large black circle character that would be perfect for our bullets. The entry code for this character is 25CF.
Because the value 25CF is a hexadecimal value, we have to convert 25CF to a decimal value. 25CF is equivalent to 9679 in decimal notation. With this final modification to our formula, we can finally achieve the perfect bullet list we were striving for.
ChrW(9679) & ” ” & Join ({?Country}, ChrW(13) + ChrW(9679) & ” “)
Text Object Result
By changing all of the CHR functions to CHRW functions, we have access to a larger variety of character choices. Since the CHRW function sees the same first 255 characters as the CHR function, we have just used the CHRW function for both the bullets as well as the carriage returns.
Note: The double-quotes with 2 spaces in between are included simply to keep the array entries from resting directing against the bullets.