The Crystal Reports Underground News - Volume 2019.07
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July 2019:
** RPT management utilities for 2019
** Preventing the "division by zero" error
** My library of Crystal Reports materials
** How to make sure you are on the last day of the month
** How to reorder tables to improve performance
** Let me create your Crystal Reports
** Finding groups where the last record meets a criteria
** Moving cross-tab numbers to Excel
** Individual Training with an expert.
** SQL Syntax for several common date ranges
** The last resort when you need an extra pass
Gems from the Archives
Troubleshooting Duplicate Records (2009.07)
Converting seconds (Universal Time) to a DateTime value (2009.07)
RPT management utilities for 2019
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
Preventing the "division by zero" error
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.
My online library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
How to make sure you are on the last day of the month
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.
How to reorder tables to improve performance
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.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Finding groups where the last record meets a criteria
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
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.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
SQL Syntax for several common date ranges
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')
The last resort when you need an extra pass
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.
Gems from the Archives
Troubleshooting Duplicate Records (2009.07)
Converting seconds (Universal Time) to a DateTime value (2009.07)
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2019 by Ken Hamady
All rights reserved - Republishing this material requires written permission