SAP Design Studio live classes by Exports

1
Crystal Reports
2
Crystal Reports Overview

• Pre-requisites
• Introduction

3
Objectives

• To enable you to develop your own reporting
  solutions in Crystal Reports with confidence

4
Course Overview

• 1 Data Structure
• 2 Simple Report Creation
• 3 Crystal Report Functions
• 4 Running Reports

5
Scala Data Structure
Section 1
6
Section Overview

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

7
How Do We Connect to the DB?
8
How Do We Connect to the DB?
Read Only
9
Database Structure

• So what is ODBC?

10
ODBC Architecture

ODBC
11
Data File Structure

• Scala Table Naming Definition

MMNNCCYY
Module
Financial Year
Unique Number
Company Code
12
Data File Structure

• Format of Data files

13
Data File Structure

• Format of Fields

14
Data File Structure

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

15
Data File Structure

• Scala5eu.mdb welcome screen

16
Data File Structure
17
Break

• Break

18
Section Review

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

19
Simple Report Creation
Section 3

20
Section Overview

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

21
Report Creation
Click
22
Report Style

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

Click
23
Data Source

• Step 2 - Select Data Source

Click
24
Data Source

• Step 3 - Choose Data Source

Note the different database options!!
25
Data Source

• Step 3 - Choose Data Source

Need to supply user name and password
26
Scala Data Files

• Step 4 - Select Scala Data Tables

Select File(s)
Click To Finish
Select Single or Multiple Tables
27
Scala Data Files

• Confirmation of Selected Files

Click Next
28
Scala Data Fields

• Step 5 - Select Fields

29
Scala Data Fields

• Report 1 Stock Listing
• Stock Code SC01001
• Description Line 1 SC01002
• Price Local Currency SC01004
• Stock Balance SC01042

30
Scala Data Fields
Select Fields
Choose Add
31
Field Tab
32
Preview Report

• When all fields are selected choose FINISH

Click
33
Report Creation
34
Report Creation

• 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

35
Break

• Break

36
Section Review

• STEP 1 - Report Style
• STEP 2 - SQL Data Source
• STEP 3 - Scala Data Tables
• STEP 4 - Scala Data Fields
• STEP 5 - Preview Report

37
Exercise

• Exercise 1
• Customer Listing

38
Crystal Reports
Section 3

39
Section Overview

• Design Preview
• Report Options
• Guidelines
• Refreshing Data
• Right Click
• Sections

40
Design Preview Tabs
41
Design Preview Tab
42
Preview Tab
43
Report Options

• Options
• Report Options

44
Options
45
Options
46
Options
47
Guidelines
48
Refreshing Data

• F5 Refreshes Data or
• Preview Sample

49
Refreshing Data

• Preview Sample

50
Right Clicking

• Useful in many areas of Crystal
• Short Cut to menu options

51
The Magic Button

• Undo / Redo

52
Sections

• Left-hand side of design tab shows report
  sections

Report Sections
53
Sections

• 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

54
Sections
55
Section Review

• Design Preview
• Report Options
• Guidelines
• Refreshing Data
• Right Click
• Sections

56
Formatting

57
Section Overview

• Adjusting Headings
• Increasing Field Width
• Adding new fields
• Adding Special fields
• Selection Expert
• Sorting
• Grouping
• Parameters

58
Adjusting Headings

• 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

59
Increasing Field Width

• Highlight the field
• Place pointer on blue boxes it changes to arrow
• Field width and height can be adjusted by
  dragging arrow

60
Adding New Fields

• Text Field
• Drag And Click to Drop

61
Adding New Fields

• Field Explorer

62
Adding New Fields

• Database fields

Select field
63
Adding Special Fields
64
Adding Special Fields

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

65
Selection Expert

• Choose Select Expert
• Then choose field to select items from. Report on
  non-report field

66
Selection Expert
Default to AND can change to OR through formula
67
Selection Expert

• Example of Selection terms

New!
68
Sorting Records

• You can sort by multiple columns.

69
Sort Expert

• Brings up sort box
• Automatic sort by column 1

Sort Fields
Report Fields
Sort Direction
70
Grouping

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

71
Grouping

• Used in Sub-Totaling
• User can define the order

72
Grouping
Define Order
Extra Options on Date field
73
Grouping

• Specified Grouping User defined order

Select Groups
74
Grouping

• Example of Grouping

75
Parameters

• 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

76
Parameters

• Then click icon to create new parameter

77
Parameters
Text that appears when report is run
Type
Next Screen
Allows parameter to hold single value or multiple
values and Ranges. Experiment to see affect
78
Parameters
Warning on Usage
79
Parameters

• Example of Usage

80
Parameters

• Enter Parameter as selection terms
• Parameter field denoted as (? Field)
• Now refresh data

81
Parameter
82
Section Review

• Adjusting Headings
• Increasing Field Width
• Adding new fields
• Adding Special fields
• Selection Expert
• Sorting
• Grouping
• Parameters

83
Exercise

• Exercise 2

84
Break

• Break

85
Totalling and Formulas
Section 6

86
Section Overview

• Strings
• Converting Strings
• Totalling / subtotalling and summaries
• Converting Dates

87
Strings

• 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

88
Converting Strings to Numbers

• Create a new formula

Click New
89
Converting Strings to Numbers

