__Archive for the 'Formulas' Category__

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.

Crystal formulas can use 3 different divide operators:

- Regular divide [ / ]
- Integer divide [ \ ]
- Percentage [ % ]

But all of these will fail if you follow the operators with a zero. The report will stop and Crystal will throw the error message “Division by Zero”. The standard solution is to check and make sure the number you are dividing by is not zero before you do the calculation, something like this:

`if {fieldA} = 0`

then 0

else {fieldB} / {fieldA}

This way, whenever the bottom of the fraction (denominator) is a zero, the formula will print a zero and NOT try to do the calculation.

But even when customers use this formula pattern I still see the divide by zero error. Some users mistakenly check the top of the fraction (numerator) instead of the bottom (denominator). Some do it correctly at first, but then change the denominator and forget to change the first line to match. So I have developed the habit of using Local variables to make things easier. My normal pattern now looks like this:

`Local Numbervar n:= {FieldA};//numerator`

Local Numbervar d:= {FieldB}; //denominator

if d = 0 then 0 else n/d

This ensures that the value being checked is always the value on the bottom. Some other advantages of this method are:

1) If d is a subtotal or a long expression you only have to enter it in one place.

2) If you have to create a series of similar expressions, like for 12 different months, you can duplicate the first example and you only have to change the values at the top of the formula.

Lots of reports require that you compare two different date periods and I often calculate prior date ranges based on the current range. But you have to be careful when the end of your prior period falls in a month with more days the the current period month?

Here is an example. Lets say your current period is in June and your prior period ends 6 months earlier in December. Calculating the Start Date is simple. If your Current Start is 6/1/2019 you can use a simple DateAdd like this:

`DateAdd ('m', -6, {@CurrentStartDate})`

but if you try a similar formula for the End Date you won’t get the right date. The following formula will return 12/30/2018:

`DateAdd ('m', -6, {@CurrentEndDate})`

The same issue occurs when your prior period is a year before and your current period ends in February. If he prior year is a Leap Year your prior period end date will be off by one day, ending on the 28th rather than the 29th.

My solution involves adding one day before you do the DateAdd and then subtracting that same day back out again, like this:

`DateAdd ('m', -6, {@CurrentEndDate} `

**+1**) **-1**

This works because adding the one day puts on you on the first of the month. This is always a clean calculation when moving forward or backward by months or years. Then once you get to the first of the month in the prior period you subtract one day which always puts you on the last day of the month prior.

One of my students presented me with the following challenge. Their address records were stored in a table that keeps an address history. That means that new addresses don’t replace the old addresses. Each new address is a new record with a time stamp. The current address is the record for that customer that has the latest timestamp.

To display the current address for each customer is fairly easy and can be done in one of two ways:

1) Group by Customer and sort by time stamp. Hide the details and place the address fields in the Group Footer. This would display the last address for each Customer.

2) Group by Customer and put in a group selection formula that says:

`{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )`

Either of these will work to show the current address for each customer. But if you want to select only current addresses in a particular state, like NY, you have to be careful. If you put the State criteria in the record select expert or record selection formula the criteria will be applied before the grouping happens. Crystal will start by selecting only New York records regardless of how old the timestamp is. Then it will do the grouping and show the last NY record in each group. You would end up with the last New York address for each customer, rather than getting the accounts that have New York in their last record. Anyone who had moved of NY to somewhere else would still show up.

My original solution involved a formula that combined the Date and the State into a single string field. Then I used a complicated group selection formula to find the right records. You can read about it here and it works fine.

But today I realized there is a simpler approach. The key is putting the State rule into the group selection formula, so it is applied after the grouping is done. So your Group Selection would look like this:

`{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )`

and {Address.State} = "NY"

As long as the last line stays in the group selection formula this will return the desired records.

I recently found a function in a Crystal Report that I hadn’t noticed before. Technically it is an additional function (UFL) but I am pretty sure it has been installed automatically with Crystal Reports for a long time. The file is dated 11/8/2000 so it might have been introduced with Crystal Reports V8.

The function is called ExtractString (). It is designed to locate two character strings within a longer string and return all the characters in between those two strings. I have done something similar using the InStr() function but it is much more complicated. A good example of a use for this is when you have XML tags in the middle of a long memo field and you want to extract the value between two specific tags. Say it looks something like this:

“blah blah blah <price>19.99</price> blah blah blah <price2>29.99</price2> blah blah blah “

To extract the value for price2 you would use the following formula:

`ExtractString ( {table.xmlField}, '<price2>', '</price2>' )`

You give the function three arguments:

- The field you are searching
- The string that marks the start
- The string that marks the end

If it doesn’t find the start string it returns a blank (even if the end string is there).

If it finds the start string but no end string it returns everything after the start string.

if it finds both the start and the end it returns all the characters between them.

It skips over any end strings that occur before the start string.

If it finds multiple starts or ends it uses the first.

Next time I have to parse XML or do something similar I will use this function and save myself a few steps.

Crystal has a function that calculates the drill-down level of the current window. This allows you to have objects and sections format differently at specific levels of drill-down. The function is called DrilllDownGroupLevel and it gives you a numeric value that tells you the current drill-down level for any preview tab. The normal preview is level 0, the first drill-down is level 1, etc. So if you want a section with column headings to appear at the second level of drill-down and ONLY at the second level of drill-down you can use the following as a suppression formula for that section:

`DrilllDownGroupLevel <> 2`

That section will be suppressed in normal preview (drill-down level 0) and also at the first drill-down level, but will appear if you drill down again from level 1. If you are ever unsure which level of drill-down you are on you can write a formula that says simply:

`DrillDownGroupLevel`

Place this formula in the section in question. When you see that section the number shown will tell you the current drill-down level.

DrillDownGroupLevel also helps you solve one of the dilemmas of report design. One of the lessons in my advanced class is how to have a report be either a detailed report or a summary report, based on a parameter prompt. You accomplish this with a parameter that has two choices (Summary/Detail) and you use that parameter in the detail section’s suppress formula. It has to be the suppress property because the “hide” property doesn’t have a condition formula button. And, because you are using the suppress property you can no longer drill-down from the summary version of the report to see the details for a group.

But by using the following as your suppress formula you can have the parameter to choose summary or detail, and still have drill-down available on the summary version:

`{?Parameter} = "Summary" and`

DrillDownGroupLevel <> 1

This will suppress the section when you choose “Summary”, but only as long as you are in the main preview tab. As soon as you drill down the suppress condition will no longer be met and the details will be visible.