What if you have a bar chart and some of the bars are at zero? Maybe that information is useful but sometimes you may want those zero bars to be skipped. One method is to exclude the zero records, but what if they are needed by other parts of the report? I just found a relatively easy way to exclude zero bars from a chart, without eliminating the zero records from the report.

For example, say you are doing an “advanced” chart (not a group chart). You want one bar per customer so the Customer field is the “On change of” field.  The field {@FedEx Counter} is the “show values” field and it is set to sum.  You preview and you see several customers have no fedex orders so their name shows an empty slot where their bar should be. Here are the steps to eliminate those:

1) Go into the Chart Expert and select the Data Tab.
2) Add the {@FedEx Counter} to the ‘on change of’ box as a second field.
3) Click the “order” button right below {@FedEx Counter}.
4) Change “in ascending order” to be “in Specified Order”.
5) On the specified order tab click “New” and type ‘zero’ as the group name.
6) Below {@FedEx Counter} select “is greater than” 0.
7) Click on the “Others” tab and select “Discard all others”.
8) Click OK > OK to get back out.

You should see all the zero bars disappear.

I recently had a report grouped by Employee with one page per employee. Each Employee showed up in the group tree on the left of the screen. But every time we clicked on an employee in the group tree, we ended up on the page of the employee just BEFORE the one we wanted. I thought it was a glitch to I changed the group options, then closed the report and eventually closed CR completely.  The problem didn’t go away so I realized it was something in the report.

After some experiments I realized what was going on. The report was printing one page per employee and printing only the group footer. But there was no actual “new page after” checked for the group footer. There was one employee per page only because the group footer was so large that only one group footer could fit on a page. So it mimicked a page break.

But the group tree doesn’t take you to the group footer of the group, it takes you to the Group Header. And in this report the Group Header for each employee was suppressed. So as soon as each Group Footer finished on a page, the next Group Header would start right below it. And because it was suppressed, it would always fit on the prior page.

So the Group Tree took us to this (invisible) Group Header which was always on the page before the corresponding group footer. All we had to do to solve the problem was check “new page after” for the Group Footer and that moved the (invisible) Group Header to the next page, right above the corresponding Group Footer. And then the Group Tree worked as expected.

If you create reports for others, you might find a use for this technique. It changes a report automatically based on the folder where the report is stored. I use it to show a message in preview while in my design environment and have the message automatically suppress when the report is moved to production.

Example:
I have a handful of customers that still use v8.5 which is almost 15 years old. Unlike all versions of CR from v9 forward, there is no way to save changes done in a later version of CR back into v8.5. So I have to maintain a copy of v8.5 just to use for these customers. When I receive a report to change the version doesn’t usually matter, so I don’t always remember to check and see if the customer still uses v8.5. I might do quite a bit of work on the report in my current version before I check. Only when I go to save the file does CR warns me that the report is from the older version of CR (if I do “Save As” it won’t warn me at all).  At that point I have to reopen the original report in v8.5 and make all the same changes a second time.

So I just came up with a way to add a warning to reports that I work on in v8.5. This warning will appear in my preview window as soon as I open the report in my environment, but will never appear to the customer. It works because I use each customer’s account number as a folder name to store all of their reports. This helps because Crystal formulas can read the path and file name of the report being run. So when I work on a v8.5 report I add logic to have it check for a specific folder to control the appearance of the warning.

I create a new Report Header section that isn’t otherwise used by the report.
I place a text object in this new section that has a large bold red warning “>>> use v8.5 <<<”
I create a section suppression formula that says something like:

not "\1234\" in FileName

The 4-digit number is a customer account number which is where the rpt is stored in my environment. It is unlikely that the report will be in a folder with that same name in the customer’s environment. So the section will suppress in their environment but show up automatically in mine the next time the report is opened.

I found on surprise while testing this.  The FileName function is read before the selection formula is processed, so you can use the folder name to change your filter.  And it is even processed early enough to affect the SQL that CR automatically generates, so it doesn’t have to slow down the report.  If you discover any creative uses for this, please share.

One of my customers sent me a question about an error message they got when they went to open a report. I recognized it as an error I had seen recently on a customer’s workstation. Then today I closed and reopened Crystal and got the same error message. For those of you who open reports by double clicking the RPT file it might seem that there is a problem with report, but this is a problem you will see anytime the CR designer starts.

I have written before about how the Crystal Reports start page will request internet access and try to load dynamic content (ie ads).   I showed users how to prevent the start page from requesting internet access by renaming a few files.  What we are seeing today is that there is apparently a problem with some java script in the way that dynamic content is loaded.  Once SAP fixes the problem the error should go away.  In the meantime if you just stop the script the reports should load and run fine.  If you don’t want the start page to load web content you can use the method in the post mentioned above.