• Give the formula a name
• Tip - Give your formula name the column name

90
Converting Strings to Numbers

• This then brings up formula editor box

Operators
Fields
Functions
Formula Text
91
Converting Strings to Numbers

• To convert a string to a number type
• ToNumber(Field name)
• Be careful of Brackets
• (,) function
• , field
• , field value (Seen later)

Function
Field
92
Converting Strings to Numbers
93
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)

94
Exercise

• Exercise 3

95
Totalling and Sub-totalling

• 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.

96
Totalling and Sub totalling

• 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

97
Sub-totalling

• Entering a sub-total

98
Totalling

• Insert grand-totals
• Choice of sum, maximum, minimum, average etc.
  Field printed in report footer

99
Summaries

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

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_name1 to 4),
• ToNumber(field_name5 to 6),
• ToNumber(field_name7 to 8))

102
Section Review

• Strings
• Converting Strings
• Totalling / subtotalling and summaries
• Converting Dates

103
Exercise

• Exercise 4

104
Formatting Numbers Dates
Section 7 Optional

105
Section Overview

• Formatting Strings
• Formatting Numbers
• Formatting Dates
• Conditional Formatting
• Highlight Expert
• Top N Selection
• Report Alerts
• Running Totals
• SQL Expressions

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
111
Conditional Formatting

• Brings up the Formula Editor Box

112
Conditional Formatting

• Button Changes to Red indicating value is
  controlled conditionally

113
Conditional Formatting

• 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.

122
Top N Selection

• 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

126
SQL Expression Fields

• Creating a new SQL expression

127
SQL Expression Fields
Note limited functionality
128
Field Explorer
Browse Data Only
New
Edit
Rename
Delete
Toggle Field Type
Insert
129
Field Explorer

• ? denotes Parameter field
• _at_ denotes formula field
• S denotes SUM or group field
• denotes running total
• denotes a SQL expression

130
Section Review

• Formatting Strings
• Formatting Numbers
• Formatting Dates
• Conditional Formatting
• Highlight Expert
• Top N Selection
• Report Alerts
• Running Totals
• SQL Expressions

131
Exercise

• Exercises 5

132
Linking Tables
Section 8

133
Section Overview

• 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
SC01
Stock Code
Order No
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)
ABC
Smith
A J
23 North Parade, Horsham
01403
215998
The Primary Key
139
What is an indexed field?
140
How to Link Tables

• To an existing report

Select
In this example use report with SL01 and link
SL03
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 FORMULAS)

145
How to Link Tables
Drag and Drop from SL01001 to SL03001
146
Linking Tables
Crystal supports different join 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
Section Review

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

149
Exercise

• Exercise 6

150
DAY 2 - Overview

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

151
Extra Crystal Functionality
152
Section Review

• Pictures and Logos
• 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 supports
• 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

159
Charting
160
Charting
161
Charting
162
Mapping

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

163
Mapping
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

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 cant undo

166
Report Style / Auto Arrange
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

168
Section Overview

• Pictures and Logos
• Hyperlinks
• Lines / Boxes
• Charting
• Mapping Function
• Report Style / Auto Arrange

169
Exercise

• Exercise 7

170
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 users then need no knowledge of Crystal
  design tools - they can just run the report
• Selection terms 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 downloaded at
• http//www.crystaldecisions.com/

176
Compiling

• Select compile report from report menu

177
Compiling

• Brings up compile box
• Click OK to compile

File name
No
178
Compiling

• Users 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 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

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 dlls it needs
  to run the report. It warns about ODBC
  components but doesnt include them
• You can adjust the included dlls but this is not
  recommended
• Then click on build which produces a setup.exe
  file which then needs to be run on clients

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

184
Web Reporting

• Scalas 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

186
Exercise

• Exercise 8

187
Database Features
Section 11
188
Section Overview

• 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
Verifying Database

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

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

193
Crystal Data Dictionaries

• Start programs Seagate crystal report tools
  Crystal dictionaries

194
Crystal Data Dictionaries
195
Crystal Data Dictionaries
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

197
Crystal SQL Designer
198
Crystal SQL Designer
199
Crystal SQL Designer
200
Crystal SQL Designer
201
Database Features
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 terms
• 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
Scala SQL view Manager
211
Scala SQL view Manager
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
Section Review

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

214
Exercise

• Exercise 9

215
Report Experts
Section 11 Optional

216
Section Overview

• Standard
• Form Letter
• Form
• Cross-Tab
• Sub-Report
• Mail Label
• Drill Down
• OLAP

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

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 )

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

231
Section Review

• Standard
• Form Letter
• Form
• Cross-Tab
• Sub-Report
• Mail Label
• Top N
• OLAP

232
Exercise

• Exercise 10

233
Appendices
234
Appendix A

• Section 14
• Reporting on Accounting Strings

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

236
Accounting Strings

• To separate accounting string you will need to
  use formulas

237
Accounting Strings
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.

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

240
Accounting Strings
241
Accounting Strings
242
Accounting Strings

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

243
Exercise

• Exercise 11

244
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

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

252
Btrieve
253
Btrieve Data Tables
254
Data File Structure Btrieve

• Scala5

DAT
GL
PL
GL06ccyy.DAT cc Company Code yy Financial Year
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
257
Crystal Report Errors

• Solution
• Set Location of Scalas data files

Click
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 DDFs 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