Transcript Creating Crystal Reports

Creating Crystal
Reports
Tips and Tricks
Presented by Ben Rimmasch
www.regoconsulting.com
Phone: 1-888-813-0444
Topics
●
●
●
●
●
When to Use Crystal Reports
Getting Started with Crystal Reports
Hands On
Deploying Reports in CMC
Best Practices
○ Developing and Maintaining
○ Versioning and Deploying
● Tips, Tricks and Strategies
● Questions
2
www.regoconsulting.com
Phone: 1-888-813-0444
When to Use Crystal Reports
● Pros
○
○
○
○
Very flexible layout for when layout is important
Specific deliverable format
Forms
Graphics
● Cons
○ Not customizable by the user
○ Changes require deployment
○ More time consuming than portlets
3
www.regoconsulting.com
Phone: 1-888-813-0444
When to Use Crystal Reports (cont’d)
versus
4
www.regoconsulting.com
Phone: 1-888-813-0444
Getting Started with Crystal Reports
● Work Environment Preparation
○ Setting data sources
• Oracle Client and Java for Oracle databases
 Add a tnsnames.ora entry for each environment
• System DSN for SQL Server
○ Installation of Crystal Editor
○ Setting Editor Preferences
• Options -> Fonts (so all reports have the same look and feel)
• Options -> Layout (to make editing easier)
5
www.regoconsulting.com
Phone: 1-888-813-0444
Getting Started with Crystal Reports (cont’d)
● Creating Your First Report
○ To wizard or not to wizard...
• All options are available and easy to get to without using the wizard. It’s
likely to not save you any time. It’s likely to cost you time.
• As far as Crystal Reports go, friends don’t let friends wizard.
○ Save immediately (and often)
• See Tips Tricks and Strategies
6
www.regoconsulting.com
Phone: 1-888-813-0444
Getting Started with Crystal Reports (cont’d)
● Adding a Command and query parameters
○ Consider the size of your dataset in choosing query parameters
○ Determine if a parameter would be single-select or multi-select. Script
the SQL accordingly.
• When creating parameters in Clarity in the report definition for each
parameter a data type must be chosen.
• For the data type of Browse or Pull-down a lookup must be specified.
• Parameters with lookups can be set to single-select or multi-select.
• Clarity will pass the hidden id of the lookup in the Browse and Pull-down
parameter types to the Business Objects server.
• For all parameters the id of the parameter in the report definition must
match the name of the parameter in the Crystal report
7
www.regoconsulting.com
Phone: 1-888-813-0444
Getting Started with Crystal Reports (cont’d)
● Adding a Command and query parameters (cont’d)
○ For single-select lookups in filters the query might contain the following
components:
• {?param_project} is NULL – For testing when the parameter is optional and not supplied.
This is only applicable INSIDE the editor because this will never be null from Clarity
• {?param_project} = 0 – For testing when the parameter is option and not supplied from
Clarity
• {?param_project} = invi.id – To do the actual filtering of a single project
○ For multi-select lookups in filters the query might contain the following
components:
• 0 IN {?param_project} – For testing when the parameter option is not supplied from
Clarity
• invi.id IN {?param_project} – To do the actual filtering of multiple projects
○ The examples above assumed the hidden id in the lookup for the parameter was a
lookup_enum and used 0. For lookups where the hidden id is a lookup_code use ‘’.
○ Test the data source with one or two fields surfaced on the report
● Setup groups (report grouping) as one of your first steps
8
www.regoconsulting.com
Phone: 1-888-813-0444
Getting Started with Crystal Reports (cont’d)
●
Sample Query
select
rim.name
,rim.description
,rim.impact_description
,rim.assumptions
,rim.resolution
,assignee.full_name assigned_to_name
,rim.created_date
,rim.priority_code
,case rim.priority_code when 'HIGH' then 3 when 'MEDIUM' then 2 when 'LOW' then 1 else 0 end priority_sort
,rim.impact_enum
,rim.impact_date
,i.name inv_name
,i.id inv_dbid
from rim_risks_and_issues rim
left join srm_resources assignee
on assignee.user_id = rim.assigned_to
join inv_investments i
on i.id = rim.pk_id
where 1 = 1
and (rim.description is not null or rim.description <> '')
and rim.type_code = 'RISK'
and (i.id IN {?param_project} OR 0 IN {?param_project})
9
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On
● Creating a report
● Navigating the editor
○ Field Explorer
• Used to add items to the report
○ Report Explorer
• Used to edit or remove items already in the report
● Options (Editor-wide)
○ Fonts
• Set fonts for consistent look and feel across all reports
○ Layout
• Make preference changes for easier editing
● Summary Options (Report specific)
○ Report Title
○ Other fields as needed
10
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Section Expert
○ Suppress (vs Hide)
• Suppressing removes a section or field from view always. Hiding removes a
section from view but leaves it available for drill-down.
○ Print at Bottom of Page
• Forces the section to be at the bottom of the page. All following sections will
then be forced to the next page (or column)
○ Keep Together
• Determines when pagination will occur
• All “Keep Together” values are essentially ignored once sections are placed in
a group and the group’s “Keep Group Together” option is checked.
• Group vs Parent Section vs Child Section
 Group will always try to print the entire group on the same page
 Parent will always try to print the children sections on the same page but when all
