The Crystal Reports Underground News

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

(For recent updates read my BLOG, The Crystal Reports Underground )

The Crystal Reports Underground News - Volume 2003.10 (October 2003)

an independent source for Crystal Reports Information by Ken Hamady

Contents for October, 2003:

** NEW - Quick Reference for Crystal in .NET
** User Contributions:
    RAS sample code
    Displaying Multiple Range Parameters
** "best class I've Taken ... very knowledgeable"
** Octoburst and OctoPDF
** Crystal Command
** User Name Security in Data Link Viewer
** My Library of Crystal Reports Materials:
    Expert's Guide to Formulas
    Expert Techniques Volumes I and II
    Quick Reference to Crystal Reports in Visual Basic
    Quick Reference to Crystal Reports in VB.NET
** When "Select Distinct" doesn't work
** "Convert Null to Default" is disabled?
** Specifying Chart Colors by Group
** Email Delays from Verio
** Read back issues at http://www.kenhamady.com/news.html

NEW - Quick Reference for to Crystal Reports in .NET:

Some of you have asked if I was going to do a "Quick Reference Guide" for VB.NET. It has been on my "to-do" list for a year. Last month, one of my customers asked me to teach the basics of VB.NET integration, which forced me to grapple with the changes from VB6 to VB.NET. As a result, I have decided to package the class material with some sample .NET projects and make this my "Quick Reference Guide to Crystal Reports in VB.NET". This short document covers the basics of .NET integration including:

    How the Core Elements work together
    Starting a New Project
    Changing the Selection Formula
    Changing the contents of a Formula Field
    Changing the Group Field or Group Order
    Changing the Sort Field or Sort Order
    Filling in Parameter Fields
    Exporting to Other Formats
    Logging into the Database
    Manipulating Subreports
    Passing a populated DataSet to a report
    Links to other resources

The material is in PDF format, is about 12 pages long and includes 6 sample projects. It costs $14. So why would you pay for this when Crystal Decisions has free documents and code samples? As a teacher, I think it is important to understand WHY things work, so I think I provide better explanations. Most of the other sources that I have seen focus on WHAT to do. Also, the typical sample code is clouded with extra variables and unnecessary objects that are found in production code. These „real-world“ code examples sometimes make it harder to see the basics. I find this especially true with .NET. Tasks that used to take only one line in VB6 now require several lines and several extra objects. So, when a code example adds a few ‘extra’ objects, it becomes difficult to sort out the essential elements from the coding style. All of my examples use the fewest objects and variables possible. The goal is to make each command self contained so you can quickly grasp its use.

See the link below for more details:

    http://www.kenhamady.com/ntref.html

RAS sample code:

This month features some items that were sent to me by readers. This first item is a file of RAS code put together by Aaron Alexander of BlueBridge.com. Aaron spent a great deal of time on the phone with Crystal tech support and decided that he wanted everyone else to benefit from his experience. The code is VB Script for ASP and it includes code for parameters (up to 4 dynamically), record selection formula, connecting to the data and changing the datasource at runtime (main and subreports). Aaron says he has been running it trouble free for several months now. So, if you are doing RAS programming you might want to see if there is anything to learn from this file. You can find it at:
http://www.kenhamady.com/rascode.html

New Formulas for Multiple Range Parameters:

In recent months 2 different readers sent me similar formulas for displaying the choices made in a Multiple Range parameter. I have decided to add both of to the formulas for displaying parameter values. Check out the bottom of Formula #7 on my formulas page, if you are interested.

"best class I've Taken...very knowledgeable" October 2003:

This comment is from my class last Friday, and is not unusual. I have another set of public classes coming in December for Both Frederick, MD and Leesburg, VA. You can use the link below to review dates, course outlines and registration info:

http://www.kenhamady.com/public.html

Or better yet - schedule a private class at your office, with your data. This is my favorite way to teach, and I charge about half of what Crystal charges for on-site classes. I have personally taught over 1500 satisfied students everywhere from Tampa to Anchorage so don't assume that you are too far away. Call for more information at (540) 338-0194.

Crystal from the Command Line:

Gary Rogers, the developer of the Crystal Desk Scheduler has just released Crystal Command for launching reports from command lines. You can View, Export, Print and Email your reports without using Crystal reports. Crystal Command also helps you build the command lines. You can then run them from the Windows Task Scheduler, SQL Server Scheduler, windows batch files or your own application. The cost is $359 for a single user.

