Crystal Reports Training1
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
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
Crystal Reports - Scoresg.vpi
December 2019 16