Archive for the 'Formulas' Category



Add an address block without blank lines

Friday 15 February 2019 @ 12:21 am

I often find one or more “address blocks” at the top of form reports, like invoices or purchase orders. These are blocks of text that typically show a customer name, two or three lines for address info, and a last line for City/State/Zip. The simple approach is to arrange the individual fields on the report, but if some address lines are blank you get empty rows in the block. Here is the approach I use to make sure that there are no empy lines in the block.

First you write a formula that combines the city/state/zip into one row like this:

//{@City, State & Zip}
{Cust.CITY} & " " & {Cust.STATE} & ", ' & {Cust.ZIP}

Then you write a formula that combines the first formula with the other potential rows of the address, like this:

{CONTACT1.Company} &
(if {Cust.ADDRESS1} > "" then CHR(13) & {Cust.ADDRESS1} else "") &
(if {Cust.ADDRESS2} > "" then CHR(13) & {Cust.ADDRESS2} else "") &
(if {Cust.ADDRESS3} > "" then CHR(13) & {Cust.ADDRESS3} else "") &
CHR(13) & {@City, State & Zip}

The formula above assumes that every record will have a Company value and a City/State/Zip value. The address lines are added if they have a value. And when they are added a carriage return is also added, using CHR(13). This way each line appears on its own row, but only when it has data. There are no blank lines when a field has no data.

Two things to watch for.
1) You should set both of the formulas above to use “Default Values for Nulls”. Otherwise a Null value for one of these fields will cause a blank address block.
2) Make sure you format the address block with “Can Grow” and then don’t put anything right below it in the same section.  Otherwise the address field might grow right over the object below it.




Minor changes can have a major impact on performance

Monday 7 January 2019 @ 11:54 pm

Over the holiday break I had a customer contact me about a report that had just started taking a very long time to run. The first place I looked was in the record selection formula where I found this in the second line:

{Orders.OrderDate} -1 in LastFullWeek

I suspected that this was the problem. To confirm I had him send me the original report that ran in the normal time. Here is what the original said:

{Orders.OrderDate} in LastFullWeek

Apparently the requirement for the report had changed from the prior week starting on Sunday to the prior week starting on Monday. That minor change causes Crystal to completely drop the date rule from the automatically generated SQL. This means the database will send back ALL dates and Crystal will have to apply the date filter locally. I had him try this instead:

{Orders.OrderDate} in Minimum(LastFullWeek) +1 to Maximum(LastFullWeek) +1

Both version 1 and version 3 return the same results but version 1 adjusts the field while version 3 adjusts the comparison values. Version 3 will make it into the SQL WHERE clause while version 1 will not.

The same problem happens when you use a function on the field. Here are two common examples I see:

Date ({Orders.CreateTimeStamp} ) in ...

Round ( {Orders.Amount} ) = ...

If the report performance is fine than these examples can stay, but if you need to speed up the report then these should be written without the functions, so that they are incorporated into the automatically generated SQL.




Using an asterisk for ALL in a parameter

Monday 17 December 2018 @ 11:43 pm

I recently wrote about using ALL when working with parameter fields. After my newsletter went out I got a comment from Bruce Ferguson of Crystal Kiwi in New Zealand (thanks Bruce!). He mentioned that he prefers to use an asterisk instead of the word ALL. This sorts to the top of the list automatically, and us old-timers are used to thinking of the asterisk as a wildcard.  It also doesn’t get confused with similar values, like the state abbreviation “AL”

This reminded me of a lesson from my advanced class. We create a string parameter and write a selection formula like this:

{Customer.PostalCode} like {?Zip}

This can be used three ways by the user.

1) If they enter the full code like 20176 they will get exact matches.
2) They could also do a partial match like 601*. This would give them all postal codes that started with 601. They could enter any number of characters to get a broader or narrower group.
3) If they enter just an asterisk, they would get ALL values, with one exception. They would get records that have a blank postal code, but they would NOT get records that have a NULL postal code.

So, if you want to use LIKE and still have the asterisk return all values, including NULL values, you would need to do something like this:

if {?zip} = "*"
then True
else {Customer.PostalCode} like {?Zip}




Crystal Reports formula function libraries (2018)

Tuesday 27 November 2018 @ 10:38 pm

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) Generate bar codes without having to install any fonts

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 can now generate 3 types of charts, crop images and process text through Google’s “sentiment analysis” engine.

