Contents for October 2009:
** Master topic index for my guides and course books.
** Customizing Crystal Reports Start or Splash screen
** My library of Crystal Reports materials
** Rounding bug mimics 'banker' rounding
** Crystal Reports and the color orange
** Merge, Split and Rotate PDF Files (for free)
** Learn Crystal Reports (your place or mine)
** Formula for todays date in contract format
** Other recent blog articles:
    SAP solves the Zebra printer issue?
    CR 2008 Web viewers lose text rotation
    Crystal Reports 2008 Service Pack list
    Clarion/Topspeed ODBC fails (and is solved) in XI/2008


Master topic index for my guides and course materials

In my last newsletter I announced the beginnings of master index to cover my series of "Expert's Guides" and "Expert Techniques" for Crystal Reports. I have now completed the index for all of these guides.  I have not included the 2 Quick Reference guides for VB and .NET (yet).  The index is available as an HTML page and also as a PDF that you can download to use off-line.

In addition, I have created 2 PDF indexes that cover my course books.  There are two indexes because many students were taught using an older version of the course books with slightly different pagination.  If your Intro book has 50 pages and your Advanced book has 80 pages then use the v12 index. If your Intro book has 49 pages and your Advanced book has 76 pages then use the v11 index.


Customizing Crystal Reports Start or Splash screen

If you spend as much time in Crystal Reports as I do you, you might appreciate being able to customize the look a bit.  I found a pair of blog posts on the SAP website that show you how to change the splash screen image, and also how to add your own background image to the Crystal Reports start page.

The splash screen is simple to change.  Just take any bmp and rename it to splash.bmp.  Then put it into the same folder that has the file CRW32.exe.  The default folder, unless you change it is:

C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0win32_x86

Changing the start page is a bit more work.  There is a file called start.html in this folder (usually):

C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0win32_x86Start Pageen

If you know how to edit HTML and Javascript you can do whatever you want with the code of this file. If you don't want to write code you can simply download the sample file provided in the blog post, then follow the instructions for naming your image file and locating it into the correct folder.

One tip I can add from having done this:  if you use an image that either dark or busy at the top, it might be difficult to read the 'recently used file list' on the start page.  I used Paint to edit my image so that it has a 2-inch white border at the top.  That gave the recently used file list, and the list of wizards, a place to print in the clear above my picture.


My complete library of Crystal Reports materials

Let a master teacher help you understand these Crystal Topics.  Each guide comes with clear explanations and sample files to illustrate each concept.

    Expert's Guide to Formulas  ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to SQL Expressions, Options and Commands ($26)
    Expert's Guide to Totals ($24)
    Expert's Guide to Cross-Tabs ($22)
    Expert Techniques Vol. 1 - 4  ($19 each)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site.


Rounding bug mimics 'banker' rounding

There are two types of rounding in Crystal Reports.  I call them 'real' rounding and 'fake' rounding. Fake rounding is what you do when you use the toolbar button (or Format > Field) to reduce the number of decimals visible.  Real rounding is when you write a formula field using the Round() function.  I usually avoid fake rounding because it doesn't change the underlying values.  If you total a column that uses fake rounding, your total won't match the visible values in the column.

Last week a tek-tips.com user wrote that fake rounding seemed to be using 'banker' rounding. Normal rounding always rounds a 5 up, but 'banker' rounding rounds a 5 either up or down to get to an even number. He asked how to turn off the banker rounding.  I had heard of 'banker' rounding but had never heard of Crystal using it, so I did some tests. I found that in versions 9 – 11 of Crystal, you do get some odd rounding that might fool you into thinking that CR is doing banker rounding.  But after a bit more testing I realized that what looks like banker rounding is really just a bug in the program.

