|
 Crystal Report Design
Info Tech Outlook can help you by creating the reports you
need for your business. Put our extensive experience working with database
technology and Crystal Reports to work for you, and achieve results FAST! Custom
reports can be designed at your location or, oftentimes, remotely
Why Custom Design?
 When your people are learning a new software tool, there's
a learning curve that will have to be traveled. Many Crystal Reports solutions
require complex formulas and relationships to the database or subreports. These
concepts may be extremely difficult for beginning users of Crystal Reports to
understand and implement to the fullest potential. It may take some time for
your users to reach the level where they are comfortable creating reports with
these advanced techniques.
And then there's the database itself. If you're
implementing a new database and Crystal Reports is to be the reporting
component, more likely than not you and your IT people have your hands full just
managing the database setup and conversion. Who will have the experience and
time necessary to create the reports that you need to make important business
decisions?
Custom Services:
At Info Tech Outlook, Seagate Crystal Reports and
databases are our specialty. We can offer you these custom services to ease your
burden:
-
Analyzing and Documenting -
We
can determine the relationships in your database so that Crystal Reports end-users may
create documents more easily
-
Custom Report Design -
Either working with someone from your team that
is familiar with the database, or analyzing the data ourselves, we can create
reports to your specifications. You describe the layout and the information on
the report; we do the designing.
-
Report Testing -
Once a report is designed, we can thoroughly
test the report for accuracy and integrity.
-
Report Maintenance -
Periodically, we can review and update existing
reports for efficiency and accuracy, reflecting changes in your business
-
Remote Design -
In many instances, report design can be done
remotely, either through WEBEX or with a sample set from your database.
Our Guarantee:
As with all of the services offered by Info Tech Outlook,
our consulting work is guaranteed. We will work to make sure that you are more
than satisfied with the services you receive.
Crystal Report Design Case
Studies:
Billing Management
The Process: A Contractor
submits an AIA or Time & Material billings to the owners and awaits
payment.
The Problem: Different owners
have different requirement on the type of billing format, the change order
billing method, and the owner waiver submittals. Owners are unhappy with
the billing and payment is delayed hence the contractor’s cash position is
weakened and subcontractors are also unpaid and unhappy. Also the bills
were generated outside of the accounting system thus increasing the
likelihood of erroneous entries.
 ITO Solution: A Crystal Clear
decision:
With the power of Seagate Crystal Reports we
designed billing and owner waiver formats that are compatible with owner
requirement. A change order billing was also setup to be billed
separately.
In addition to increasing the employees
productivity, we also preserved a healthy owners relationship both of
which pave the way to more profit be it via decreasing cost or increasing
revenue. All the more reason to satisfy owner billing requirements.
Supplier Chain Management
The Process: A subcontractor
submit waivers for the amount of the check to the general contractor and
awaits payments.
The Problem: Subcontractor has
not submitted insurance information, a signed copy of the subcontract, or
a signed copy of the change order. The subcontractor does not receive
payment and the relationship is damaged. Subcontractor could lien the
property and the relationship with the owner is also ruined.
ITO Solution: A Crystal Clear
decision: We designed a Crystal report linked to the accounting system
that has four components:
1. A
cover letter including a details of needed documents in addition to the
waiver.
2. Insurance
requirement page.
3. A
final or partial waiver to be signed (multiple formats to meet 20
different owner requirements).
4. An
Access database to track secondary waivers.
The Subcontractor now is aware of all the
documentation required in order to receive payment and the relationship is
maintained.
Crystal Report Frequently Asked
Questions:
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 .
|
