The Crystal Reports Underground News

The Crystal Reports Underground News - Volume 2021.09

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

Contents for September 2021:

** Comparison of Crystal Reports viewers (2021)
** My Crystal Reports Expert Series released as free downloads
** Let me create your Crystal Reports
** Adding an “All” option to a dynamic parameter
** Hot-swapping fields
** Individual Training with an expert.
** “Column mode” in the Crystal formula editor
** Matching colors in your reports
** Using reserved characters in formula names

Gems from the Archives
Drawing tables in a Crystal Report (2011.03)
Mysterious blank pages (2011.05)

Comparison of Crystal Reports Viewers (2021)

You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?

The most cost-effective method for letting a user run reports is to install a third-party client-based viewer. These are offered by ten different vendors. Don’t get sidetracked by the official SAP “viewer” because that tool won’t refresh reports. Every viewer in my list allows you to refresh reports.

Every September I update the features of these viewers. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (xls) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren’t familiar with the terminology. There are a couple of new features added to this year’s matrix which are marked in blue.

There are 10 active products in this year’s review and 4 “ghost” products that are mentioned as warnings. A ghost product has a web site but it hasn’t changed in years and no one responds to requests for information.

The active vendors are:

Crystal Corral by Groff Automation
rptView by Pursuit Technology
CR Dispatch by APB Reports
cView by Chelsea Technologies
ViewerFX by Origin Software
CrystalKiwi Viewer by CrystalKiwi
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW

If you have feedback to provide on any of these products, I would love to know what you think.

My Crystal Reports Expert Series released as free downloads

Last month I released my Intro and Advanced course books as free downloads. Tonight I released the entire Expert’s Series of guides as free downloads. All of these are all on the “Library” page of my site. You are free to use them and share them for free as long as they are not modified or sold. The titles include:

    Expert's Guide to Formulas
    Expert's Guide to Subreports, Parameters and Alerts
    Expert's Guide to SQL Expressions, Options and Commands
    Expert's Guide to Totals
    Expert's Guide to Cross-Tabs
    Expert Techniques Vol. 1 - 4
    Quick Reference to Crystal Reports in Visual Basic
    Quick Reference to Crystal Reports in .NET

Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.

Adding an “All” option to a dynamic parameter

If you want your parameter’s list of values to be pulled from the database you can use a dynamic parameter. But one of the down sides of a dynamic parameter is that you can’t type additional values to include in the list. A dynamic parameter can only show values pulled from the data source. So if, for instance, you are pulling in a list of products and you want the list to have an “All” option at the top, you can’t simply add the word “All” to the list like you could with a static parameter.

My preferred way for adding an “All” option to a dynamic parameter is to use a SQL command as the source for the dynamic parameter. Using a SQL command gives you several other advantages as well, such as allowing you to filter your the list of values. Here is an example of a SQL command that will add an “All” option to the list of values (incorporating suggestions from MHurwood below):

Select Items.ID, Items.Desc From Items Where Items.Status = ‘A’

UNION ALL

Select ‘…All’, ‘…All’

The part above the UNION creates a list of all the active items, showing both the ID and the description of the items. The part below the UNION adds one row to the results of the query with the “All” option. Notice that “All” entry has several periods in front of it. This is one way to sort that value to the top of the list. You can use this method to add several values to your dynamic list, if needed.

Note that you wan to avoid using the fields from this command in other parts of the report. It should be used only for the dynamic parameter.

One of my colleagues, Angela Meharg of Datisfy, reminded me that you can use optional parameters to do something similar. Instead of explicitly selecting a word like “All”, you can skip over the parameter. Then you can program the selection formula to say that when the users doesn’t select a value they get all values. The formula would look something like this in the Crystal selection formula:

and (if not (HasValue({?Items})) then True else {Table.Item} = {?Items})

In English this means, if there are no values in the items parameter, then every record qualifies. Otherwise the items that qualify are the ones that match the parameter.

