Discovery Conference 2009

Crystal Reports for

Beginners (Workshop)
Greg Norris and Michelle Lelempsis
 Agenda

Structure and Introduction of the Workshop

Overview of Crystal Reports

Creating a Basic Report

Record Selection

Linking tables

Using Parameter fields

iMIS Standard Reports Overview

1 hour lunch Break (12:30 – 1:30)

Workshop Exercises
 Overview of Crystal Reports

What is 'Crystal Reports‘ ?

ODBC

Relational Database

iMIS Tables

Crystal Reports Design Window
 What is 'Crystal Reports‘ ?

Crystal Reports is a powerful solution for transforming
data from virtually any source into presentation quality
reports. The Crystal Report Writer is a professional
report writer that has the ability to communicate with
iMIS through open Database Connectivity (ODBC).

“IMIS provides a variety of standard reports that can be
used to gather membership or other information from the
iMIS database.”

“Although you can could create customised reports, the
time invested in creating the reports can be costly. With
the standard reports you can clone or copy a report and
modify the report to meet your specific needs.” (ASI)
 CR9 or XI

Crystal Reports 9 - supported

Crystal Reports XI – work, but

Crystal Reports 2008 – may do...

SQL Server Reporting Service et al.

 Open Database Connectivity (ODBC)

ODBC is a means of connecting a client (e.g.iMIS) with a

database of almost any persuasion located, almost,
anywhere.
 Open Database Connectivity (ODBC)

START- All Programs Do

– Administrative Tools c n
un han ot
– Data Sources (ODBC). kn less ge
ow y
ho ou
w…
 Database Server

Servers manage Databases (DBMS)
\\myserver
 Relational Database

A relational database is constructed of tables which are
two-dimensional objects consisting of Fields (Columns)
and rows that are related to one another by Key Fields.
 iMIS database tables

In iMIS, the ID is the key used when
relating to Customer (Name) records.

Bill to ID (BT_ID) and Sold to ID (ST_ID)
are used in Orders and Invoices tables.
 iMIS Tables

The iMIS database comprises many tables and
groups of tables. (i10.6 = 302 tables, i15.1 = 873 tables)

Name, Activity, Trans & Orders are key tables in
iMIS.
Crystal Reports Design Window
Questions…
 Creating a Basic Report

Identify the steps in creating a Crystal Report

Connect the report to the iMIS database

Use the Report Wizard

Format the fields using the Format Editor

 Creating a Report: 5 Steps
1. Chose the report type – select the template
2. Choose a data source
3. Choose the tables
4. Choose the fields
5. Choose the records (rows)
When building a report write down the answers to these
questions as your plan of what you are tying to achieve.
 Choose report type

Identify from your existing reports which
“Style” you want to follow.

Using the KISS principal makes life easier
and is usually easy to modify later on –
even to integrate as a sub report.
 Choose Datasource

Where is the data coming from?

Which server

Which database

which ODBC connection to use…
 Choose Tables & Fields

Where is the data going to come from?

Tables - which ones, or

View/s ( a “view” of data from a predefined set
of one or many tables)

Fields from these table/s or view/s

Identify which are required for your report.

Perhaps more than are displayed

Can use MSExcel or SQL Query Analyser to
help identify
 Choose the records

Which rows of data do you need?

What filters do you need to limit the report to
only the correct data?

Location

Date

Event / Transaction type

Value $

A combination of above
 Crystal H E L P

Comprehensive help is available. (F1)
Open or use the Wizard
Select Method
Data – using ODBC
Locate Table/s
Add Table/s then Fields

3
 Ignore Group -|- Do Filter

Skip Grouping
Select a City to filter with
Preview Results
Design View
 Format Editor Window

The Format Editor window is used to
format a field and contains tabs specific to
the format selected. The following tabs
and options are available on the Format
Editor window.
Format Editor Window
 Format Editor Window
Common tab – General formatting to the field data and
text objects

Object name: Specify a name for the object you are

formatting. A default name is created for each field in a
report.

Read-only: Enable this option to make the selected
report object read-only so that it cannot be formatted.

Lock Position and Size: Enable this option to lock the
position of the selected report object so that is cannot be
moved.

Suppress: Enable this option to suppress printing the
field’s data on your report.
 Format Editor Window

Can Grow: Enable this option to allow the
text object to expand vertically to fit all tet
and data. Maximum number of lines can
be used to restrict the fields size.

Text Rotation: Use this list to rotate the
text on your report.

Suppress if duplicated: Enable this
option to suppress printing duplicate data
on your report.
Border Tab
 Border Tab

Line style: Left, right, top and bottom

Drop shadow

Colour: Border and background

Sample: Displays an example of the

formatting.
Font tab
 Font tab
Allows you to format the:
• Font
• Style
• Size
• Colour
• Effects
• Spacing
• Sample
 Paragraph Tab
Allows you to apply paragraph styles
• Indentations
• Spacing
• Reading order
• Text interpretations
Hyperlink Tab
 Date and Time Format Editor

On date and time fields
this tab is also available
in the format editor
window to customise the
date and time style.

Exercise 2 will review

this section.
 Report Design Window

The Report design window has several
toolbars that help you format and design a
report.
Report Design Window
 Report Design Window

Standard toolbar

Formatting toolbar

Insert tools toolbar

Expert tools toolbar
 Design Tab

The design tab allows you to select the
fields and format the various report
sections.

Report Header

Page Header

Details

Report Footer

Page Footer
Design Tab
 Record Selection

Record selection, and most report
automation, requires the use of formula
e.g. City = “Huston”

