I have just updated my comparison of RPT management utilities for 2019. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC

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.