Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2008.011 (November 2008)
an independent source
for Crystal Reports Information
by Ken Hamady
Contents for November
2008:
** Changes to CrossLoop TOS
** On-Site classes just about anywhere:
** Email Hoaxes
** Extra Rows and Columns in Excel Export
** My Library of Crystal Reports materials
** Checking Dependencies
** InnoScript/Inno Setup handle Crystal Reports dependencies
** Overridden Qualified Table Names
** Grouping by a non-calendar week
** Read Back Issues
Changes to CrossLoop Terms of Service
It has been over a year since I started using CrossLoop with my
consulting customers. CrossLoop is a free desktop sharing service that
allows me to "remote control" any customer's computer. I use it
for training, troubleshooting and even report creation. And it is
simple enough for a non-technical user to install in 2-3 minutes, and
that install only needs to be done once. So I now do quite a bit
of consulting and training by remote session. If you need me to
create a report or demonstrate a technique, give me a call.
But for a few days this week I was researching other options after a
change in the CrossLoop Terms of Service page (TOS). It now says
that all commercial work done using CrossLoop has to go through their
payment system and involves paying them a commission. But after
contacting them, they said that this change doesn't apply when I bring
my existing customers to Crossloop. So even though this exception
isn't currently in the TOS, I was assured that users like me can keep
using CrossLoop for free as long as we aren't accepting referrals from
CrossLoop. They assured me that they would eventually clear up
the TOS page.
Open Enrollment classes in Frederick:
You need information to run your business, so
stop struggling with Crystal Reports and learn how to use it
fully. Come join one of my small classes (3-5 students) and learn
how to make Crystal work for you. My Intro class makes sure you
know all of the basics. We even include material on cross-tabs,
charts and formulas. The Advanced class shows you how to solve
several common reporting problems as well as when to use advanced
features like running totals, subreports, parameters and commands.
Users of any version are welcome. See my web site for more details or call me at (540) 338-0194.
So what makes my class different? I have written my own course
materials and have used them to teach over 2,500 satisfied
students. And, I give you a toll-free number so you can call me
after class with questions.
Or, if you want to schedule a class at your office, using your data,
that is my specialty. I have even found several other top-notch
instructors around 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.
Email Hoaxes
I am surprised at how many of these get forwarded to me. They are easy to spot because they usually:
1) Arrive with a half page of CC Email addresses at top.
2) Urge you to forward the message to all of your contacts.
3) Describes some emergency or noble cause that needs everyone's immediate attention.
I can assure you that ANY message that asks to be forwarded to everyone
on your contact list is a hoax. (So is every request for UPC codes,
pull tabs, post cards, etc - but that is another story.) So if you are
ever tempted to click "forward", first copy the key phrase from the
message and Google it along with the word "hoax". There are lists of
hoaxes out there just for this purpose.
Extra Rows and Columns in Excel Export
A few years ago I wrote a newsletter article
related to Excel exports. I described what you should do to prevent
extra rows, extra columns and merge cells when exporting to Excel from
Crystal Reports. Just last month I found another piece of the puzzle
that explains why my methods would occasionally not eliminate all of
the extra rows. It appears that if a row of objects is not at the very
top of the section (position 0) then Crystal will create double rows in
the spreadsheet. Even if all the objects are the same height, and all
are aligned in one clean row across, a tiny space above the row of
objects will export to a spreadsheet with data on every other row. Read
the original article above for more information on cleaning up your
Crystal Reports exports
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 Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. I ($19)
Expert Techniques Vol. II ($19)
Expert Techniques Vol. III($19)
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.
Checking Dependencies
The latest versions of Crystal Reports have a menu option in the
"Report" menu called "Check Dependencies". This is a handy little
feature that allows you to see if there is anything that would prevent
the report from running. For instance, I often find formulas that use a
field or function that is no longer available.
How does a report end up with a missing dependency? Here are two examples:
1) You remove a table and don't realize that fields from that table are being used.
2) Your formulas use custom functions and those function dlls are no longer available.
I see the second example quite often in reports for MAS90 and PeachTree
accounting. Both of these applications provide dlls containing
specialized custom functions. But if you open the report on a PC that
doesn't have these function dlls, the report can't run.
To check the dependencies simply select the item from the Report menu.
Crystal will generate a list all of the missing dependent objects in
the report.
InnoScript/Inno Setup handle Crystal Reports dependencies
For those of you who do VB development, there are several choices for
creating an installation file. The main commercial tools are
InstallShield and Wise, but there is also a free tool (donations
accepted) called Inno Setup, by Jordan Russell.
For those of you that create VB applications incorporating Crystal
Reports, you should check out Inno Setup. One of the main advantages
(besides being free) is that you can use a great little script
generator for Inno Setup called InnoScript by Randem Systems. InnoScript isn't free but is a great deal for $25.
InnoScript is designed for VB developers and generates complete Inno
Setup scripts directly from the VB project file. Best if all,
InnoScript now provides full and automatic support for Crystal Reports
versions 8.5 thru 11(XI). Randem Systems even maintains an archive of
the Crystal redistributable files. You can download the correct Merge Modules in an installable MSI format. To quote Will Fastie, a freelance IT management consultant:
"This is a brilliant piece of work that is available nowhere else, at
least as far as I know. I'm using this system now. Flawless."
And thanks to Will for pointing out these tools.
Overridden Qualified Table Names
Many of you have purchased my guide to using Crystal Reports in VB (v6) or my guide to using Crystal Reports in VB.NET.
In those guides I cover the basics of configuring and running a report
from within an application. I also explain how to change the basic
features of the report (groups, parameters, formulas, etc.) and how to
log the report into a secured database.
One thing that isn't currently described is how to log the report into
a connection that is different than the connection used at design time.
While the code is the same, there is one thing you have to do within
the report itself to allow it to work. In version 9 and later you have
to set the "Overridden Qualified Table Name" property for all tables in
your report. If you don't do this, the report will keep trying to get
data from the tables used at design time.
Some of you may remember that when we set the location of a table in
older versions of Crystal we occasionally had to remove the 'owner'
from the connection information. What is described above is the
equivalent step in the current versions of CR. What I find odd about
this is that 99% of the time the value you enter for this property is
simply the table name. Since Crystal already puts the table name into
the table alias property, you would think they could also fill the
"Overridden Qualified Table Name" property with the table name as well.
That wouldn't affect the reports that are run standalone, but it might
cut down on confusion among developers such as found in this post.
The post mentioned above also covers the alternative method of dealing
with this issue: writing a loop of code that sets this property for
every table at runtime. Below is some example code just in case the
forum page is deleted:
For all tables in the report you can do:
dbTable.SetLogOnInfo TheServer, TheDB, "<User_ID>", "<PWD>"
dbTable.Location = TheDB & "<Table_Owner>" & dbTable.Name
dbTable is a reference to a table in the report
Table_Owner is the owner of that table e.g. "dbo"
Grouping by a non-calendar week
If you group on a date field in Crystal Reports, the groups are always
based on calendar periods. So a group by year will be by calendar year
and a group by week will be by calendar week (Sunday to Saturday). If
you want to group by a non-calendar period, like a fiscal year, then
you have to write formulas. I have BLOG entries that show you how to
group by fiscal year and fiscal quarter.
One of my former students asked me today how to group dates into weeks
that aren't calendar weeks, like their pay week that starts on a Monday
and ends on a Sunday. I sent her a quick version, specific to her week
and then decided to make a generic version that I could use with other
customers. Below is a formula that you can use to have weekly groups
that start on any day of the week. Your date field goes in the first
line. You specify the start of your week by putting a number in the
second line between 1 (Sunday) and 7 (Saturday). In the example below
the number 4 means start the week on Wednesday. (You don't change the
number 7 in the last line.)
There is one important rule to remember when using this formula. When
you group on the formula you should set the grouping option to be "for
each DAY" rather than "for each WEEK". If you group "for each week" the
group name will be the Sunday before your week, even though the 7 dates
in each group will be right. But if you remember to group "for each
day" you will get both the correct weekly group and the correct group
name.
DateTimeVar YourDate := {Orders.Order Date};
Numbervar WeekStart :=4;
Date (YourDate) - DayOfWeek (YourDate) + WeekStart
- (if DayOfWeek (YourDate) < WeekStart then 7)
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
2008 by Ken Hamady
All rights
reserved - Republishing this material requires written permission