A customer Emailed me a report that had a ‘Comment’ field. The field in the application allows the text to be formatted by the user, so they can specify the font, the size, make words bold, etc. The application stores these properties in HTML. The report uses HTML interpretation to display the comments in the appropriate format.

The customer asked if the report could ignore the HTML font and have all of the comments appear in Arial 9. He had tried choosing the font in the report, but with HTML interpretation turned on his choice was ignored. Turning off the HTML interpretation would expose the raw HTML in the output.

My first attempt worked in most cases. I wrote a formula that searched for the font size tag and replaced the number after it with a 9. This worked fine if there was only one font instance in the comment, but then I found comments with multiple instances. I would have had to write a loop to do it as many times as needed.

Then I wondered what would happen if I simply eliminated the words “font-size” from the entire comment. I wrote the following formula:

replace ( {tbl_abc.Comments}, 'font-size:', '' )

The result showed me I did not have to remove the entire tag. HTML is apparently forgiving enough to ignore any tag it doesn’t understand. Removing that key part of the tag meant the HTML engine no longer knew what to change so it skipped that tag. I did the same thing with [font-family] by using a nested replace and that allowed HTML interpretation to still work for other properties but not assign  font properties:

replace(
replace({tbl_History_Master_Newman.DETAILS},'font-size:','')
,'font-family:','')

That allowed me to specify any font or size I wanted in the report.

I found an apparently longstanding bug in Crystal Reports recently. It can be demonstrated by creating a simple report with three formulas (A, B and X).  Formula A can reference any field. Formula B contains formula A. Formula X contains formula B and also uses either the Next() or Previous() function. For a very simple example, use these three formulas:

Formula A:  {table.field}

Formula B:  {@A}

Formula X:  Next ( {@B} )

Place these 3 formulas on the details band of the report, side by side. They should all return the same values, with X values one row ahead.

To see the bug, open Formula A and add or append a literal value to the existing field.  If the field is a string you can use:

{table.field} & ‘AnyText’

If your field is a numeric or a date you can add a number to it:

{table.field} +10

When you save Formula A with this change you will immediately see Formula A values change in preview.  You would expect to see the same change in Formula B, but because of this bug, Formula B will not change.  You can make any change to Formula A that doesn’t cause a refresh/recalculate, and Formula B won’t change – at least not right away.

Now go into Formula B and make a simple change.  For example, hit [Enter] to add a blank first line and save Formula B. Now Formula B displays the change and matches Formula A. The formula updates finally, but that update didn’t happen right away.

To see it again you can go back to Formula A and take out the added value. Again Formula B won’t change until you do something else to make the report recalculate or refresh.

Somehow, using the Previous()/Next() functions in this three-formula series confuses Crystal’s internal formula dependency.  The fields always get back in sync after a refresh, or whenever the report recalculates all the formulas.  But many minor formula changes don’t force the report to recalculate so things can be off for a while.  I discovered this bug while trying to troubleshoot a very complex report.  It took hours to isolate the behavior, confirm that the formulas were correct and identify Crystal as the source of the problem.

I also went back and tested CR v10 (2004) and CR v8.5 (2001) and found the exact same behavior, so this is not a new bug.  I have never noticed it before, but it might explain some of the unexplained gremlin situations I have encountered over the years.

One solution to this is to add “WhilePrintingRecords” to either Formula A or Formula B, but that adds significant limitations to how these formulas can be used.

If you are using Sage 50 (PeachTree) on Windows 11 and your Crystal Report gets the SQL error below, I think I have a fix for you.

The error says:

Failed to retrieve data from the database
Details: ADO Error Code: 0x
Source: Pervasive PSQL OLE DB Provider
Description: [LNA][Pervasive][ODBC Engine Interface]Syntax error
[Database Vendor Code: 268435456]

The error means that Crystal is generating Pervasive SQL that has bad syntax. In the case I faced the same report would work on an older system but not on the Windows 11 system. Other reports were fine in Windows 11 but not this one.

First we were able to narrow it down to a subreport. Then I found that the subreport would work with no selection criteria so we knew it was an error in the WHERE clause. We took out each rule in turn to narrow it down to the Date criteria. The failure only occurred when there was a Date rule in the selection formula.

