You can use the ToText () function to turn dates into strings, which is handy in cases when you need to combine a date with other characters. For instance I use this pattern in IF-THEN-ELSE formulas, when I want to give the user a parameter to choose sorting on a date value vs a string value:

else ToText ({Tale.Date} , 'yyyy-MM-dd')

This turns the date into an 8 character string that can still be used as a sort field and will still sort the records in chronological order. The [yyyy] returns a 4-digit year, the [MM] in the middle returns a 2-digit (0 padded) month and the [dd] on the end returns a 2-digit (0 padded) day. The dashes can be any character that you want to use as a divider. So using the formula above the date value 2/4/2015 ends up looking like this:

2015-02-04

For purposes other than sorting you might want a 2-digit year or a month/day value that is not zero padded. In that case this formula:

ToText ({Tale.Date} , 'yy-M-d')

creates a date that looks like this:

15-2- 4

The digits are correct, but if you look closely you will see an extra space in front of the days value. For some reason a single-digit month value does not have a space but a single-digit day value does have a space. This must be a bug since I can’t think of any reason for the difference. And it is a long standing bug since I just tested this in v10 (2004) and v8.5 (2001) and both have the exact same behavior.
If you ever encounter this and want to remove the space from in front of a single digit day, you have to use a replace function like this:

Replace ( ToText ({Tale.Date} , 'yy-M-d') , " " , "" )

A customer had a large cross-tab and he wanted to add some extra headings above the first two columns (The ‘row’ fields).  The row fields don’t normally get headings in a cross-tab but it isn’t difficult to put a couple of text objects above those columns in the empty “notch” above the cross-tabs row names.  The first he had was that the cross-tab was in the report header and spilled over to the next page.  Text objects in the report header would not repeat on subsequent pages.

Now, the simplest way to get something on every page is to put it in a page header.  But page headers don’t start printing until the report header is done, so that wouldn’t help us.  So I told the user to move the cross-tab to the report footer.  This does the same thing as the report header, and when the report footer spills onto multiple pages it includes page headers.

So now we had the text headings on every page, but they were higher then they should be. He wanted them lined up with the other headers above the cross-tab summary fields.  So the last step was to set the page header to “underlay”.  This means that instead of printing above whatever section comes next, it would print superimposed over whatever section comes next.  This made it easy to align things the way he wanted.

All this took less than 15 minutes.  So if you find that you can’t get Crystal Reports to do what want, it might be worth giving me a call.

Crystal allows you to put your groups in order based on the value of each group’s subtotal. This is found in the Group Sort Expert (or the TopN feature in older versions). But the group sort expert in CR can’t rank groups based on a formula – you have to use a subtotal as the ranking field. Sometimes you can convert your formula into a subtotal, but the formulas I get asked about most are ratios. People divide one subtotal by another to produce an average or a ratio and they want to use that as a group ranking field. Unfortunately, you can’t rank groups in CR based on ratios.

One solution is to use a cross-tab, because CR can rank rows of a cross-tab using a ratio. But not everyone can meet their layout requirements using a cross-tab.  This week a customer needed to rank groups based on a ratio and I couldn’t get the layout they wanted with a cross-tab, so we had to go to the last resort.  We calculate the two subtotals in SQL using a “Group By” and then crystal can calculate the ratio as a Continue Reading »
Ranking groups based on a ‘ratio’ formula

Regular expressions, for those of us who don’t see them often, are a group of symbols that allow you to efficiently define a complex string pattern. You can define this pattern with optional characters, varying lengths, and alternate spellings and punctuation. Below is a simple example showing how you would define the pattern found in a formatted height value using feet and inches:

^[0-9]+\'[ ]?([0-9]{1,2}[\"]?|)$

This says that the string must start with a digit. The digit must be followed by a single quote and then an optional space. The string must end with either 1 or 2 digits followed by an optional double quote.

Unfortunately, regular expressions are not a native feature of Crystal syntax. But Ido Millet of Millet software has just added several functions to the CUT Light DLL that make regular expressions available inside Crystal formulas. The functions allow you to test, search and replace strings based on the patterns defined using regular expressions. To read more about CUT Light as well as other DLLs that can add power to your Crystal formulas, you should read my article on User Defined Function Libraries (UFLs).