an independent source for Crystal Reports Information
by Ken Hamady, MS

Contents for January 2010:
** Server based options for Deploying Crystal Reports
** Advertising on KenHamady.com
** Crystal Reports 2010?
** Learn Crystal Reports the fast and flexible way
** Buying older versions of Crystal Reports
** Using unlinked tables (cross joins)
** My library of Crystal Reports materials
** "ALT" key temporarily releases guidelines and grid
** Using negative subscripts
** Using the parameter "batch" interface toolbar

Server based options for Deploying Crystal Reports

(This is a summary of a more detailed article posted to my blog)

There are many ways to deploy Crystal Reports to users.  I normally lean toward the simpler and less expensive options, like locally installed viewers.  But there are environments where a server based option makes sense.  The 'official' options from SAP are Crystal Reports Server and BO Enterprise.  You can read about these on the SAP website under the heading "SAP Business Objects portfolio".  But there are other products out there that most users never see. These are third party products that also allow you to centrally manage your report deployment from your server.  The list includes:

Ripplestone – Traditional portal
rePORTAL - Traditional portal
ReCrystallize Pro – Web Launch page generator.
Universal Report Server (URS) – Server-based scheduling engine
Report Launch – Bridge between BO server products and server based applications.

The products vary widely and are aimed at several different markets. You can read a brief rundown of what these products do on my blog.  You can also download a feature comparison matrix (PDF) that shows some of the specifics, including prices.  Links to all products are in the blog post and also on my LINKS page.

If you have tried one of these products, or are currently using one,  I would love to ask you a few questions – on or off the record.


Advertising on KenHamady.com

I have begun to offer banner ad space on some of my pages, replacing the ad server ads I have been hosting for the past few years.  Each page family will be a flat monthly fee and my initial response indicates that my prices are currently pretty reasonable. So if you have a product that you would like to promote to thousands of Crystal Reports users, give me a call. 


Crystal Reports 2010?

It appears that SAP will release a new version of Crystal Reports this year.  This isn't a big surprise given that it has been two years since the last version.  There are no details yet, just a casual mention by George Peck when discussing the SAP Users Group.   If anyone does have any details to share, I would love to hear from you.

And thanks to Gordon Portanier of Crystalize in Canada for giving me the heads up.


Learn Crystal Reports the fast and flexible way

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 for you. 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 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 instruction time with me.  And this is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free.  Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and shared desktop.  Your time can be used to review lessons, answer questions, troubleshoot or even help you create new reports.  For more 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.  Along with myself I have discovered some top-notch instructors all over the US, UK and Canada. All of us can deliver a class at a very competitive price.  Call for more details.


Buying older versions of Crystal Reports

If you need to buy an older version of Crystal Reports, all the way back to v8.5, you can now download them directly from BO (for $595).  This link includes all versions from 8.5 forward.

If you want to save a few bucks you can check on Ebay, where I regularly find older versions for sale.  Just make sure you know what you are purchasing.  In 2002 I wrote an article about what to look for when buying CR on Ebay.  That article still applies although some of the product codes might be different in newer versions.

Or check with me.  I sometimes know of extra copies that are available for sale.


Using unlinked tables (cross joins)

Normally if there is an unlinked table in one of my reports it is there by mistake.  However, I have recently been reminded of some of the neat things you can do with unlinked tables, as long as you are careful.  The official name for having unlinked tables is a 'cross join'.  The data that is returned from a cross join is known as a Cartesian product, which means every possible combination of records between the two tables. That is why you have to be careful.

So say you have a customer table with 100 customers in it and you use that table to create a report listing just the customer name.  Without adding any filters you would get all 100 records in the report.  But then you add a second table that doesn't link directly to the first, something like the master list of products.  If this table has 25 products in it then the cross-join for these two tables would be 2500 records (100 customers * 25 products).  Each customer would now have 25 records, one for each product.

So when can you use a cross join?

