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.

A few years ago I wrote about a limit on the number of tables you can use when connecting to MS Access. The limit is 31.  If you add table #32 to a report it will crash with no error message.  Even using 32 tables in an MS Access query will generate an error message. I see this occasionally when dealing with reports for Raiser’s Edge.

Today we needed table #32 and I found a way to get it. I made a duplicate copy of the MDB and connected to the first instance to get 30 tables. Then I connected to the duplicate MDB to get the other two tables. I selected those two because their joins made up a separate branch from the other 30.

This worked because Crystal does two separate queries, one for each MDB, and then merges the two result sets locally. Crossing connections a last resort, and is never very efficient.  The report was a bit slower but at least it would run.

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.