Crystal Reports Training1

  • Uploaded by: api-3704027
  • 0
  • 0
  • November 2019
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Crystal Reports Training1 as PDF for free.

More details

  • Words: 6,350
  • Pages: 277
Crystal Reports Version 3.0

The New Standard in Quality Education

Crystal Reports Overview

2



Pre-requisites



Introduction

The New Standard in Quality Education

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

3

The New Standard in Quality Education

Course Overview 1 2 3 4

4

Data Structure Simple Report Creation Crystal Report Functions Running Reports

The New Standard in Quality Education

Scala Data Structure Section 1

The New Standard in Quality Education

Section Overview ● ● ●

6

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

The New Standard in Quality Education

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

The New Standard in Quality Education

How Do We Connect to the DB?

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

8

Read Only Database

The New Standard in Quality Education

Database Structure ●

9

So what is ODBC?

The New Standard in Quality Education

ODBC Architecture Application (Access / Excel) ODBC Driver Manager (ODBC API connect methods)

10

Driver

Driver

Driver

SQL Server Database

Btrieve Database 6.15

Pervasive SQL Database 7.x

}

The New Standard in Quality Education

= ODBC

Data File Structure ●

Scala Table Naming Definition

MMNNCCYY Module

Financial Year

Unique Number

11

Company Code

The New Standard in Quality Education

Data File Structure ●

12

Format of Data files

The New Standard in Quality Education

Data File Structure ●

13

Format of Fields

The New Standard in Quality Education

Data File Structure ●

● ● ●

14

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

The New Standard in Quality Education

Data File Structure ●

15

Scala5eu.mdb welcome screen

The New Standard in Quality Education

Data File Structure

16

The New Standard in Quality Education

Break ●

17

Break

The New Standard in Quality Education

Section Review ● ● ●

18

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

The New Standard in Quality Education

Simple Report Creation Section 3

The New Standard in Quality Education

Section Overview ● ● ● ● ●

20

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

The New Standard in Quality Education

Report Creation Click

21

The New Standard in Quality Education

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

Click

22

The New Standard in Quality Education

Data Source Step 2 - Select Data Source

Click

23

The New Standard in Quality Education

Data Source Step 3 - Choose Data Source

Note the different database options!!

24

The New Standard in Quality Education

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

25

The New Standard in Quality Education

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

Select Single or Multiple Tables 26

The New Standard in Quality Education

Scala Data Files Confirmation of Selected Files

Click Next

27

The New Standard in Quality Education

Scala Data Fields Step 5 - Select Fields

28

The New Standard in Quality Education

Scala Data Fields ●

Report 1 Stock Listing – – – –

29

Stock Code Description Line 1 Price Local Currency Stock Balance

The New Standard in Quality Education

SC01001 SC01002 SC01004 SC01042

Scala Data Fields

Select Fields Choose Add

30

The New Standard in Quality Education

Field Tab

31

The New Standard in Quality Education

Preview Report When all fields are selected choose FINISH

Click

32

The New Standard in Quality Education

Report Creation

33

The New Standard in Quality Education

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

The New Standard in Quality Education

Break ●

35

Break

The New Standard in Quality Education

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

The New Standard in Quality Education

Exercise Exercise 1 Customer Listing

The New Standard in Quality Education

Crystal Reports Section 3

The New Standard in Quality Education

Section Overview ● ● ● ● ● ●

39

Design & Preview Report Options Guidelines Refreshing Data Right Click Sections

The New Standard in Quality Education

Design & Preview Tabs

40

The New Standard in Quality Education

Design & Preview Tab

41

The New Standard in Quality Education

Preview Tab

42

The New Standard in Quality Education

Report Options ● ●

43

Options Report Options

The New Standard in Quality Education

Options

44

The New Standard in Quality Education

Options

45

The New Standard in Quality Education

Options

46

The New Standard in Quality Education

Guidelines

47

The New Standard in Quality Education

Refreshing Data ● ●

48

F5 Refreshes Data or Preview Sample

The New Standard in Quality Education

Refreshing Data ●

49

Preview Sample

The New Standard in Quality Education

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

The New Standard in Quality Education

Sections Left-hand side of design tab shows report sections

Report Sections

52

The New Standard in Quality Education

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

The New Standard in Quality Education

Sections

54

The New Standard in Quality Education