If you need help deploying one of these functions in a project let me know.




Selecting ALL with a parameter field

Sunday 25 November 2018 @ 9:52 am

The most common use for parameter fields is in the selection formula, where they are used to filter the records. So if I have a parameter called {?State}, I can use it in the selection formula like this:

{Customer.State} = {?State}

But what if the user wants to be able to select ALL states? If the user enters the word “ALL” in the State parameter with the selection formula above, the query would look for records with a state value of “ALL”, and likely wouldn’t find any. But you can modify the formula to read:

( if {?State} = “ALL”
then True
else {Customer.State} = {?State} )

With this selection formula, the user can enter the word “ALL” and will get ALL states (including those that are blank and null).
Note that this rule is contained in a pair of parens. Those are only strictly necessary when there are other rules in the selection formula but it is safer to add them so they aren’t forgotten. Without the parens a rule that follows the logic above will be considered part of the else and won’t be applied all the time.

One other note.  This works for string fields.  If your field is a numeric or date your options are to:

  1. Make the parameter a string so you can enter “ALL” and then convert entered values in the selection formula to the correct data type.
  2. Use a unique number (e.g. 99999)  or a unique date (e.g. 1/1/2001) as your “All” value.
  3. Make the parameter an optional parameter and then have the blank value represent “All”.



Flaw in XML Exports

Monday 22 October 2018 @ 11:06 am

I have a customer who had me create report with a complex layout and lots of optional sections. In addition to using the report itself, he wanted to export the report values into a format where they could be read by another program. We decided to use XML so that the program could search for specific field name tags and extract the matching values. When we started testing the program we noticed some discrepancies between the report values and the XML export values. It mainly had to do with variables that were accumulated in the details and then displayed in the group or report footers.

It took over an hour to identify the root of the problem. Even after fixing the problem I couldn’t explain it, so I created a very simple report to test it. It had one group, and the the following two formulas:

//Accum
WhilePrintingRecords;
NumberVar Accum;
Accum := {Customer.Customer ID}

//Display
WhilePrintingRecords;
NumberVar Accum;

I placed the Accum on the details band and hid that section. I placed the Display formula on a group footer. This exported to XML and showed all the Group Footer values correctly.

Then I split the detail band into A and B subsections. Both subsections were still hidden. The preview of the report looked the same, but when I exported to XML all the group footer values in the XML were zero. It didn’t matter if I put the Accum formula in Details A or Details B. Whenever the Details section was both SPLIT and HIDDEN, the Accum formula would increment correctly in preview but NOT for the XML.  If I used “suppress” instead of “hide” the Group Footer values exported correctly to XML. But even though the details did not appear in preview, they would now be included in the XML.

This behavior looks like a bug to me.  That means there may be other situations where variables don’t behave correctly in XML. So, if you are going to use XML exports, and you are using variables in your report, you need to test the output carefully to confirm that the variables export correctly.




New functions in Cut Light

Sunday 14 October 2018 @ 9:35 pm

Ido Millet at Millet Software has recently added some unique new functions to the Cut Light UFL. Cut Light now adds 130 new formula functions to Crystal Reports. Here are the ones most recently added.

1) Creating charts from formulas.
This includes advanced gauges, sparkline charts (bar and line) and bullet charts. These charts are generated in real time by a formula function. The functions do not rely on Crystal’s integrated chart engine. They are generated as image files which are automatically read into the report. These charts are simple and have no customizable features. There is also no drill-down capability. But these charts can be generated on any series of numbers, including numbers that come from print time variables or even shared variables . Ido has posted some image examples: And you can read the details in the user manual.

2) Image cropping.
Allows you to read an external image file and crop it before displaying it in the report. This can be used with the chart images above and also with existing Cut Light functions that allow you to read the image properties and resize an image. Here is the relevant section of the Cut Light user manual:

3) Google sentiment analysis.
This allows a formula to process a block of text through Google’s “sentiment analysis” engine. The result will be 2 numbers which represent the score (positive, negative or neutral) and the “emotional magnitude”. This can be used to evaluate any comments, Email messages or product reviews you have in your database. It does require that you have an ID to use the Google API, but there is no charge for the first 5,000 calls per month.  You can read more details in the user manual.




