Crystal Reports Training1 3n2cr

Crystal Reports Version 3.0

The New Standard in Quality Education

Crystal Reports Overview

2



Pre-requisites



Introduction


Objectives “To enable you to develop your own reporting solutions in Crystal Reports with confidence”

3


Course Overview 1 2 3 4

4

Data Structure Simple Report Creation Crystal Report Functions Running Reports


Scala Data Structure Section 1


Section Overview   

6

What is a Database? How do we connect to the Database? Scala data file structure


How Do We Connect to the DB? Scala Client Win95, 98, NT Web Browser

Scala Program Layer

Scala5.x Modules

Btrieve or SQL data management layer

Data Management

Database

7


How Do We Connect to the DB?

Crystal Reports DDF’s – Describe Database (Btrieve) ODBC – Connects to Database table SYSOBJECTS

Read Only Database

8


Data File Structure 

Scala Table Naming Definition

MMNNCCYY Module

Financial Year

Unique Number

11

Company Code



12

Format of Data files



13

Format of Fields



  

14

Database to Interpret field and file names ScaDBconv.exe Scala5eu.mdb in Scala root directory Table in SQL database SCA_TAB_COLUMNS



15

Scala5eu.mdb welcome screen


Data File Structure

16


Break 

17

Break


Section Review   

18

What is a Database? How do we connect to the Database? Scala data file structure


Simple Report Creation Section 3


Section Overview     

20

Report Style SQL Data Files Scala Data Files Scala Data Fields Preview Report


Report Creation Click

21


Report Style Report A - Stock Listing Step 1 - Choose report style

Click

22


Data Source Step 2 - Select Data Source

Click

23


Data Source Step 3 - Choose Data Source

Note the different database options!!

24


Data Source Step 3 - Choose Data Source Need to supply name and

25


Scala Data Files Step 4 - Select Scala Data Tables Select File(s)

Click To Finish

Select Single or Multiple Tables 26


Scala Data Files Confirmation of Selected Files

Click Next

27


Scala Data Fields Step 5 - Select Fields

28


Scala Data Fields 

Report 1 Stock Listing – – –

–

29

Stock Code Description Line 1 Price Local Currency Stock Balance


SC01001 SC01002 SC01004 SC01042

Scala Data Fields

Select Fields Choose Add

30


Field Tab

31


Preview Report When all fields are selected choose FINISH

Click

32


Report Creation

33


Report Creation 



34

You can return to the report expert at any time by using the function Recommend that the report is saved before returning to the expert


Break 

35

Break


Section Review     

36

STEP 1 STEP 2 STEP 3 STEP 4 STEP 5

- Report Style - SQL Data Source - Scala Data Tables - Scala Data Fields - Preview Report


Exercise Exercise 1 Customer Listing


Crystal Reports Section 3


Section Overview    

 

39

Design & Preview Report Options Guidelines Refreshing Data Right Click Sections


Design & Preview Tabs

40


Design & Preview Tab

41


Preview Tab

42


Report Options  

43

Options Report Options


Options

44


Options

45


Options

46


Guidelines

47


Refreshing Data  

48

F5 Refreshes Data or Preview Sample


Refreshing Data 

49

Preview Sample


Right Clicking

50



Useful in many areas of Crystal



Short Cut to menu options The New Standard in Quality Education

The Magic Button 

51

Undo / Redo


Sections Left-hand side of design tab shows report sections

Report Sections

52


Sections 





53

Right-hand clicking in the sections area brings up menu Format sections command gives further details of sections. Section can be hidden allowing drill down function or suppressed


Sections

54


Section Review    

 

55

Design & Preview Report Options Guidelines Refreshing Data Right Click Sections


Formatting


Section Overview      

 

57

Adjusting Headings Increasing Field Width Adding new fields Adding Special fields Selection Expert Sorting Grouping Parameters The New Standard in Quality Education

Adjusting Headings  



58

Highlight field To rename double click or | edit | text object As with many windows products Crystal Reports s drag and drop and copy and paste


