My computer career goes back to the first IBM PC, long before the mouse was introduced. That makes me a big fan of keyboard shortcuts. I find that many tasks go faster when I don’t have to lift my fingers off of the keyboard to grab the mouse. For example, I usually cut, copy and paste from the keyboard using Ctrl+X, Ctrl+C and Ctrl+V.

So I was very pleased to learn some new keyboard shortcuts that are specific to the Crystal Reports formula edtor. I knew these two:

Ctrl+A : standard ‘select all’ selects the entire formula
Ctrl+S : save the formula (and close formula editor)

But I did not know these three until one of my customers pointed them out:

Alt+S : save the formula (without closing the formula editor)
Alt+M : comment / uncomment selected lines
Alt+C : check the formula

The Alt+S is one I have been looking for so I have already added it to my toolbox. And, thanks to Laurie Weaver, a developer at Wyse Solutions, for sharing this this idea.

I had two different cases in the past week where a customer wanted to know why a specific record was missing from a report. I used the same process in both cases and thought I would share it. So say a report should include Order 273 and it doesn’t.

The preliminary step: missing or hidden?

Before we start we want to make sure that the ‘missing’ record is really missing and not just hiding in the report.  Go into the select expert and add a new rule that says [ {Order ID} = 273 ] and then refresh the report. When it is done look at the record count in the status bar at the bottom of the screen. Do NOT look at the report totals or anything else for this step. If the number in the status bar is not zero than the record is not missing, it is hidden. It might be suppressed or it might have been grouped in an unexpected place, or it might be filtered out using group selection, but it is already in the report. If the record count is zero then you have a missing record and can start the process below.

Selection Criteria or Inner Join:

There are really only two things that can prevent a record from being included in a report, an Inner Join or the Selection Criteria. So if Order 273 is not in the report then there is either an inner join to a table with no matching records, or Order 273 does not qualify on one of the report’s criteria. So we need to back these things out until the record shows up. Go into the selection formula and cut out or comment out the entire formula. Then add the test rule that I mentioned above:

{OrderID} = 273

Refresh the report with that criteria and see if the record count in the status bar shows more than zero records. If it does then the problem is in the selection formula. Leave the new rule in place and add one of the original rules at a time back into the selection formula.  See which one makes the record count drop to zero. If you find a suspect rule, put back all of the other rules and see if Order 273 still shows up. This way you know for sure that only one rule was the problem.

If you have removed all of the rules in the selection formula (except for the test rule) and the record count is still zero, there is one more place to check. Criteria can sometimes hide in the group expert if you use specified order. If all of the groups use ascending (A) or descending(D) order you can skip to the “Joins” step below. But if any groups show “S” for “Specified Order” go into the Options button for that group and check the “Others” tab.  An others setting of “discard” could eliminate Order 273. Change this to say “Leave in their own groups”, refresh and check the record count.

If after all that the record count is still zero then the problem has to be an inner join. Go into the Database Expert (Links tab) and hit the “auto-arrange” button. If the table used in our test criteria is not on the far left, then reverse any joins  that come out of the left side of that table.  To reverse a join, right click on the line, select reverse join, and then hit “Auto-Arrange” again. Repeat this process until the table that contains the test criteria field is on the far left. Then take all the join lines and change them to Left Outer Joins. Now the record should show up. To figure out which join is the problem you can start changing the Left Outer joins back to inner joins, one at a time, and refresh each time. Start with the joins that come out of the far left table. Then change the joins that go from those tables to other tables, and so on. When the record count goes back to zero, that will be the table with unmatched records.

And, if you need help applying these steps, you can always give me call to schedule a session.

I had a user ask me about this error. He was using Version 11.0.0.895 (the original release), so I suggested that he apply some updates or check for font problems. He did some digging and found that if her removed Windows Update KB3102429 the problem went away. That led me to this discussion on the same topic which nails down a font problem.  Since XI is still widely used, and since this might affect other versions, I thought this might help someone else.

And thanks to  Josh Pals, Controller at United Gear and Assembly, Inc., or sharing his find.

I just read a “what’s new” PDF from SAP that talks about Crystal Reports 2016.  The new features are not earth shaking but it is nice to see that product development on the stand-alone version isn’t completely idle. From what I read the new features are:

• A vertical alignment control with a condition button (my “functions to nowhere” will get used after all)
• Conditional formatting controls with condition buttons for line and box properties. One control is for the style of the box  which will allow the box to be conditionally suppressed.
• A formula function that retrieves descriptions stored with parameter fields
• Some new native drives for Oracle,  SQL Server and a few SAP databases.

I haven’t upgraded my own software since 2008, but this list would make an upgrade worthwhile for me.  The release is tentatively set for the first half of next year. The full PDF is here but it covers all SAP/BO products. Crystal Reports starts on slide 97.

And thanks to Andrew Baines of Pursuit Technology for sharing the PDF and his own blog post with me.