Application Development Report Design Web Design
Crystal Report Frequently Asked Questions:
Click on the questions below to view the answers
Q: "Suppress if Zero" is not working, or a formula using "If <field/formula> = 0 then…" is never returning true, or a Record or Group Selection Formula using "<field> = 0" is not returning any records.
A: In the majority of these cases, the problem is that the field or formula that is being compared, is not exactly zero, but rather, is a field of very small value (ie. 0.0002, 0.001, etc.). This may not be readily apparent to you, especially if you have reduced the number of decimal places being shown. To solve this problem, try changing your conditional formulas, or selection formulas to use the Round ( ) function:
1.) Conditionally Suppress the field/object when: Round ({field}) = 0
2.) Change conditional formulas to use: If Round ({field}) = 0 then ...
3.) Change selection formulas to use: Round ({field}) = 0
Q: "Why is my suppressed/hidden data being included in my Subtotal or Grandtotal?
A: All auto-inserted summary fields (subtotal, summary, grand total), always return values based on calculations using all records, regardless of conditional suppression or sections that are hidden. This is because summary calculations are processed at the beginning of the 2nd pass on the data, whereas conditional suppression and "hiding" of sections or objects occurs at "WhilePrintingRecord" time, at the end of the 2nd pass (in essence, after all values are calculated and known).
To get around this, you can create a conditional summary that only summarizes the values that meet your condition. Before proceeding, the first question to ask is "Is my condition a 1st or 2nd pass condition?"
A 1st pass condition is one that just requires the value of the current record to assess. For example, the following would be a 1st pass conditional formula:
If {Customer.Last Name} = "Smith" then ...
Let’s assume that you had a report on the Customer table from the Craze database. This report was grouped by Region, and you were wanting to add up the field {Customer.Last Year’s Sales} anytime the condition shown above was True. To accomplish this, create this formula and place it in the details section:
If {Customer.Last Name} = "Smith" then
{Customer.Last Year’s Sales}
Else
0
Now, right click on this new formula in the details section, and because it is a numeric 1st pass formula, you will get an option in the popup menu to "Insert Subtotal". Pick the group on Region and presto! You have a conditional formula that will only add up the {Customer.Last Year’s Sales} for customers with a last name of "Smith".
A 2nd pass condition is one that is based on a summary value, or a condition that is dependent on records other than the current record being processed. The following would be an example of a 2nd pass conditional formula:
If {Customer.Last Name} = Next ({Customer.Last Name}) then ...
Let’s assume that you have a report on the Customer and Orders tables from the Craze database. This report was again grouped by Region, and you were wanting to add up the field {Orders.Order Amount} anytime the condition shown above was True. To accomplish this, you will have to create a set of Running Totals formulas, because the condition is a 2nd pass condition.
Please visit Crystal Decisions's website and download the Technical royalbluepapers Running total formulas in Crystal Reports, and The Running Total UFL functions in Crystal Reports 5 and 6 , for full details on how to create this type of conditional summation.
Q: "I want to find out how to get better performance out of my report. It is taking too long to process and/or too long to print."
A: Please visit Crystal Decisions's website and download the Technical whitepapers: Tips to improving report processing speed in Crystal Reports 5 and 6, Factors affecting print speed in Crystal Reports, and Performance considerations when creating your Record Selection Formula in Crystal Reports.
Q: "Why is it necessary to update my printer driver when reports are missing fields and/or contain incorrect formatting when printed? Why are other Windows applications not experiencing similar problems printing to the same printer and printer driver?"
A: Crystal Reports does not get font information like other programs do. When font information is obtained in the manner used by other Windows applications, performance has been known to drop considerably.
In order to maintain report performance, Crystal Reports makes a GetTextMetrics API call that queries the printer driver for each of the font elements such as: average character height, character width, height of ascenders/descenders, internal/external leading, and maximum character width (to name a few).
Problems can arise, especially with older printer drivers, when the printer driver does not return the font metrics accurately. In many cases, the newer, updated drivers will provide more accurate font metrics. This is why updating the printer driver will usually solve the problem of missing or inaccurate printing.
Q: "Why does my client experience problems printing my report? The report is not printing landscape, or is not printing on the right paper size, or from the incorrect printer tray."
A: It is important to understand how Crystal Reports handles these types of printer settings. When your report is loaded, go under the File menu and pick Printer Setup. This dialogue screen is what controls how and where a report will print. However, it is important to realize that some of these features will change how someone else (a client) prints the same report.
The first thing to check on this window is WHERE the report will print, what printer will it use? This is controlled through the dropdown "Printer Name" box. If the person designing the report picks their system’s Default Printer (evidenced by the Status line saying: "Default Printer Ready"), then the report will try to print on the client’s Default Printer (if they have one selected). If the report is not designed on the Default Printer, then it is considered a "Specific Printer" report, and the report will try to print on the client’s printer of the same NAME as the printer used on the developer machine. Then, if that printer is not found, it will print to the client’s Default Printer.
The second most important selection you make on this screen is the "Default Properties" checkbox. If you have this box CHECKED, then the report will print using the Client’s Printer’s Default Properties, NOT the properties that you specify in this screen (regarding paper type, paper source and page orientation). If you have this option UNCHECKED, the report will try to force the settings you specify (regarding paper type, paper source and page orientation) onto the client’s printer. So long as the client’s printer supports ALL of the settings, the report will print similarly to the developer’s printer. However, if any of the settings are not supported by the client’s printer, the report will use the Default Properties of the client’s printer.
The best rule of thumb to follow is this: If you are wanting to force paper type, paper source (trays) or page orientation on the client’s printer, then always UNCHECK the Default Properties checkbox.
Q: Can I graph on WhilePrintingRecords formulas? Can I graph on Running Totals formulas?
A: No. At the current time, all versions of Crystal Reports process the graphing component mid-way through the 2nd pass, just after Totals are calculated, but just before the Group Selection Formula is processed. Because of this, WhilePrintingRecords formulas and Running Totals, that are processed at the END of the 2nd pass (after graphs), cannot be graphed on.
Q: "Why can’t I browse my field or use it in formulas?" or "Why can I not see my field in the formula editor?"
A: If you right click on a field, and the "Browse Data" option is greyed out, it is being treated as a Memo Field in Crystal Reports. Any field that exceeds: 254 characters in length; is a VarChar field (variable character length field); a blob field or a true memo field, will be converted into a Memo Field within Crystal Reports.
Currently, all versions of Crystal Reports do not support Memo fields in formulas or selection formulas. Because of this, you will not be able to browse data in Memo Fields, and will not be able to reference them in formulas.
Q: "Why does my exported report format not look the same as what Crystal Reports prints and shows in the Preview window?"
A: As stated in the Crystal Reports 6 User’s Guide, page 148, "When you export a report to a different file format other than Crystal Reports format (.RPT), you may lose some or all of the formatting that appears in your report. The program attempts to preserve as much formatting as the export format allows."
If you are seriously considering exporting your report to formats other than .RPT, you need to understand what report features are supported by the export DLL you are choosing. For example, if you have designed a form letter report, and you export it to Excel format (a spreadsheet program), you will loose almost ALL formatting. The rule of thumb to remember, is export to a format that is complimentary to your report style.
To assist you in deciding what export format is best for your needs, please visit Crystal Decisions's webpage and download the Technical royalbluepaper Exporting guidelines & supported features of export DLLs in Crystal Reports.
Q: "I try to preview my report, and get a database or "file not found" error" or "I have tables and/or fields missing in the Visual Linking Expert."
A: Anytime you get behavior like this, chances are you have experienced some change in your database or database structure. Often this can be a result of changes to field names, field types, table names, additional fields, additional tables, or removal of fields or tables. When these types of changes occur, you must update Crystal Reports to recognize the new data structure.
To do this, load your report, stay in the design view, select the Database menu and choose the item "Verify Database". If it says something like "There have been changes to the table <tablename>. Do you wish to fix up your report?", then there HAVE been changes to the data structure, and you have two choices:
Saying YES to the above question, will allow Crystal Reports to automatically "fix" your report by removing any fields that are no longer found in the database. It will not result in formulas being removed, however, due to changes in the data structure, you may find it necessary to edit a number of your formulas to have them work properly.
Saying NO to the above question, will mean that you must physically remove all fields that no longer exist in the data structure yourself. In addition to editing any formulas that reference incorrect field names or field types.
It is also recommended to do a "Save As…" and save your report under a different filename, anytime you encounter data structure changes. That way you are able to go back to your original report if anything goes awry during the verify process.
Q: "When I preview my report, some of my date/number fields are not showing in preview, they’re returning blank. What is going on?"
A: Both of the situations above are nearly always attributed to NULL data in fields. When Crystal Reports encounters a null result in a field, one of two things can happen: (a) if the field is a number, currency or date field, you will easily recognize the nulls because those records will return "blanks" instead of numbers or dates; (b) if a formula encounters a null, it will immediately fail and not process.
There are two methods to correct these situations:
1.) Go under the File menu, choose Report Options, and put a check in the box beside "Convert Null to Default". This will make Crystal Reports return empty strings for null string fields, zeros for null currency/number fields, and Date (0,0,0) for date fields.
2.) Build "null" checks into your formulas, and replace fields that are returning nulls with formulas containing "null" checks. For example, assume you have a number field that may contain a null for some records in the database, and you never want to see "blank" number fields, but rather, want to always see zeros. You could build a formula like this:
If IsNull ({numberfield}) then 0 else {numberfield}
Q: "I keep getting errors like "a date is required here" or "a string is required here", in my formulas that are doing Date conversions. What can I do?"
A: These errors indicate that you may be returning your date or date/time fields in the wrong format for the way you have written your formula. You can either correct your formula, or go under the File menu, select Report Options, and select "Convert Date/Time to Date" or "Convert Date/Time to String" (the opposite of whatever it is now), and that often can correct the problem.
For example, assume you have a formula that is built to convert a date/time string into a date field using the following syntax:
DTStoDate ({Date/TimeField})
If you had "Convert Date/Time to Date" selected on this report, this formula would fail with the message "A string is required here", because all of the DTSto??? functions expect your date/time fields to be in STRING format, not Date format.
B. Other Resources
Q: "I am having trouble printing distributed reports."
A: Visit Crystal Decisions's website and download the Technical royalbluepaper Printer driver dependency document .
Q: "I have lots of duplicate records being returned on my report" or "I am not sure how to link my tables to get the report data I want."
A: Visit Crystal Decisions's website and download the Technical royalbluepaper Defining file links for PC-Based databases in Crystal Reports .
Q: "I am having trouble with subreports and/or linking my subreports on the right data from my main report."
A: Visit Crystal Decisions's website and download the Technical royalbluepaper Subreports - Linked & Unlinked in Crystal Reports 5 and 6 .
Q: "I am having trouble creating a graph" or "I am having trouble creating a multi-series graph" or "I am having trouble understanding how to use the PG Editor graph tool."
A: Visit Crystal Decisions's website and download the Technical royalbluepaper Graphing tutorial & Using the PG Editor in Crystal Reports 6 .