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