Increasing Field Width 





59

Highlight the field

Place pointer on blue boxes it changes to arrow Field width and height can be adjusted by dragging arrow The New Standard in Quality Education

Adding New Fields Text Field

Drag And Click to Drop 60


Adding New Fields Field Explorer

61


Adding New Fields 

Database fields

Select field

62


Adding Special Fields

63


Adding Special Fields 

64

A number of the special field values are set in Summary info from file menu


Selection Expert 



65

Choose Select Expert

Then choose field to select items from. Report on non-report field The New Standard in Quality Education

Selection Expert

Default to AND can change to OR through formula 66


Selection Expert 

Example of Selection

New!

67


Sorting Records 

68

You can sort by multiple columns.


Sort Expert 

Brings up sort box

Sort Fields Report Fields



69

Sort Direction

Automatic sort by column 1 The New Standard in Quality Education

Grouping  

70

Used to group data together Similar to sorting but adds headers and footers


Grouping  

71

Used in Sub-Totaling can define the order


Grouping

Define Order

Extra Options on Date field

72


Grouping 

Specified Grouping – defined order

Select Groups

73


Grouping 

74

Example of Grouping


Parameters  





75

Used with selection Provides an easy way to change selection when a report is refreshed Parameter can be used as a field in the report. For Example in the report title. Compatible with compiled reports or web based reports


Parameters



76

Then click icon to create new parameter


Parameters Text that appears when report is run

Type Next Screen

77

Allows parameter to hold single value or multiple values and Ranges. Experiment to see affect The New Standard in Quality Education

Parameters Warning on Usage

78


Parameters 

79

Example of Usage


Parameters



Enter Parameter as selection Parameter field denoted as (? Field)



Now refresh data



80


Parameter

81


Section Review      

 

82

Adjusting Headings Increasing Field Width Adding new fields Adding Special fields Selection Expert Sorting Grouping Parameters The New Standard in Quality Education

Exercise Exercise 2


Break 

84

Break


Totalling and Formula’s Section 6


Section Overview   



86

Strings Converting Strings Totalling / subtotalling and summaries Converting Dates


Strings 





87

Scala 5.1 SQL holds the majority of fields in the correct format. However due to historical reasons some fields will need to be converted Strings are collections of characters alpha and/or numeric Strings need to be converted to numbers for Crystal to perform operations The New Standard in Quality Education

Converting Strings to Numbers 

Create a new formula Click New

88





89

Give the formula a name

Tip - Give your formula name the column name The New Standard in Quality Education


This then brings up formula editor box

Operators

Fields

Formula Text

90

Functions



To convert a string to a number type

Function

Field

ToNumber({Field name}) 

Be careful of Brackets –

– –

91

(,) = function {,} = field [,] = field value (Seen later) The New Standard in Quality Education

Converting Strings to Numbers

92







Unfortunately this formula does not always work. Scala leaves blanks in database instead of zeros. Therefore we need to add to the statement If {field_name} = “” Then 0 Else ToNumber({Field_Name})

93


Exercise Exercise 3


Totalling and Sub-totalling 



95

Sub total or grand total fields must be numeric. Highlight the field you require a total for and select totalling from insert drop down menu.


Totalling and Sub totalling 



96

Right hand clicking on the report field will also bring up a shortcut method to enter totalling.

If the options are missing on the menu you have not chosen a numeric field The New Standard in Quality Education

Sub-totalling 

97

Entering a sub-total


Totalling 



98

Insert grand-totals

Choice of sum, maximum, minimum, average etc. Field printed in report footer The New Standard in Quality Education

Summaries Similar to subtotal but will give same totalling options as grand total function 

99


100

Converting Dates 

Again the majority of dates in Scala database are held as datetime or date fields. However some may need conversion from a string field

19980915 YYYYMMDD


101

Converting Dates 

Conversion requires following formula: – –

– – –

