This was the first time I found a practical use for renaming a report object, a feature that has been available since 2002.

Every object placed on a report is given a unique “object name”. These names appear in the Report Explorer (View > Report Explorer) which lists all of the objects and where they are placed. The default object name for fields is the name of the field with a number after it. The number makes each object name unique, since the same field can be placed on the report multiple times. Graphic objects are also given object names. For instance, a series of lines would have the default names of Line1, Line2, etc.

In a report I did last week I needed to show the status of each transaction by displaying a small image that represented each status. This required stacking several images in the same place and suppressing most of them so that only one was visible at a time. The challenge was that once the images were stacked it was very difficult to determine if the correct one was selected. All the object names were Picture1, Picture2, etc.

Fortunately, you are allowed to modify the object name, as long as it is unique. To rename an object you right-click on the object, select “Format Object” and go to the common tab. The object name is at the top.  I renamed each image with the status it represented. Now when I select an object in the Report Explorer I know which one it is and can apply the correct suppress condition.

Notes:
1) Lines and boxes do not have a common tab, so there is no way to change the object names.
2) The Report Explorer was introduced with version 9 and that is the first version that allowed us to view and edit object names. If you open a report from CRv8 or older the object names will be more generic (Field1, Field2, etc). However if you copy and paste one of these objects while in a later version the newly created object will be named like current objects.

One of my customers learned a new trick from me today. They wanted a report to automatically select the data for the prior month. They didn’t realized that Crystal has a dedicated function for that and you can use it in both the select expert or in the selection formula. So I figured I would share this, since there may be others that have never seen this function in Crystal.

Go into your select expert and add a rule using any field that is a Date or DateTime. Then in the comparison list look toward the bottom for the option “is in the period”. When you select this option the next drop-down list will automatically populate with 25 pre-programmed functions. You will find items like “LastFullWeek”, “LastFullMonth” and “YearToDate”. These periods are automatically calculated each time you run the report, based on today’s date.
So say you want a report to automatically select the records for the prior month without having to enter the date range or change the report each time. You use the LastFullMonth option in the select expert, or you could do the equivalent, like this, in the selection formula:

{Date.Field} in LastFullMonth

One note, Crystal calculates all of these periods based on the print date which is normally the system date of your computer. But Crystal also allows you to override the system date for any report. Just go to [Report > Set Print Date] and you can pick another date. Just don’t forget to reset the date before you save the report or Crystal will continue to use that same date (for this report) until it is reset.

I periodically hear from Adam Butt of APB reports (Norway), usually when he stumbles on a neat Crystal Reports trick. This month he wrote to share a method for controlling the format of dates in charts.

When you do a “group” chart, Crystal creates a bar for each group in the report. The label under the bar will be the same as the group name. If you group on a date (e.g. by day or by week) the corresponding date will appear under each bar. But unlike date fields on the report, there is no format option for dates within a chart. To change the date format you have to use the properties in the group expert. The options tab in the group expert allows you to create a customized formula for the group name. And with Totext you can determine exactly how the date should appear on the report, in the group tree and also in the chart labels of a group chart.

But if you do an advanced chart, which means the chart is grouped by a field that is not an existing report group, this option is not available. You are stuck with the default date format that CR chooses. And advanced charts are handy because you can use any field for grouping the chart without having to match the groups in the report.  Some users make the chart a  “numeric axis” chart which gives them some date options and formatting control, but haven’t had good results from these charts.

Fortunately, Adam found a clever way to get the flexibility of an advanced chart while still having the custom group name ability of a group chart. He uses a cross-tab and creates the chart from the cross-tab. Cross-tabs groupings are independent of the groups of the report (just like advanced charts). And once you create a row group or column group in a cross-tab they both have the same group name property that you find in the groups in the report. Once the chart is created the section containing the cross-tab can be suppressed so it doesn’t need to take up space on the report.

So thanks to Adam for sending in the tip. And if you need help getting CR to create the charts you want, give me a call.

