My blog automatically checks for links that no longer work. It recently found a batch of broken links for old Crystal support articles. These were hosted on a Kanisa support site which is gone. Now they have (mostly) been incorporated into the SAP support engine. If you ever need to search SAP for support content and KB articles you should now use the following link:

http://search.sap.com/

Hidden in the advanced formatting options for numbers are several ways of displaying zero values on your reports.  To find these options, right click on a numeric field and select “Format Field”. Then click on the “Number” tab and finally click on the “Customize” button at the bottom of that screen.

One option is to suppress the zeros completely so they show up as blanks.  To do this there is a checkmark in the upper left.

Another option is to display something specific instead of zeros and this is found in the lower right of the same screen.  You will see a drop-down under the heading “Show zero values as”.  Here you can keep the default behavior or use another value, like a dash or a zero.  I wondered what the difference was between Default and the zero and it took some testing to figure it out. The default might show decimals, like 0.00 or 0.0000 but if you set the default to zero the decimals won’t show.

But the real secret of this last setting is that it is one of the only drop-down lists in CR where you can type your own value.  In other words you can type whatever you want to show as the zero value, like “n/a”, or “Pending”.  This is similar to using a “Display String” (on the common tab) but without the need to write a formula.

A user recently asked about setting the format of a large number of fields. He wanted to have all the negative numbers show in red and positive numbers show in black. The formula wasn’t a problem, but he was looking for a way to avoid writing a unique formula for each field.

I suggested that he use this as the font formula for one of the fields:

if CurrentFieldValue < 0
then CRRed
else CrBlack

Then all he would have to do is use the format painter (paintbrush icon) to paint this format to all the other formulas that need to be changed. Since the formulas refer to the CurrentFieldValue the formula is exactly the same for every field, including formulas and subtotals. And to speed it up even more you can double click on the paintbrush to make it ‘sticky’. Then you can single click every field that needs that same format.

Whenever you have multiple tables in your report you need to specify the linking fields that connect the tables (the join). Crystal will often take a guess at the correct links using “auto-linking”. In recent versions of CR you can auto-link two different ways, “by name” or “by key”. CR tries “by name” as the default.  This works when the linking fields are the same name in both tables and when there are no other fields that have the same name between the tables. In my experience it correctly links the tables one out of three times. It more often adds extra links that break the join.  Just because Crystal has created a link you can’t assume that the link is correct.

But some databases are configured with primary and foreign key relationships that are visible through the database connection.  These databases allow Crystal to auto-link “by key” pretty reliably.  I tested it recently while Continue Reading »
Auto-linking tables by key.

When my customers send me report problems they sometimes feel the need to send me a photo album. I will get a screen shot of the linking window, a screen shot of the preview tab, a screen shot of some key formulas, etc.  It is much more efficient to send me the RPT file. With the RPT I can open the linking window myself, open any formulas, and open anything else that might help me troubleshoot the report. An RPT can replace most of the screenshots I get and gives me much more information. If the RPT can’t be saved with data I may Continue Reading »
Troubleshooting from Screen shots

Customers watching me work on their reports will sometimes see me use a shortcut that is new to them. Usually these are shortcuts that have become second nature to me so they happen in a blink and the customer has to ask me how I did what I did.  Two of the most common are the “Align” and “Size” options in the “format” menu.  If you have a row of objects that are not even or a column that is out of alignment you can use these options to get everything back in place.  Just select a group of objects, then go to either Format>Align or Format >Make Same Size.  Each will give you a list of choices that you can apply to that group of objects.

I often do both together, and in that case it is best to do the size first. This is because changing the size will often change the alignment points.  So if I have a row of objects to align vertically I will first make sure to Continue Reading »
Aligning and sizing a group of fields

I have written before about Crystal windows that end up off screen.  This often happens when you open something like the Database Expert or the Formula Editor.   This happened to me the other day when I was using my laptop outside of my office and so my second monitor wasn’t available.  It can also happen if you switch from a high resolution mode (which shows a larger workspace) to a lower resolution mode.  When I wrote about this before my solution involved digging into the registry.  Since then I have found some keyboard shortcuts that allow you to move a hidden screen back into the viewable area.  One of these 3 things should work:

1) Hold down the Windows key and hit the up and left arrows until it appears in your viewable area
2) Hit ALT-Space, then hit the X.  This will maximize the window in your current screen.
3) Hit ALT-Space, then hit the M.  This will put you in ‘Move’ mode and you can use your up and left arrows as mentioned above.

I solved a mystery this morning that has bugged me for a long time. As you can imagine I use lots of reports to run my business. I just noticed that half of them give me the “invalid printer” message when they are opened. This message usually appears when you move a report from one PC to another that has different printers. Because I fix other peoples reports on my PC, I see this message all the time, so I am used to ignoring it. But I only have one printer so it doesn’t make sense that I should see it on my own reports. The normal fix (selecting an existing printer) doesn’t work here because these reports are already pointed to an existing printer.

The message is also harmless. It doesn’t prevent the report from running. It just adds an extra click every time I open a report. That is why I ignored it to the point where it became invisible. But after one of my customers asked about the message I decided to see what it took to get rid of it.

So the next time the message came up I selected a different printer on my PC and hit ‘apply’. I then re-selected my main printer and hit ‘apply’ again. This fixed the problem on almost all reports.

On the few occasions where it didn’t fix the problem I clicked the printer ‘preferences’ button. This doesn’t appear for all printers but my Brother brand printer has a button called preferences. In there I found a setting that sets the printer preferences to the default. This also fixed the problem so I used that method on reports where the first method didn’t work.

After my last newsletter I received a note from James Flowers with some formula suggestions, and thought his comments along with my own might be helpful:

1) James likes to number the formulas in sequence to show which formulas depend on other formulas (feeder formulas). So if the Tax formula relies on the Sales formula he would number them 01_Sales, 02_Tax, etc. Of course there would have to be a longer sequence to make this worthwhile.

I have done this a few times when I had a complex sequence of calculations and it helped. But usually my formula relationships look more like a family tree than a straight sequence. When I Continue Reading »
Formula tips from a reader

I was helping a customer set the location of several tables from one database (or catalog) to another identical database on the same server. We tried to set the location using the ODBC connection, but they are on the same server so it didn’t change the catalog. We also tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. But this only changed the catalog shown in the connection properties. The table properties still showed the original catalog. When we refreshed the report the data still came from the original catalog.

So we figured we would have to set the location table by table. That would allow us to specify a table within the new catalog. But that is when things got weird. We Continue Reading »
No tables shown in the Database Expert