If {Field_Name} = “” then Date(0,0,0) Else Date(ToNumber({field_name}[1 to 4]), ToNumber({field_name}[5 to 6]), ToNumber({field_name}[7 to 8]))


102

Section Review   



Strings Converting Strings Totalling / subtotalling and summaries Converting Dates


Exercise Exercise 4


Formatting Numbers & Dates Section 7

Optional


105


    

Formatting Strings Formatting Numbers Formatting Dates Conditional Formatting Highlight Expert Top N Selection Report Alerts Running Totals SQL Expressions The New Standard in Quality Education

106

Formatting Strings Right-hand click on field or chose format field from format menu 


107

Formatting Numbers Extra Format Tab 


108

Formatting Numbers


109

Formatting Dates Extra Format Tab Choose Customize 


110

Conditional Formatting Conditional Vs Absolute 

Select a number field for this example The New Standard in Quality Education

111

Conditional Formatting 

Brings up the Formula Editor Box


112


Button Changes to Red indicating value is controlled conditionally


113




Conditional formatting can be used throughout Crystal reports Advanced technique that is particularly useful in dealing with Sections


114

Highlight Expert 



Works similarly to previous example of conditional formatting Gives a Traffic Light effect to the data


115

Highlight Expert


116

Report Alerts 





Similar to Highlight expert but a window appears when a report is run to indicate records correspond to a certain set of conditions For Example – when printing a customer statement shows customers with a balance exceeding £1000 New functionality in Crystal 8.5


117

Report Alerts


118

Report Alerts


119

Report Alerts


120

Report Alerts 

When report data is refreshed the report will then produce an exception report


121

Top N Expert 

Used for finding the top N on any column. For example the top 20 outstanding supplier balances on purchase ledger.


Top N Selection 

122

You must create a sub total or summary before the top N selection will work.

Specify value of N


123

Running Totals 



Used to create cumulative totals in reports Totals will be dependent on the grouping of the report


124

Running Total


125

SQL Expression Fields 







Used only with SQL compatible databases Allow SQL syntax to entered in Crystal report Improves report performance when grouping, selecting or sorting data as query is run on server instead of workstation Limited in functionality compared with crystal formulas The New Standard in Quality Education

126

SQL Expression Fields 

Creating a new SQL expression


127

SQL Expression Fields

Note limited functionality


Field Explorer Browse Data Only New Edit Rename Insert

128


Delete

Toggle Field Type

129

Field Explorer    



? denotes Parameter field @ denotes formula field Σ denotes SUM or group field # denotes running total % denotes a SQL expression


130


    

Formatting Strings Formatting Numbers Formatting Dates Conditional Formatting Highlight Expert Top N Selection Report Alerts Running Totals SQL Expressions The New Standard in Quality Education

Exercise Exercises 5


Linking Tables Section 8


133


Why link tables Rules of linking What is an indexed field How to link tables


134

Why Link Tables 





Scala holds data in multiple data files For the majority of reports data files need to be linked. Warning! Linking files will slow down report speed. Keep the number of data files to a minimum.


135

Rules Of Linking 





Not all data files can be linked directly Data files must have common element Performance is improved if the common element is an indexed field


136

Rules of Linking 

 

Linking must be done in a logical sequence It must flow through the report Jot down blueprint of report before starting to build it


137

Linking Tables 

How do we link the following tables together – Start with SL01

OR01 Order No

SC01 Stock Code Customer Code

OR03

SL01


138

What is an indexed field? 



A field that the data file uses as a sort or search key Example Telephone directory

Smith A J

23 North Parade, Horsham 01403

215998

= The Index (Ordering) Smith A J

23 North Parade, Horsham 01403

= The Primary Key The New Standard in Quality Education

215998

139

What is an indexed field? Key 1

Key 2

2087

2307

Clive's Coffee Shop

Customer Address

2190

2087

Arnot Limited

Customer Address

2307

2498

Drew PLC

Customer Address

2498

2190

Blackmore Electronics

Customer Address

Index 2

Key 1

Key 2

Arnot Limited

