The Crystal Reports Underground News - Volume 2016.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2016:
** Milestones
** Updated comparison of Crystal Reports viewers (2016)
** My library of Crystal Reports materials
** Deleting all unused formulas at once
** Mixing (AND and OR) or (AND and IF THEN ELSE)
** Let me create your Crystal Reports
** More ways to find where a field is in use
** A very old way to find where a field is in use
** Field is in use? The last resort
** Open enrollment Crystal classes in Frederick, MD
** Questions related to commands
** Adding a ratio calculation to a cross-tab
Gems from the Archives:
How to format a paragraph with a "hanging indent" (November 2005)
Conditional Drill-Down (January 2006)
Milestones
When I first started my 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.
Updated comparison of Crystal Reports viewers (2016)
You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?
The most cost effective method for letting a user run reports is to install a third-party client-based viewer. They are offered by a dozen different vendors. Don't get sidetracked by the "viewer" that is put out by SAP because that tool won't refresh reports. Every viewer in my list allows you to refresh reports.
Every September I compare the features of these viewers and post the results. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (PDF) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren't familiar with the terminology.
This year there are 12 vendors in the review but one of them is only listed as a warning (EasyStreet). Their web site is up and the phone still rings, but no one has heard from them in several years. The Better Business Bureau has given them a "D-" rating for lack of response.
The 11 active products are:
Crystal Corral by Groff Automation
rptView by Pursuit Technology
cView by Chelsea Technologies
ViewerFX by Origin Software
Crystal Kiwi Viewer by Crystal Kiwi
Report Viewer Pro by Report Viewer Limited
RV by Climate 27
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW
If you have feedback to provide on any of these products, I would love to hear from you.
My complete library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Deleting all unused formulas at once
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 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.
Mixing (AND and OR) or (AND and IF THEN ELSE)
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}
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
More ways to find where a field is in use
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.
A very old way to find where a field is in use
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.
Field still in use? The last resort
I got to test the methods above 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 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 in any of the fly-outs. As far as I know there is no way to find that use within Crystal Reports without checking every running total (or going to a
third party tool ). But now I know one more place to check.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my November classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
Questions related to commands
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.
Adding a ratio calculation to a cross-tab
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.
Gems from the Archives:
How to format a paragraph with a "hanging indent" (November 2005)
Conditional Drill-Down (January 2006)
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2016 by Ken Hamady
All rights reserved - Republishing this material requires written permission