Sometimes database fields store their data with HTML formatting tags. When you put that field on the report it will display the raw HTML tags. However, if you format the field to use “HTML interpretation” Crystal will try to use the HTML tags to format the text. Crystal doesn’t support ALL HTML tags and properties, but there is a list of the ones that are supported on this page.

Another way to use HTML in Crystal is to include HTML tags in your formulas. For instance I could use a few HTML tags to highlight only a portion of a formula field’s output. Below is the formula for a 3-line address with the City name in bold blue text:

{Customer.Customer Name} & '<br>' &
{Customer.Address1} & "<br>" &
"<b><font color='#00559c'>" &
{Customer.City} &
'</font></b>, ' &
{Customer.Region} & ' ' &
{Customer.Postal Code}

The formula uses 4 different tags:

The tag <br> after the first 2 lines creates a new line.
The tags <b> </b> mark the beginning and end of the bold text.
The tags <font color=’#00559c’> </font> mark the beginning and end of the blue text.

You can look up other color code using a color picker site.

When you first put the formula on the report it looks like the left example below. You then use the menu options “Format > Field” and select the “Paragraph” tab to see the “Text Interpretation” option at the bottom. Select “HTML Text” and the formula will look like the right example below.

Last week I mentioned the U2lwin.DLL. It is a Crystal Reports user function library (UFL) that is currently available in 32-bit but not yet in 64-bit. This means that users who have this dll and who upgrade to CR 2020 will lose some of their formula functions.

This week I checked in with the developer, Bjarke Viksoe.  He has always posted the source code for this dll on his web site. He said it is fine if someone else takes the source code and creates a 64-bit version of the dll. He doesn’t even mind hosting the upgraded file if that helps.

Of course, most of my readers are not application programmers, and neither am I. But I know that a few of you write serious code. If anyone wants to take a crack at this you can download the source code and give it a shot. The current source code uses Microsoft Visual C++ 6.0.

In the meantime, if you upgrade to CR 202o, there is another DLL that you can purchase called Cut Light by Millet Software.  It is one the UFLs in my annual comparison of UFLs. Cut light has at least twice as many functions as U2lwin with many that are the same. So it might have the functions you need, and it comes in both 32-bit and 64-bit.  It isn’t free but it also isn’t very expensive.

Last summer I posted a draft of an improved formula for calculating business hours between two dates. It was meant to replace formula #13 on my formulas page, but I was waiting to do some additional real-world testing. Yesterday I got a good test case when a customer asked me to tweak the original formula to deal with start and end times outside the business day. I cleaned up a few typos and updated my formulas page.

This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you specify:

• The DateTime fields to use for the Begin and End of each event
• The business start and end times assigned to each day of the week
• The list of holiday dates, which you can enter for multiple years

The output is a numeric value in hours as a decimal. If you want to show the value in “HH:MM” format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 3600 since the input for the “Elapsed Time String” formula is seconds.

If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.

One of my readers recently stumbled onto a trick and decided to share it with me. He wanted to display how long his reports took to run. He knew he could tap into the DataDate and DataTime to get the time the report started. His recent realization was that he could put a subreport in the report footer and get a second DataDate/Time at the end of the process. By comparing the two he would know the time it took for the report to run. By using DataDate and DataTime the values wouldn’t change, even if he saved the report with data and opened it a few days later.

The subreport can be completely blank other than this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT

Then in a separate report footer (below the section containing the subreport) we add this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT;

NumberVar TotalSec := DateDiff ('s', DataDate + DataTime, EndDT);

NumberVar H := Truncate(Remainder(TotalSec, 86400)/3600);
NumberVar M := Truncate(Remainder(TotalSec, 3600)/60);
NumberVar S := Remainder(TotalSec , 60) ;

Totext ( H, '0' ) + ':' +
Totext ( M,'00' ) + ':' +
Totext ( S,'00' )

The end result uses one of my web formulas to generate a string formatted as h:mm:ss, but there are other format options.

And thanks to Mark Edwards at DataReport Consulting for sharing his discovery.

Recent versions of Crystal allow you to automatically “Convert Nulls” in specific formulas. The setting is on the formula editor tool bar as a drop down.  You can change “Exceptions for Nulls” to “Convert Null Values to Default values”. Crystal then automatically replaces any null values encountered in the formula with an empty string [""]. This prevents your formulas from returning a blank whenever they encounter a null value.

This works great for formula fields, but not so great in the record selection formula. This problem is that this setting doesn’t get converted into SQL.

Let me give a simple example from the Xtreme sample data. There are 10 records in the customer table that have a null value for Postal Code. If I write a formula field that says:

if IsNull ({Customer.Postal Code})
then "Unknown"
else {Customer.Postal Code}

It will show TRUE for these 10 records. I could also write a formula that says:

if {Customer.Postal Code} = ""
then "Unknown"
else {Customer.Postal Code}

These 10 records will show blank at first, but if I set that formula to “Convert Null to Default”. Then it will also show TRUE for those 10 records. Now lets say I use this as my selection formula:

IsNull ({Customer.Postal Code})

That will pass to the WHERE clause as:

`Customer`.`Postal Code` IS NULL