sections are longer than a page it will page if necessary where the child section
groups not set to keep together
○ Suppress Blank Section (almost never applicable because all values have to
be fields)
11
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Groups
○ Group By
• Section will be printed on any change of this field. Order matters! If the values
have already been through a group or have been sorted and the field occurs in
your records a few times and then again in your records two groups will be
created for that field.
○ Ordering
• Often a “virtual” field must be created for ordering to occur correctly in
groups.
○ Customize Group Name Field
• Very useful when the “virtual” sorting field scenario comes into play.
○ Keep Group Together
• Overrides almost all other keep together settings
○ Repeat Group Header on Each Page
• Very useful as a makeshift page header in subreports
• Useful for breadcrumb or hierarchy data display
12
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Menu and Buttons
○ Insert Text Object
• The primary way to insert static text into a report.
• Fields and formulas can be dragged into text objects.
○ Insert Group
• Opens the group creation dialogue (see previous slide)
○ Insert Summary
• Summary vs Running Total Fields
 Summary fields can be used anywhere and the values have already been calculated. Very
limited options compared to running total fields.
 Depending on where you put a running total field the values may not have accumulated.
This is sometimes desirable and sometimes not.
○ Insert Subreport
• Restricted to parent report and one level below. Subreports may not contain subreports.
• Subreports don’t have page headers or footers.
• Once a subreport is placed parameters, fields and formulas from the main report can be
passed to the subreport as parameters by right clicking the subreport and changing the
subreport links. Data types of parameters must match.
13
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On(cont’d)
● Menu and Buttons (cont’d)
○ Insert Line and Insert Box
• Both objects will grow if placed across sections
• Won’t snap to grid or guidelines
○ Insert Picture
• BMP always works and is usually editable through Crystal
• JPG and PNG sometimes throw errors and won’t import and are never
editable.
○ Insert Chart (charts and graphs)
• Provides basic charts, Gantts, pies, cross-tabs and many others.
• There are many options and features here that won’t be covered in class
because it’s beyond scope.
14
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Cross-tab Expert
○ Right click an existing cross-tab
○ This is an additional way to get totals other than summaries and running total
fields. Essentially the same options as running total fields but in a grid with
additional grouping outside of the groups setup in the report.
● Database expert
○ Adding and editing commands
○ Changing database locations (“Set Datasource Location”)
● Parameters
○ Data types must match those of Clarity.
• Clarity always passes numbers for checkboxes
• Clarity always passes empty strings (‘’) instead of nulls for blank strings
• Clarity never passes a null array for empty multi-value lookups with lookup_code as the
hidden key. It passes an array with one value and that value is an empty string. ([‘’])
• Clarity always passes 0 instead of null for blank numbers
• Clarity never passes a null array for empty multi-value lookups with lookup_enum as the
hidden key. It passes an array with one value and that value is 0. ([0])
• The two rules above will dictate how your report filter lookups are designed.
15
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Formulas and Formula Workshop
○ Believe it or not, the built-in Help library is very informative.
○ Ctrl + space brings up autocomplete drop down
○ Option of Crystal or Basic syntax
• Crystal syntax is included in all versions of Crystal Reports
• Basic syntax is modeled on Microsoft Visual Basic
• Note that record selection formulas and group selection formulas cannot
be written in Basic syntax. For this reason I prefer Crystal syntax across the
entire report.
○ Basic operators and functions are available
• if, else if, else, IIF, switch, loops (do while, do, etc.), for (supports exit),
assignments, scoping variables, totext, len, concatenate, substring
(through operator), in (contains), split, replace, plus many more.
○ Strings are delimited by double quotes e.g. “this is a string”.
○ IsNull and HasValue for checking if fields or parameters have values
16
www.regoconsulting.com
Phone: 1-888-813-0444
Hands On (cont’d)
● Running Total Fields
○ Choose when to evaluate and when to reset
○ Multiple summary types including sum, count, distinct count, max,
min, Nth largest, Nth smallest, average, sample variance and standard
deviation, population variance and standard deviation, median
○ Useful to duplicate spreadsheet-like reports
● Special Fields
○ Report elements that can be used visually or in formulas to access
some of the components of the report
○ Examples
• Record Number, Page Number, Print Date and Print Time, Data Date and
Data Time, etc.
17
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC
● Login Screen
(Almost always uses Enterprise Authentication)
18
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC (cont’d)
● If you don’t see the Folders tab or Folders link you do not have
sufficient rights
19
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC (cont’d)
● To add reports navigate to the appropriate folder and left click the folder
in the folder explorer (the left pane) to select it.
Click the manage button and then go to Add and select “Crystal Reports.”
If you do not see “Crystal Reports” from the Add menu you do not have sufficient
rights.
20
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC (cont’d)
● Choose a file from the file dialogue popup window. You may
also rename the file in the “General Properties” section but
only after choosing a file.
21
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC (cont’d)
● Setting the Database Configuration
○ Choose “Use custom database logon information specified here.”
○ Specify the “Server”. This will typically match the “Server Name” field
in CSA).
○ Usually the rest of the information can be left at blank or default
values. (See image on next slide)
22
www.regoconsulting.com
Phone: 1-888-813-0444
Deploying Reports in CMC (cont’d)
● Setting the Database Configuration
○ The values that are blank will be filled in
by Clarity during the handshake that
takes place between Business Objects
as long as “Prompt the user for
database logon” is chosen.
○ If you don’t want these values to be
automatically filled in (like if you
wanted a report to run across
environments for example) you can
specify the database, user and
password as necessary.
○ The table prefix field can be used if the
database server has additional schemas
setup but is typically not necessary.
23
www.regoconsulting.com
Phone: 1-888-813-0444
Best Practices
● Developing and Maintaining
○ When placing database fields place them inside text objects.
• If you have to change your query this can help you avoid losing formatting.
• This will allow you to use the format painter field across all of your text. You
can’t format paint the format of a text object onto a database field and vice
versa.
○ Use underscores in variables, formula, and database field names. This
allows for easy copying and pasting later when the requirements change
down the road and you will inevitably have to rewrite portions of the
report.
○ Use a naming scheme for similar variables e.g. all running total fields for a
certain group and a certain field match and are easily identifiable.
○ Limit the number of records when possible in the query. Often times the
Business Objects server is separate from the database server and
transferring many records can negatively impact report speed.
24
www.regoconsulting.com
Phone: 1-888-813-0444
Best Practices (cont’d)
● Developing and Maintaining (cont’d)
○ Grouping Records in Crystal is magnitudes of times slower than
grouping records on the database. Sometimes avoiding grouping in
Crystal is not possible but strive to do so when possible because
grouping records in Crystal is one of the largest contributors to poorly
performing reports.
25
www.regoconsulting.com
Phone: 1-888-813-0444
Best Practices (cont’d)
● Versioning and Deploying
○ Save a copy of your report in your local work environment before you start
a new version. I like to use the following format as it allows files to sort
nicely on the file system:
• OriginalReportName yyyy-mm-dd
○ Save a copy of your report in CMC before you deploy a the new version. I
like to follow the same naming scheme as above. If many revisions to the
reports happen an archive folder can be used so the main report folder
does not become cluttered and time consuming to navigate and search.
● Clarity Report Definitions
○ Because users can have saved parameters and add reports to the “My
Reports” portlet and because Clarity supports security on a per report
basis and because it’s less effort it is preferred to change the original
report definition. If a legacy copy must be kept XOG the original report
definition out and then in with a new name and id.
26
www.regoconsulting.com
Phone: 1-888-813-0444
Best Practices (cont’d)
● Clarity Report Definitions (cont’d)
○ Report Read XOG
<?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header version="8.0" action="read" objectType="contentPack"
externalSource="NIKU">
</Header>
<JobQuery>
<Filter name="code" criteria="OR">RPT_DATA_SETUP</Filter>
</JobQuery>
</NikuDataBus>
27
www.regoconsulting.com
Phone: 1-888-813-0444
Tips, Tricks and Strategies
● Save often! There are a few ways that the Crystal editor can get into
a state where it will not save.
○ If the computer that the Crystal Editor is running on goes into sleep mode
often when it wakes up the save and save as functions will error out with a
“Failed to save document.” message.
○ If the dataset is very large and a lot of moving around in the pages is
performed graphical glitches will begin to manifest and Crystal will soon
crash or stop responding.
○ When deleting subreports Crystal will sometimes crash.
○ Crystal will sometimes crash when editing reports in general.
○ SAVE OFTEN!
28
www.regoconsulting.com
Phone: 1-888-813-0444
Tips, Tricks and Strategies (cont’d)
● Space fields with real data but don’t do all formatting until everything is placed in the
final font and size.
○
○
○
Fine tune and format the fields after everything fits.
Use the guidelines! They will save development time and make reports appear more professional.
If the entire dataset is reasonably small run the report wide open (with all possible records) and look
for problems. This will help eliminate fields that have values that are too long.
● Consider using a text object with background colors rather than separate images for
“lights.” They’re easier to create, repurpose and move around.
● If the out-of-the-box Gantt chart doesn’t meet your needs you can create your own
using ratios and formulas to set the size and position of text objects.
● If the report renders in the Crystal viewer in Clarity incorrectly check to see if the report
has a printer configured. Sometimes removing or configuring a printer can solve difficult
rendering problems.
● Vertical alignment is not possible within a text object or field so changing the height
(size) of the element’s box and changing its location is the best option
● If you have text from one column bleeding over into another column and overwriting
characters you can solve this by opening the format editor and changing the “Text
Interpretation” of the field that is bleeding to HTML. Another option is to turn the field
into a formula and check the length and truncate extra characters when necessary.
29
www.regoconsulting.com
Phone: 1-888-813-0444
Tips, Tricks and Strategies (cont’d)
● Obtain report resources (logos, fonts, color palettes, icons) as soon
as possible from the marketing or branding department.
● A report can be set to have multiple columns in the section expert.
All of the paging functions will continue to put records on the next
page rather than the next column. To force sections to the next
column a detail section forced to “Print at Bottom of Page” will
suffice.
● Reports can be ran from a URL and set as Actions on Clarity objects.
● Reports can be auto-scheduled to email directly from Business
Objects.
● A report can render differently in the editor, the viewer and its final
intended format (pdf, Word, Excel, etc.). Test all three forms.
● Suppressed subreports can be used to perform calculations, set
variables, etc. Even though they’re suppressed they still run.
30
www.regoconsulting.com
Phone: 1-888-813-0444
Tips, Tricks and Strategies (cont’d)
● Clarity always passes the following “hidden” parameters:
○ param_language = This can be used for localization
○ param_user_id = This can be used to personalize the report to the logged
in user
○ param_host_server = This can be used to create URLs back to Clarity
● The following formula can be used to check if no records are
returned from inside a details section:
○ IsNull (Count ({Command.INVESTMENT_KEY})) where
{Command.INVESTMENT_KEY} is any field that can’t be null in Clarity
○ This is useful to create a detail section for the user to inform them that the
filter criteria chosen didn’t return any results.
● Give the report a title from the Summary Info dialogue box under
File in the menu. It will change the title in the report’s editor tab as
well as populate the correct value in the “Report Title” special field.
31
www.regoconsulting.com
Phone: 1-888-813-0444
Questions
Contact US
Thank you!
888.813.0444
Email Contact
[email protected]
Web Site
www.regoconsulting.com
32
www.regoconsulting.com
Phone: 1-888-813-0444