Preventing the “division by zero” error

Friday 26 July 2019 @ 9:52 am

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.





How to make sure you are on the last day of the month

Friday 19 July 2019 @ 9:18 am

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.





Finding groups where the last record meets a criteria

Monday 15 July 2019 @ 9:52 pm

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.





Moving cross-tab numbers to Excel

Thursday 27 June 2019 @ 9:00 am

If you are trying to move cross-tab numbers into a spreadsheet, there is a short cut. You can simply copy and paste the entire cross-tab into your spreadsheet. Right-click in the upper left (empty) cell of the cross-tab and select “copy”.  Then switch to the spreadsheet and right-click in a cell and select “paste”. The cross-tab numbers should appear in the spreadsheet.

The only limitation is that the cross-tab has to fit on one page in Crystal. Fortunately, all recent versions of Crystal allow the page to be as large as needed. Go into “File > Page Setup” and check the option called:

“Disassociate Formatting page size with Printer Paper size. “

Then set the height and width to whatever you need to accommodate your cross-tab.

If you have an older version of CR (before CR 2008) the option above is missing. Instead you can use a PDF virtual printer like Cute PDF, and set a custom paper size so you have enough room.





SQL Syntax for several common date ranges

Friday 21 June 2019 @ 8:26 pm

I have been writing lots of SQL commands lately for my reports.  I am doing this so much now that I regularly tap into my SQL cheat sheet which shows the most commonly used SQL functions and calculations for the seven SQL flavors that I see the most.

Recently a customer needed several date range functions to be used in the WHERE clause of a SQL Command. The list was quite similar to the date range function list available in Crystal. He wanted the following functions: YearToDate, MonthtoDate, LastFullMonth, LastFullYear, LastFullWeek, Last7days, PreviousDay.

So I did some research and found some excellent discussions on the best approach to take. For instance, I read that you don’t want to use any functions on the database field itself. I had started to use the Oracle TRUNC() function to strip the time off of the database field, but I read that this can prevent the query engine from using an index. However functions don’t affect the index when used to calculate the literal dates that you are comparing. I also read that when you don’t strip the time from the DB field it gets harder to use a BETWEEN comparison. The most knowledgeable posts I read avoid Between and instead used the raw DB field in two separate comparisons like this:

db.field >= CalculatedRangeStart and
db.field < CalculatedRangeEnd

The CalculatedRangeEnd is always the day AFTER your range ends, so you can use < to get all times on the last day.

This is what i came up with for SQL Server:

Previous Day
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) -1,0) and
X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullWeek
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) -6,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) +1,0)

Last 7 days(to yesterday)
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)-7 ,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

Month to Date(to yesterday)
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

YearToDate(to yesterday)
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullMonth
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

LastFullYear
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)

And this is what i came up with for for Oracle:

Previous Day
X.DATE >= TRUNC(sysdate) - 1 and X.DATE < TRUNC(sysdate)

LastFullWeek
X.DATE >= trunc(sysdate - 7,'WW') and X.DATE < trunc(sysdate,'WW')

Last 7 days (to yesterday)
X.DATE >= TRUNC(sysdate) - 7 and X.DATE < TRUNC(sysdate)

Month to Date(to yesterday)
X.DATE >= TRUNC(Sysdate,'MM') and X.DATE < TRUNC(sysdate)

YearToDate(to yesterday)
X.DATE >= TRUNC(Sysdate,'Year') and X.DATE < TRUNC(sysdate)

LastFullMonth
X.DATE >= TRUNC(TRUNC(Sysdate,'MM') - 1, 'MM') and X.DATE < TRUNC(Sysdate,'MM')

LastFullYear
X.DATE >= TRUNC(TRUNC(Sysdate,'Year') - 1,'Year') and X.DATE < TRUNC(Sysdate,'Year')





How to reorder tables to improve performance

Monday 17 June 2019 @ 12:20 pm

So say you have four tables A, B, C and D. A joins to B, B joins to C and C joins to D. If all the tables are required in the results (i.e. you are using inner joins) you can theoretically use 4 different link configurations that should give you the exact same output. If you start with A or D the joins would be in a straight line (ABCD or DCBA).  If you start with B or C you would get a fork, like B to A and B to C with C linking to D. But even though the results will be the same, the performance could be dramatically different. So how do you decide which pattern is most efficient?

There isn’t a simple answer that works in every case, so testing is important. However, there are two places I look that often help: the indexed fields and the WHERE clause fields. You can often see the indexed fields in the linking window (colored tabs) or you can ask someone who knows the database what the indexes are on each table. To see the WHERE clause fields go to the database menu in Crystal and select “Show SQL Query”. The fields mentioned in the WHERE clause should match your record selection formula.  If they don’t you may need to tweak the formula so that the criteria can translate into SQL.

Indexed fields:
When linking you want your join to go TO indexed fields and ideally to ALL the fields in that index. So say Tables A and B are linked on two fields from each table. And say that these four fields all have red index tabs. But table B has a third field with a red tab and that field isn’t part of the join. That would mean you should link from B to A.  This uses the complete index in A which is the more efficient than linking to the partial index in B.