1) When adding a "Company Info" table (or something similar) to a report.  The "Company Info" table usually has only one record to store the software owner's company name and address, etc.  You might even find other single-record tables in your system.  You can add these tables without a link, because with only one record the Cartesian product causes no inflation.  Even if a table has two or three records, you can add a filter to get the one record you want and again no link is needed.  You can then use the columns of these unlinked tables just like any other field in the report.

2) Intentionally inflating the data.  You could use this method when you want to print a second "file" copy of every invoice, or when you want to generate duplicate copies of shipping labels.  You simply add an unlinked table that has two records to create a cross join (or even a table that you can filter down to two records).  This causes your existing data to double in size.  Using a three-record table will repeat the data three times, etc.  Just remember to add at least one field from the cross join table – otherwise Crystal will ignore it.

3) Filling in missing groups.  Some users want every group to appear, even if there is no data for that group. This can be especially tricky if you have several group levels, because you need every possibly combination to exist in the data.  One option is to use a cross-join to quickly create all possible record combinations.  This will probably require using a command so you can UNION your existing results to the cross join results.

I plan on writing up a more detailed discussion of this, with examples, to include in my next Expert Techniques volume.


My library of Crystal Reports materials:

Do you struggle with subreports?  Are you curious about cross-tabs? Why not let me explain these Crystal Topics to you through one of my Expert's Guides.  Each guide comes with clear explanations and sample files to illustrate the concepts.

    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.


"ALT" key temporarily releases guidelines and grid

I have come to depend on Crystal's Guidelines for aligning objects. I find them much more useful then the "Snap to Grid" feature, although I do use both from time to time. But there are often cases where the object needs to be somewhere in between the lines or grid points. It can be frustrating to try and position an object while it jumps up or down because it is trying to snap to a grid point or a guideline.

If you run into this problem there is a little known trick that might help. Hold down the "ALT" key while you are moving or sizing the object. The "ALT" key temporarily turns off both snap to grid and guidelines, without making any permanent changes to your settings.  Release the "ALT" key to activate them again.


Using negative subscripts

If you need to pull a single character out of a character string, or a single item out of an array, you can use a subscript to identify the position you want like this:

This example starts counting from the left to identify the third character or element.  Over the years I have had people tell me you could use a negative number to count from the right, but I had it stuck in my head that this was not reliable, so I hadn't done anything with it.  But I recently decided to do some testing and found that it works well, especially when you want the last character in a string or the last element in the array.  To get the last element in a string you would say:

To get the last element in an array you could say:

There are other methods using Length, Count or Ubound, but I think the negative subscript is as simple as it gets.


Using the parameter "batch" interface toolbar

Have you ever noticed that some parameters include a small toolbar below the List of Values (LOV) drop down?  This toolbar has two scroll arrows on either side of a small drop down list and two buttons with funnels on them.  The toolbar doesn't show up very often so it took me quite a while to figure out where it came from and what it is supposed to do.  So this article is for anyone else out there who is puzzled.

This is a called the batch interface.  It is included in Crystal versions 11 and 12 and appears automatically whenever there are more than 200 values in the parameter list of values (LOV).  To make the long list easier to navigate Crystal automatically batches the values into groups of 200 and exposes the batch interface toolbar.  You can scroll through the batch numbers by clicking the left and right arrows.  The number one is the first batch, two is the second batch, etc.  You select a batch and can click the drop down to see the values in that batch.  Once you find the batch that has the value(s) you want, you can start selecting.

You can even narrow your search by typing in a few characters of the value you want.  Only values that contain those characters  will be displayed.  If more than 200 values match your filter string they will be batched as described above.

But there are some odd features of this interface:

1) It treats numbers or dates as if they were character values.  So if you had a list of 300 numbers you could filter for the number 17 and you would then see only numbers like 17, 117, 171, 175, or anything else in the list that contained those 2 digits.

2) The filter is case sensitive.  So if you type 'bike' it will not see values that have the word 'Bike' in them.

My main complaint is that this feature appears with no explanation and no help.  The first few times I saw these extra buttons I didn't even know why they had appeared, let alone how to use them.  And finding the answer wasn't easy.  The explanation of this toolbar is under the heading "long lists" in the index.  So unless you already know why this interface appears, it is pretty tough to find.

Contact Information