Archive for the 'Formulas' Category



Another option for selecting ALL in a parameter

Saturday 23 March 2019 @ 11:04 am

I wrote last fall about selecting “ALL” with a string parameter field. My comments at the end list options for making the technique work for numbers and dates. Recently I received a note from one of my colleagues on a better approach, using optional parameters.

Starting with CR 2008 (v14) Crystal has allowed us to define a parameter as “optional”. This allows the user to not enter a value at all in a parameter. However, when you use an optional parameter in a formula you always have to test for the existence of a value in the parameter field before you use. The test often looks like this:

if HasValue ( {?Tax Rate} )
then {Orders.Order Amount}*{?Tax Rate}
else 0

This way the report knows what to do when the parameter is skipped.

Another way of creating an ALL option is to tell the report that any time there is no value entered in the parameter, the user wants ALL values. The formula would look something like this:
(not HasValue ( {?State} ) or {Customer.State} = {?State})

Note that the HasValue() test has to come before any other test that uses that parameter. If you reverse the two tests in this formula it will generate an error whenever you don’t fill in the parameter.

And thanks to Luc Rascar, a Crystal Reports/Business Objects consultant in France, for pointing this out.




Carriage returns in a formula that will survive a text export.

Friday 8 March 2019 @ 12:40 am

There are several common uses for exporting to text format.

I use text format whenever I need a Fixed Length export file. These are files where the exported record doesn’t have a comma or pipe delimiter. Each field in the string is identified by its character position in the string, since each field is a specific number of characters. This means that all records end at the same position, regardless of how long individual field values are.

I also use text export format for some CSV exports, because there is more flexibility.  For instance, when I need to generate two CSV rows from the same detail row in the data I find it easier to structure the CSV rows in a formula  and export as text.

And that brings me to what I learned last week. If you are exporting to “text” format and the formula you are exporting has carriage returns in it, you might find that they don’t work after the export. For instance the formula below would show 3 rows in the preview of Crystal Reports:

{@String1} & CHR(13) &
{@String2} & CHR(13) &
{@String3}

The function element CHR(13) creates a carriage return between the different elements of the string. But if you export this formula using “text” format you will find that the carriage returns don’t survive the export. The text file would not have the three rows that you see in preview. But with a little experimenting I found that adding a second related function in the formula works better:

{@String1} & CHR(13) & CHR(10) &
{@String2} & CHR(13) & CHR(10) &
{@String3}

In CR preview both formulas will appear the same. However, the second formula will provide carriage return that survives into the text export, while the first one will not.

Within a week of making this discovery for one customer, I found I needed the same thing for a second customer. I probably should have figured this out even sooner. When working text files and hidden codes I have seen that you usually need both a carriage return, Chr(13), and a line feed , Chr(10) to start a new line. But since it only takes one of these in Crystal preview, it is easy to forget that they work together.




Applying a formatting condition to multiple fields

Thursday 28 February 2019 @ 9:59 am

If I want to remove the decimals of several fields at once you can use CTRL-click or a cursor lasso to select all the fields, then go to the toolbar and hit the “reduce decimals’ button. Each click will remove one decimal from all of the selected fields.

You can do something similar when you want to apply a formatting condition formula. For example, if you want to turn negative numbers red while leaving positive numbers black. The font color condition formula looks like this:

if currentfieldvalue < 0
then CrRed
else CrBlack

To apply this formula to one field you select that field and then select the menu items “Format > Field”. On the “Font” tab you click the condition formula button next to font color. It will usually look like the top one of these buttons:

Once inside you paste in the formula above and then click “Save and Close. The formula button should turn red and look like the middle button above. This means the condition has a formula. When you click OK the negative values for that field will turn red.

Note that the formula doesn’t refer to a specific field, but to the function “CurrentFieldValue”. This function is only available when you do condition formulas and refers to the value of the field you are formatting. The advantage is that the same logic can be used on any numeric field and the condition will be exactly the same, rather than each field having to have a different formula that mentions a specific field.

If you want to apply this formula to several fields at once you could select that group of fields and then select the menu items “Format > Objects”. Like above, you go to the “Font” tab, click the [x+2] and paste in the formula. When you click “Save and Close, then then click OK all of those objects should have that property.

One thing to look out for when you are formatting multiple fields at once is a purple condition button (bottom example in the picture above). This only appears when you try to format multiple fields at once. This tells you that some/all of these fields already have a condition and that not all of them are the same. If you click a purple condition button it will show you a blank formula. If you put in a new formula you will overwrite any existing logic and all of the selected fields will end up with the new condition.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server