For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned Continue Reading »
Controlling the color of many objects from one formula

Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this: Continue Reading »
A total of a formula instead of a formula of totals.

This week I had a customer who was having trouble getting some version 8.5 reports to run on another another computer using CR v11.  The report wouldn’t connect to a new data source consistently.  So I gave him the following steps that seemed to clear up the problem. Here they are in case someone else has a similar situation.

Any time you need to run a v8.5 report in a later version of CR the first step is to change the version of the RPT.  To do this open the old report Continue Reading »
Running v8.5 reports in later versions of CR

When you specify field values in Crystal Reports formulas do you have to match the case?  Well, it depends on where you are. Take this example:

{Name} = “Joe”

It would only be true when the first letter was upper case and the other two were lower case.    So what if you have values in your data that use different case patterns?  You could get around the problem Continue Reading »
Case sensitivity in Crystal Reports

Last night I had a customer with a strange problem.  Their report ran fine and looked great in preview and when printed.  But if it was exported to PDF it had a long line down the page.  This didn’t happen if it was printed to a PDF driver so the only time it was visible was when using the CR export function.

I opened the PDF and there was a vertical line on every page, even on blank pages, starting near the top.   I opened the RPT and there was no visible object anywhere near the sections at the top where the object should be sitting.  I was getting ready to start deleting objects one at a time to see if it was tied to a specific object, but first I decided to Continue Reading »
Mystery line in PDFs

I had two different customers in the past week ask me why their distinct count grand totals were not totaling correctly. They had distinct count subtotals at the group level and distinct count grand totals at the report level.  But when they added up all the subtotals the sum didn’t match the grand total.  Usually it won’t because of what a distinct count is designed to do.

A distinct count summary will count how many different values there are in a column.   So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level.   And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer.  But it is very likely that this grand total is not going to match the sum of the doctor subtotals.  This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.

If you ever have a requirement where you need the grand total to be the sum of the subtotals you can purchase Expert Techniques volume 1 and read tip 25.

On occasion I have worked with customers where the field explorer has become undocked and we have trouble getting it to dock again.  I also remember clicking toolbar buttons and accidentally dragging the toolbar out of position, then not being sure how to get things back the way they were.  So I was pleased to find a feature (tucked inside a little-used menu item) that will fix these issues.  It is currently available in versions 11 through 14 and I assume that it will be available in later versions as well.

To use this feature go into the VIEW menu and select TOOLBARS.  You will see a window that allows you to control which toolbars are visible. At the bottom is an option called “Reset all toolbars and explorers on the next restart”.  Check this option and then close and re-open Crystal Reports.  All your toolbars will go back to their default positions and your field explorer (and other explorers) will re-dock in their original positions.

Now I would have been even happier if this also reset the entire Formula Workshop.   I have written before about the formula workshop disappearing, or the panels not docking correctly.  Unfortunately these features are not currently reset by this option.  So if  you have these problems in the formula workshop you will still have to dig into the registry to fix them.

Here is a trap in Crystal when it comes to using the selection formula.  Say you have two fields A and B. You want to include all records where either A or B is equal to X. Your selection formula would look like this:

{A} = “X” or {B} = “X”

But what happens if A is null on a record where B is equal to X? Null values cause CR formulas to stop working, so CR would stop processing that formula before it ever got around to looking at B. CR would skip that record. But not if the the formula was Continue Reading »
Null values and the selection formula

I just had a potential student ask the following question:

>> the rumor I hear from other I.T. people is that the
>> long term health of crystal is suspect and
>> that its future is short for this technical world …

Here is how I responded:
————————-
I am probably not the best situated for a ‘big picture’ view, and it is devilishly tricky to predict the ‘long term’ in regards to anything technical, but I sure don’t see any basis for that opinion.  CR has changed hands several times and each time Continue Reading »
Do you see CR as a declining technology?

I just discovered 2 features of the linking window that make it easier to work with a large number of tables.   Some of you may have already found these but maybe I am not the only one that overlooked them:

Change Linking View:

If you right click in the background of the linking window there is an option to “Change Linking View”.  This collapses all tables to just their headers and shows a single line join for all links.  I find that this is great for a big picture view of the tables.  To see individual fields for a table you can double click on the heading of a table and it opens up that table to normal view.  Another double click on the heading collapses it back down again.

Locate Table:

If you have lots of tables and you don’t want to scroll around to find a particular one you can right click in the background and select “Locate Table”.  This will give you a list of the tables in the window in alphabetical order.  Highlight any table in the list and it takes you directly to that table.   This is especially helpful when you have really long table names and they are wider than the tables, making it tough to distinguish tables with similar names.