The Crystal Reports® Underground

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 when overhauling a report I will have a long list of formulas with many of them no longer needed. You can tell which formulas CR is actually using by the check mark next to each field in the Field Explorer. You can delete these unused formulas individually. If there are groups of them listed together you can select a range of formulas and hit “delete”. But here is a quick way to delete ALL of the unused formulas:

• Click on the the first formula in the Field Explorer (even if it is in use)
• Hold your shift key and click on the last formula in the Field Explorer (even if it is in use)
• Hit the delete key.
• You will get a warning that you are trying to delete fields that are in use. Click “No”
• Crystal will delete all of the fields in the list that are not being used, but leave the others alone.

Even if you click “Yes”, Crystal can only delete some of the formulas that are in use. If a formula is sitting on the report layout but is not being used in any other way, Crystal can delete that formula from the list. To delete the other formulas you have to find where they are being used and replace them.

One common problem, especially in selection formulas, is when you have both AND and OR but no parentheses to clarify which should come first.   Take the following example:

{Orders.Order Amount} > 5000 and
{Customer.Country} <> "USA" or
{Customer.Region} = "CA"

If there is a CA order for $2,500, will it meet the criteria?  Using the above formula, yes.  That is because without any parentheses, the AND takes priority over the OR.  So in English this formula means:

Return all records that are outside the US and above $5,000. Also return all records that are in CA regardless of the amount.

If I intended that ALL locations were above $5,000  then I need to put parentheses around the OR to tell the report to treat that as one rule.  It could look like this:

{Orders.Order Amount} > 5000 and
( {Customer.Country} <> "USA" or
{Customer.Region} = "CA" )

A similar thing happens when you include an IF THEN ELSE in your selection formula like this:

if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State}
and {Table.Date} in {?DateRange}

In this case the AND will combine the DateRange rule with the ELSE.  The DateRange parameter will be ignored when you select ALL States.  To apply the DateRange in ALL cases you need to put the IF THEN ELSE statement inside a pair of parentheses like this:

( if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State} )
and {Table.Date} in {?DateRange}

When I first started this blog I wondered how long it would be before I ran out of things to write about. The Internet is littered with abandoned blogs. But this month marks 10 full years of The Crystal Reports Underground. And, next month marks 15 full years of my newsletter. Thanks for reading!

If Crystal lasts another decade I plan to still be writing about it.

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.

Since CR 2008(v12) we have been able to right-click on a field and check to see if it is used in any formula in the report. This is helpful if you are trying to delete a formula or drop a table from the report. It lets you see where the field is used and modify the formula to use an alternate field.

The problem is that the field might be used in several places that are not considered formulas.  For instance it could be used as a group field, a sort field, a subtotal or a subreport link.  Or it might just be sitting on the report, somewhere.

After updating to CR 2016 I noticed that when you right click on a field in the field explorer (and only in the field explorer) there are several new similar choices:

Find in Grouping condition
Find in Sorting condition
Find in Subreport Links

The first two are moderately useful but all they do is open the “group expert” or the “record sort expert” if the field is being used. The Subreport Links is more of a time saver since it checks all the subreports at once.

These were not available in CR 2011* or CR 2013* but are available in CR 2016.

*thanks to Ian Cartwright of Vero Software and Ido Millet of Millet Software for checking these versions.

Twice in the past 2 weeks I have had to take two different summary fields in a cross-tab and calculate a ratio of one to the other.  The most common example would be when one summary is “Sales” and the other is “Cost of Goods” and you want to calculate a Gross Profit Percent from them.  This type of calculation got much easier with CR 2008, because you can now add calculations that draw values from other cells in the cross-tab.  The functions that do this is can get pretty complex but this example is not that bad.

In our example the row and column fields can be anything.  All we need are two different summary fields:

Sum of Sales
Sum of Cost of Goods

And for this example the summary fields will be arranged vertically, with the Sales on the top and the Cost of Goods below it.  The steps are:

1) Right click on the Cost of Goods Summary in any cell and select

Embedded Summary > Insert Embedded Summary

This should add a third summary row in each cell of the cross-tab. The will all say “Edit this formula”.

2) Right click on that phrase and select

Embedded Summary > Edit Calculation Formula

3) Paste in the following formula:

Local NumberVar  Sales :=
GridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -2 );

Local CurrencyVar COGS :=
GridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -1 );

if Sales = 0 then 0 else
(Sales - Cogs) % Sales

Here is how the above formula works.  The first two rows read the cross-tab value from 2 rows up (Sum of Sales) and hold that value in a local variable called Sales.  That way I can refer to that value several times simply without having to repeat the entire function each time.  The next two rows do the same thing with Sum of COGS which is pulled from 1 row up.  The last two lines do the actual calculation, including a check to make sure that Sales are not zero.  That prevents the ‘divide by zero” error.   (Note, if your Sales and and COGS fields are currency fields the formula will give you an error, so change the variables to CurrencyVar).

4) Save the formula and the third summary row in every cell should calculate the ratio based on the two numbers immediately above.  The cross-tab will perform the calculation in all group levels of the cross-tab including the grand total rows and columns.

If you need help deploying this formula or need something slightly different, let me know and we can schedule a session to get you what you need.

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.