The Crystal Reports Underground News - Volume 2002.12 (December 2002)
an independent source for Crystal Reports Information by Ken Hamady
This months contents:
** NEW! The Expert's Guide to Crystal Reports Formulas
** Download a complete list of all of Crystal's Technical Briefs
** Expert on-site training or consulting
** Converting MS Access Reports to Crystal Reports, automatically
** Crystal Desk 3.0 and a New Report Viewer
** Public classes in Leesburg, Baltimore and Philadelphia
** Using Cross-tabs to Increment Running Totals
** A New Formula Debug/Trace Window in Crystal v9
** Warning! Crystal v9 Commands can now Read and Write
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html
NEW! The Expert's Guide to Crystal Reports Formulas:
Let me take you "under the hood" with my new book, "The Expert's Guide to Crystal Reports Formulas". The guide explains Crystal Formulas from the simplest to the most complex. And, you get more than just examples of syntax. I explain HOW the various techniques work, WHY they work and WHEN to use them. I cover all of the major topics, including tips and tricks that are rarely taught in classes. So, if you want a clear explanation of variables, arrays, looping logic and select case statements, this guide is for you. See the full topics list on my web site.
The guide includes 28 annotated reports that demonstrate the techniques discussed in the material. These reports are saved with data so you can immediately preview them, modify them and experiment. The guide contains 45 no-nonsense pages of detailed explanations and practical examples. It is applicable to all versions of Crystal, although the sample reports will open only in versions 7 through 9.
The cost is only $36 and you can usually have it within an hour. For more details, visit my website at:
http://www.kenhamady.com/form00.html
Complete list of Crystal technical briefs:
Each month I scan Crystal's website to find new and useful technical documents. Now you can download a complete directory of all of the technical documents on the site. The directory is contained in several PDF files that list all of documents. Each list shows the name of the document, a short description and a direct link to download the document.
The Links page on my website has a the link to the directory PDF files.
Expert On-Site Training or Consulting:
I have personally taught over 1400 satisfied students in more than 30 states. Training with the customer's data is my specialty, and I charge about half of the "List Price" for Crystal Training.
Do I know my stuff? Check out the Crystal Reports Forum at Tek-Tips.com and you will find me listed as the top Crystal Reports expert out of over 21,000 members:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Can I teach? Many students have said that my classes are among the best technical classes they have taken. Ask to speak to a recent customer as a reference. Call for more information at (540) 338-0194.
Converting MS Access Reports to Crystal Reports:
Diamond Edge, a small outfit in Utah, is selling the first tool on the market that can convert an MS Access Report into a Crystal Report. It is a part of a product that they sell called the Access Converter that is designed to convert Access applications into a VB/Java applications. This is great news for anyone with a heavy investment in MS Access.
The tool costs $395, and requires that you have CRv8.5 Developer Edition installed on the same PC. It does NOT currently work with CRv9 Developer Edition, but this may change in the near future. I was told by one of the Diamond Edge staff that the reports may need some minor tweaking, especially if you use complex joins. And, since Crystal Reports allows formulas to be written in either Crystal or Basic syntax, the Converter leaves all expressions in basic syntax.
You can download a trial version, but it will only convert the application and reports in the MS Access sample database, Northwind.MDB. I wish I could tell you how well it worked, but since I have version 9 of Crystal, I couldn't run it. But, even if this tool provides only a rough conversion, it could still be a huge timesaver, especially if you have to convert many reports.
You will find the Access Converter on the Links page of my web site, listed under utilities.
Crystal Desk 3.0 and Report Viewer 9.0:
CrystalDesk has just released Report Scheduler v3.0, an update to the original Crystal scheduler program. It allows you to run reports on a regular schedule or on a one time basis. The new version adds PDF export capability and has a more user-friendly interface. See my links page for details.
Price - Single User License $229, Site License $998
CrystalDesk has also added another report viewer to the market, called Report Viewer 9.0 (compatible with CRv9). It allows you to View, Print, Export and Email any Crystal Report. It can view reports with saved data or connect to your database and refresh the data. It even allows command line control so that you can use it with applications or batch files.
Prices - Single User License $48, 5-Pack $189, Site License $948.
Don't just take a class, learn from a Crystal Expert:
Baltimore, MD
Introductory class January 29-30
Advanced class February 25-26
Philadelphia area, PA
Introductory class January 27-28
Advanced class February 27-28
Leesburg, VA
Introductory class February 6-7
Advanced class January 13-14
Each class is $675 per student.
Course outlines and registration info are on my website at:
http://www.kenhamady.com/public.html
Or, call for more information at (540) 338-0194.
Using Cross-Tab cells to create running totals:
I read an article on Crystal's website this week that has opened up a whole new area to explore. If you you have worked with variables in Crystal Reports, particularly running totals, you know that the location of a formula determines how often it "fires" the variables. These formulas are usually located in one of the 7 sections of the report. But, you can also fire a variable in the formulas that control conditional formatting.
What I learned this week is that you can fire variables in the conditional formatting formulas of every cell in a cross-tab. These formulas give you access to the values in the cross-tab, using the "CurrentFieldValue" function. That means that you can take the values inside a cross-tab and pass them to formulas later in the report. The example given in the article showed that you could recreate a running total in a cross-tab located in the group header, and display the running total before the details of the group. This is something that isn't normally possible in a report.
The variables that you can fire in the cross-tab can't be displayed within the cross-tab itself, but they can be used in other formulas in the report. Starting with version 8 you can also store the row and column heading values of any cell, and then pass them to other formulas. So, for example, I could write a formula that checks each cell and stores the highest value in the cross-tab. When I find it, I could also store its row and column labels, and use display these values outside the cross-tab.
The cells that you choose to conditionally format will determine how often your formula will fire. In general, the formula will fire each time the formatted cell appears in preview, and most cells appear more then once. A basic cross-tab, with one row field and one column field, has 8 cells in design mode. If you format the center cell, your variable will fire on every instance of that cell in preview. In other words, it will fire on each of the interior cells. If you format the middle cell of the bottom row, your variable will fire at the bottom of each interior column (not the first or last).
The one surprise came when I tried to figure out which cells of the cross-tab would fire first. To check, I formatted every cell in my cross-tab to append its current value onto the end of a single long string variable. The order of the items in the string told me which cells had fired in which order. The order I discovered, shown below, wasn't what I expected:
First all of the cells with totals:
1) The first column of totals from the top total down to the bottom.
2) The next column of totals from the top total down to the bottom.
3) The other columns of totals, in turn, going from left to right.
Then the cells with labels:
4) The lower left corner cell that has the label "Total".
5) The row labels (first column) starting at the bottom and going up.
6) The upper right corner cell that has the label "Total".
7) The column labels (top row) starting at the right and going left.
One note, you may want to make the Cross-tab invisible, however this takes a bit of work. If you simply suppress the cross-tab, or the section holding the cross-tab, it won't run. None of your formatting formulas will fire. You can, however, suppress the cells and lines of the cross-tab, making it invisible. And then underlay the section so that it doesn't take up much space. For a more detailed explanation, and a sample report, you can purchase my "Expert Techniques Volume I" which shows this technique and 29 other Tips and Tricks.
Debug window in v9:
Many of you have worked with complex formulas. You know how frustrating it can be to get a message that says:
"the subscript must be between 1 and the size of the array"
and not have the faintest clue which of your twenty subscripts is causing the problem. Crystal v9 now provides some help. There is a new debug/trace window that appears when a formula generates and error.
When the error occurs, the Formula Workshop opens, showing you the formula that generated the error. The last few steps of processing are now shown in a pane to the left of the formula window. Each step shows the field, variable or expression as it was processed. Next to each item is the value of that object at that point in the process. Variables show their current value(s), even if the value is an array or a range. Fields show the value returned from the current record in the table. Expressions show the calculated result of the expression. This is a great time saver for complex formulas.
Warning! Crystal v9 Commands can now Read and Write:
One of the selling points for Crystal Reports has always been that you can give it to end users without having to worry about them destroying data. Unlike MS Access, Crystal has never provided the ability to write data back to an existing table. Even when using the Export feature, an export to ODBC was always forced to create a new table, so that it could never affect the data.
While this is still essentially true in v9, there is one way that Crystal can be considered a read/write tool. This is in the area of SQL commands. Crystal v9 allows you to run your report using any SQL statement as a data source. This can include statements pasted into Crystal from another source, like an existing stored procedure. This adds significant power to Crystal Reports, but comes with a new and significant risk.
The risk is that SQL can both read and write to the database. If you copy a SQL statement from another source, you might not notice that it makes changes to the data tables. If you run this statement from Crystal, and you have read/write access to the database, you could unknowingly corrupt your database. Of course, this has always been true in MS Access, but this is new with Crystal.
Thanks to Bruce Ferguson at Chelsea Technologies for pointing this out.
VB Quick Reference Guide, updated for v9:
If you need to incorporate Crystal Reports into a VB application, you should get my Quick Reference Guide. It gives an overview of the integration methods, with syntax examples for the most important commands. It comes with source code examples including a simple report viewer. It also includes troubleshooting tips. It is only $16 and can be Emailed to you, usually within an hour. Follow the link below for more details:
http://www.kenhamady.com/vbref.html
Removal instructions:
I have sent you my newsletter because you are a former client, or because you have contacted me regarding Crystal Reports. If I am mistaken, or if you don't wish to receive the newsletter, please reply to this message with the word 'unsubscribe' in the header.
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written permission