All reports pull their data from tables of some kind, and these tables do change over time. Fields are added or removed and sometimes field names or their data types are changed.  Crystal has the “Verify Database” feature to update the report so that the table structure stored with the report matches what is in the database.   If a field used by the report is removed, renamed or has been changed to a different data type, you will have to map the old field to the new field in a small mapping window.

But I found an interesting exception.  One of my customers had a character field that was changed from a long string to a memo field.  Apparently the Verify Database ‘mapping’ feature can’t see memo fields, even when they have the exact same name as the original string field.  So when I went to Continue Reading »
Field disappears during Verify Database

I was reviewing a report for a customer that was based on a cross-tab. The cross-tab had several rows that had a different color font and background. Yet when I went into the formatting properties of the cells, I couldn’t see where the color logic was entered. I searched the formatting formulas and all the places I could think of where you could change the colors and couldn’t find it. Then I started right-clicking different areas to see if there was something I had forgotten and found the answer in the ‘Highlighting Expert’.

Currently, I never use the Highlighting Expert. That is because when it was first introduced it was only good for numeric fields, and you could only write conditions based on the current field’s value. So I continued using the conditional formatting formulas, which are give you more control over the condition logic.

But it seems that the Highlighting Expert has become Continue Reading »
Highlighting individual rows and columns in a cross-tab

Today I was dealing with this error message. It was generated by a report that uses a SQL command with a parameter. The parameter was a numeric and when we replaced the parameter with a specific number, the error went away. Putting the parameter in the same spot generated the error.  So as part of troubleshooting I started taking out pieces of SQL until there was very little left of the query, and the error remained.  So we were surprised to find that moving the same SQL to a new report worked fine, even with the parameter.  The customer was ready to recreate the report from scratch.

Then she made a comment that she had once deleted and recreated a parameter to fix a similar problem.  Unfortunately that hadn’t worked for her today.  But this rang a bell, so I asked her if she had used the same parameter name when recreating the parameter.  She had, so I deleted the parameter and created a new parameter with a different name. I used the new parameter in place of the old one and the SQL ran correctly with no error message.  It appears that this parameter name, which she had used successfully in several other reports, had somehow gone flaky inside this report.  She tried to rename the working parameter back to its original name and the same error popped up.  So the problem wasn’t with the SQL Syntax or with the value but something specific to that parameter name in that one report.

So if you run into a similar problem with a SQL Command parameter and nothing else seems to fix it, here is one more thing you can try.

I cleared up a mystery today, one that I thought was just a Crystal glitch. It explains why Crystal formulas sometimes fail without an error message to describe the problem. We are not talking about invalid formulas. An invalid formula generates an error when you save it. We are talking about a formula that fails only when the report is run and it finds data that it can’t process. For example, a formula that divides one field by another is valid, but will fail if the denominator field has a value of zero.  A formula that converts characters from a string field into a date will also fail if the characters don’t make up a valid date. Both of these failures would normally pop up an error message.  So what does it mean when it doesn’t?

Take an example where formula A is used inside formula B, and then formula B is in turn used inside formula C. If formula A fails then Continue Reading »
Formula failures without error messages

A customer sent me a report today with an unusual behavior. There was a total on the report and the problem was that the total would change when she unsuppressed a details section and change back when she re-suppressed the details. Even adding a blank details section made the total change.

At first I figured she had to be mistaken. I could think of no way that suppressing a section or splitting a section could have an effect on totals. But as I thought about it I realized there was one one obscure way. Both of the changes she mentioned could change the number of pages. And there is one way that adding pages can affect totals.

There is a property in the Continue Reading »
Add a blank section, and the totals change?

In the current versions of CR, you have 3 choices for the select expert and the selection formula.  The main one is Record Selection which is the one you use for database fields and most formula fields.  Then there is Group Selection which is used for subtotals and other summary fields.  But the third an most recent addition is the one for “Saved Data”.  I have ignored this feature since it was introduced, mainly because the explanations I read didn’t make any sense to me. The idea is that you can put rules here when you only want the rule to apply to saved data. But if you have saved data you can put the same rule in the regular record selection formula and just tell the report to use saved data, so it seemed to simply confuse things. It might make sense for those using the SAP viewer where you are limited to saved data. This would allow you to deploy parameters that don’t ask the user about refreshing the data.  But since my customers typically use viewers that CAN refresh data this doesn’t come up.

But after a customer asked me about this feature I gave it some more thought.  I realized that a better way of saying this is that it is a rule that is applied locally, meaning that it Continue Reading »
“Saved Data” Select Expert/Formula