2307

Clive's Coffee Shop

Customer Address


2087

Arnot Limited

Customer Address

Clive's Coffee Shop

2498

Drew PLC

Customer Address

Drew PLC Drew PLC

2190


Customer Address

Index 1


140

How to Link Tables 

To an existing report

In this example use report with SL01 and link SL03 The New Standard in Quality Education

Select

141

How to Link Tables

Select


142

How to Link Tables


143

How Link Tables

OK


144

How to Link Tables 

Smart Linking works on following conditions – – –





Field name is same in both tables Field size is the same in both tables Field type is the same in both tables

Note: Because of this you may receive warnings when linking tables because of different data type Linking can only be done between database fields. (NOT FORMULA’S) The New Standard in Quality Education

145

How to Link Tables Drag and Drop from SL01001 to SL03001


146

Linking Tables

Crystal s different types One to One (equal) One to Many (Left Outer) Many to One (Right Outer) And others….


147

Linking Tables  



Once the tables are linked click OK The fields in the table then become available to report on To return to the visual linking expert


148


Why link tables Rules of linking What is an indexed field How to link tables


Exercise Exercise 6


150

DAY 2 - Overview    

 

Scala Database Structure Simple Report Creation Crystal Functionality Formatting More formatting and field types Linking Tables


152


Pictures and Logo’s Hyperlinks Lines / Boxes Charting Mapping Function Report Style / Auto Arrange


153

Extra Functionality Picture files can be embedded in report to give them a professional finish  Crystal s bitmap BMP, jpeg JPG, tiff and png files 


154

Extra Functionality



In the properties of the picture or any field a hyperlink can be set so when clicking on the logo it takes you directly to the web site


155

Extra Functionality


156

Lines and Boxes 

These extra formatting tools are available from the insert menu


157

Lines and Boxes


158

Charting 





Tool can be used to create professional looking graphs and charts that can be embedded into the Crystal report Drill down functionality can be used in conjuncture with charts Charting works on subtotal values in the report however the whole report can be suppressed with just the chart being visible The New Standard in Quality Education

159

Charting


160

Charting


161

Charting


Exercise

Exercise 7


Running Reports Section 9


171

Section Overview  

  

Exporting What is compilation and Distribution? Compiling Report Distribution Expert Web Reporting


172

Exporting 



Crystal has links with other software including excel, adobe, HTML, RTF, text and XML To export report file | print | export or


173

Exporting 

The report can also be emailed or saved to an exchange or lotus notes folder for publication


174

What is Compilation? 





Reports can be saved as an executable Report s then need no knowledge of Crystal design tools they can just run the report Selection or parameters can be changed


175

Compiling 





Historically Seagate software has included the compiling and distributing tool as part of the standard product In Version 8 & 8.5 this tool was replaced by web reporting however due to demand they included an update to the product which can be ed at http://www.crystaldecisions.com/ The New Standard in Quality Education

Compiling 

176

Select compile report from report menu


177

Compiling 

Brings up compile box

File name

No



Click OK to compile The New Standard in Quality Education

178

Compiling 

s can run compiled reports only if they have crystal reports installed on their workstation


179

Compiling

Defaults to refresh if no data saved with report


180

Report Distribution Expert 





Can be used where does not have Crystal installed Copies program files that report needs to run. s who run report will need to ODBC connection to the Scala Database set-up


181

Report Distribution Expert

Can Distribute more that one report at a time

Indicate shared destination directory


182

Report Distribution Expert 





This expert will then run the report in the background and prepare a list of dll’s it needs to run the report. It warns about ODBC components but doesn’t include them You can adjust the included dll’s but this is not recommended Then click on build which produces a setup.exe file which then needs to be run on clients The New Standard in Quality Education

183

Web Reporting 





Crystal Version 8.5 requires crystal enterprise in order to run web reports If you purchased the Professional or Developer edition of Crystal Reports, then you also received the Crystal Enterprise Standard CD, along with five free concurrent access licenses of Crystal Enterprise Standard. More information is available on the fact sheet The New Standard in Quality Education

