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.

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)

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.

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.