and it will select those 10 records. But, if I write the selection formula like this:

{Customer.Postal Code} = ""

Even if I tell the selection formula to “Convert Null to Default”, the WHERE clause will be:

`Customer`.`Postal Code` = ''

There are no records that meet that criteria and so no records will be selected.  The key here is that using “Convert Null to Default” works within Crystal, but is not translated to SQL. That is why I avoid using this setting in the record selection formula.

I had a customer today who wanted to write a formula that assigned a numeric value to each Employee. Normally I would write an IF THEN statement like this:

if {table.EmpNO} = 123 then 10 else
if {table.EmpNO} = 124 then 15 else ...

But the list of employees and values was in a spreadsheet with 4000 employee rows. A linked table wasn’t an option so I decided to do something I don’t do very often. I wrote the formula using SELECT CASE instead of IF THEN.

Normally I prefer to use IF THEN because you can use more complex conditions.  I also find that IF THEN reads more naturally. But in cases like this where you have a single field and LOTS of comparison values, the SELECT CASE pattern is more efficient. The pattern below reduced the total character count by 70% of an equivalent IF THEN structure:

Select {table.EmpNO}
case 123:10
case 124:15
...
default : 0

The first time I used SELECT CASE was when I had to translate a large spreadsheet grid into a formula. I wanted the formula structure to look like the spreadsheet so we could visually check the formula by comparing the columns. The spreadsheet grid had 30 rows (Types) and 20 columns (Sizes) so my formula ended up with 30 rows that looked like those below, except that each row had 16 more case instances where you see the ellipsis […]:

if {prod.Type}=1 then(select {prod.Size} case 01:03 case 02:03 case 03:05 case 04:06 ... default:00) else
if {prod.Type}=2 then(select {prod.Size} case 01:02 case 02:03 case 03:04 case 04:05 ... default:00) else

Note that I combined an IF THEN to determine the row (Type) with a SELECT CASE to determine the value for each column(Size).  I found this more manageable and easier to validate than 600 additional IF THEN loops.

My last post talked about Group Selection and showed off some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.

First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:

{Orders.Order Date} - Previous ({Orders.Order Date})

This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?

Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:

{Orders.Order Amount} > Average ({Orders.Order Amount})

Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?

My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.

When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:

{table.state} = 'PA'  and  {table.Amount} > 50

This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.

But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:

Sum ( {table.Amount} , {table.state}) > 5000

When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula.  Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).

Just keep in mind that using Group Selection comes adds a few quirks to your report.

1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.

2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.

3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.

I took another dip into the registry today. I had to fix the formula editor toolbars for a customer (see below).

Two sections would not dock and the field tree on the left could not be made visible. We tried the field tree icon and right-clicks to allow docking. Nothing worked. I tried using the Toolbar Reset check mark in View > Toolbars. That didn’t work either. He even tried uninstalling and reinstalling Crystal, which also didn’t work.

But I remembered from my last post that the toolbars have lots of registry keys which store their settings and positions and I figured these were corrupted. And since re-installing didn’t solve things that meant the bad registry keys weren’t being replaced by the install. So we did another uninstall and then we went into the registry. Most of the Crystal registry entries had survived the uninstall. I was tempted to delete just the node for the formula editor, but we decided to play it safe and delete the entire node named “Crystal Reports”. It is found in this path:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\

We exported this node to a .reg file just in case we needed to restore it and then deleted it.  When we reinstalled Crystal Reports the formula editor went back to normal.

Today I did some more testing because I wasn’t sure if we needed to do the full uninstall.   I closed Crystal, then I went into the registry and exported the “Formula Editor” node. This is found under the “Crystal Reports” node mentioned above.  Once it was exported I deleted the “Formula Editor” node.  I then went into Crystal Reports and worked in the formula editor.  When I checked the registry the “Formula Editor” node had been recreated.  It wasn’t fully populated but it was there.  I went in and did a SQL expression and a custom function and this created more of the keys for that node.  In other words, it may not require a complete re-install to fix this part of the registry.  Crystal appears to create these keys, as needed, when they don’t exist. So if you have windows or toolbars that won’t behave, you can try this approach first.

I recently had complaint from a customer that a numeric field was changing format when moved from one PC to another. The format was fine when run on the developers PC but on the user’s PCs the format would change. I have seen this happen with both numeric and date fields.

The source of the problem is the format option at the top of the list called “System Default”.  If you leave a field formatted with this choice the report will use whatever format is set in the regional setting of the PC where the report is being run. Of course this means that a report might look different when run on another PC. If you don’t want the format to change from one PC to another then you should identify the specific format you want for numeric, currency, date and datetime fields.

Using a specific format should solve this 99% of the time. I have only seen a few instances where a report moved to another environment would have formatting changes despite the format being specified in the report. In those cases the final workaround is to write a formula to display the value. You can use Totext() to convert the field into text that is formatted the way you want. You use the text version for display purposes while using the original field for other functions like sorting and totaling.

Here are some examples:
Totext ({Date.Field}, 'yyyy-MM-dd') //converts a date into a string like 2020-09-21
Totext ({Number.field}, 0,"") // converts a number into a string with no decimals or commas