Crystal Report tips and Techniques

(1)

SUNGARD SUMMIT 2007 | sungardsummit.com 1

A Community of Learning

Crystal Report tips and

Techniques

Presented by: Instructor Jim Tule

March 20, 2007 Course ID 1391

(2)

2

Course ID 1391

Introduction

Crystal Reports tips and techniques used to enhance the reporting capabilities in your user community.

 Learn how to set up more common Crystal Report options

to work with the Advance environment.

 Learn how to create and use formula fields in reports.  Learn how to create and use Hyperlinks with in your

Crystal reports.

(3)

(4)

4

Course ID 1391

(5)

5

Course ID 1391

Crystal Reports Options Tab

• More common Reporting options

Use these options to handle null values to 0 (numeric) or blank (Character).

Never Set this when using reports

(6)

6

Course ID 1391

Crystal Database Options Tab

Increases report performance

Can Restrict what database information can be used in

(7)

7

Course ID 1391

Crystal Field Options Tab The Options menu

can help users

define defaults for different field

formats. As an example, you can customize defaults for number values or for Currency. Other options include adding borders and highlighting.

(8)

8

Course ID 1391

Crystal Font Options Tab Formatting fonts,

size and colors for different object in the report can be

completed using the Fonts section in the Options menu.

(9)

(10)

10

Course ID 1391

Crystal Report Formulas – Address Blocks

Creating and formatting address lines in reports.

(11)

11

Course ID 1391

• This checkbox

allows for any detail line with blank

information to be removed

from the report output.

(12)

12

Course ID 1391

• Create a formula to handle foreign or domestic address

lines. (Format used in this formula is Crystal formula.)

 Please note that in Advance address tables the default is a blank  IF {ADDRESS.FOREIGN_CITYZIP} = " " THEN {ADDRESS.CITY} +

", " + {ADDRESS.STATE_CODE} + " " +{ADDRESS.ZIPCODE} +{ADDRESS.ZIP_SUFFIX} ELSE {ADDRESS.FOREIGN_CITYZIP}

(13)

13

Course ID 1391

• Commonly used in Mail Lists. So how can you do this

(14)

14

Course ID 1391

• Creating Profile Reports

 a lot of Data to be captured on the page  formulas can be used to encapsulate data

(15)

15

Course ID 1391

(16)

16

Course ID 1391

(17)

17

Course ID 1391

• Formulas Provide custom field creation and data

(18)

18

Course ID 1391

• Functions Used:

 CHRW(13) – Provides Carriage Returns

 Right({Field}, N) – Provides Values to the Right N  Left({Field}, N) – Provides Values to the Left N  IF THEN ELSE – Provides Control Structure

• Other Helpful Functions

 Trim(Str) – Removes leading and trailing spaces  Length(Str) – Returns the number of characters  ToNumber(Str) – Converts string to number

(19)

(20)

20

Course ID 1391

Crystal Report Hyperlinks

• Creating a Hyperlink to Email

(21)

21

Course ID 1391

Crystal Report Hyperlinks

• Hyperlink Tab Options

 Link to static Email  Create a Formula Link

• Other Options

 Link Websites  Link Files

(22)

22

Course ID 1391

Crystal Report Hyperlinks – Formula Example (email)

(23)

23

Course ID 1391

Crystal Report Hyperlinks – Formula examples (Font)

• Set font attributes

(24)

24

Course ID 1391

(25)

25

Course ID 1391

• Font underline use

(26)

26

Course ID 1391

Crystal Report Hyperlinks – AWA Hyperlinks

• Creating AWA Hyperlinks

• Can be placed in stored procedure • Can be created via formulas

• SQL syntax example:

SELECT 'reportlink.aspx?type=ENTITY&key1=‘ || e.id_number AS id

• Crystal syntax:

reportlink.aspx?type={keytype}&key1={keydata1}[&key2 ={keydata2}][&key3={keydata3}]

(27)

27

Course ID 1391

Crystal Report Hyperlinks – AWA Hyperlinks Adding a link using the formula editor

(28)

28

Course ID 1391

• From Report!

(29)

29

Course ID 1391

• To Entity Overview!

Use the Back button to get back to the report

(30)

30

Course ID 1391

• Stored Procedure Hyperlinks

 Useful with Giving Reports  SQL Output Example

(31)

31

Course ID 1391

• Report Screen

Receipt # Link Code

(32)

32

Course ID 1391

(33)

(34)

34

Course ID 1391

Creating Crystal Reports with Sub Reports

• Allows users to create additional reports within a main

report.

• Ability to present data from unrelated datasets • Allows multiple presentations per dataset

(35)

35

Course ID 1391

• Crystal Sub Report Design Mode

Sub Report Main Report

(36)

36

Course ID 1391

• Sub report is a Graph/Chart Format Listing Giving Totals over a 5 year period • Main Report is a Detail Report listing Donors with giving larger than $500000 in the current year

(37)

37

Course ID 1391

• Ability to return a subset of data to the report section

(38)

38

Course ID 1391

(39)

39

Course ID 1391

Summary

• Crystal report Options sets standards for data and

reporting values.

• Formulas provide great flexibility and power by enabling

you to create derived fields not available in data

sources. Formulas also enable you to create advanced conditional formatting and use flexible selection

formulas in reports.

• Hyperlinks Bring additional reporting power to Crystal

Reporting, allowing linking to web pages, files and email.

• Sub Reports bring multiple datasets to a single report

(40)

40

Course ID 1391

(41)

41

Course ID 1391

Thank You!

Jim Tule

[email protected]

Please complete the online class evaluation form Course ID 1391

SunGard, the SunGard logo, Banner, Campus Pipeline, Luminis, PowerCAMPUS, Matrix, and Plus are trademarks or registered trademarks of SunGard Data Systems Inc. or its subsidiaries in the U.S. and other countries. Third-party names and marks referenced herein are trademarks or registered trademarks of their respective owners.