The Crystal Reports Underground News - Volume 2016.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2016:
** Crystal Reports 2016 for sale by mistake?
** Server-based scheduler comparison (2016)
** My library of Crystal Reports materials
** Crystal Reports and duplex printing
** Suppress the Page Header on the last pages
** Let me create your Crystal Reports
** DateDiff gives some surprising results
** Custom functions in Crystal Reports
** Open enrollment Crystal classes in Frederick, MD
** Another option for sharing arrays
** Getting MAS 90 to run reports with SQL commands
** Bug in the Crystal Reports "Alert" feature
Gems from the Archives:
"New Page After" on group 2 orphans group footer 1 (July 2005)
Chart on a Parameter Value (September 2005)
Crystal Reports 2016 for sale by mistake?
I just purchased CR 2016, but it might just be a fluke of the SAP store.
One of my customers needs to buy a new copy of Crystal Reports. They asked if the should buy CR 2013 or CR 2016. I told them that CR 2016 was not yet released and they said it was listed in the SAP store. So I went to the Crystal Reports product page and hit "buy SAP Crystal Reports". It took me to CR 2013. But then I clicked the product name to see the store listing for CR 2013. On that page, in the list of "Other Versions" of the product, was a link to "SAP Crystal Reports 2016". That link allowed me to put Crystal Reports 2016 into my cart and purchase it.
I already have CR 2008 so I purchased the upgrade to CR 2016. I received a download link, downloaded the software and started to install it. But the download link didn't come with an installation key. As a long shot I tried the installation key from my CR 2008 license, but that didn't work. So now I am waiting for a support response, on a long holiday weekend.
I have a suspicion that the store prepared the purchase page to be ready for the upcoming release, but didn't mean to make it visible. That would explain why the product page points to CR 2013. Also, no one else is selling or even mentioning CR 2016. SAP usually offers a big promotional discount on the current versions just before the new version is released and I haven't seen that.
But despite all of that that the SAP store has CR 2016 listed, and even flagged as a "popular" item. I was tempted to buy the full license just to see if that would send me an install key. But the SAP store has a "no refund/no credit" policy, so I will just be patient and let SAP figure it all out.
If anyone else out there has successfully purchased and installed CR 2016, or has more information on this, please let me know.
Server-based scheduler comparison (2016)
I have just updated my comparison of server-based scheduling tools for 2016. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.
There are 10 products on the list this year and a few few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.
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.
Crystal Reports and duplex printing
This has come up twice in recent weeks so it is time to make it a blog article.
The first question is usually this: Can Crystal print on both sides of the paper? This is called "duplex" printing and requires a printer that can print on both sides of a piece of paper. Any program that can print can be used to print in duplex, because duplex is controlled by the printer, not by the program. In other words when Crystal prints 2 pages it is up to the printer to decide if page 2 is on the back of page 1 or if it is on a separate piece of paper. Crystal won't know or care.
But the second question is a Crystal question. How do I make sure every document starts on an 'odd' page when I am printing in duplex?
Say I am printing a series of documents, like purchase orders, in duplex. If the first PO is 3 pages long the next PO would start on the back of the last page of the current PO. So I would need every every PO that has an odd number of pages to generate an extra blank page. That way the next PO can start on it's own piece of paper.
Here are the steps:
1) Take the group footer that marks the end of the document (e.g. the PO ) and split it into two sections, A and B, leaving the B section empty.
2) Go into the section expert for the B section and check "New Page After"
3) Click the expression button next to "New Page Before" and put in the following:
Remainder (PageNumber, 2 ) = 1
4) If you need to reset the page number for each group, put in that checkmark in this B section as well.
5) Click the expression button next to the "Suppress" for this section and put in the following:
Remainder (PageNumber, 2) = 1 and not OnLastRecord
I have read several forum threads on this but haven't seen anyone that mentions step 5. That is because the problem that step 5 solves only shows up in rare cases. But lets say that the details and GF1A fill 2 pages exactly to the bottom of the page. Even if GF1B is very small it will create a new page all by itself and that will be an odd page. In this case the next group will start on an even page. So I have found that by adding the condition in step 5 that rare problem won't occur.
Suppress the Page Header on the last pages
I have run into several situations recently where I needed to have one Page Header for most of the pages but a different page header at the end of the report. It may be that you have an object in the report footer that might add several pages after the regular report pages. This could be a subreport, a crosstab or a large block of legal boilerplate. When this happens the Page Header for those pages may need to be different. For instance you may need to turn off the column headings for those extra pages or switch to a different heading on the page. So you need some type of flag to tell the report when to switch.
Sometimes this is as simple as suppressing the Page Header with the function OnLastRecord. This probably works 97% of the time. But there are times when this approach misfires. For instance when the last record of the report is the only record on the last page. Your Page Header will be OnLastRecord but you will still want the headings on that page over the last record's data. So here is a more complicated but more reliable method.
Insert a new subsection below your last Report Footer section
Suppress this new subsection of the Report Footer.
Create a formula field that says this:
//{@Set RFPassed}
WhilePrintingRecords;
BooleanVar RFPassed := True
Place this formula in the suppressed Report Footer.
Use the following suppress formula for the Page Header to be suppressed:
WhilePrintingRecords;
BooleanVar RFPassed
When the report gets to the suppressed Report Footer it triggers the formula which changes the variable from True to False. Every Page Header before that point will have seen the variable as False and won't suppress the section. But after that point the Page Header will see the variable as True and will suppress the section.
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.
DateDiff gives some surprising results
I recently had a user who asked how he could report a bug in Crystal Reports. He was using the DateDiff function and thought it was behaving strangely. He gave me the following example to get the number of hours (h) between two DateTime values:
DateDiff ('h', {@Begin}, {@End} )
He was surprised to see that:
2:10pm to 2:50pm (40 minutes) would return a 0 hour difference while
2:50pm to 3:10pm (20 minutes) would return a 1 hour difference.
I explained that this isn't a bug, but it is exactly what DateDiff was designed to do. This is true in Crystal Reports and pretty much any other language that uses DateDiff. When you ask for the difference in hours DateDiff truncates both DateTime values to the hour and ignores everything after that. Then it subtracts one truncated value from the other. Here is an example copied directly from the CR "Help" that explains the same thing for doing a DateDiff with years (yyyy):
—————————————————-
DateDiff ("yyyy", #12/31/1999#, #1/1/2000#)
Returns 1 (a 1 year difference), even though there is only a 1 day difference between the dates.
DateDiff ("yyyy", #1/1/1999#, #12/31/1999#)
Returns 0 (a 0 year difference), even though there is a 364 day difference.
—————————————————–
So, if you want to be more precise than that you need to specify a more precise interval. For instance, when I want to do hours or minutes I will usually do a DateDiff in seconds and either divide by 60 or 3600 to get minutes or hours.
Custom functions in Crystal Reports
Crystal Reports introduced custom functions back in version 9, but I have only recently started using them in production. To make them worthwhile you have to have reports that do the same type of calculation many times but with different fields. You also have to be willing to move the function from one report and add it to another whenever you need it in a different report.
But I have one very active customer who needs very complex reports with hundreds of formulas. In these reports we found ourselves doing the same two things over and over again:
1) Calculating an elapsed time number (seconds or minutes) and then converting that into a string (hh:mm).
2) Converting a time string (hh:mm) back into a number for use in other calculations.
After writing dozens of similar formulas (and sometimes forgetting an important piece of the logic) we decided to create two custom functions. So here are the two examples of the custom functions described above. You can paste them into your reports if you have a use for them:
//SecondsToTimeString:
Function (numberVar TotalSec)
NumberVar Hours := Truncate (Abs( TotalSec / 3600)) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
(if Totalsec < 0 then '-' else '') +
Totext ( Hours , '00' ) + ':' +
Totext ( Abs(Minutes) , '00' )
//TimeStringToMinutes
Function (StringVar TimeString)
Val (TimeString ) *60
+ (Val (Right (TimeString,2)) *
(if TimeString startswith '-' then -1 else 1) )
To use one of these functions you first have to add them as "Report Custom Functions". You will find these within the formula editor, just above the list of formulas in the side panel. You right click on the category, select NEW and give the new function a name. Then you paste in one of the functions above, save and close. Once they are saved you can edit any formula field to confirm that it is in the function list. There should be a new category of functions called "Custom Functions" and the new function should be listed.
These new functions can be used in any formula. The first needs a number argument (in seconds). The second needs a string formatted as a positive or negative time string (hh:mm or -hh:mm). For example:
TimeString to Minutes ( {String.Field} )
SecondsToTimeSTring ({Number.Field})
Just remember that when you add a custom function like this it adds the function to the one report that is open. If you want to use it in other reports you have to create the function in each of those reports. The only way to make a function available to all reports in your environment is to compile the function as a DLL and place it in one of Crystal's library folders. These are called a UFLs or User Function Libraries. There are several of these libraries for sale and some that are free. These are listed and explained in my annual review of UFLs.
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 July 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.
Another option for sharing arrays
I recently wrote about a method for sharing an array between a main report and a subreport. But that method is only practical under certain conditions. Now, Ido Millet of Millet Software has given us another method by adding three functions to his "Cut Light" UFL. These functions allow you to store and retrieve "key-value pairs" in memory. You store them using one function and retrieve them using another. The key-value pairs can be stored and retrieved anywhere within a report including within any subreports.
The advantage of this method is that it eliminates the slow looping logic you need to search for a value in a large array. And while Crystal arrays can hold a maximum of 1000 elements, there is no limit to the number of key-value pairs you can store.
The functions are called:
uflLookupAddEntry()
uflLookupGetEntry()
uflLookupResetEntries()
The first function adds a key pair, the second retrieves the value based on the key, and the third is used to clear the memory space at the beginning of each report run. I did a test with 2000 key-pairs and found it very fast.
Getting MAS 90 to run reports with SQL commands
I recently learned how to fool MAS 90 into running a custom report based on a SQL command. This might only apply to older versions but I thought it was worth sharing just in case.
I created a report for a customer based on a command. It ran fine within Crystal but when added as a custom report in the MAS custom reports folder it generated an error. We learned that MAS does a conversion step with custom reports and has to verify that all the tables exist. Our command obviously wouldn't be found as a table so the conversion would fail.
After some web searching I found an old forum thread that described a workaround for using external tables in a custom report. A few experiments confirmed that the same technique works for reports based on a SQL command.
The steps were to:
1) Use a similar report that uses only standard tables, and let SAGE do the conversion step on that report.
2) Open the real report (the one that uses a command) and go to File > Summary Info.
3) Modify the "keywords" section to say "Converted to version 4.40" (or your version) and save the real report.
4) Replace the first report that Sage converted with the real one.
Apparently SAGE marks the converted reports in the keywords section and skips that step if it has been done. So by adding that phrase we cause SAGE to skip the conversion, and the report seems to run fine.
My customer is using an MAS 90 4.4 which was replaced with 4.5 in 2012, but maybe this will help someone else. And if this works for you in a newer version, please let me know.
Bug in the Crystal Reports "Alert" feature
The alert feature in Crystal Reports allows you to define a trigger condition in your report. Then when you run the report Crystal will check for that condition throughout the report. If that condition occurs, Crystal will pop up a message box with text that you define, telling you that your trigger condition has been met. It will even allow you to generate a list of the records or groups that triggered the alert.
Normally you can create two types of conditions. One is at the record level using a database field:
{Orders.Amount} > 8000
and the other is at the group level using a subtotal:
Sum({Orders.Amount}, {Customer.Name}) > 8000
You can also create more complex alerts that combine both record and group level conditions, but this is where the bug pops up. If you create a condition that combines 3 or more record level rules and one or more group level rules Crystal will accept that condition formula, but only on a new alert. If you try to modify that alert logic Crystal will give you one of the following error messages:
"One of the previous fields is not constant over group level of this field"
or
"This field is not constant over the group that the Alert applies to"
But if you take that same modified condition and use it in a NEW alert, it will work just fine. So if you have to maintain alerts that are triggered by complex conditions, you might want to keep this in mind.
And if you want to see how you can use alerts to have your reports "self-report" triggered conditions, give me a call to schedule an appointment.
Gems from the Archives:
"New Page After" on group 2 orphans group footer 1 (July 2005)
Chart on a Parameter Value (September 2005)
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 2016 by Ken Hamady
All rights reserved - Republishing this material requires written permission