__Archive for the 'Formulas' Category__

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 convert numbers to Arabic text and provides more robust encoding for Barcode 128, along with some other enhancements to existing capabilities.

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

Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that skips values in one field whenever there is a duplicate in a separate “key” field. It would work something like this: “only add each customer’s balance into the total once – even if a customer shows up in several different places in the report. The customer ID would be the “key” field while the balance would be the field you are totaling. It should only count the value on the first instance of the key field.

If you can group the duplicates together the solution is simpler. You can use a running total and set it to “evaluate on change of group” where the group is the key field. I teach this method in my advanced material. But a long ago student showed me a clever way to identify duplicates, even when they were scattered. I had never seen that method and couldn’t find it described anywhere else. I published it in my newsletter in 2004. When I needed to use it last week I decided to post it here.

Last week my challenge was a payroll report that showed employees grouped by department, showing pay and withholding. Pay was split by Dept but the withholding was combined. So when an employee worked in two different departments, his withholding would show up twice in the totals. I needed the totals to count the withholding once per employee, even if the employee showed up in several departments (on different pages).

To use this method you first create a running total field that is a distinct count of the “key” field. Then you create another running total, this time using variables, to total the numeric field. This formula has logic to only add the value when the first running total has changed from the record before. Any time there is a change in the first running total it must mean there is a new value for the “key” field. When the first running total doesn’t change it means that the “key” field value has appeared before. The formula I used looks like this:

`WhilePrintingRecords;`

NumberVar Prior;

NumberVar LYS;