Section Review ● ● ● ● ● ●

55

Design & Preview Report Options Guidelines Refreshing Data Right Click Sections

The New Standard in Quality Education

Formatting

The New Standard in Quality Education

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 supports drag and drop and copy and paste

The New Standard in Quality Education

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

The New Standard in Quality Education

Adding New Fields Field Explorer

61

The New Standard in Quality Education

Adding New Fields ●

Database fields

Select field

62

The New Standard in Quality Education

Adding Special Fields

63

The New Standard in Quality Education

Adding Special Fields ●

64

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

The New Standard in Quality Education

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

The New Standard in Quality Education

Selection Expert ●

Example of Selection terms

New!

67

The New Standard in Quality Education

Sorting Records ●

68

You can sort by multiple columns.

The New Standard in Quality Education

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

The New Standard in Quality Education

Grouping ● ●

71

Used in Sub-Totaling User can define the order

The New Standard in Quality Education

Grouping

Define Order

Extra Options on Date field

72

The New Standard in Quality Education

Grouping ●

Specified Grouping – User defined order

Select Groups

73

The New Standard in Quality Education

Grouping ●

74

Example of Grouping

The New Standard in Quality Education

Parameters ● ●





75

Used with selection terms Provides an easy way to change selection terms 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

The New Standard in Quality Education

Parameters



76

Then click icon to create new parameter

The New Standard in Quality Education

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

The New Standard in Quality Education

Parameters ●

79

Example of Usage

The New Standard in Quality Education

Parameters ● ●



80

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

Now refresh data The New Standard in Quality Education

Parameter

81

The New Standard in Quality Education

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

The New Standard in Quality Education

Break ●

84

Break

The New Standard in Quality Education

Totalling and Formula’s Section 6

The New Standard in Quality Education

Section Overview ● ● ●



86

Strings Converting Strings Totalling / subtotalling and summaries Converting Dates

The New Standard in Quality Education

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

The New Standard in Quality Education

Converting Strings to Numbers ●



89

Give the formula a name

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

Converting Strings to Numbers ●

This then brings up formula editor box

Operators

Fields

Formula Text

90

Functions

The New Standard in Quality Education

Converting Strings to Numbers ●

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

The New Standard in Quality Education

Converting Strings to Numbers ●





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

The New Standard in Quality Education

Exercise Exercise 3

The New Standard in Quality Education

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.

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

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]))

The New Standard in Quality Education

102

Section Review ● ● ●



Strings Converting Strings Totalling / subtotalling and summaries Converting Dates

The New Standard in Quality Education

Exercise Exercise 4

The New Standard in Quality Education

Formatting Numbers & Dates Section 7

Optional

The New Standard in Quality Education

105

Section Overview ● ● ● ● ● ● ● ● ●

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 ●

The New Standard in Quality Education

107

Formatting Numbers Extra Format Tab ●

The New Standard in Quality Education

108

Formatting Numbers

The New Standard in Quality Education

109

Formatting Dates Extra Format Tab Choose Customize ●

The New Standard in Quality Education

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

The New Standard in Quality Education

112

Conditional Formatting ●

Button Changes to Red indicating value is controlled conditionally

The New Standard in Quality Education

113

Conditional Formatting ●



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

The New Standard in Quality Education

114

Highlight Expert ●



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

The New Standard in Quality Education

115

Highlight Expert

The New Standard in Quality Education

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

The New Standard in Quality Education

117

Report Alerts

The New Standard in Quality Education

118

Report Alerts

The New Standard in Quality Education

119

Report Alerts

The New Standard in Quality Education

120

Report Alerts ●

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

The New Standard in Quality Education

121

Top N Expert ●

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

The New Standard in Quality Education

Top N Selection ●

122

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

Specify value of N

The New Standard in Quality Education

123

Running Totals ●



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

The New Standard in Quality Education

124

Running Total

The New Standard in Quality Education

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

The New Standard in Quality Education

127

SQL Expression Fields

Note limited functionality

The New Standard in Quality Education

128

Field Explorer Browse Data Only New Edit Rename Insert

The New Standard in Quality Education

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

The New Standard in Quality Education

130

Section Review ● ● ● ● ● ● ● ● ●

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

The New Standard in Quality Education

Linking Tables Section 8

The New Standard in Quality Education

133

Section Overview ● ● ● ●

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