If you have trouble with one of these options, you can schedule a short consult and I can give you a hand.

Hot-swapping fields

I just started a project of reading through my old blog posts from the very beginning. I am deleting things that are obsolete and updating posts with newer links and information. I even expected to find a few useful things that I had written and forgotten. I found one in my 3rd post.

In August of 2006 I wrote about some new features introduced in Crystal Reports v11.5 (XI r2). One of them I don’t remember at all: hot-swapping fields.

Say you have a field on your report. It is the right size, in the right position and with all the right formatting. Then you realize that you should have used a different field. Crystal allows you to hot-swap another field into that place. All you have to do is drag the field onto the report from the field explorer and hold down the shift key. When the new field gets to be over the old field the new field will snap into place and you will see two gray arrows pointing in opposite directions. If you release the mouse at that point the new field will take the place of the old field, keeping the same size, position and most formatting properties. You can swap any type of field with any other (but not text objects).

I say most formatting properties because it will not keep conditional formatting formulas. But it will keep the formatting properties you can set from the toolbar (font, size, underline, color, etc.). If you want to apply the conditional formatting properties to the new field, you can copy the original field and paste it somewhere else before you swap it out. Then after the swap you can use the “Format Painter” (paint brush) to apply all the original properties to the new field. Then delete the extra copy of original field.

One other issue has to do with column headings. Some fields are tied to a column heading that were automatically generated by Crystal Reports. This happens when you start a report using the report wizard or if you place a new field on the details band. When you hot-swap a field tied to a column header the header will automatically change to the field name of the new field. If you prefer the original column heading you can make a copy of the heading before you hot-swap and place it somewhere else on the page. Then after the hot swap you can delete the new column heading and add the original one.

Let me create your Crystal Reports

“Column mode” in the Crystal formula editor

I have written several articles about using Notepadd++ for writing long formulas or SQL statements. I just found out that one of the features I use in Notepad++ has been (partially) available in Crystal Reports forever. I never noticed. The feature is called “column mode” and allows you to select text in a column without selecting the entire row.

Lets say you start a formula with DateTime variables like this:

WhilePrintingRecords;
DateTimeVar DateA;
DateTimeVar DateB;
DateTimeVar DateC;
DateTimeVar DateD;
DateTimeVar DateE;

Then you realize that you want them to be Date instead of DateTime. You can highlight a “column” made up of the word “Time” on all 5 rows at once, and then hit delete. To select a column you hold down the ALT key and then click the mouse in the upper left corner of rectangle and drag to the lower right corner. In this case you would start just before the “T” in Time in the first row, and drag down and across until your cursor was just after the “e” in Time in the last row. By using the ALT key you will highlight a rectangle of 4 characters across and 5 rows down. Then you hit delete and just those 20 characters are deleted.

You can also copy and paste a rectangle. Say you have to write a formula that is something like this:

If month({Trans.Date}) = 01 then {Balance.Pd01} else
If month({Trans.Date}) = 02 then {Balance.Pd02} else
If month({Trans.Date}) = 03 then {Balance.Pd03} else
If month({Trans.Date}) = 04 then {Balance.Pd04} else
If month({Trans.Date}) = 05 then {Balance.Pd05} else
If month({Trans.Date}) = 06 then {Balance.Pd06} else
If month({Trans.Date}) = 07 then {Balance.Pd07} else
If month({Trans.Date}) = 08 then {Balance.Pd08} else
If month({Trans.Date}) = 09 then {Balance.Pd09} else
If month({Trans.Date}) = 10 then {Balance.Pd10} else
If month({Trans.Date}) = 11 then {Balance.Pd11} else
If month({Trans.Date}) = 12 then {Balance.Pd12}

Normally I would start by typing the first row and then copy it 11 more times. Then I would change each row to use a different number from 1 to 12. Once I have changed the column of values after the ‘=’ sign I could copy that 2-digit column and past it over the values in the other 2-digit column in one step.