Crystal or Basic?

Basic Syntax 101 appended.
 Formula Language

Formula syntax

Syntax rules are used to create correct formula. Some basic rules are:

Enclose text strings in quotation marks.

Enclose arguments in parentheses (where applicable).

Referenced formulas are identified with a leading @ sign.

Crystal and Basic syntax

When creating formulas, you have the option of using either Crystal or Basic syntax. Almost any formula written with one
syntax can be written with the other. Reports can contain formulas that use Basic syntax as well as formulas that use Crystal
syntax.

Crystal syntax is the formula language included in all versions of Crystal Reports.

If you are familiar with Microsoft Visual Basic or other versions of Basic, then Basic syntax may be more familiar to you. In
general, Basic syntax is modeled on Visual Basic except that it has specific extensions to handle reporting.

If you are already comfortable with Crystal syntax, you can continue to use it, and benefit from the new functions, operators
and control structures inspired by Visual Basic.

Note:

Record selection and group selection formulas cannot be written in Basic syntax.

Report processing is not slowed down by using Basic syntax. Reports using Basic syntax formulas can run on any
machine that Crystal Reports runs on.

Using Basic syntax formulas does not require distributing any additional files with your reports.
Questions…
 Linking Tables

To add tables you have to link them
together - or all records will mesh with all other records in a
Cartesian join where 10,000 names with their postal addresses
could amount to 100 Million records returned to Crystal rather
than the 10,000 you expected.

Stored Procedures reduce rows returned at Database level .

Linking is very simple process using the
Database Expert.
Database Expert
Linking
Linking in action
Using Parameter Fields
Adding Parameters
 Crystal Reports in iMIS

Crystal Reports produces a report file with
the rpt extension.

iMIS uses a special interface to modify the
query to suit the data you require.

In order to run the rpt file you need a
Crystal Reports client. This is provided in
the iMIS client installation.
 Clone Reports

Always clone, never modify standard
reports. Changes will be lost on an upgrade.

Establish and follow naming conventions.

Add (Crystal) to identify Crystal reports.

Add information including report path and
name to description.

Clone with a period (“.”) at the beginning of
the name of the cloned report to
make it the default report for its type.
 iMIS standard reports

StdRpts –folder in iMIS
 CustReports in iMIS 10.?
File
– System Setup
 AR / Cash reports
AR / Cash
- Generate Reports
Report Specs
Parameters – iMIS - reports
Crystal Report Parameters
Cloning is simple..
ARStatement.rpt
 Crystal Reports XI

The same but different from CR9..
 Beyond Basic Reports

Tomorrow – (Thurs) 9.00 am – 12.15 pm

Advanced Crystal Reports Workshop
 Questions…

Next - Off to lunch

– back at 1:30 for Workshop
 Basic Syntax 101

Tips on learning Basic syntax

If you have no programming experience

• In many cases you may not need to use the formula language. Crystal Reports includes several Experts that automatically
handle situations where formulas could be used. These include the Select, Search, and Highlighting Experts, and the Insert
Summary and Create Running Total Field dialog boxes. Before creating formulas, check to see if you can use one of these
tools.

However, you may need to create formulas without the help of an Expert. Read this section to learn about Basic syntax and
the rules you need to follow to create a formula.

If you know Microsoft Visual Basic, VBScript, or another version of Basic

In order to create formulas using Basic syntax, you need to understand the following:

How a Basic syntax formula refers to other fields in the report such as database fields, parameter fields, summary fields,
running total fields and other formula fields.

How to return a value from a formula by setting the special variable named formula.

How to use functions specific to report processing such as ReportTitle and OnFirstRecord.

How to use the type system. Basic syntax is strongly typed, similar to using Option Explicit in Visual Basic and there is no
Variant type. Basic syntax supports separate Date, Time and DateTime types unlike just the Date type in Visual Basic. It also
supports range types such as 10 To 20 to allow for ranges of values which are common in many reporting applications.

Read this section to familiarize yourself with the above details.
 Cont/.. Basic Syntax 101

Familiar features

Many Basic syntax functions work in the same way as their counterparts in Visual Basic. This includes string functions such
as Len, Mid and Filter, math functions such as Abs, Rnd and Sin, financial functions such as PV, programming shortcut
functions such as IIF and date functions such as DateSerial, DateAdd and DateDiff.

Most operators supported by Visual Basic are also in Basic syntax. For example, string concatenation (&) and date-time
literals (#...#).

Most statements and control structures use the same syntax as in Visual Basic. This includes the If, Select, Do While, Do
Until, While and For/Next statements.

The overall look of the formula will be unmistakably Basic like. For example, Basic style comments and line continuation
characters are supported as is the Basic language use of new lines, colons, and the equal sign.

If you already know Crystal syntax

The main adjustment is getting used to the parts of the Basic language that are common to every version of Basic. Read this
section for a detailed introduction.

--------------------------------------------------------------------------------

Crystal Decisions

http://www.crystaldecisions.com/

NOW – SAP

http://www.sap.com/solutions/sapbusinessobjects/index.epx
 Tips

When changing any standard iMIS report, we
recommend saving the Crystal report under a
new name and “Cloning” the existing iMIS
report in the report specs
 References

Crystal Reports Online Help

Imis Report Specs –NiUG Library Baltimore conference.
(jzatz@csystemsllc.net)

ASI Documentation

http://docs.imis.com

http://docs.imis.com/10.6/

ASI runs training in Crystal and other modules.

http://www.advsol.com/AM/Template.cfm?Section=Courses4

Thank you for your input into today’s session.