Archive for the 'Formulas' Category



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.




A temporary change that expires automatically

Saturday 25 August 2018 @ 8:55 pm

Sometimes I need to make a temporary change to a report. For instance I want to might want to skip invoicing one or two customers for a week or two. So I will put a rule in the selection formula that eliminates them from the report. However, I have a tendency to lose track of these changes. Several months later I will realize that these customers have not been getting invoices.

So I have started putting in changes that are time limited. That way, I don’t need to remember to reverse the changes. For example, if I want to hold off on invoicing two customers for the next few days I could add something like this to the selection formula:

. . . and (if DataDate < Date (2018, 8, 31) then not ( {Cust.ID} in [‘ABCD’ , ‘EFGH’] ) else True )

In English this says, ‘if this report is refreshed before 8/31 then don’t include these two customers, otherwise ignore this rule’.

Eventually I will notice the rule and take it out but I don’t have to worry about when, because the rule turns itself off automatically. You can put a similar time limit on any change that can be driven by a formula.




Cloaking Groups

Tuesday 24 July 2018 @ 7:49 pm

There are times when you want a group level to be optional. Maybe you want to give the user a choice between having 1 group level or 2 group levels in a report. While it is easy to suppress Group Headers and Group Footers for group 2, this doesn’t make the group level go away. Group 2 will still put the records in order, and will take priority over any sorting you have specified. A group can’t be removed based on a parameter, but you can have the same effect by ‘cloaking’ the group. Even though it is still there, it has no effect on the report.

To cloak a group you first create a parameter with 2 choices. For example, our user has a parameter called {?Group choice} that allows them to select the number of group levels:

Group by State only
Group by State and City

You set Group 1 to use the field “state”. Group 2 is set to use a formula that says:

If {?Group choice} = “Group by State only”
then “All”
else {table.City}

So, if the user picks “Group by State and City”, then the second group will be the field “city”. But, if the user picks “Group by State only”, then Group 2 will be one big group. Since all the records in Group 2 have the same value, the details will sort as if there were only one group.

So what do you do with the Group 2 Header and Footer? You suppress these sections with a suppress condition that says:

{?Group choice} = “Group by State only”

Now the Group 2 has no effect on the sort, and it is invisible.

A more complex example is when the user wants to see the Top 10 customers but wants to choose if the Top 10 is based on the Average or the Sum of the amount. There is no option for this in the Group Sort Expert. The workaround is to create a parameter so the user can choose either “Sum” or “Average”. Then create two cloaking formulas like the one above, using the Customer field and opposite IF conditions. Group on both fields and set the Group Sort for one group to the average and the other to the sum. Add suppression logic to the group headers and footers to completely cloak one group or the other.




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series