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