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.
Three D Graphics is a software developer that specializes in data-driven graphic software (charts and graphs). They are the vendor that created the charting DLL used in every version of Crystal Reports (and several of Crystal’s competitors as well). They also sell an enhanced DLL called CRChart. It upgrades the standard DLL that comes with Crystal Reports, adding special capabilities and new chart types.
Now that the 64-bit Crystal Reports 2020 is out, a 64-bit version of CRChart has been released. You can still purchase the original (32-bit) version of CRChart if you are using an older version of Crystal. So, if you want your reports to generate waterfall charts, Gannt charts or transparent overlapping area charts you might want to look at the capabilities of CRChart.
Eric Hittinger discovered that the French call their pie charts camembert (as in a wheel of cheese) and decided to see what these charts are called in other languages. He has taken the results and presented them in (what else) a camembert. The twitter thread is getting lots of feedback and he is making updates.
One of my readers recently stumbled onto a trick and decided to share it with me. He wanted to display how long his reports took to run. He knew he could tap into the DataDate and DataTime to get the time the report started. His recent realization was that he could put a subreport in the report footer and get a second DataDate/Time at the end of the process. By comparing the two he would know the time it took for the report to run. By using DataDate and DataTime the values wouldn’t change, even if he saved the report with data and opened it a few days later.
The subreport can be completely blank other than this formula:
Shared DateTimeVar EndDT
Then in a separate report footer (below the section containing the subreport) we add this formula:
Shared DateTimeVar EndDT;
NumberVar TotalSec := DateDiff ('s', DataDate + DataTime, EndDT);
NumberVar H := Truncate(Remainder(TotalSec, 86400)/3600);
NumberVar M := Truncate(Remainder(TotalSec, 3600)/60);
NumberVar S := Remainder(TotalSec , 60) ;
Totext ( H, '0' ) + ':' +
Totext ( M,'00' ) + ':' +
Totext ( S,'00' )
The end result uses one of my web formulas to generate a string formatted as h:mm:ss, but there are other format options.
And thanks to Mark Edwards at DataReport Consulting for sharing his discovery.
I wrote about CR 2020 last summer, and linked to a blog post about things that were different. One of the things mentioned in that article was that CR 2020 no longer supports DAO connections. DAO allows Crystal to connect directly to classic MS Access (.mdb) and classic Excel (.xls) files. The DAO driver came as part of Crystal Reports.
In CR 2020 you have to use OLEDB or ODBC to connect to these files. This requires that you have the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable, which is a separate download.
I just got an Email from Gordon Portanier of ReCrystalize who reminded me that this will affect anyone using the Xtreme sample database which is in .MDB format. This database was originally provided as part of Crystal Reports, but starting with CR 2008 it was no longer included. I use it in my course materials so I still provide it as a download for my students and others.
So if you are using CR 2020, here are the revised instructions for connecting to the Xtreme.MDB from CR 2020. For this to work you have to have already downloaded and installed the redistributable mentioned above.
1) In the Database Expert, open the node called “Create New Connection”
2) Under that open the node called “OLE DB (ADO)”
2) Select the provider “Microsoft Office 12.0 Access Database Engine” and click “next”
3) Choose “Access” from the database type drop-down.
4) Click the ellipse button and locate the folder where you have Xtreme.mdb
5) Double click Xtreme.mdb and click “Finish”
Gordon also mentioned that SAP has published their own document on connecting to MS Access from CR 2020. However, this document says that you can still use the DAO method in addition to ODBC and OLEDB. Neither Gordon nor I could get that approach to work in CR 2020 and both had to use OLE DB in the end.
There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also provide web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.
I have created a page on my blog that lists and compares these products, and I update it every January with info from the vendors. This year the list features 8 products:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Server – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
Bezlio – a SaaS Web viewer
The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (xls) that shows some of the specifics for comparison, including prices.
If you have any feedback to share on these tools I would be happy to hear from you.