184

Web Reporting 



Scala’s new product iScala Web reporter uses Crystal enterprise technology Crystal enterprise is not currently included in this course


185

Section Review     

Exporting What is compilation Compiling Report Distribution Expert Web Reporting


Exercise Exercise 8


Database Features Section 11


188




Crystal Report Functions Crystal Data Dictionaries Crystal SQL Designer SQL Views Scala SQL View Manager


189

Crystal Report Functions 



SQL query produced automatically when building the report This query can be viewed and manually edited in Crystal reports


190

Crystal Report Functions


191

ing Database 

If database is changed while is connected in Crystal need to run function in order to update Crystal


202

SQL Views 





Can only be written by a system manager of the SQL server Similar in design to Crystal SQL designer however far more powerful Are important for reporting in certain area of Scala as they cut down the number of sub-reports needed and cut down on complexity of report


203

SQL Views 



Improve Crystal report performance as sorting and linking completed on the server Need access to SQL enterprise manager in order to build views


204

SQL Views


205

SQL Views

Tables and Linking

Select Columns sorting and criteria SQL Query Result Set


206

SQL Views


207

SQL Views 

SQL Views are particularly useful for –

Creating Alias for column names » E.g. SL01001 = Customer Code

– –

–

Changing data types for Columns Working with tables SY24 general code file and SL23 payment / delivery Working with General ledger transaction file GL06 which contains a binary field type for transaction type


208

Scala SQL view Manager 

Include on the application CD –



Convert\SQL view manager.exe

Translates column numbers into meaningful names by the way of creating SQL views for each table


209

Scala SQL view Manager


210



211



212

Scala SQL view Manager 





Views need to be created for each company Recreate views when a new financial year is created in Scala Recreate views when a service release is applied to Scala


213




Crystal Report Functions Crystal Data Dictionaries Crystal SQL Designer SQL Views Scala SQL View Manager


Exercise

Exercise 9


Report Experts Section 11

Optional


216

Section Overview      

 

Standard Form Letter Form Cross-Tab Sub-Report Mail Label Drill Down OLAP The New Standard in Quality Education

217

Report Experts


218

Report Experts 

 

Drill down report created by hiding sections Mail Label Report – Self Explanatory OLAP report (On-Line Analytical Processing). – – –

Uses cubes of data to report on Cubes Create with SQL server Advanced Technique


219

Form Report 





Form report asks for bitmaps for different sections of report. These bitmaps need to be created first outside Crystal An example of a report using Form expert is in Section 6 of the course binders Get Similar result inserting pictures into sections The New Standard in Quality Education

220

Form Letter 



 

Used for sending out letters with data from database Design the report as usual getting the basis or the report together with the Expert Example in Section 6 of Course Binders Form Letter creates one large text block per section


221

Form Letter


222

Form Letter


223

Cross Tab 





Cross Tab combines the use or rows and columns along with a summarized field Expert creates the cross tab in report header section Example of Cross Tab in section 6 of the course binders


224

Cross Tab


225

Cross Tab

Summarized field format


226

Cross Tab


227

Sub Report 







Can be used to combine any two reports together Example In Section 6 of Course Binders A Sub report can be entered into an existing report using the function on the insert menu Sub Reports can be linked using a common element ( parameter field ) The New Standard in Quality Education

228

Sub Report


229

Sub Report


230

Sub Report 





The sub report is inserted into a section on the main report and is run every time that section occurs The sub Report is an advanced technique but can provide good results. Data can be ed from the sub report to the main report by the use of variables


231


 

Standard Form Letter Form Cross-Tab Sub-Report Mail Label Top N OLAP The New Standard in Quality Education

Exercise Exercise 10


Appendices


Appendix A Section 14 Reporting on ing Strings


235

ing Strings 





In Scala 5 s cost centres etc are not in separate fields they are held in one long field. Therefore to produce reports you may need to Separate this field into each dimension. Before you start you need to know the exact length of each dimension The New Standard in Quality Education

