The Crystal Reports Underground News - Volume 2017.07
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July 2017:
** RPT management utilities for 2017
** Crystal Reports versions over time (2017)
** My library of Crystal Reports materials
** Possible solution the cascading subreport failure
** Refresh prompt when you change pages?
** Let me create your Crystal Reports
** Caution when using "Select Distinct"
** Sorting numbers that come after letters
** Open enrollment Crystal classes in Frederick, MD
** Date range parameters for dates stored as strings
** A switch to turn "stealth" subreports on and off
Gems from the Archives:
Recover a corrupted RPT File 0611
Formula naming tips 0611
RPT management utilities for 2017
I have just updated my comparison of RPT management utilities for 2017. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list now includes 9 tools:
Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
Report Analyzer by Cortex Systems
.rpt Inspector 3 Professional Suite by Software Forces, LLC
Crystal Reports versions over time (2017)
I do a chart each year based on my newsletter subscribers. It shows the versions of Crystal Reports that they were using when they signed up for the newsletter. Over time, it shows which versions have staying power and how quickly new versions take hold. I have included numbers for 2017, even though the year is only about half way there. Those numbers will change in next year's chart when 2016 is complete.
The chart shows that over 40% of my 2017 subscribers are using Crystal Reports XI, which is over 10 years old. I was concerned that some users might really be on CR 2011 and confused the numbers. Those two versions get confused quite often. So I Emailed some of those people to see if that was a factor, but so far I haven't heard from anyone who made that mistake.
I think the main factor is probably related to the runtime engine. For some CR customers, upgrading past XI would be a major undertaking, because all the newer versions are limited to the .NET runtime. Customers built an application around the older runtime engine would have to rebuild their app in order to upgrade to a later version of CR.
My complete library of Crystal Reports materials (still half price):
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.
Possible solution the cascading subreport failure
After my last newsletter went out I heard from a user who had the "cascading" failure problem and received a solution from SAP. She was told to go into each subreport and open "File > Report Options". There she was told to take out the check mark for the feature "Show Preview Panel". She started doing this with all of her subreports and she never saw the problem again.
I suggested this fix to two other users that had experienced the same problem. It solved the problem for one user but not the other. So it may not be a sure fix for everyone, but it certainly is worth trying. Please let me know if this works for you or if it doesn't.
I didn't know, that subreports could have their own "report options", different from the main report. I had never checked but just assumed that the properties in "File > Report Options" applied to the entire container. Always good to learn something new.
Refresh prompt when you change pages?
This strange behavior has nagged at me for a while. I refresh a report that has subreports and preview the first page. When I move to the next page I get a prompt that says:
"Change in record selection formula"
and the choices are "Use Saved Data" or "Refresh Data".
Now it is obvious that I haven't changed the selection formula by clicking the "next page" arrow, and yet I have seen this behavior in several reports. Usually it doesn't cause a problem and I just ignore it.
But today I worked with a customer who wanted to fix that behavior so we took the time to do some experiments. After a few tries it finally dawned on me that there was one way that a formula will change, all by itself, between one subreport and the next. When a formula references the current time it will change slightly as you page through the report. Sure enough, the selection formula in the subreport was limited to activity in the last 15 minutes:
{DateTime.Field} in DateAdd ('n', -15, CurrentDateTime) to CurrentDateTime
To fix the problem we need a single DateTime value to use in all of the subreports. So we decided to calculate a value in the main report and pass it down to the subreports as a parameter. To make sure the value didn't change on each page we wrote a formula to combine the functions DataDate and DataTime like this:
// {@DataDateTimeEnd}
DateTime(DataDate, DataTime)
The advantage of using the DataDateTime is that once the report is refreshed that value doesn't change from one page to the next. The CurrentDateTime function can return different values as the report goes through the pages.
I then calculate a second DateTime that is 15 minutes before the value above:
// {@DataDateTimeBegin}
DateAdd ('n', -15, {@DataDateTimeEnd})
Last, I pass these values down to the subreports as linked parameters and use them in the selection formula like this:
{DateTime.Field} in {?Pm-DataDateTimeBegin} to {?Pm-DataDateTimeEnd}
We still get the last 15 minutes of transactions, but without any prompts.
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.
Caution when using "Select Distinct"
There is one thing you have to watch out for when you use the "Select Distinct Records" setting to eliminate duplicate records. There is often a difference between what you consider a duplicate and what SQL considers a duplicate.
When you activate this feature (Database > Select Distinct Records) Crystal changes the first line of the SQL query from:
Select
to
Select Distinct
This causes the SQL engine to look for duplicates within your raw results. To the SQL engine, duplicates are any records that have the exact same value for every column in the results or every field listed in the SELECT clause of the query. This will be every field used by the report in any way. You can see which fields these are by looking for the check marks next to the field names in the field explorer. If the database finds multiple records with the exact same values for every field, it will eliminate the extras and return only one of the duplicate records.
So here is the risk. Pretend that you have three records in the results that were mostly identical but had one field that was different, say 3 different timestamps. As long as the report doesn't use the timestamp field then the "Select Distinct" will return only 1 of the 3 records. But as soon as someone decides to use that timestamp field, even if they just place it on a section, the SQL will see three unique records. Your report will then start to show all three records, even though you might consider them duplicates.
So if you are using "Select Distinct", make sure you test any changes carefully, especially when they involve adding new fields to the report.
Sorting numbers that come after letters
I recently had a customer ask if I could help him sort part numbers. The problem was that the part numbers start with one or more letters followed by 2 to 7 digit number and sometimes a dash in between. Since the field is a string, and since the numbers vary in length, the sort doesn't work as expected:
APD009
B-1025
B326
B-52
(See this article for why dashes are sometimes ignored in sorting. )
So here are the steps to get these to sort in a meaningful way. First I wrote a formula to determine how many initial characters there are. This is based on a formula I wrote about before that was designed to strip all numbers off of the right end of a string:
//{@Initial Chars}
Local StringVar x := replace ( {ITEM.ITEMNO} & '1' , '-' , '' );
Local NumberVar y := Length( Totext( Val( strReverse( x ) ) ,0,'') );
if length(x) = y then 0 else length(x) - y
Then the remaing steps are:
1) Eliminate the dashes.
2) Strip off the initial letters and convert the remainder to a numeric using Val().
3) Use Totext () to convert the number into a zero-padded string
4) Tack the initial letters back on.
With local variables you can see the process, step by step.
Local StringVar x;
Local NumberVar y;
x := Replace ({ITEM.ITEMNO} , '-', '') ;
y := val (x [ {@Initial Chars} +1 to 99 ]);
x [ 1 to {@Initial Chars} ] & Totext (y, '0000000')
The resulting values would look like this if you placed them on the report. But these would probably only be used for sorting and not displayed.
APD0000009
B0000052
B0000326
B0001025
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my public classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near several major cities and are willing to travel. Call for details.
Date range parameters for dates stored as strings
A few years ago I wrote a blog post about creating a date range parameter when your date field was stored in numeric values (eg. 20170614). I found that the best approach was to create a normal date range parameter, write formulas to convert the begin and end dates into numeric values and then use those two formulas in the selection formula. This gives the user a familiar calendar interface but still passes index eligible values to the SQL query.
Recently I faced a similar problem, but in this case the customer's database stored the dates as strings. They were using a formula to convert the string values from the database into a true date and then they used that in the selection formula. The problem is that this criteria can't be passed to the SQL, so the database ends up sending back ALL of the dates to Crystal. Then Crystal has to select the correct dates locally.
I changed the report to follow the same model as above. I used the existing Date Range parameter but wrote two formulas to convert the begin and end dates from the parameter into corresponding strings. The formulas to convert the parameter values looked like this:
//BeginDate:
Totext (Minimum ({?Date Range}), 'yyyyMMdd')
//EndDate:
Totext (Maximum ({?Date Range}), 'yyyyMMdd')
Then our selection formula was:
{GL_MASTER.LAST_DATE_CHG} in {@BeginDate} to {@EndDate}
The users didn't see any differences when selecting dates, but they certainly saw a difference in the performance of the report.
A switch to turn "stealth" subreports on and off
I wrote a long time ago about the "Stealth" subreport. This is an invisible subreport that runs in the background and provides a value to the main report as a shared variable. But if you have a few of these subreports in the same container report it can be difficult to do troubleshooting. To see what the subreports are returning, you have to go into each of the subreport's key sections to unhide or unsuppress them.
But I received a suggestion from Gordon Portanier that makes it simple to activate and deactivate "stealth" mode in all of the subreports in one place. First you write a formula like this and place it in the report header of the main report:
WhilePrintingRecords;
Shared BooleanVar Stealth;
Stealth := True;
Then you go to each of the sections in the subreport(s) that you want to turn on and off when troubleshooting. Instead of hiding or suppressing those sections, you put in a suppress condition that says:
WhilePrintingRecords;
Shared BooleanVar Stealth;
As long as the first formula isn't modified, those sections should stay suppressed and the subreport should stay invisible. When you want them to appear you comment out the bottom line in the first formula, the one that assigns the value TRUE to the variable. Because Boolean variables are FALSE by default the variable will revert to FALSE and all the sections where you used that condition will now appear.
So, thanks again to Gordon Portanier of Crystalize in Canada for sharing this suggestion.
Gems from the Archives
Recover a corrupted RPT File 0611
Formula naming tips 0611
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2017 by Ken Hamady
All rights reserved - Republishing this material requires written permission