The Crystal Reports Underground News - Volume 2021.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2021:
** Comparison of desktop-based schedulers (2021)
** CRChart for Crystal Reports 2020
** My library of Crystal Reports materials
** An error has occurred in the script of this page
** What “pie charts” are called in other languages
** Let me create your Crystal Reports
** Inflation table query for Oracle
** Displaying the report processing time
** Individual Training with an expert.
** Connecting to the Xtreme sample data from CR 2020
** Max rows/columns of a cross-tab
Gems from the Archives
Setting the RGB color of multiple fields at the same time (2010.09)
Mixing single values and ranges in the select expert (2010.09)
Comparison of desktop-based schedulers (2021)
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.
CRChart for Crystal Reports 2020
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.
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.
An error has occurred in the script of this page
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.
What “pie charts” are called in other languages
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.
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.
Inflation table query for Oracle
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.
Displaying the report processing time
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:
WhilePrintingRecords;
Shared DateTimeVar EndDT
Then in a separate report footer (below the section containing the subreport) we add this formula:
WhilePrintingRecords;
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.
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.
Connecting to the Xtreme sample data from CR 2020
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.
Max rows/columns of a cross-tab
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.
Gems from the Archives
Setting the RGB color of multiple fields at the same time (2010.09)
Mixing single values and ranges in the select expert (2010.09)
Removal instructions
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