236

ing Strings To separate ing string you will need to use formulas


237

ing Strings


238

ing Strings 



If code is longer than 6 just adjust the field values at end of formula Continue on like this for cost centre, product, project etc.


239

ing Strings 





You may get problems with the previous formula if you are using alpha numeric cost centres, products or projects. As we have seen before these does not convert to a number If this is the case then use the next formula The New Standard in Quality Education

240

ing Strings


241

ing Strings


242

ing Strings The trim function does exactly what it says it trims a field down to the lengths controlled by the square brackets [].


Exercise Exercise 11


Appendix B

Converting Reports from Btrieve to SQL


245

Crystal Reports  

Converting report from BTR to SQL Open up the crystal report and select convert database driver from the database drop down menu


246

Crystal Reports 

Select the correct data source


247

Crystal Reports 

Then get error as below –

Solution set location of data files in database drop down menu (remove the .dat at the end)


248

Crystal Report


249

Crystal Reports 





Map the fields across to the SQL database The SQL database holds all field names as numbers e.g. SC01001 for stock code. Therefore use the scala5eu.mdb database to lookup the correct field codes for field names Alternatively create views from the SQL database using the SQL view manager and map your fields to theses The New Standard in Quality Education

250

Crystal Reports


251

Crystal Reports 





Once all data files are mapped across check the visual linking expert for the correct file linking Then run report. The report will probably give errors due to the different types of field type e.g. Numbers are formatted as numbers in SQL not as strings Then check report is returning the same information / totals The New Standard in Quality Education

252

Btrieve


253

Btrieve Data Tables


254

Data File Structure Btrieve Scala5

DAT GL PL GL06ccyy.DAT cc = Company Code yy = Financial Year The New Standard in Quality Education

255

Btrieve DDF Files Description of files, fields, indexes  Gives location of Data  Btrieve Information database  Scala5 

File.ddf Field.ddf Index.ddf


256

Crystal Report Errors

Click

Click The New Standard in Quality Education

257

Crystal Report Errors

Click

Solution –

Set Location of Scala’s data files


258

Set Location


259

Set Location Set Location

Select File Location


260

Set Location 



The setting of location only needs to be done once. Each subsequent time the report is ran Crystal will find the data.


261

DDF Builder 



Scala 5.1 Btrieve does not come with the data dictionary files (DDF) these files need to be created using a program called Scala DDF Builder The Scala DDF Builder can create DDF’s for companies, years and modules


262

Crystal Reports 

To install DDF Builder


263

Crystal Reports


264

Crystal Reports


265

Crystal Reports


266

Crystal Reports


267

Crystal Reports


268

Crystal Reports


269

Crystal Reports


270

Crystal Reports


271

Crystal Reports


272

Crystal Reports


273

Crystal Reports


274

Crystal Reports


275

Crystal Reports


276

Crystal Reports


277

Crystal Reports 

Help for DDF builder –

If you install DDF builder to the Default location help can be found in » C:\program files\DDFstart\ddfbuilder.hlp





At year end you can add the next years DDF files to you current files This program saves a lot of time over smithware DDF builder although it is not as reliable The New Standard in Quality Education

Crystal Reports Training1 3n2cr

Overview 26281t

More details 6y5l6z

Related Documents 3h463d

Crystal Reports Training1 3n2cr

Crystal Reports Installation Guide 4s546h

Manual Crystal Reports i4q46

Crystal Reports Tutorial Beginner c5v1q

Crystal Reports Tutorial ca1v

Using Crystal Reports 6m321r

More Documents from "Percival A Fernandez" 49420

Crystal Reports Training1 3n2cr

Course Syllabus Introduction To C++ Programming 5w5867

Godzilla (1977 Crestwood House) (c2c Tranzor) 2x473s

Analisis Musical Conceptos[1] o3x20

Sociology Glossary.pdf 2x581c

Fundamentals Of Technical Writing 1k5s1s