Date formulas in Accpac without pwFormatDate()

Wednesday 3 October 2018 @ 11:40 am

If you write Crystal Reports against Accpac, now called Sage 300 ERP, your reports have probably seen or used the function pwFormatDate(). This is a custom function installed by Accpac that converts the numeric dates in Accpac into true date values. The raw data is an 8 digit numeric like 20161231 which is converted into a date by a formula like this:

pwFormatDate ({BKTRANH.TRANSDATE})

Sometimes my Accpac customers will try to send me a report saved with data. But because I don’t have Accpac installed, I don’t have the dll that provides this custom function (u2lcapw.dll). Crystal can’t load the saved data because the formulas that use this function generate errors. In researching this topic I have found that even some Accpac users get this error, despite the fact that the dll is installed.

Fortunately, you don’t need the dll or the custom function to convert these numbers into dates. I have had formulas on my web site for years that convert string and numeric dates into true dates. I just recently updated the formula that can replace pwFormatDate(). The formula above could be replaced by the following formula. It is longer, but it doesn’t require installing or troubleshooting any dlls:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
Local StringVar DateString :=
If input < 19500101 then '19500101' else Totext (Input, 0, '');

Date (
Val (DateString [1 to 4]),
Val (DateString [5 to 6]),
Val (DateString [7 to 8]) )

Also, using this formula instead of using pwFormatDate() allows your reports to be opened with saved data on any PC that has Crystal Reports. So, if you are getting errors that say this dll can’t be found you can convert to using the formula above and be done with the dll.

Update 10/12/208:

Ido Millet of Millet Software suggested an alternate approach:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
If input < 19500101
then Date (0,0,0) else
Date(Picture(ToText(input,0,""),"xxxx/xx/xx"))




Numbers that touch operator words

Friday 21 September 2018 @ 2:36 pm

I was shown an unusual formula today. It looked something like this:

if {Customer.Customer ID} = 14then 30else 0

Note the space missing between the numbers and the words THEN and ELSE. These two words didn’t turn blue so I figured they weren’t being recognized and would generate an error. Surprisingly, the formula saved without error and the number 30 showed up in the correct places. The reserved words were still being recognized correctly, even without the spaces.

I did some testing and found that any formula where a number is usually followed by a space and then an operator word will work the same without the space. I tested this in some older versions of Crystal and it worked the same at least as far back as Crystal v8.5 (2001).

The only reserved words I can think of that can follow a number are these operators: AND, OR, IN, TO, THEN, ELSE, MOD and DO.  These all behave the same way.  I can’t think of any functions that can directly follow a number. A space is still required if the number follows these words.

So if you ever find a formula like this and wonder how Crystal is handling the error, now you know.




Selection based on two different records in the same group

Sunday 16 September 2018 @ 8:38 am

I was visiting Tek-Tips.com today and found an interesting question to answer. A user was having trouble counting sales orders that had items with a certain comment. He wanted to group and count the occurrences by the item ordered. It sounded simple, but the item number was in an “item” record while the comment was on a separate “comment” record. When he filtered by the comment there would be no item numbers and if he grouped by item number the comments would be in a different group. He needed to get the two records into one record.

To solve a problem like this you need to add the sales order table to the report two separate times to create what is called a “self-referencing join”. The second time you add the table, Crystal will give the table a slightly different name (an alias) usually by adding _1 to the end of the table name. Then you treat the two instances as if they were two separate tables.  In this case you could think of the first table as the “item” instance and the second as the “comment” instance.  You link them based on the Sales Order and then add two filters, something like this:

{SalesOrder.LineType} = "Item" and
{SalesOrder_1.NComment} = "Your specific comment"

Note that the two rules above reference one field from each of the two table instances, bringing the two separate records together as one. From there you can group by item number (from the first table) and count the number of records within each group.  Here is a link to the tek-tips question which has a screen shot of the data.

Update 10/3/2018:

Doug Weiner at Beacon Legal Software reminded me to mention that you can always rename (change the alias) of any table used in a report.  Just go to the database expert and click on the table name, then right-click to get ‘rename’ or hit F2 on the keyboard.  You will be allowed to type any alias you want for that table.  This is especially helpful when you use the same table twice, so you can assign names that help you remember which instance is which.  These names are just for the report and don’t affect anything in the database.




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series