I wrote last fall about selecting “ALL” with a string parameter field. My comments at the end list options for making the technique work for numbers and dates. Recently I received a note from one of my colleagues on a better approach, using optional parameters.

Starting with CR 2008 (v14) Crystal has allowed us to define a parameter as “optional”. This allows the user to not enter a value at all in a parameter. However, when you use an optional parameter in a formula you always have to test for the existence of a value in the parameter field before you use. The test often looks like this:

if HasValue ( {?Tax Rate} )
then {Orders.Order Amount}*{?Tax Rate}
else 0

This way the report knows what to do when the parameter is skipped.

Another way of creating an ALL option is to tell the report that any time there is no value entered in the parameter, the user wants ALL values. The formula would look something like this:
(not HasValue ( {?State} ) or {Customer.State} = {?State})

Note that the HasValue() test has to come before any other test that uses that parameter. If you reverse the two tests in this formula it will generate an error whenever you don’t fill in the parameter.

And thanks to Luc Rascar, a Crystal Reports/Business Objects consultant in France, for pointing this out.

One of my favorite parts of writing this blog is when people read a post and then send me an alternate approach that teaches me something new.  Like today.

Last month I shared a formula for creating an address block that will automatically remove blank lines. Today one of my readers showed me how he does this with a text object. He uses a formatting property called “Suppress Embedded Field Blank Lines”.  I had never seen this option before so I quickly checked my version of Crystal. There it was in the formatting properties of text objects (not fields). I thought I might have missed this because it was a recent feature, so I started working backwards through the different Crystal versions to see when it appeared. I stopped when I found it in CRv8.5 which is nearly 20 years old. So much for missing a recent feature.

To use this feature you add a blank text object to your report. You then ’embed’ fields by dragging each field over the text until you see a hash mark. This indicates where the field will be embedded in the text, even in the middle of a sentence.  When the hash mark is in the right spot, you release the field and it becomes embedded into the text object at that point.

To create an address block you would add all the address fields into a text object and hit <Enter> between each one so that each field is on it’s own line. At first, any empty fields will create a blank line in the block. But if you go into Format > Text> [common tab], and check the property mentioned above, these blank lines go away automatically.

This may not work in every situation, but it is much simpler then the formula approach I posted last month.  And thanks to Duane Fenner, an Accounting Support Specialist at LTi Technology Solutions for sharing this with me.

There are several common uses for exporting to text format.

I use text format whenever I need a Fixed Length export file. These are files where the exported record doesn’t have a comma or pipe delimiter. Each field in the string is identified by its character position in the string, since each field is a specific number of characters. This means that all records end at the same position, regardless of how long individual field values are.

I also use text export format for some CSV exports, because there is more flexibility.  For instance, when I need to generate two CSV rows from the same detail row in the data I find it easier to structure the CSV rows in a formula  and export as text.

And that brings me to what I learned last week. If you are exporting to “text” format and the formula you are exporting has carriage returns in it, you might find that they don’t work after the export. For instance the formula below would show 3 rows in the preview of Crystal Reports:

{@String1} & CHR(13) &
{@String2} & CHR(13) &
{@String3}

The function element CHR(13) creates a carriage return between the different elements of the string. But if you export this formula using “text” format you will find that the carriage returns don’t survive the export. The text file would not have the three rows that you see in preview. But with a little experimenting I found that adding a second related function in the formula works better:

{@String1} & CHR(13) & CHR(10) &
{@String2} & CHR(13) & CHR(10) &
{@String3}

In CR preview both formulas will appear the same. However, the second formula will provide carriage return that survives into the text export, while the first one will not.

Within a week of making this discovery for one customer, I found I needed the same thing for a second customer. I probably should have figured this out even sooner. When working text files and hidden codes I have seen that you usually need both a carriage return, Chr(13), and a line feed , Chr(10) to start a new line. But since it only takes one of these in Crystal preview, it is easy to forget that they work together.