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.
Some of my customers are transitioning from 32-bit versions of Crystal Reports to Crystal Reports 2020, which is 64-bit. This creates some issues since ODBC/OLEDB drivers are either 32-bit or 64-bit. Switching to CR 2020 requires different drivers and (if using ODBC) different ODBC Data Source Name (DSN) entries.
The same applies to UFL function DLL files. For instance the free ufl named u2lwin32.dll only comes in a 32-bit version. Some commercial ones come in both 32-bit and 64-bit editions. Switching to CR 2020 requires that you use 64-bit UFL libraries.
The good news is that RPT files in CR 2020 are backward compatible with earlier versions, probably back to Crystal Reports v9. So you can modify and run a single RPT file in both CR2020 and earlier versions. But you do need different connectivity. What my customers are doing is naming the connections the same in both the 32 and 64 bit environment. That way users in both environments can run the same report without modification.
If you run into any strange behaviors in using CR 2020 or in the transition, please let me know and I will share with others.
I have just updated my comparison of server-based scheduling tools for 2021. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.
There are 11 products on the list this year and a few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.
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.
I just figured out how to solve an annoying Crystal bug that I have seen, on and off, for years. This bug has to do with text objects that are added using the default font of Arial 10. I don’t see this issue every day but it happens regularly with some customers and even on my own machine.
What happens is that a new text object gets horizontally squished. The characters are sized correctly, but the horizontal space from one character to the next is only about 10% of what it should be, so the characters overlap. You don’t see the problem while you are typing the text (Before), but as soon as you click outside the text object you see the squish (After).
Today I found a report that had this issue, and took the time to experiment. Here are some factors that might contribute to the problem:
- The report is opened on a PC that uses a different printer driver than the design PC.
- The report is opened on a PC that has a different version of Crystal.
- Your default font for text objects is set to Arial 10 (and possibly other fonts).
If you add a new text object to the report under these conditions, you might see the squish. For a long time I would have to change the font, change the style (to bold) or change the font size to correct the problem. But now I know two quick ways to eliminate the squish.
1) Go into File > Page Setup, don’t change anything, and click OK.
2) Save the report, close it and reopen it.
Either of these should get the font calculations back in line with the new printer driver.
If anyone runs into this situation (or something similar) and the above methods don’t work, please let me know.
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.
I work with many different CR users. It seems that whenever I open the Report Explorer view in Crystal Reports, the user is a bit surprised. I get the impression that not many people use or know about this feature. I wrote about it once before (a decade ago) but since then I have found two more uses that I tap into regularly.
1) Selecting one of several superimposed objects.
One report I created for an educational assessment company had 4 superimposed picture objects in different colors. They were all in the same spot, but had suppress conditions so that only one would appear at a time. Trying to select a specific one of these objects is a challenge. But when you open the Report Explorer for that section, the objects are listed separately. You can select the object in the list of the Report Explorer and it behaves the same as when it is selected in design mode. You can also right-click on the object in the list and get all the same options you would get if you right-clicked the object in design mode.
2) Locating subreports
I recently had a very crowded report and was having trouble with a shared variable, that came from a subreport. The trouble was that the subreport was small and I was having trouble finding it. One of the features of the report explorer is that you can decide which of three object categories to have it show (Grids and Subreports / Fields / Graphic Objects). By turning off Fields and Graphic Objects the list showed only Grids (cross-tabs) and Subreports. This made the one lone subreport simple to find.
So if you haven’t ever used the Report Explorer, go into the View menu to activate it. You might find it useful.
I have decided to make my Intro course materials available to download, for free. There are several reasons behind this decision:
1) Crystal use is slowly declining. This might encourage use of the product in a small way.
2) Classroom training was becoming rare, and stopped completely by the pandemic.
3) My work is now mostly consulting, so there is less call for course materials.
4) I hope for some good karma.
So you are welcome to download the materials and use them. Share them with your friends. Please do not modify them or try to sell them.
Note that as a consultant, people often pay me to help them use or learn Crystal. You can, too. So if you have questions about Crystal Reports I am happy to schedule a short consult for you. This is explained further on these links: