The Crystal Reports Underground News - Volume 2016.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for November 2016:
** Crystal Reports formula function libraries (2016)
** Save 10% on Crystal Reports
** My library of Crystal Reports materials
** Automatically translate report text to another language
** Using Crystal's pre-programmed date ranges
** Let me create your Crystal Reports
** Custom date format in charts
** Sorting on a memo field.
** Open enrollment Crystal classes in Frederick, MD
** Date vs DateSerial
** Slope, intercept and correlation of x-y coordinates
Gems from the Archives
Copying Objects Within a Report (June 2003)
Copying Objects to Another Report (June 2003)
Crystal Reports formula function libraries (2016)
It is time for my annual comparison of formula function libraries. If you aren't familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:
1) Carry values from today's report to tomorrow's report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.
If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:
Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)
The only product that has changed since last year is Cut Light, which now includes a handful of new functions:
Convert time strings (hh:mm) into seconds
Resize images
Convert HTML or a URL into an image
Set/Get/Reset lookup values in memory arrays
If you need help deploying one of these functions in a project let me know.
Save 10% on Crystal Reports
For a limited time, you can save 10% on any Crystal solutions product purchased individually. Just visit
crystalreports.com to buy new licenses or upgrade from an older version. Then use the promo code:
TENCRYSTAL
And if you want to purchase one NUL (named user license) of the following three products:
SAP Crystal Reports
SAP Crystal Dashboard Design (Excelsius)
SAP Crystal Server
You can click here to purchase the entire bundle for $495 (a savings of 43%). Of course there isn't much to be done with one NUL of CR server, and I don't use Excelsius myself. But if you were planning to use these products this is an inexpensive way to get them.
And if these links or promo code stop working, I would call the SAP Sales number and ask for the discounts. It never hurts to ask.
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.
Automatically translate report text to another language
Supporting multiple languages in Crystal Reports just became much easier. Ido Millet of Millet Software has added a new translation feature to DataLink Viewer. This allows reports to automatically translate all static text objects from one language to another. The feature taps into Google Translate, so it supports any of the 100 or so languages that Google supports. Automated translation is not perfect, but I have found that Google Translate is amazingly accurate for an automated tool, and is getting better all the time.
To take advantage of this feature the report has to be run with web access. It also needs a Google account and a Google API key. The Google API key is not free but it is only $20 for 1 million translated characters. (DLV only translates each text object once per report, even if it appears on multiple pages.)
If you want to translate the dynamic content of your reports (fields and formulas) then you can add Millet Software's Cut Light dll to your environment. This adds a formula function that can pass text from fields and formulas to the same API for translation.
Here is a link to the new section of DLV user manual which explains how it all works and a short video demonstration.
If you have a use for this I am sure that Ido would be happy to hear from you.
Using Crystal's pre-programmed date ranges
One of my customers learned a new trick from me today. They wanted a report to automatically select the data for the prior month. They didn't realized that Crystal has a dedicated function for that and you can use it in both the select expert or in the selection formula. So I figured I would share this, since there may be others that have never seen this function in Crystal.
Go into your select expert and add a rule using any field that is a Date or DateTime. Then in the comparison list look toward the bottom for the option "is in the period". When you select this option the next drop-down list will automatically populate with 25 pre-programmed functions. You will find items like "LastFullWeek", "LastFullMonth" and "YearToDate". These periods are automatically calculated each time you run the report, based on today's date.
So say you want a report to automatically select the records for the prior month without having to enter the date range or change the report each time. You use the LastFullMonth option in the select expert, or you could do the equivalent, like this, in the selection formula:
{Date.Field} in LastFullMonth
One note, Crystal calculates all of these periods based on the print date which is normally the system date of your computer. But Crystal also allows you to override the system date for any report. Just go to [Report > Set Print Date] and you can pick another date. Just don't forget to reset the date before you save the report or Crystal will continue to use that same date (for this report) until it is reset.
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.
Custom date format in charts
I periodically hear from Adam Butt of APB reports (Norway), usually when he stumbles on a neat Crystal Reports trick. This month he wrote to share a method for controlling the format of dates in charts.
When you do a "group" chart, Crystal creates a bar for each group in the report. The label under the bar will be the same as the group name. If you group on a date (e.g. by day or by week) the corresponding date will appear under each bar. But unlike date fields on the report, there is no format option for dates within a chart. To change the date format you have to use the properties in the group expert. The options tab in the group expert allows you to create a customized formula for the group name. And with Totext you can determine exactly how the date should appear on the report, in the group tree and also in the chart labels of a group chart.
But if you do an advanced chart, which means the chart is grouped by a field that is not an existing report group, this option is not available. You are stuck with the default date format that CR chooses. And advanced charts are handy because you can use any field for grouping the chart without having to match the groups in the report. Some users make the chart a "numeric axis" chart which gives them some date options and formatting control, but haven't had good results from these charts.
Fortunately, Adam found a clever way to get the flexibility of an advanced chart while still having the custom group name ability of a group chart. He uses a cross-tab and creates the chart from the cross-tab. Cross-tabs groupings are independent of the groups of the report (just like advanced charts). And once you create a row group or column group in a cross-tab they both have the same group name property that you find in the groups in the report. Once the chart is created the section containing the cross-tab can be suppressed so it doesn't need to take up space on the report.
So thanks to Adam for sending in the tip. And if you need help getting CR to create the charts you want, give me a call.
Sorting on a memo field
A customer wanted to sort on one of the fields in his report. But when he went into the Record Sort Expert that field was not shown. So he sent me the report to troubleshoot. I noticed that the data type of that fields was Memo, which is a data type that has an unlimited length. Crystal can't sort on a memo field regardless of the number of characters that are actually stored in the field. Crystal also can't group on, or summarize a memo field.
But in this case, as in many cases, the memo field wasn't being used to store paragraphs of text. It was being used to store a short description. So I showed him how to create a formula that returned only the first 40 characters of the memo field, like this:
{Order.Notes} [ 1 to 40 ]
This formula was more than wide enough to include all of the short description. And because it has a fixed length it can be used for sorting, grouping or summary fields. So now he can sort on his short description.
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.
Date vs DateSerial
There are many functions in Crystal with overlapping capabilities, like Val() and ToNumber(). I have always thought of Date and DateSerial as identical functions that both allow you to specify a literal date in a formula by specifying the year, month and day as numbers like this:
Date (2016,11,24)
DateSerial (2016,11,24)
The three numbers can either be literal values, fields or calculations like this:
Date ( {table.Year}, {table.Month} + 2 , 3 )
This will create a date value by taking the Year value from the table, adding two months to the Month value from the table and then taking the 3rd day of that month.
But I just discovered (via a post by AndyMc in Tek-Tips.com) an important difference between the two functions, which is how each function would respond if the value in the field {table.month} was 12. The Date() function would generate an error because 12 + 2 = 14, which is an invalid month. The Date() function requires that the month number be from 1 to 12. Also, the day number has to be between 1 and 31 and has to be a number valid within that month.
But the DateSerial() function doesn't have this limitation. If the formula above used DateSerial(), adding 2 months to month 12 would automatically move forward to next February. You can also give DateSerial() a day number that is more than 31 and it will move automatically to the next month.
So the The following expression would return 5/1/2016:
DateSerial (2016, 4, 31)
But using Date() would generate an error:
Date (2016, 4, 31)
Slope, intercept and correlation of x-y coordinates
I was recently asked to convert an Excel spreadsheet into a Crystal Report. I found that the spreadsheet used some Excel statistical functions that don't exist in Crystal, so I had to replicate those in Crystal syntax. The functions calculated the slope, intercept and correlation of a series x-y coordinates. Once I had them working I thought I would post them here. That way I can find them the next time I need them.
To use these you need to be able to create two arrays in Crystal. One for all of the [x] coordinates and one for all of the corresponding [y] coordinates. Then you reference the arrays in these formulas. Not that the intercept refers to the slope formula.
Slope:
WhilePrintingRecords;
NumberVar array x; //array of all the x coordinates
NumberVar array y; //array of all the y coordinates
Local NumberVar array xx := x; //summation of x squared
Local NumberVar array xy := x; //summation of x*y
Local NumberVar i;
for i := 1 to count (xx)
do (xx [i] := x[i]^2 ; xy[i] := x[i]*y[i]) ;
(count(x) * sum(xy) – sum(x)* sum(y))/(count(x) * sum(xx)–sum(x)^2)
Intercept:
WhilePrintingRecords;
NumberVar array x;
NumberVar array y;
(sum(y) – ({@Slope} * Sum(x))) / Count (x)
Correlation:
WhilePrintingRecords;
NumberVar array x;
NumberVar array y;
Local NumberVar array z := x ;
Local NumberVar i;
for i := 1 to count (x)
do (z[i] := (x[i] – Average (x)) * (y[i] – Average (y)));
( Sum(z) / (StdDev (x) * StdDev(y)) ) / (Count (x)-1)
Gems from the Archives
Copying Objects Within a Report (June 2003)
Copying Objects to Another Report (June 2003)
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 2016 by Ken Hamady
All rights reserved - Republishing this material requires written permission