The New Standard in Quality Education

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.

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

137

Linking Tables ●

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

OR01 Order No

SC01 Stock Code Customer Code

OR03

SL01

The New Standard in Quality Education

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

Blackmore Electronics

2087

Arnot Limited

Customer Address

Clive's Coffee Shop

2498

Drew PLC

Customer Address

Drew PLC Drew PLC

2190

Blackmore Electronics

Customer Address

Index 1

The New Standard in Quality Education

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

The New Standard in Quality Education

142

How to Link Tables

The New Standard in Quality Education

143

How Link Tables

OK

The New Standard in Quality Education

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

The New Standard in Quality Education

146

Linking Tables

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

The New Standard in Quality Education

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

The New Standard in Quality Education

148

Section Review ● ● ● ●

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

The New Standard in Quality Education

Exercise Exercise 6

The New Standard in Quality Education

150

DAY 2 - Overview ● ● ● ● ● ●

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

The New Standard in Quality Education

Extra Crystal Functionality

The New Standard in Quality Education

152

Section Review ● ● ● ● ● ●

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

The New Standard in Quality Education

153

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

The New Standard in Quality Education

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

The New Standard in Quality Education

155

Extra Functionality

The New Standard in Quality Education

156

Lines and Boxes ●

These extra formatting tools are available from the insert menu

The New Standard in Quality Education

157

Lines and Boxes

The New Standard in Quality Education

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

The New Standard in Quality Education

160

Charting

The New Standard in Quality Education

161

Charting

The New Standard in Quality Education

162

Mapping ●

If you report works with geographical data then there is also an option to map this data

The New Standard in Quality Education

163

Mapping

The New Standard in Quality Education

164

Charting and Mapping ●

Once you have a chart of map inserted into your report you then have the analyzer drop down menu that provides the formatting and functionality options of the map or chart

The New Standard in Quality Education

165

Report Style / Auto Arrange ●



Used to provide quick formatting to a report Make sure you save the report before running this function as you can’t undo

The New Standard in Quality Education

166

Report Style / Auto Arrange

The New Standard in Quality Education

167

Report Style ●



For examples of report styling check out Crystal Web Site or the demo reports that are installed with Crystal There is also a section in your binders that shows a few of the reports that have been designed by Scala

The New Standard in Quality Education

168

Section Overview ● ● ● ● ● ●

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

The New Standard in Quality Education

Exercise

Exercise 7

The New Standard in Quality Education

Running Reports Section 9

The New Standard in Quality Education

171

Section Overview ● ●

● ● ●

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

The New Standard in Quality Education

172

Exporting ●



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

The New Standard in Quality Education

173

Exporting ●

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

The New Standard in Quality Education

174

What is Compilation? ●





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

The New Standard in Quality Education

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 downloaded at http://www.crystaldecisions.com/ The New Standard in Quality Education

Compiling ●

176

Select compile report from report menu

The New Standard in Quality Education

177

Compiling ●

Brings up compile box

File name

No



Click OK to compile The New Standard in Quality Education

178

Compiling ●

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

The New Standard in Quality Education

179

Compiling

Defaults to refresh if no data saved with report

The New Standard in Quality Education

180

Report Distribution Expert ●





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

The New Standard in Quality Education

181

Report Distribution Expert

Can Distribute more that one report at a time

Indicate shared destination directory

The New Standard in Quality Education

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

The New Standard in Quality Education

185

Section Review ● ● ● ● ●

Exporting What is compilation Compiling Report Distribution Expert Web Reporting

The New Standard in Quality Education

Exercise Exercise 8

The New Standard in Quality Education

Database Features Section 11

The New Standard in Quality Education

188

Section Overview ● ● ● ● ●

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

The New Standard in Quality Education

189

Crystal Report Functions ●



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

The New Standard in Quality Education

190

Crystal Report Functions

The New Standard in Quality Education

191

Verifying Database ●

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

The New Standard in Quality Education

192

Crystal Data Dictionaries ●



Modify ODBC connection to make it more user friendly Can be used to restrict access to tables however this should be done through security in SQL server

The New Standard in Quality Education

193

Crystal Data Dictionaries ●

Start | programs | Seagate crystal report tools | Crystal dictionaries

The New Standard in Quality Education

194

Crystal Data Dictionaries

