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.