if {#CustCount} = Prior + 1 //test if first record for this customer

then LYS := LYS + {Customer.Last Year's Sales};

Prior := {#CustCount}; //store current count to use for next record

LYS

If you need a Distinct Sum as a Subtotal you would reset the running total and both of the variables with each group.

I have seen other developers solve this problem by creating an array of all of the “key” values and checking each “key” value against the array before adding the numeric. This method lets Crystal handle that duplicate check so there is no reason to maintain an array.

My web site has a page for commonly used formulas. Many have been there for years. Formula page 9 has two versions of a formula that will convert a number of seconds into an elapsed time string. The long version has days, hours, minutes and seconds. The shorter version is just hours and minutes.

I was using the short version in a customer’s report and we noticed that the minutes value was sometimes off by one. After some testing we found that if the remaining seconds were between 30 and 60 the formula would always round down because the formula used a Truncate() function. The Truncate() works correctly in the long version of the formula, because you truncate down to the whole minutes and then display any remaining seconds. But since the shorter version doesn’t display seconds it is more accurate to Round () the seconds to the nearest minute. So I have updated the short version formula on my site to use the Round () function for minutes.

So those of you who have used that formula should update your reports by changing the last Truncate to a Round, or just taking the updated formula from page 9.

Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to “repeat” on each page.

Any time you have a Group Header you can set it to repeat on each page. One feature of a repeating GH is that it won’t appear on the last page of a group, unless that page has at least one detail record. I wrote about that behavior in another article long ago. So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.

So how do you create a group that includes all the records in the report? You group on a value that doesn’t change. If you have a DB field like “company” that doesn’t change you can use it. But you can always create a formula that isn’t tied to any data fields. My favorite dummy group formula is:

`WhileReadingRecords;`

"All"

The word “All” can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.

Once you create the formula you use it as Group 1 in the report and then go into Group Options and check “Repeat Group Header on each new page”. If you put your column headings in this Group Header they will appear on every page, but won’t appear on the last page (unless there are details printing on that page).

And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.

I was recently working on a formula that had the crystal syntax for “is one of”. It looked something like this:

`{LastName} in [ "Smith","Jones","Thompson","Rutledge","Harris" ]`

Each value goes in quotes (single or double). You separate the values with a comma and you put square brackets around the list. So I was surprised today when I saw that the formula was actually like this:

`{LastName} in [ "Smith""Jones","Thompson","Rutledge","Harris" ]`

Notice the comma missing between the first two values. The report had been running for months without error messages which I didn’t understand, so I started testing. My experiments pointed me to a syntax rule that I had read about but never used. It is for when your formula includes a literal string that you surround with double quotes, and when the literal string itself contains double quotes. Crystal would assume the first quote is the ‘open’ and the second one is the close, even when you want the second one to be part of the visible output. One solution is to use two consecutive double quotes within the literal. Whenever Crystal finds two consecutive double quotes within a literal surrounded by a pair of double quotes, Crystal will interpret the consecutive quotes as one literal quote and not as a closing quote. This is much easier to explain with an example. So if I wanted a formula to output this string:

`The syntax for "is one of" uses brackets`

I could write it like this inside the Crystal formula:

`"The syntax for ""is one of"" uses square brackets"`

The formula engine would only display one of the two consecutive double quotes in each pair. That explains why my formula with the missing comma doesn’t generate an error. Crystal treated the consecutive double quotes as a single literal and then combined the first two elements in the list as being the single value:

`Smith"Jones`

There was no error message, but the results would not have been correct.

BTW, the same principle applies to single quotes. If you put two consecutive single quotes in a string surrounded by single quotes, Crystal will ignore the first and treat the second as a literal quote.

The reason I didn’t think of this right away is that I have never used consecutive quotes in a formula. If I have a string that needs to contain single quotes I surround it with double quotes. And if the string needs to contain double quotes I surround it with single quotes. In the rare case that a string needs both I would split it into separate pieces and combine them. I find that using consecutive quote pairs makes the formula harder to follow.

Sometimes there are details printing on the last page and sometimes the last page is just the Group or Report Footer. You may not want column headings on the last page if there are no details. I have seen and tried several methods to accomplish this but not all are reliable. For instance using “OnLastRecord” as a suppress condition will work most of the time, but will also suppress the headers when the last page still contains exactly one detail record. I have found the approach below to be the most reliable.

First you split the Details band into A and B subsections. You suppress Details B and use Details A for your visible fields. Then you create the following formula field and place it in Details B:

`WhilePrintingRecords;`

BooleanVar Ending := OnLastRecord

Last you use the following formula to suppress the Page Header, or the part of the Page Header with column headings:

`WhilePrintingRecords;`

BooleanVar Ending

A Boolean variable is False by default. The variable will only be True once the report has passed the last Detail A, which means that the last record has printed. If there is a page header after that point it will be suppressed, because the “Ending” variable is now True.

Crystal makes it easy to reduce the number of decimals that you display for numeric values. There are two buttons on the toolbar (near the percent sign) and these allow you to either reduce or increase the decimals displayed. When you reduce decimals the remaining digits will either round up or down based on the digits no longer displayed. In other words, if you are showing 75.28 and you click the button to reduce the decimals displayed by one, the value will display as 75.3. This is because the hidden 8 causes the value to round up to the next 1/10th.

However, this is deceptive. The underlying value in the report is still 75.28. And if you were to total that column or use that value in a calculation the value used would still be 75.28. This can give unexpected results, because the visible total at the end of the report might not reflect the sum of the visible values that go into that total. For that reason, I call this ‘fake’ rounding. You will find the same type of issue in Excel spreadsheets.

Most of the time, this isn’t an issue. And many people who read financial statements understand ‘rounding errors‘. But there are cases where you need ‘real’ rounding. Real Rounding requires writing a formula that uses the Round() function. For instance, if you have to apply a tax rate to a purchase and the calculated tax amount. You could use a formula like this:

`{Sales.Charge} * {Sales.TaxRate}`

But if the tax value has more than two decimals, those extra decimals aren’t real. Most transactions have to be rounded to the nearest penny. In these cases you would need a formula like this:

`Round ( {Sales.Charge} * {Sales.TaxRate} , 2 )`

This rounds the tax to the nearest penny. If you used the first formula and created a total of the tax values, the total would include all the fictional fractions of a penny. If everything was displayed with two decimals the total tax would not match the individual tac values, because those would be using fake rounding to display only two decimals. But if you use the second formula and then create a total of that formula the total should reflect the visible values. The total calculation is using values that display the same number of digits as the underlying value.

One of my colleagues sent me a New York Times article about a math tweet. The tweet was of a deceptively simple math equation and asked people to calculate the result. All of the internet mathematicians immediately divided into two camps based on their interpretation of the rules of precedence, which were intentionally ambiguous in the equation. The point of the article (and of my colleagues sharing) was the importance of using parentheses to clarify the order in which things should occur. I often add extra parens, even when the order of precedence wouldn’t require them, just to give myself a visual clue of what is supposed to happen when.

The funny thing about the original article, and an interesting follow-up article, is that experts can still be found on both sides of the divide. That is somewhat surprising given the apparent simplicity of the problem.

And thanks to Zvi Flanders of Huron Consulting for sending me the link.

I am not a big fan of Daylight Saving Time (DST). I even hear some states and countries are talking about dropping it (yeah!). But in the meantime there are plenty of reports that need to adjust the time twice a year. This usually happens when the datetime values are stored in Greenwich Mean Time (GMT) and have to be converted to a local time. Then you need to know when DST begins and ends.

The first two formulas below calculate the beginning and ending dates of DST, based on the year of your transaction date. The third formula uses the first two formulas to make the one-hour adjustment. Substitute your GMT date fields into the first and third formula.

`//DST Start`

DateVar Start:= Date (Year ({@YourDateTimeGMT}) , 3, 15);

DateVar BOM:= Start - Day(Start)+7;

DateVar BOW:= BOM - DayOfWeek(BOM) + 8;

DateAdd('h', 2, BOW);

`//DST End`

{@DST Start} + 238 ;

`//Adjusted DateTime`

if {@YourDateTimeGMT} in {@DST Start} to {@DST End}

then DateAdd('h', 5, {@YourDateTimeGMT})

else DateAdd('h', 4, {@YourDateTimeGMT})

Last week a customer was really befuddled. He had a formula that said:

`if {@field} = 0 then ...`

He could see lots of zero values but the formula didn’t work as expected. He couldn’t figure it out so he sent it to me. The first thing I did was add a few more decimals to see if it was a rounding issue. That didn’t show anything, but the formula still insisted that the value was somehow NOT equal to zero.

So I went into the formula and multiplied the current value by one trillion. Then instead of zeros I started to see some small numbers. I am not an expert on floating point values or database precision but I have seen this before in reports. The solution is to round the value in the formula before comparing it to zero. In this case we rounded the value to two decimals like this:

`if Round ({field}, 2) = 0 then ....`

That made the formula behave as expected. The odd part is that I have seen the same problem with two other customers in the past week. It could be just a coincidence, but I figured I would mention this and see if this is happening to more people.