The Crystal Reports Underground News - Volume 2021.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2021:
** Comparison of server-based schedulers (2021)
** Crystal Reports course materials – free download
** My library of Crystal Reports materials
** The wrong way to design a report
** Improved formula for elapsed business hours
** Let me create your Crystal Reports
** Arial 10 text objects get “squished”
** More uses for the Report Explorer
** Individual Training with an expert.
** I finally found a use for the Crystal Reports Workbench
** One way to exceed the table limits in MS Access
Gems from the Archives
Using Autosave in Crystal Reports (2010.11)
Shortcut Keys in Crystal Reports (2010.11)
Comparison of server-based schedulers (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.
Crystal Reports course materials – free download
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:
https://kenhamady.com/faq.shtml
https://kenhamady.com/support/default.html
My online library of Crystal Reports materials
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
The wrong way to design a report
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.
Improved formula for elapsed business hours
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.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Arial 10 text objects get “squished”
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.
or
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.
More uses for the Report Explorer
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.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
I finally found a use for the Crystal Reports Workbench
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.
One way to exceed the table limits in MS Access
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.
Gems from the Archives
Using Autosave in Crystal Reports (2010.11)
Shortcut Keys in Crystal Reports (2010.11)
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2021 by Ken Hamady
All rights reserved - Republishing this material requires written permission