It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is CUT Light, where Millet Software has added a handful of new functions. You will find these highlighted in the matrix. If you need help deploying one of these functions in a project let me know.

If you create reports for others, you might find a use for this technique. It changes a report automatically based on the folder where the report is stored. I use it to show a message in preview while in my design environment and have the message automatically suppress when the report is moved to production.

Example:
I have a handful of customers that still use v8.5 which is almost 15 years old. Unlike all versions of CR from v9 forward, there is no way to save changes done in a later version of CR back into v8.5. So I have to maintain a copy of v8.5 just to use for these customers. When I receive a report to change the version doesn’t usually matter, so I don’t always remember to check and see if the customer still uses v8.5. I might do quite a bit of work on the report in my current version before I check. Only when I go to save the file does CR warns me that the report is from the older version of CR (if I do “Save As” it won’t warn me at all).  At that point I have to reopen the original report in v8.5 and make all the same changes a second time.

So I just came up with a way to add a warning to reports that I work on in v8.5. This warning will appear in my preview window as soon as I open the report in my environment, but will never appear to the customer. It works because I use each customer’s account number as a folder name to store all of their reports. This helps because Crystal formulas can read the path and file name of the report being run. So when I work on a v8.5 report I add logic to have it check for a specific folder to control the appearance of the warning.

I create a new Report Header section that isn’t otherwise used by the report.
I place a text object in this new section that has a large bold red warning “>>> use v8.5 <<<”
I create a section suppression formula that says something like:

not "\1234\" in FileName

The 4-digit number is a customer account number which is where the rpt is stored in my environment. It is unlikely that the report will be in a folder with that same name in the customer’s environment. So the section will suppress in their environment but show up automatically in mine the next time the report is opened.

I found on surprise while testing this.  The FileName function is read before the selection formula is processed, so you can use the folder name to change your filter.  And it is even processed early enough to affect the SQL that CR automatically generates, so it doesn’t have to slow down the report.  If you discover any creative uses for this, please share.

One of my customers sent me a question about an error message they got when they went to open a report. I recognized it as an error I had seen recently on a customer’s workstation. Then today I closed and reopened Crystal and got the same error message. For those of you who open reports by double clicking the RPT file it might seem that there is a problem with report, but this is a problem you will see anytime the CR designer starts.

I have written before about how the Crystal Reports start page will request internet access and try to load dynamic content (ie ads).   I showed users how to prevent the start page from requesting internet access by renaming a few files.  What we are seeing today is that there is apparently a problem with some java script in the way that dynamic content is loaded.  Once SAP fixes the problem the error should go away.  In the meantime if you just stop the script the reports should load and run fine.  If you don’t want the start page to load web content you can use the method in the post mentioned above.

In 2012, I wrote about a tool that allows you to update the data source in all the reports in a folder. It is called CR Data Source and is put out by R-Tag. The original version would only work on reports that use a single connection, which is the majority of the reports that I do. But R-Tag has just updated this tool so that it can deal with reports that have multiple connections. It will still only work with OLEDB and ODBC connections. If you want to learn more about the tool, you can check out a short video on the CR Data Source web page.

Jared uses a web-based forms application that collects and stores signature images as Base64-encoded PNG files. The Base64 data is stored in a nvarchar(MAX) column in a SQL Server database. He was looking for a way to render the data as images in his Crystal Report and couldn’t find a way to put all the pieces together and contacted me.

As it happens, I have a report that I created that reports on Email messages in my Goldmine database. The body of those messages is stored in my database using a binary field (data type ‘image’). To display the message body on a report I had to write a SQL Command to convert this binary into text.

CONVERT(VARCHAR(8000),CONVERT(VARBINARY(8000),RFC822)) AS message

This converts the first 8000 characters of the binary field called “RFC822” back into a readable text column called “message”. It sounded to me like Jared had the opposite problem with an image stored as Continue Reading »
Working with binary fields in SQL