One of my customers has many complex reports that we have created over the years. In the past few weeks one of the reports started behaving oddly. Opening the report with only 55 records of saved data caused Crystal to freeze for 8 minutes (not responding). Making a simple change in preview, like moving or sizing a field, would cause a similar freeze. The report wasn’t accessing the database so I wasn’t sure what was causing the delay. The report uses several arrays and some looping logic so a glitch in a formula could theoretically cause extra processing time.

As a test, we opened an earlier version of the same report. We found that this version behaved normally.  I took the two versions, exported them both to “Report Definition” format, and then did a text comparison of the two files.  This showed me all the formula changes we made to get to the current version. I added all those same changes to the old report, one at a time, to see if I could identify a specific change that cause the odd behavior.  When I was done the old version was virtually identical to the current version and was still behaving normally.

To confirm they were the same, I also compared the output of the two reports.  This time I exported the report output of both reports to text files. I did another text compare and found the output was also identical. This confirmed that the issue was the result of a glitch in the report file itself, some internal corruption that didn’t prevent the report from running.  Not sure I have seen that before.  Since we now had a working version we just scrapped the faulty report and went forward with report that worked. The main lesson in this, other than sharing the process, is the value of having historical versions of reports. The prior version gave us a window into when the glitch occurred and allowed us to recover without starting from scratch.

Even on simple modifications, I usually rename a report before making changes.  When problems show up I can run the original to determine if the issue was already in the original or was the result of the changes.  For a single update I might just add my initials to the modified report. For long development projects I like to add a sequence number to the end of the file name (01,02,etc.).  Some users prefer to add dates in the file name but I prefer shorter file names that sort cleanly.  The modification date is always visible in the folder.

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 just wrote about installing 64-bit drivers to get CR 2020 to work with Raiser’s Edge legacy MDB files. Here are a few other things I learned:

1) If you modify a Raiser’s Edge report using CR 2020 you are saving it connected to a 64-bit data source. Raiser’s Edge will still run the report just fine using 32-bit connections. Apparently, when RE runs the report automatically replaces any connections in the report with the connection  configured in RE. This was a happy surprise.

2) When running reports from inside other applications you can export the report to .rpt format. This saves the report with data, letting you open it in Crystal Reports and page through the saved data. When I tried this from RE the saved data would generate an error. It appears that RE adds a new formula to the report when it is run.  This formula uses a proprietary custom function that is only available inside the RE environment. This prevents the report’s saved data from opening up in Crystal.

3) You can make most changes using CR 2020 without having to connect to the data at all. You wouldn’t be able to preview the changes in CR, and you wouldn’t be able to add any new tables, but most other changes can be made without a connection.

4) You can still purchase Crystal Reports 2016 and keep using the legacy connections. CR 2016 is still for sale from SAP and simplifies things when you are using legacy data like this. SAP’s web site shows that support for CR 2016 will no longer be available in 2025, but that isn’t an issue for most users. I have many customers using ‘unsupported’ versions, some from 20+ years ago, and the lack of SAP support has never been an issue.

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.

If you put a picture object on a report, that object will start out in full size. Usually I find this is much larger than I need and I have to reduce the size of the object. When you resize an image object using the sizing handles (sides and corners) you can easily change the ratio of the height to the width, which might distort the image. This ratio is called the “aspect ratio” and typically you want to keep the same ratio as in the original image.

One way to change the image size and keep the aspect ratio is to right click on the image, select “Format Graphic” and go to the “Picture” tab. There you can use the “Scaling” percentage to enlarge or reduce the height and width.  If you use the same number in both boxes the image will get bigger or smaller but will keep the aspect ratio of the original.

Today one of my colleagues showed me something he learned from one of his customers. Maybe some of you know this already but it was new to me.  If you select an image object and then hold down your “Shift” key you can change the size of the image and the “Shift” key will lock the aspect ratio.  Using the sizing handles you can make the image bigger or smaller, but the height and width will stay in proportion to each other.

One advantage of this over entering the numbers as described above, is that you can visually choose the size you want. When entering the numbers you have to exit to see the result.  This often means some trial and error to get the image to the size you want.  In testing this tonight I found that after using the “Shift” method the “Scaling” numbers in the “Picture” tab were sometimes slightly off – usually less than 1% different.  If you want them exactly the same you might still use the “Shift” method to get the size to look right visually, then go into the “Picture” tab and adjust the “Scaling” numbers so that they are exactly the same.

Most Crystal parameters are static, meaning they use fixed list of values is stored in the report. But Crystal also allows you to create dynamic parameters. Dynamic parameters generate a list of values from the database each time the report is refreshed. This is useful for lists that change frequently.

Normally you can pull a dynamic parameter directly from a table or view used by the report. But if your parameter is part of a stored procedure you won’t be able to pull the parameter’s list of values from that stored procedure.  That’s because the stored procedure needs a parameter value before it can return any data. And once it returns data it the only it will have is the one value you choose in the parameter. So trying to pull all the possible parameter values from the stored procedure won’t work.

However, you can still provide a dynamic list of values for a stored procedure parameter.  But the list has to come from a separate source. You can choose another table or view that has the list you want to show. Even better, you can write a SQL command that allows you to filter the list to show the exact values you need. This other object (table/view/command) is added to the report along with the Store Procedure.  You use it for the dynamic parameter but make sure you don’t use any fields from that object for anything else in the report.  That force a separate SQL query, which is a sure way to slow the report down.

Once the object is added you can use it in the dynamic parameter to provide the values and the descriptions. If needed you can add a second level to the parameter to make the dynamic parameter into a ‘cascading’ parameter. A cascading parameter has 2 or more levels. You select values for the first level of the cascade and this determines which values appear in the next level.

For more information on using parameters you can download my Advanced course book and the free “Expert’s Guide to Subreports, Parameters and Alerts“. And if you need more help you can always call to schedule a consult.

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.

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.

Anyone looking to test the limits of Crystal Reports might be interested in the stats for the report I was sent recently. It has over 1,200 Report Footer subsections that covered 30+ pages.  There were 2,200 running totals with conditions and 7,500 formula fields. I have never seen anything like it.

Surprisingly, it ran fine – but making changes to the layout was very slow and sometimes crashed Crystal. The customer wanted me to add two more columns. If I followed the existing model it would have taken another 250 running totals and another 750 formulas.

The reason the numbers were so high came down to an odd choice made by the original developers. They created the 30+ pages using only the Report Footer.  Every visible number is a unique formula or a running total. Each running total has a condition that assigns specific account numbers. There is no grouping even though the report has recurring sections that would lend themselves well to grouping.

I convinced the customer to let me rebuild the report from scratch using grouping. I have already redone 18 pages and needed only 100 formulas an no running totals. The account number mapping that was scattered in thousands of running total conditions is now consolidated into one formula, making the report much easier to maintain.

So, if you have a beast of a report that needs taming you might want to let me have a look.

I finally had a use for a feature in Crystal Reports that I never use. It is called the Workbench. It is a place where you can create shortcuts to rpt files, and then organize them into projects. I was working on a report that was similar to some other recent projects and I wanted to keep the example reports handy (but not all open). By adding all the reports to the Workbench I could open and close them as needed without having to hunt for them each time. And these shortcuts didn’t roll off like files in the recently used file list.

To activate this feature you go to the VIEW menu and select “Workbench”. You can right-click to add a new project, or to add reports to an existing project. You can also move report shortcuts from one project to another by dragging them up or down. To open a report you right click on the shortcut and select open. The interface is simple and intuitive.