Contents for July 2009:
** NEW! Expert Techniques Volume 4
** Troubleshooting Duplicate Records
** Converting seconds (Universal Time) to a DateTime value
** My Library of Crystal Reports materials
** IT World Canada weighs in on CR licensing
** SAP/BO 2009 User Conference
** Learn Crystal Reports (your place or mine):
** Refreshing reports hosted on CrystalReports.com
** Other recent blog articles:
    Formula trouble using "Integer Divide" (the backslash)
    SAP users experiencing "performance" problems   
    Lawson structure documentation on the web
    Changes in the BOCP registration process
    CR2008 SP1 is incompatible with SP0


NEW! Expert Techniques Volume 4

Many of you have purchased one or more volumes in my Expert Techniques series. These contain my favorite tips and tricks in Crystal Reports, accumulated over the years. I have just put together Volume 4 with another 30 articles. Each is illustrated with an annotated report that demonstrates how to use the technique.  As always, they cover a wide range of topics.

Some are specific advanced methods like:
How to do a fixed length text export from a Crystal Report (#115)
How to do a multi-level Bill of Materials (#118)

Some are little formatting tricks like:
How to automatically size the dotted line between two columns – like a TOC (#105)
Lines and boxes that grow or disappear based on a condition (#107-108)

Some are general purpose lessons like:
Different ways to use "feeder" formulas (#94-95)
Tips for naming formulas in a logical way (#91)

Check out the full list of articles that are in Volume 4. Even if the article titles don't seem to apply to your reports, I am confident that you will learn some tricks that you will find useful down the road. The price is only $19 per volume – a bargain even if only one article helps you solve a problem.


Troubleshooting Duplicate Records

Last week I had 4 different customers ask about the same problem – duplicate records in a report.  After explaining my process for troubleshooting duplicates several times I decided it would make a good article. So here is what I do when facing duplicates:

1) The first thing I do is check the joins to see if one of them is obviously wrong or only partially complete.  A partial join is a join that needs 2 or more fields and not all the needed links were made.  For instance if you have an accounting system that allows multiple companies you may need to link two fields to identify a vendor, the Company ID and Vendor ID. This is because Vendor #7 might be a different in company A than in company B. Or maybe the link to a GL account also needs a subaccount.  If you only make one of these links then your link isn't specific enough and you will get multiple irrelevant matches for each record.

2) If that doesn't solve the problem, and I don't see any obvious source for the duplicate, I start a brand new simple report to test each link.  I start the report with just the primary table and include enough fields to confirm that there aren't any duplicates there.  Then I add a second table to the report, link it to the first, and add one or two fields from that table. I do each table one at a time. After the fields from each table are added I check to see if the duplicates appear.  When they appear I know the source of the duplicates, the last table added.  (Note – it is very important to add at least one field from each new table to the layout of the report.  Otherwise Crystal won't incorporate that table into the query, and you don't really know if the link is valid or not.)

3) Once you identify the table that is the source of the duplicates you have to determine if the duplicates are irrelevant records caused by an incomplete join, or if they are valid records in an intentional one-to-many table relationship. For instance, if you link payments to charges you may find that a single charge is allowed to have more than one payment.  Linking a charge to multiple payments would make the charge appear multiple times, as if it were a duplicate.  But this is an intentional part of the database design.

To answer the question you add a few more fields from the last table to see if the data corresponds to the existing data.  If the new columns from the last table add valid data to the existing columns then you probably have an intentional on-to-many relationship. But if some (or all) of the data in the last table does NOT match the existing values, then you probably have a bad or incomplete link.

If you need help with this process, or dealing with intentional duplicates, give me a call.


Converting seconds (Universal Time) to a DateTime value

Universal Time (also known as UTC/Unix time/Posix time) is a DateTime value stored as the number of seconds since 1/1/1970 (at the Royal Observatory in Greenwich, England).  The values for 2009 are around 1.2 billion. One of my readers did some work to convert these values into normal DateTime values and wanted to work on adjusting for her local time zones, as well as adjusting for Daylight Saving Time.   She contributed some logic and I polished it up a bit and the end result is posted below.


//Calculate UTC as a DateTime
DateTimeVar UTC:= DateAdd ('s' , {Your.UTCField} , Datetime (1970,1,1,0,0,0));
//Adjust UTC to Eastern Standard Time (or your time zone)
DateTimeVar EST:= DateAdd ('h' , -5 , UTC);
//The optional lines below does the DST adjustment for 2009 in Eastern Time
If EST in DateTime (year(EST),3,8,2,0,0)  to DateTime (year (EST),11,1,0,59,59)
then DateAdd ('h' , 1 , EST)
else  EST

If you don't know how to convert Greenwich time to your local US time zone you can refer to this chart.

In this example you would have to change the DST dates each year based on when DST starts and stops. I have a formula in Expert Techniques volume 3 that can automatically calculate the DST dates for each year. There are also custom function DLLs that you can use to do these calculations for you.  See my previous blog post for more information on using these DLLs.

Note that some databases have something like a UTC that counts seconds since 1900 and some mainframe systems even count one hundredths of a second.  If you don't know how to make adjustments for these situations, give me a call.  And if you like tips like this you should check out my Expert Techniques series.


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.


IT World Canada weighs in on CR licensing

IT World Canada just published an article on the games that SAP/BO is playing with the proposed new CR license terms.  The article includes some history and feedback from instructors, analysts and publishers.  I found it interesting and even learned a few things I didn't know.


SAP/BO 2009 User Conference

The 2009 SAP BusinessObjects User Conference is being held in Dallas this year on October 18-21, 2009. You can read more about it on the official site. The cost is $1,495 at the door, $1,395 in advance and $1,195 before 8/21/2009.

If you want to go, and aren't sure you can get that amount approved, you can always download their handy "Conference Justification Document".  This is a boilerplate proposal (fill in the blanks) that you can submit to your manager to convince them that the conference is worth the money and time.  The first paragraph ends with my favorite line:

"This content will be directly applicable to my work on <project(s) you are working on>."

I won't be attending the conference but several of my colleagues are regulars.  If you decide to go let me know if you found it worthwhile.  If you decide not to go I can think of several ways to spend that amount of money and truly make it 'directly applicable' to the reporting project that you are working on (see below).


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 Crystal Reports 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 time with me.  You can start with 2 hours and get free course materials with exercises.  Do as much work as you like on your own and use your prepaid time to work with me by phone and remote connection. We can review lessons, discuss questions or even troubleshoot existing reports - my time is yours.  For 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 several top-notch instructors all over the US, UK and Canada that can deliver my class at your location for a very competitive price.  Call or visit my web site for more details.


Refreshing reports hosted on CrystalReports.com

Some of you are probably familiar with CrystalReports.com (known as CRDC).  This is the site sponsored by SAP/BO where you can host your reports and make them available on the web.  When CRDC was introduced a few years ago it only allowed you to share reports that had saved data.  Then it changed to allow you to refresh reports if your had a server product like CR Server or BO Enterprise.  More recently they broadened this to refresh data from your SalesForce.com database.

I just learned recently that Crystal Reports.com now has the ability to refresh reports from pretty much any data that can be made available to your web server, which makes a workable solution for some users. To make it work you have to install some software on your server which allows CRDC to reach behind your firewall.  This software runs the query against your local database and send it back to CRDC.  The software is called the Open Data Connector (or ODC).  There is an online PDF that explains how to install and configure the ODC but it is a significant project and requires that you have a web server configured. In my opinion, there are simpler solutions to consider.

You also have to consider the limitations of the CRDC/ODC setup.  Reports that use the ODC can only use one data source and can't contain any subreports. And the costs of CRDC can add up quickly.  There is a free version of CRDC but that doesn't allow refreshing reports.  The free version is limited to 3 users and 10 reports.  If you upgrade to the professional version the lowest plan is $300 a month (for 10 users) or $3,600 for the first year.  There are less expensive options for web deployment and Email scheduling on the LINKS page of my site.  Don't forget to request my "favorites" list.

When writing this I noticed some glowing testimonials from CRDC users. I then noticed that two of the five testimonials were written by people I knew.  So I contacted those users to see if they were still happy with CRDC. What I found was that both had since found better and less expensive solutions using my favorites list. So they abandoned CRDC and are both using solutions I recommend on my site.  I didn't try to contact the other three users, but I was "two for two" so you can draw your own conclusion.


Other recent blog articles

    Formula trouble using "Integer Divide" (the backslash)
    SAP users experiencing "performance" problems   
    Lawson structure documentation on the web
    Changes in the BOCP registration process
    CR2008 SP1 is incompatible with SP0