Archive for May, 2021
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.