I took the faulty SQL that Crystal generated and tried running it as a command a few different ways until I figured out the Pervasive SQL was looking for a DateTime value instead of a Date Value. I changed the selection formula to use DateTime values and the syntax error went away. Here is the syntax I used with two date parameters:

{JrnlHdr.TransactionDate} in
DateTime({?FromDate}, Time(0,0,0)) to DateTime({?ToDate} , Time(0,0,0))

If you have a date range parameter you could use:

{JrnlHdr.TransactionDate} in
DateTime(Minimum({?DateRange}), Time(0,0,0)) to
DateTime(Maximum({?DateRange}), Time(0,0,0))

I can’t say for sure this error only occurs in Windows 11 since I found the exact same error in a forum question from 2018 (before Windows 11 was released).

Update – 6/24/2023:

In the forum discussion above a Sage specialist reports that the error is related to CR 2008 SP1, and that upgrading to CR 2008 SP2 solves the problem. You can check your version by looking up the full Crystal Reports version number on the [Help > About] screen.   If your version starts with  12.1 you are on CR 2008 SP1.  If it starts with 12.2 you are on SP2.   If your version doesn’t start with 12, then you aren’t using CR 2008. 

And thanks to John Dierkhising of Bottom Line Accounting for point this out.

I resolved another minor mystery recently after a question from a customer. It has to do with the behavior of variables whenever you open a new tab by doing a drill-down on a group. The same thing can happen when you double click on a subreport in preview mode to open the preview of that subreport.

What I had seen was that some variables would carry to the new tab others would reset to zero/blank. So when a customer explained that this was causing a problem in their report I did some digging and experimenting. I discovered that this problem only occurs with shared variables, which pass to and from subreports. Shared variables will not carry from the preview into the new tab, while default (global) variables carry just fine.

I did some more digging to find a workaround and only found a partial one. I say partial because it will work for group drill-downs but not subreport drill-downs.  If you drill down on a group, typically you will see the Group Header, the Group Footer and the details for that group. The workaround is to make a second copy of the subreport that generates the shared variable, and put it into the group header of that group. To make sure that subreport doesn’t run repeatedly you can either hide the group header, or suppress the subreport with a condition like this one:

DrillDownGroupLevel = 0

Either of these will keep the extra subreport from firing until you do the drill-down. Then one instance of the subreport will run inside the drill-down and the shared variable will carry to the details and group footer inside that drill-down tab.

The downsides are

1) The subreport will have to run again if you do another a drill-down.

2) It won’t work for a subreport because you can’t put one subreport inside another.

If you have any questions about using variables, call to schedule a session.

Added 4/2/2023 – Another solution, if you are willing to use a third party dll, is to use the “Key-Value pair” functionality in CUT Light. This allows you to “Set” and “Get” values that are independent of the report’s variable process, so they aren’t reset by the drill-down.
https://www.milletsoftware.com/CUT_Light_User_Manual/#uflLookupGetEntry()

I was recently reminded of how handy it can be to create a “NULL” value in a formula. I had to use this in two recent situations and thought a description of these two uses would make a great blog post. But, as happens so often, I looked at my past posts and found I had written about both of these before, one in 2016 and one in 2007. So rather than write them up again I am just going to link to those posts with some brief comments.

1) If you ever need to do a distinct count of values that meet a condition. This usually means an IF THEN ELSE formula and if you don’t use a NULL as your ‘ELSE’ your distinct count is likely to be inflated by one.

2) If you want to do a minimum or maximum of values that met a condition. Again you would need an IF THEN ELSE formula and the simplest approach I have found is to use a NULL as your ‘ELSE’ value.

Note 1: Don’t think that skipping the ELSE altogether will generate a NULL value.  It will generate a default value (empty string, zero, etc.).

Note 2:  These NULL formulas are strings, so if you need an else that is a NULL numeric you can use:

Val({@Null})

If your else value needs to be a date you can use:

Date (Val({@Null}),1,1 )

These will still be null values, but they will have the correct data type for your formula.

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