I wrote two posts last month about finding where a field is used. I got to test these methods recently while helping a customer clean up a report. There were a handful of formulas shown as “in use” that we needed to delete. So I thought this would make a good demo of the new features.

First, we checked all of the options in the fly-out menus and found most of the formulas so we could replace them with other fields and delete them. Then we used the “FIND” method in design mode to see if any were sitting on the report. But there was one field still in use that didn’t show up in either method.

Next I tried a very old method. I exported the report to a “Report Definition File” and opened the exported file in NotePad. This usually works, but not this time.  I was surprised to find that there were no references to that formula anywhere in the report definition.

So now I was curious.  Where could you use a field that wouldn’t show up anywhere? So I started chopping:

• I saved the report under a new name
• I deleted every object from the report layout.
• I deleted all the groups.
• I deleted any extra sections so I was down to the basic 5.
• For each of the 5 basic sections I inserted a new section below it and then deleted the original section. At this point there would be no conditional formatting formulas.
• Then I deleted all of the formula fields. But I still couldn’t delete the problem formula because it was still “in use”.

So I looked for other things to delete. After deleting the parameters I noticed that there was a lone running total field. The running total wasn’t being used, but as soon as I deleted it the problem formula was no longer marked as in use. So I did an “undo” to check the running total and found that my problem formula was the summary field of the running total. So I did a bit of testing and here is what I learned:

If you do a regular summary of a formula (Insert > Summary) that formula will show up in the Report Definition export.

Also, if you use a formula field in one of the the condition formulas of a running total it will show up in the Report Definition and also in the “Find in formulas” fly-out.

But if you use the formula as the summary field in a running total, that formula will NOT show up in the Report Definition or any of the fly-outs. As far as I know there is no way to find that using Crystal Reports without checking every running total (or going to a third party tool). But now I know one more place to check.

Sometimes the location of a formula can change the way things work, especially with variables. So I sometimes spend a few minutes trying to find where a formula was placed within a crowded report. My last resort has been to export the report to a “Report Definition” file and then do a text search for the field name to see which section contains that field. So while writing the last post (More ways to find where a field is in use) I had originally included a sentence that said:

“It would be really nice if they added a simple way to find where a field was sitting in the layout of the report.”

As I wrote that sentence I thought about how I would implement that feature in Crystal.  I would use the existing “find” feature but allow users to search while in design mode.  And then I realized that after using the find feature (binoculars) for 20 years I had never thought to try it in design mode. (Doh!) Yes, the feature I wanted has been there a long time – since v8.5(2001) or earlier.

So, if you are in design mode and and do a ‘Find’ for the text Qty you will find every text object or field name that contains those characters.  Or if you search for the word Sum you will find every text object and field name that has the word Sum, including summary fields that do a Sum of another field. It even works when the field is so narrow that the text you are searching for is truncated and not visible. And if you use the ‘Mark All’ button instead of ‘Find Next’ it will select all of the objects at once.

One note – a few times my ‘find’ did not see matches that were obviously there.  If that happens just click anywhere in the design screen before you do the ‘Find Next’.  This seems to fix the ‘lost focus’ and allows it to work.

I answered two questions this week related to commands and thought the answers would make a good blog post.

1) Is it bad to link a command to other tables using the Database Expert?
In general it is more efficient to build all of the tables into the command when possible. This allows the database to optimize the query and tap into the indexes. When you link a command to a table in the Database Expert, Crystal has to request two separate datasets, load the results into memory and then try to match the records on your PC.  This is usually going to slower since you don’t have the power of the server.

But there are cases where this might work acceptably.  For instance, if the results are small and if you are going FROM the command TO an indexed table. If the command is coming from a different server than the tables, then you have no choice but to link them within Crystal.

2) If you use a command and then add a  selection formula in the report, does the criteria get added to the SQL?
No.  In a normal table-based report the selection formula is usually transferred into the WHERE clause.  But in a command-based report the command will return results based on the WHERE clause in the command itself.  Any criteria that you add in the selection formula will be applied as a second step, as Crystal reads the records in from the database.  So it is best to move the criteria from your selection formula into the command’s WHERE clause.