The first clue that it was a bug was that it only affects numbers that have four or more decimals. Rounding a two or three decimal number with fake rounding will always round a 5 up in the traditional way.  And even on these four or five decimal numbers, it will round a 5 to both odd and even numbers.  Banker rounding should consistently round a 5 to an even number.  To see this visually you can open my test (pdf) that shows the dispersion of the rounding error in 600 consecutive values ending in 5.  Note the inconsistent pattern in the values that are rounded down.

This bug does not affect version 8.5 or version 12 (CR 2008).
 

Crystal Reports and the color orange

When you want to set the color of an object in a CR formula, you can pick from one of the 15 default color words like CRRed or CRYellow. If you want an unlisted color you have to give the formula the three RGB values for the desired color.  For example Color ( 238 , 130 , 238) gives you violet.

One way find these values is using the color selector on the toolbar.  Drop down the selector and click "more' to get what I call the "smear palette".  This lets you click anywhere within a rainbow-like area and see the matching 3 numbers for the exact shade where you are clicking.  But what if you aren't sure which flavor of 'orange' is really your basic orange?

A second approach is to go to a site like this one which maintains a color chart by NAME. That way, if you know that you want "dusty rose" you can just look up the name and get the numbers. This chart shows the standard orange to be color (255 , 165 , 0).  Now you might think that color naming is subjective, but I checked several other charts and didn't find any variations.  For instance, all made the same exact distinction between "Bisque" (255/228/196) and "Blanched Almond" (255/235/205) although these colors are almost indistinguishable (at least to me).

Of the several sites that show colors with names, the list linked above has the longest list.  For other examples you can Google "RGB to color name".


Merge, Split and Rotate PDF Files (for free)

If you export two reports to PDF and want to merge them into one, or combine them with other PDF files, then you might want to look at a free tool I just discovered called PDFsam.  This isn't a trial version of something but a free open source tool.  I tried it recently when I needed a way to merge a Word doc and a Spreadsheet into a single PDF (rotating the Spreadsheet 90 degrees in the process). It did the job.

Whenever available, I opt for "free open source software" (FOSS).  Software that is open source means that it was developed by a volunteer team that provides the source code for their application for free. Not only do you know it is free but it makes it much less likely that someone will try to include malicious code.   If you want to see what is available in FOSS you should check out SourceForge.net, one of the most reliable sources for FOSS.

 
Learn Crystal Reports (without needing a bailout)

Even with budgets tight there is no need to miss out on Crystal Reports training. You still need information to do your job, so stop struggling with Crystal Reports and learn what it can do. The most cost-effective way to be taught Crystal Reports is in my individual training program.  It is ideal for people who:

   Can't take 2 days off for a regular class.
   Want to learn in their own database.
   Need to cover only a few topics
   Want to learn from someone who has taught 2,500 satisfied students.

Remember, the cost of a typical 2-day Crystal class is enough to buy 7 hours of individual instruction time with me.  And 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.

Or, if you want to schedule a class at your office, using your data, I can save you money there as well.  I have discovered some top-notch instructors all over the US, UK and Canada and use them to deliver my class at a very competitive price.  Call for more details.


Formula for todays date in contract format

One of my customers recently needed to change today's date into text for contract wording.  That meant changing 9/1/2009 into  "this 1st day of September, 2009″.  Most of it was simple but the tricky part was adding the "ordinal suffix" (as in 1st, 2nd, 3rd, etc).  So I did a bit of playing around and came up with this:

WhileReadingRecords;
NumberVar DayIn := Day (PrintDate);
"This "
& Totext (DayIn , 0 )
& (if DayIn in 4 to 20 then 'th' else
if remainder (DayIn , 10) = 1 then 'st' else
if remainder (DayIn , 10) = 2 then 'nd' else
if remainder (DayIn , 10) = 3 then 'rd' else 'th')
& " day of "
& Totext (PrintDate , "MMMM, yyyy")

Note that there are several formulas that can do ordinal calculations for larger numbers, but this simple one works for dates, since day numbers are never bigger than 31.