The New Standard in Quality Education

195

Crystal Data Dictionaries

The New Standard in Quality Education

196

Crystal SQL Designer ● ● ● ●



Used for Querying data Primitive Report Accesses data through ODBC Allows the Use of Structured Query Language script for speed Can be used by Crystal report for data source however cannot use any other source in conjecture with it

The New Standard in Quality Education

197

Crystal SQL Designer

The New Standard in Quality Education

198

Crystal SQL Designer

The New Standard in Quality Education

199

Crystal SQL Designer

The New Standard in Quality Education

200

Crystal SQL Designer

The New Standard in Quality Education

201

Database Features

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

204

SQL Views

The New Standard in Quality Education

205

SQL Views

Tables and Linking

Select Columns sorting and criteria SQL Query Result Set

The New Standard in Quality Education

206

SQL Views

The New Standard in Quality Education

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 terms Working with General ledger transaction file GL06 which contains a binary field type for transaction type

The New Standard in Quality Education

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

The New Standard in Quality Education

209

Scala SQL view Manager

The New Standard in Quality Education

210

Scala SQL view Manager

The New Standard in Quality Education

211

Scala SQL view Manager

The New Standard in Quality Education

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

The New Standard in Quality Education

213

Section Review ● ● ● ● ●

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

The New Standard in Quality Education

Exercise

Exercise 9

The New Standard in Quality Education

Report Experts Section 11

Optional

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

221

Form Letter

The New Standard in Quality Education

222

Form Letter

The New Standard in Quality Education

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

The New Standard in Quality Education

224

Cross Tab

The New Standard in Quality Education

225

Cross Tab

Summarized field format

The New Standard in Quality Education

226

Cross Tab

The New Standard in Quality Education

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

The New Standard in Quality Education

229

Sub Report

The New Standard in Quality Education

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 passed from the sub report to the main report by the use of variables

The New Standard in Quality Education

231

Section Review ● ● ● ● ● ● ● ●

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

Exercise Exercise 10

The New Standard in Quality Education

Appendices

The New Standard in Quality Education

Appendix A Section 14 Reporting on Accounting Strings

The New Standard in Quality Education

235

Accounting Strings ●





In Scala 5 accounts 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

Accounting Strings To separate accounting string you will need to use formulas

The New Standard in Quality Education

237

Accounting Strings

The New Standard in Quality Education

238

Accounting Strings ●



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

The New Standard in Quality Education

239

Accounting 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

Accounting Strings

The New Standard in Quality Education

241

Accounting Strings

The New Standard in Quality Education

242

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

The New Standard in Quality Education

Exercise Exercise 11

The New Standard in Quality Education

Appendix B

Converting Reports from Btrieve to SQL

The New Standard in Quality Education

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

The New Standard in Quality Education

246

Crystal Reports ●

Select the correct data source

The New Standard in Quality Education

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)

The New Standard in Quality Education

248

Crystal Report

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

253

Btrieve Data Tables

The New Standard in Quality Education

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

The New Standard in Quality Education

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

The New Standard in Quality Education

258

Set Location

The New Standard in Quality Education

259

Set Location Set Location

Select File Location

The New Standard in Quality Education

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.

The New Standard in Quality Education

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

The New Standard in Quality Education

262

Crystal Reports ●

To install DDF Builder

The New Standard in Quality Education

263

Crystal Reports

The New Standard in Quality Education

264

Crystal Reports

The New Standard in Quality Education

265

Crystal Reports

The New Standard in Quality Education

266

Crystal Reports

The New Standard in Quality Education

267

Crystal Reports

The New Standard in Quality Education

268

Crystal Reports

The New Standard in Quality Education

269

Crystal Reports

The New Standard in Quality Education

270

Crystal Reports

The New Standard in Quality Education

271

Crystal Reports

The New Standard in Quality Education

272

Crystal Reports

The New Standard in Quality Education

273

Crystal Reports

The New Standard in Quality Education

274

Crystal Reports

The New Standard in Quality Education

275

Crystal Reports

The New Standard in Quality Education

276

Crystal Reports

The New Standard in Quality Education

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

Related Documents

Crystal Reports Training1
November 2019 18
Crystal Reports
November 2019 18
Crystal Reports
December 2019 20
Learn Crystal Reports
May 2020 11
Manual De Crystal Reports
December 2019 17