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:
How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.
There are 11 active products in the list again this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.
I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:
SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;
The “100” value can be replaced with whatever number you need.
So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:
- Print multiple labels for a single row based on the quantity value in that row.
- Splitting some orders into multiple records for shared commissions.
- Creating a series of dates from a single date record.
- Separating a multi-value field into separate single value records.
- To repeat all records several times, grouped differently each time.
If you have a task like this and want some help, give me a call.
I have written several times in the past about script errors on the Crystal Reports “Start” page. The message is:
“An error has occurred in the script of this page”
I have seen at least 3 customers experience this problem in the past week so I figure it is time to mention this again. You will see this error when you first open Crystal Reports or when you close a report and Crystal Reports reverts to the “Start” page. Some users think there is a problem with the report they are trying to open, but in truth this error is unrelated to any reports.
The error is usually caused by one of SAP’s servers having a problem. If you want to prevent this error you can follow the directions in this blog post on how to prevent Crystal from requesting an internet connection.
I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it. But it just popped up in one of my reports.
I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport. It tested just fine on the sample data set.
But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors. To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.
Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.