• • • SQL Server
    • MySQL
    • Oracle
    • MS Access
    • Pervasive
    • Progress
    • PostgeSQL
    • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.

One of my customers wanted to highlight all of the empty values in a string column, but still leave the highlighted space empty. I tried using a background color formula but that didn’t work. Then I remembered an article I wrote about display strings  and how you can’t force a display string to show anything when a field was empty. It seems the same thing is true about background colors.

I tried using IsNull, converting nulls to default values and even the highlight expert. The only thing that worked was to replace the field with a formula like this:

If {Table.Field} = ''
then ' ' //single space
else {Table.Field}

The second line isn’t an empty string but is a single space. Because a space is a character, Crystal will format it with a background color.

Timing is everything. In October of 2004 I added a new formula to my website. Formula #22 showed how to round a dollar amount to the nearest nickel, but could also be used to round any number to whatever increment was needed. I have used that formula many times since – including my blog post from 2 weeks ago about rounding a DateTime to the nearest 15 minutes.

Today I heard from Ido Millet of Millet Software who suggested some improvements to that blog formula. His formula used the Crystal Reports MRound() function. I had never used the MRound function so I looked it up to see what it did. It does exactly the same thing as Formula #22.

After a bit of research I found that I mentioned the MRound() function in my January 2005 Newsletter. It was one of a dozen new functions in Crystal Reports XI.  But I somehow missed the fact that it did the same thing as Formula #22, which I had posted 3 months earlier.  Today I updated Formula #22 to use the MRound() function, but I have left the original formula for users who are still using CR versions before XI.

This morning a customer asked me to round a DateTime value to the nearest quarter hour. I had written this logic for them before but we couldn’t find the formula so I started from scratch. When I was done it didn’t look familiar so I suspected I had used a different approach this time.

Later we found the original formula, and it was longer and more complex. That is when I remembered writing it, and saying at the time that there had to be a more elegant approach. I must have been off that day because I couldn’t seem to find the simple solution and just wrote something that worked.  Today it clicked:

Local DateTimeVar DT := {Orders.Ship Date};
Local DateVar D := Date(DT);
Local numbervar Sec := DateDiff('s', D, DT);
DateAdd('s', Mround(Sec, 15*60), D);

And thanks to Ido Millet of Millet Software for suggesting the even better approach to this formula now shown above.

If you want your parameter’s list of values to be pulled from the database you can use a dynamic parameter. But one of the down sides of a dynamic parameter is that you can’t type additional values to include in the list.  A dynamic parameter can only show values pulled from the data source. So if, for instance, you are pulling in a list of products and you want the list to have an “All” option at the top, you can’t simply add the word “All” to the list like you could with a static parameter.

My preferred way for adding an “All” option to a dynamic parameter is to use a SQL command as the source for the dynamic parameter. Using a SQL command gives you several other advantages as well, such as allowing you to filter your the list of values. Here is an example of a SQL command that will add an “All” option to the list of values (incorporating suggestions from MHurwood below):

Select Items.ID, Items.Desc
From Items
Where Items.Status = ‘A’


UNION ALL


Select ‘…All’, ‘…All’

The part above the UNION creates a list of all the active items, showing both the ID and the description of the items. The part below the UNION adds one row to the results of the query with the “All” option. Notice that “All” entry has several periods in front of it. This is one way to sort that value to the top of the list. You can use this method to add several values to your dynamic list, if needed.

Note that you wan to avoid using the fields from this command in other parts of the report. It should be used only for the dynamic parameter.

One of my colleagues, Angela Meharg of Datisfy, reminded me that you can use optional parameters to do something similar.  Instead of explicitly selecting a word like “All”, you can skip over the parameter.  Then you can program the selection formula to say that when the users doesn’t select a value they get all values.  The formula would look something like this in the Crystal selection formula:

and (if not (HasValue({?Items})) then True else {Table.Item} = {?Items})

In English this means, if there are no values in the items parameter, then every record qualifies. Otherwise the items that qualify are the ones that match the parameter.

If you have trouble with one of these options, you can schedule a short consult and I can give you a hand.