This is also true for parameters that are used as filters.  It is best to add these parameters directly into the command’s WHERE clause.  Otherwise, like the selection criteria mentioned above, they are applied to the data as it is read into Crystal.

Unless you are using a command, you will need to link your tables in the “Links” tab of the Database Expert. And typically you create the link by dragging a field from one table and dropping it on a field from another table. This works fine in most cases, but can be awkward when:

• The tables are far apart and not visible at the same time, like when you add a new table
• The field you are linking to is near the top or bottom edge of the table
• You are working on a slow connection

The first item usually requires dragging tables around the window, which is slow.  In the second case the table has a tendency to scroll up or down quickly when you drag a field near the top or bottom edge.  In the last case a slow connection or a screen delay can interrupt the signal, causing the field to drop in the wrong spot.  All three of these can be fixed by using a nifty feature added to recent versions of CR.

When you want to drag Field A and drop it on Field B you can right click on Field A and in the fly-out menu you will see the words “Start Link”.  You select that and then scroll around until you see Field B.  Then you right-click on Field B and select “End Link”.  This will create a link between those two tables, wherever they are on the screen.  You can then hit “Auto Arrange” to bring the newly joined tables closer together.

And here are some other tips that you can use on the LINKS tab.

I have written several times over the years about exporting reports to Excel. My first post was in 2005 and follow-ups in 2008, 2012 and 2015. It took much trial and error but I eventually found solutions to most of the challenges.

So I was a bit surprised to find a 15-page document dedicated to exporting to Excel on the SAP website. It was originally published years ago, but exactly when is hard to say. The web page that hosts the document says it was created in 2008 and updated in 2012. But the page footer inside the PDF has a time-stamp from 2006 right next to a copyright of 2004. Obviously it isn’t new and was available when I was struggling with these exports. It would have been nice to know about the document. In some ways it goes into more depth, like explaining how all of the export options work.

But my posts cover several points that are not covered or not covered clearly in the SAP document. For instance, they don’t provide any help when it comes to subreport data. The also document points out 3 main reasons you will get extra rows:

1) Objects not horizontally aligned in a section
2) Objects in a section have different heights
3) Complex conditional suppression formulas.

It also mentions that extra space at the bottom of a section can cause extra rows.  But the document never clearly explains that extra space at the TOP of a section does the same thing. It took me several years to figure that one out. In other words, the objects could be all the same height, be perfectly aligned, have no conditional suppression and all be touching the bottom of the section. Despite all that you would still get extra rows if there was a sliver of space between the top of the objects and the top of the section. To fix this you can use the “Size and Position” property to set the Y position to zero, but this can only be done one object at a time.  Or you can just push the objects against the top of the section. One trick I use is to select all the objects in a section and align them, and then hit [CTRL-up arrow] until the row jumps to the next section. Then hit [CTRL-down arrow] once. They should jump right back into the original section and should end up at position zero.

So you should certainly download this document. I have, and have added it to my Useful Links page. But you should also note the links above to get the full picture.

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.

One of my customers received an upgrade from CR XI to CR 2011 when he upgraded to SAGE 100 software. He was surprised that the new version was missing the DB connector he used to report off of his file system for document management and version control. I have written about reports that read a Windows folder before. I suggested that he try a custom install to make sure that the option was checked and he reported that there was no option for this driver in his install.

So I had him run one of the existing reports that used this connection to see if it would run. CR gave an error that said he was missing the CRDB_FileSystem.dll. So I told him to move the CRDB_FileSystem.dll from the XI environment to the 2011 environment and then the report ran with no problems.

I am not sure if this dll was dropped from the SAGE distribution, or dropped from the 2011 upgrade but if you are using this type of connection and lose it on an upgrade, you should be able to get it back with the dll from your old environment.