To do this you select the 2 digit column as a rectangle using the ALT key. You can right clock in the column to copy (or use Ctrl-C). Then you select the other 2-digit column and right-click to paste (or use Ctrl-V).

Note, in Crystal you should select these rectangles starting in the upper left corner, especially if you plan to copy and paste. You can get inconsistent results if you start in one of the other corners.

NotePad++ has a much more sophisticated column mode, allowing you to:

Paste a single word or character value into multiple rows at once.
Select a column and start typing. The new text is added to ALL the rows at the same time.
Use Shift-Alt to mark the column using arrow keys , instead of the mouse.
Select the column of text starting in any corner.

Unfortunately, these don’t work in Crystal.

Matching colors in your reports

I had a project this week where I had to replicate the look of a very colorful spreadsheet. It featured 4 different shades of green. Matching an existing color in a Crystal Report usually involves a fair amount of trial and error, but I can usually get close. This time I was having a hard time matching a light green and it dawned on me that there might be a web site to identify colors. I had just gone to the paint store with a chip of drywall and came home with a gallon of paint that exactly matched a faded wall color, so I figured the odds were good.

I did a quick search and found many free sites where you can upload an image file and click on a color to get the RGB / HEX / CMYK codes for that color. I took screenshots of several spreadsheet cells, uploaded them and got instant matches. The customer was impressed and I didn’t have to do any guess work.

You can find a page full of sites with a search on “image color picker”. I like these two in particular because you can put in a URL for an online image file as well as uploading an image. Not all the sites had that part working well.

https://imagecolorpicker.com/en
https://www.ginifab.com/feeds/pms/color_picker_from_image.php

After posting my blog I heard from Adam Butt of APB reports, who pointed out that there is a color picker tool in most image editing programs, including the classic MS Paint, Paint.net and Paint 3D. I have been using classic MS Paint forever and never paid attention to the eye dropper icon:

This lets you click anywhere in the image and gives you the color codes at that spot.

Individual Training with an expert

Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.

This type of training is ideal for people who:

   Need to cover only a few topics.
   Want to use their own data and reports as examples.
   Want to learn from someone who has taught over 2500 satisfied students.

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 remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.

Using reserved characters in formula names

I recently heard from Gordon Portanier (from the Crystalize consulting group in Canada). He was doing something I have seen several customer do over the years. He would start certain formula names with a symbol so those formulas would sort to the top of the field explorer list. Gordon happened to use the @ symbol.

When he upgraded to CR 2020 these reports ran fine, but he found that he could not create a new formula that had an @ symbol in the name. A message told him that four characters [ @ { } ? ] were now considered reserved characters and could no longer be used anywhere within the name of a formula field.

Existing formulas, however, are not affected unless you try to rename them. So Gordon found another report that had the formula names he needed and copied them to the current report. The formulas work fine.

Up through CRv12(CR 2008) a formula name could include ANY character in any position. But when I tested this in CRv14.2 (CR 2016) I got the same warning as Gordon. My guess is that this restriction started with CRv14 (CR 2011) and so it would also affect CRv14.1 (CR 2103) and all later versions. If any of you are using CRv14 (CR 2011) or CRv14.1 (CR 2103) you can confirm this by trying to add a formula with an @ in the name. Let me know what happens.

An interesting point. This rule only applies to formula names. It does not affect the names of SQL expressions, parameters or running totals.

Another interesting point is that the @ (which represents formulas) and the ? ( which represents parameters) are reserved. But you can still use the % symbol (which represents SQL Expressions) and also the # (which represents running totals).

Gems from the Archives

Drawing tables in a Crystal Report (2011.03)
Mysterious blank pages (2011.05)

Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com

Copyright 2021 by Ken Hamady
All rights reserved - Republishing this material requires written permission

Follow Me on BlueSky