Note - using a scheduler to fully automate report distribution to more than 50 users may require special licensing from Crystal Decisions.

Multilevel Bursting to a directory Tree Structure:

If you would like to take a single a Crystal Report, fragment the sections into individual PDFs, and distribute the pieces into personalized folders, you might want to look at a combination of tools called OctoPDF and OctoBurst. This combination can take a Crystal Report export, burst it into any number of PDFs, and place each PDF into a user specific folder. One hospital uses this tool to burst a single large report into hundreds of individual department PDF reports. The reports are placed into folders with specific permissions, so that each department can only see their specific data.

The tools are not limited to Crystal Reports. It is mainly used to burst Line Printer files and convert them into PDF format, but it can work with several text formats, including some exported by Crystal Reports. You would need both OctoPDF ($495) and OctoBurst ($695 with OctoPDF) to accomplish what is described here. See the LINKS page on my web site for more info.

User Name Security in Data Link Viewer:

Millet Software recently added a feature to "Data Link Viewer" that I missed. You now have the ability to identify the user ID of the person running the report, and incorporate that into the selection formula. This allows you to develop a simply row level and group level security model for client based reports. In other words, each user can automatically be limited to seeing their own data.

My Library of Crystal Reports Materials:

Expert's Guide to Formulas ($36)
The most in-depth guide to formulas I have seen, includes sample reports for all formulas.

Expert Techniques Vol. I ($19) and Vol. II ($19)
60 of my favorite tips and tricks, all illustrated with sample reports.

Quick Reference to Crystal Reports in Visual Basic ($16)
The basics of the 3 most common integration methods.

Quick Reference to Crystal Reports in .NET ($14)
Launching reports from VB.NET applications.

When Select Distinct doesn't work:

Current versions of Crystal have a feature that allows you to automatically eliminate duplicate records from any "SQL" based report. This options is in the Database menu, and is called "Select Distinct". The menu item toggles the feature on and off.

Users are sometimes confused because they misunderstand what Crystal considers a "duplicate". To be a duplicate, two records would have to have the same values for every field USED by the report. This isn't just limited to fields that are visible on the report, but includes fields in the Select Expert, Sorting, Grouping and fields used in any formula. Users often ask me why they still have duplicates records after they activate this feature.

To see why the duplicates are still there, go to the database menu and open the "SHOW SQL Query" window. Look at the SELECT section at the top of the Query. This lists all of the fields used by the report. Put all of these fields on the details band of your report and you will quickly see which fields have different values in the seemingly duplicated records.

"Convert Null to Default" is disabled?

Crystal also has a feature that allows you to eliminate all NULL values in a report. It is found in the File Menu under "Report Options". I don't use this option very often because NULL values are often important to the report. However, sometimes it is the simple solution in environments with lots of NULL values that get used in formulas.

I recently opened a customer's report and found that the "Convert Null" option was disabled. I had never seen that before. I puzzled over it for quite a while and could not figure out how or why that option would be disabled. It took a while to stumble over the secret. The report had another feature activated, the "Perform Grouping on Server" feature. Apparently, this disables the "Convert Null Values" option. Since "Perform Grouping on Server" has little effect on most reports, we simply shut that feature off and then were able to activate the "Convert Null" feature.

Specifying Chart Colors by Group:

CRv9 has a nifty feature that I didn't notice till recently. You can now link chart colors to specific group values. For instance you can specify that Republican is always Red and Democrat is always Blue in a pie chart.

To see this option you must go into the Chart Expert and take out the checkmark that says 'Automatically set chart options'. This exposes the "Options" tab which has a button marked "Format". This button allows you to specify color conditions. You can use either the "On Change Of" field of the chart or the Subtotal value. There is an example described in Crystal KB article number c2011954. If you put this number into the search window of Crystal's website, the article will pop up.

Verio Email Delays:

My apologies to some of you who have had trouble communicating with me via Email, recently. It appears that the company that hosts my site and domain ( Verio ) has been having trouble with their Email servers for over a month now. They say it affects everyone with a shared hosting account with Verio. Any Email you send to kenhamady.com might take anywhere from 2 minutes to 2 hours to get delivered. On rare occasions it might bounce back to you without a clear explanation. Please give me a call if this happens to you. And if you were planning on switching to a Verio "shared hosting" account, you might want to confirm that this problem is resolved before you do.

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. I do not share these Email addresses with anyone else.