The Crystal Reports Underground News - Volume 2015.07
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July 2015:
** RPT management utilities for 2015
** Crystal Reports versions over time (2015)
** PeopleSoft dropping support for Crystal Reports
** My library of Crystal Reports materials
** Date parameters when your dates are numeric
** Customizing the formula editor
** Let me create your Crystal Reports
** RPT management for the enterprise level
** Is your viewer or scheduler vendor AWOL? Get 20% off from R-Tag
** Automating "Database > Set Location"
** Open enrollment Crystal classes in Frederick, MD
** Formula names that end with spaces
** Export file names forced to lower case
** Gems from the Archives
Distinct Sum when the duplicates are not consecutive (October 2004)
Tricks to using the Mailing Label Wizard (October 2004)
RPT management utilities for 2015
I have just updated my comparison of RPT management utilities for 2015. These are tools that allow you to scan, document, compare and update RPT files. This year the number of products is twice as long as when I last posted about this category. The list now includes:
Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source by R-Tag
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge Enterprise 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 (2015)
Check out my annual chart showing the versions of Crystal Reports that my newsletter subscribers were using when they signed up. It gives you a sense for which versions are being used and how quickly the new versions are catching on. I have included numbers for 2015 so far even though the year is only about half way there
Note that 43% of this year's new subscribers are actively using products which are 10 years old (XI) or even older.
PeopleSoft dropping support for Crystal Reports
If you are using Crystal Reports to read PeopleSoft data you may be in for a nasty surprise soon. Once you upgrade to PeopeTools 8.55 you will no longer be able to use Crystal Reports.
According to this support thread (available to users with support subscriptions) there will be no way to connect to the PeopleSoft security layer in PT8.55. And the process scheduler will no longer recognize Crystal Reports. Users are being told that they will have to replace Crystal Reports with Oracle's BI Publisher tool, but I am being told that BIP can't match the features of Crystal Reports. I am going to ask for some specific examples and will try to incorporate the Oracle BI Publisher into my new comparison of reporting tools.
If this affects you, it might not hurt to let your contacts at PeopleSoft know.
My complete 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 ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Date parameters when your dates are numeric
There are certain software vendors out there that like to store their dates in numeric fields. This includes several SAGE accounting and ERP products. This poses a challenge when a user wants to create a report with a date or date range parameter.
One option is to use numeric parameters. This works fine but the user no longer gets to enter a normal date, and no longer gets a calendar control to use.
Another option is to write a formula to convert the numeric field into an equivalent date. This allows a date parameter in the selection formula, but it will usually make the report very slow. This is because it prevents the date criteria from being passed to WHERE clause in the SQL query. Crystal has to wait until ALL the data is returned from the database before it can convert the numbers into dates and apply the filter.
A better option is to create the parameter as a date data type, and write a formula to convert the date into the corresponding number. If you use the numeric formula field in the selection formula, Crystal will still be able to pass the value calculated to the WHERE clause. This is because the formula is tied to the parameter and can be calculated before the SQL is generated, unlike the first formula which is tied to the database field.
Here is a formula that converts a date parameter into an 8-digit numeric:
Local DateVar x := {?StartDate};
Year (x) * 10000 +
Month (x) * 100 +
day (x)
If you are using a date range you can write two formulas, one for the StartDate and one for the EndDate. They would be exactly like the formula above, except for the first line. The first line of each would be:
//This is the first line of the StartDate formula
Local DateVar x := Minimum( {?DateRange} );
//This is the first line of the EndDate formula
Local DateVar x := Maximum( {?DateRange} );
Customizing the formula editor
I write lots of formulas and that means spending lots of time in the formula editor, staring at text. So I appreciate the fact that we can change the font, size and colors used in the editor to make things easier to read. By default Crystal uses Courier 10 but I prefer Lucida and will often bump the size up to 12. I started doing this when I was teaching so that students could read formulas on the screen. Then I found it helpful to increase the size when I remote into another PC which reduces the size of the screen. Now I find myself making these changes in my own designer, making formula work easier on the eyes.
To get to these settings go to File>Options and click on the "Formula Editor" tab. If you do some experiments and want to go back to the default settings you can use the "Reset All" button at the bottom of the window.
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.
RPT management for the enterprise level
Find it EZ has recently released Dev Surge Enterprise 2015 which is designed to work with SAP's enterprise level software products (BOE Enterprise and CR Server). It does everything that the original Code Search Pro does, but adds a number of features including:
Reading reports contained in Crystal Server 2008 through 2013 repositories
Reading reports contained in BOE XI 3.0 through BOBJ BI 4.1 repositories.
Reporting on the properties of BOE Infoview Crystal Report schedules
Reporting on user security access to BOE Infoview Reports and shortcuts
Reading reports contained in several version control systems (VSS, git, etc)
Doing report comparisons
Dev Surge Enterprise 2015 costs $468 and more information is available on the product web page.
Is your viewer or scheduler vendor AWOL? Get 20% off from R-Tag
There have been reports of Crystal third party vendors that have gone out of business and/or at least stopped responding to calls and email. If you are looking to switch from an AWOL vendor, R-Tag is offering a 20% discount on their products from now until 9/1/2015. Just show them evidence of your product purchase and get a discount on:
- R-Tag Crystal Viewer
- R-Tag Crystal Reports Scheduler
- R-Tag Crystal reports bundle
For more information about viewers or schedulers (including R-Tag) see my annual comparisons:
Comparison of Viewers
Comparison of Desktop Schedulers
Automating "Database > Set Location"
One of my customers has about 50 reports that all point to the same SQL Server database. When they moved their database they needed to "Set Location" for all 50 reports. Normally you can set all the tables from one connection to another in one step by updating the old connection to the new connection. This should work as long as all the tables have the same name.
But in SQL Server, Crystal sees the table names like this:
DatabaseX.dbo.TableZ
So if the name of the database is changed Crystal thinks the table name is different. So when this user tried to "Set Location" at the connection level, she got "invalid object" errors. She learned that the name of the database had been changed when setting up the new server.
When Crystal can't find a matching table name in the new connection, you have to map each table in the report to the corresponding table in the new connection and update them individually. With 6-8 tables in each report the process would take a few hours. I thought we might be able to fool Crystal into just looking at the table portion of the name, but after trying a few things it didn't seem like we were getting anywhere.
So I suggested that she check out the report management utilities on my annual comparison. There happens to be one that is only $99, CR Data Source by R-Tag, and it is designed to do just that one thing – Set Location. I did wonder if it would have trouble with the database change but the customer reported back that it did the job and saved her several hours of tedium.
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 January 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 and dates.
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 Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
Formula names that end with spaces
I have written before about using auto-complete when writing Crystal Reports formulas. I have actually written about it twice because I sometimes forget about my own previous posts.
During a recent remote consult I was writing a new formula. I used auto-complete to insert an existing formula into the new one. My instance of Crystal Reports immediately crashed. I thought it was a fluke so I reopened Crystal and repeated the same steps. Crystal crashed a second time.
So I had the customer send me the report and I opened it on my own computer. I added the same formula using autocomplete and it didn't crash, but I did get an error message saying "Invalid Argument". That happened every time I tried to add that field using auto-complete. So I used the mouse to select the field and that worked fine. That is when I noticed that there was an extra space at the end of the formula name. I have seen one bug with spaces at the beginning of a formula name so I figured the space on the end might be causing the new problem. I tried other formulas and found that auto-complete worked fine as long as there was no space on the end. I guess in some environments that error can cause CR to completely crash.
I can think of no reason to intentionally end a formula name with a space. But when I am duplicating and renaming a series of formulas it is easy to leave a space at the end and not notice. And these spaces won't be very obvious when you look at a list of formulas. So if you get one of these behaviors, this is one more thing to check.
Export file names forced to lower case
This only affects those of you using older versions of Crystal Reports, up through XI. This includes those of you who have the runtime engine from one of these older versions in your application.
In current versions of Crystal you can export a file to a spreadsheet or PDF and type in any file name you want. If you want to use upper case, lower case or mixed case Crystal will save the file exactly as you typed it. But in older versions the file name is converted to lower case regardless of how you type it. I tested this in v10 and v12 and got two different results. I then checked the 'Options' menu and looked through the registry to see if there was any way to turn this feature off. I found nothing.
You would think this would have come up before, but I never noticed it. I guess exported file names, whether in upper or lower case, were never critical to what I was doing. But apparently some people find this frustrating. The only solution is to upgrade to CR 2008 or later.
Gems from the Archives:
Distinct Sum when the duplicates are not consecutive (October 2004)
Tricks to using the Mailing Label Wizard (October 2004)
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 2015 by Ken Hamady
All rights reserved - Republishing this material requires written permission