And don’t assume that because B is sitting on the left that the join starts at B and goes to A. I always hit the “auto-arrange” button in the links window to confirm the direction of the joins. After hitting “auto-arrange” all the joins flow from left to right. If a join is backwards, you can right-click that join and select “reverse join”, then click “auto-arrange” again to confirm the new direction.

Here are some other posts where I discuss the affects of linking on indexes:
https://kenhamady.com/cru/archives/2923
https://kenhamady.com/cru/archives/2653

WHERE clause fields:
Now lets also say that most of the WHERE clause criteria applies to the C table. I try to take the table with the most restrictive criteria and put it all the way to the left (or as far left as possible). That way they query starts out with the smallest data set possible and each subsequent join has fewer matches to find.

If the primary field in the WHERE clause is found in more than one table you get some flexibility. You can select the table that works best for indexing and then use the field from that table in the criteria.

So based on the above scenario I would recommend starting with table C. Then forking from C to both B and D, with a final link from B to A.

In some rare cases the indexed fields  and the WHERE clause fields can’t both be optimized at the same time because they point in opposite directions. When that happens you have to test different join patterns to see which works best.

One last note. In most reports the order of the joins is obvious from the link pattern.  But if you look at the SQL and the links aren’t in the order you want, you might have to use the “order links” feature of the database expert.

 





The last resort when you need an extra pass

Saturday 8 June 2019 @ 2:07 pm

I had a request this week that sounded relatively simple on the surface. The data was a list of people with from 1 to 10 characteristic rows. The wanted me to assemble all the characteristics for each person into a single alphabetized string, and then count how many people had each string combination. This meant that I had to Group [by person] and then Sort [by characteristic] then Group again[by the combined string]. That is one pass more than Crystal Reports can do.

My normal solution for this would be to do the first pass in the database using a SQL command. And I would have succeeded if the data had been in SQL Server or Oracle. The RowNumber () and Partition functions I wrote about recently would have been part of the solution. But, alas, the data was a classic MS Access MDB file.

After quite a bit of research I found a way to write a SQL command for MS Access that would do the job.  It worked in my test data, but it took hours to run on a normally sized sample of data.

So I offered the customer a relatively fast two-step approach, which is my last resort for getting an extra pass. This involves writing one report to do part of the work, then exporting the results to a spreadsheet, and finally creating a second report to create the final output from the spreadsheet data.

In this case the first report groups, sorts and assembles the string of characteristics for each person. This is exported to a spreadsheet as one column of data, with one row per person. Then the second report reads this spreadsheet and groups on that column and counts occurrences of each value. The process takes a few minutes.

One thing to note, this process is very simple if your export can use classic (XLS) spreadsheets. Crystal includes a native driver that can read tables in XLS files. But XLS files are limited to 64K rows.

The newer XLSX files can hold up to one million rows, and versions of Crystal since 2011 can export to XLSX files. But reading an XLSX file with Crystal requires that you have newer MS Office drivers that don’t come with Crystal Reports. To see if you have these drivers you can create a new OLEDB connection and look in the providers list for:

“Microsoft Office 12.0 Access Database Engine OLE DB Provider

If you don’t see this provider listed you can download and install the drivers.





Server-based scheduler comparison (2019)

Monday 27 May 2019 @ 10:20 pm

I have just updated my comparison of server-based scheduling tools for 2019. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 11 products on the list this year and a few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.





Using the ExtractString function

Friday 24 May 2019 @ 7:49 pm

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.





Impossible link in Pervasive SQL (Elliot)

Tuesday 14 May 2019 @ 3:14 pm

I have written before about databases that take selection criteria from Crystal and then use the wrong index so that valid records are missing from the results. The solution is to write the criteria so those rules don’t make it into the SQL WHERE clause. Crystal can then apply that criteria locally so it is done correctly.

But today I ran into a similar problem that didn’t have a simple solution. I was creating a report to read Elliot data. Elliot is what used to be called Macola Accounting. We were connecting to a Pervasive SQL DB using an ODBC connection. We were trying to link the Item file to a second instance of the Item file to get a list of components for manufactured items. What we found is that when we joined the component ID from instance one to the part ID in instance two, the results would not return a single match between the two tables.

Looking at the tables separately showed the matching data was there. And when I tried to filter to a single PartID the results would not find that ID. This is when I realized that we had an index problem like the one I described above.

So I looked at the index tabs in the Database Expert and noticed that this table had two red index tabs, meaning there were two fields in the primary index. The tabs were on Item Number and Sequence number. We were linking from a table where there was a component item number but there was not a component sequence number. It appears that Pervasive SQL defaults to using the primary index for ODBC joins, even if the fields you are using for the join don’t completely match the fields in the index. So the link will fail every time. I even unchecked the option “use indexes or server for speed” to see if that would help, but it didn’t have any affect.

We were lucky that the table we were using had an equivalent view. We linked this view to itself and we were finally able to find matching records. I assume this worked because this view, like most views, are not indexed.





Next Posts »» «« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server