crystal reports for fscm 9.0 created on 2/4/2010 10:10:00 am training material... · training guide...

Crystal Reports for FSCM 9.0 Created on 2/4/2010 10:10:00 AM

COPYRIGHT & TRADEMARKS

Copyright © 1998, 2009, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names

may be trademarks of their respective owners.

This software and related documentation are provided under a license agreement

containing restrictions on use and disclosure and are protected by intellectual property

laws. Except as expressly permitted in your license agreement or allowed by law, you

may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute,

exhibit, perform, publish or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for

interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted

to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone

licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS

Programs, software, databases, and related documentation and technical data delivered to

U.S. Government customers are “commercial computer software” or “commercial

technical data” pursuant to the applicable Federal Acquisition Regulation and agency-

specific supplemental regulations. As such, the use, duplication, disclosure, modification,

and adaptation shall be subject to the restrictions and license terms set forth in the

applicable Government contract, and, to the extent applicable by the terms of the

Government contract, the additional rights set forth in FAR 52.227-19, Commercial

Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway,

Redwood City, CA 94065.

This software is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous

applications, including applications which may create a risk of personal injury. If you use

this software in dangerous applications, then you shall be responsible to take all

appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of

this software. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software in dangerous applications.

This software and documentation may provide access to or information on content,

products and services from third parties. Oracle Corporation and its affiliates are not

responsible for and expressly disclaim all warranties of any kind with respect to third

party content, products and services. Oracle Corporation and its affiliates will not be

responsible for any loss, costs, or damages incurred due to your access to or use of third

party content, products or services.

Training Guide Crystal Reports for FSCM 9.0

Page iii

Table of Contents Crystal Reports for FSCM 9.0 ......................................................................................1

Crystal Report Basics ............................................................................................................ 1 Understanding Crystal Enterprise and Crystal Reports ....................................................................... 1 Understanding Menu Items ............................................................................................................... 6 Understanding Toolbar Buttons ...................................................................................................... 13 Understanding the Supplementary Toolbars .................................................................................... 15 Understanding Crystal Cursors........................................................................................................ 17

Creating Crystal Reports .................................................................................................... 19 Creating Basic Reports ................................................................................................................... 20 Creating Mailing Labels ................................................................................................................. 32 Creating a Basic Crystal Report Template ....................................................................................... 37 Creating a Crystal Report Document From a Template .................................................................... 45

Formatting Crystal Reports ................................................................................................ 56 Inserting Report Titles .................................................................................................................... 57 Inserting Dates ............................................................................................................................... 61 Inserting Page Numbers .................................................................................................................. 65 Inserting Text Objects..................................................................................................................... 68 Formatting Text Boxes ................................................................................................................... 71 Drawing Boxes ............................................................................................................................... 86 Drawing Lines ................................................................................................................................ 88 Suppressing Duplicates ................................................................................................................... 93 Using Colors .................................................................................................................................. 99 Using Graphics ............................................................................................................................. 103

Advanced Crystal Report Options .................................................................................... 106 Sorting Data ................................................................................................................................. 107 Grouping Data .............................................................................................................................. 111 Sorting Data Within Groups .......................................................................................................... 117 Using Summary Operations .......................................................................................................... 120 Using Running Totals ................................................................................................................... 133 Creating Cross-Tabs ..................................................................................................................... 141


Page 1

Crystal Reports for FSCM 9.0 Crystal Reports for PeopleSoft from Crystal Decisions helps you generate clear and easy-to-read

printed reports containing data from your PeopleSoft applications. You can generate standard reports already created and saved in Crystal Reports, or you can create your own custom reports.

Upon completion of this module, you will be able to: • Identify basic Crystal interface features.

• Create a report.

• Create mailing labels. • Create a report template.

• Create a report using a template.

• Format a report.

• Sort data in a report. • Group data in a report.

• Sort data within groups in a report.

• Use summary operations in a report. • Create cross-tabs in a report.

Crystal Report Basics

Crystal Decisions is a third-party reporting tool that can be used with PeopleSoft applications. This lesson will discuss some of the common interface features of Crystal Reports.

Upon completion of this lesson, you will be able to: • Describe how Crystal Enterprise and Crystal Reports are used in PeopleSoft Enterprise.

• Identify menu items.

• Identify toolbar buttons.

• Identify supplementary toolbar options. • Identify Crystal cursors.

Understanding Crystal Enterprise and Crystal Reports Crystal Enterprise and Crystal Reports are third-party report management tools. In this topic,

we will give a high level overview of how these tools are used with PeopleSoft Enterprise.

Procedure

Training Guide

Crystal Reports for FSCM 9.0

Page 2

Step Action

1. Crystal Enterprise is a report management tool that is installed on a server. Crystal

Enterprise enables the management of Crystal formatted reports that are generated

from a PeopleTools 8.48 application.


Page 3

Step Action

2. Crystal Reports is a windows-based report formatting application that has the ability to import queries from the PeopleSoft PeopleTools database. The imported queries

provide Crystal with the fields and data necessary to create professional looking

reports.

Training Guide


Page 4

Step Action

3. The Crystal Reports application can save new and changed reports to Crystal

Enterprise.


Page 5

Step Action

4. Crystal Reports version 9 supports running previously existing Crystal reports via

PeopleSoft Process Scheduler.

Step Action

5. Crystal Reports version 10 may be installed to design new reports and save them in

Crystal Enterprise 10. Process Scheduler is not compatible with Crystal Reports 10.

Training Guide


Page 6

Step Action

6. Users have always been able to export reports from the Windows-based PeopleSoft

Query (this created a default report in the Windows-based Crystal Reports 9 client)

but not from Pure Internet Architecture PeopleSoft Query.

7. With PeopleTools 8.48, you have the ability to create the default report on the web-based Pure Internet Architecture PeopleSoft Query (this creates the default report in

the Crystal Enterprise repository and opens it using the web-based Report Explorer).

8. This concludes the "Understanding Crystal Enterprise and Crystal Reports" topic.

End of Procedure.

Understanding Menu Items Use of menu items is a standard Windows convention. The majority of features available in

Crystal Reports are accessible through menu items. Such features include navigation options, wizards, insertion of graphics and special features, and more.

This topic describes the basic functions of the menu items in Crystal Reports.

Procedure


Page 7

Step Action

1. The File menu lists actions that save or print reports. You can use this menu to change Crystal Reports settings or exit Crystal Reports. Some of these options are

also available through the various toolbars.

Training Guide


Page 8

Step Action

2. The Edit menu lists various data entry functions to use when you create a report.

These options are also available through the toolbar and function keys.


Page 9

Step Action

3. The View menu lists navigation options and layout tools to create a report.

Step Action

4. The Insert menu lists various options of text and other objects you can insert into a report you create.

Training Guide


Page 10

Step Action

5. The Format menu lists various options to format text, add borders, activate wizards,

and more as you create a report.


Page 11

Step Action

6. The Database menu lists various database options you use to refresh the data in

your report, log on to the database server, set locations, remove data from a report,

and more.

Step Action

7. The Report menu lists various report options, edit record and sort options, wizards,

and more.

Training Guide


Page 12

Step Action

8. The Window menu lists various options to change the view of your Crystal Reports

window.


Page 13

Step Action

9. The Help menu lists various help options available from Crystal Reports.

10. This concludes the "Understanding Menu Items" topic.

End of Procedure.

Understanding Toolbar Buttons Toolbar buttons provide you quick access to many functions in Crystal Reports. You use the

main toolbars to perform basic tasks in Crystal Reports. These toolbars are located at the top of

the Crystal Reports window.

This topic describes some of the commonly used toolbar buttons.

Procedure

Step Action

1. Use the New button to open the Report Gallery dialog box, enabling you to create a new report.

2. Use the Open button to open an existing report.

3. Use the Save button to save the report you create.

4. Use the Print button to send your report to a local printer.

Training Guide


Page 14

Step Action

5. Use the Print Preview button to display a preview of the report on the Preview

page.

6. Use the Export button to send the report to another application.

7. Use the Refresh button to update the data in your report from PeopleSoft Query.

8. Use the Cut button to cut the highlighted section from your report and place it on

the clipboard.

9. Use the Copy button to copy a highlighted section from your report and place it on the clipboard.

10. Use the Paste button to paste a section held on the clipboard into your report.

11. Use the Undo button to reverse the last action.

12. Use the Redo button to restore the last undo action.

13. Use the Toggle Group Tree button to toggle on and off the Group Tree view on the

Preview page. This button is available only on the Preview page.

14. Use the Field Explorer button to open the Insert Fields dialog box, enabling you to insert fields from a linked query.

15. Use the Report Explorer button to open the Report Explorer dialog box, which

enables you to reenter report parameters.

16. Use the Repository Explorer button to open the Insert Summary dialog box,

enabling you to assign a summary operation for a selected field.

17. Use the Find button to open the Search Expert dialog box, enabling you to search your report for a specific value.

18. The Zoom Control enables you to zoom in and out on the report, from 25 to 400

percent of its actual size. You can either type a number within the above range or

select a value from the drop-down list.

19. Use the Help button to open a specific Crystal Help section from the current window. With this option selected, a question mark appears on the arrow cursor.

Click any item in the window for information on that item in Crystal Reports Help.

20. Use the Suppress button to suppress the selected field or report object.

21. Use the Lock Format button to lock the formatting of the selected data so that it

cannot be changed accidentally.

22. Use the Lock Size/Position button to lock the size and position of the selected field or report object in relation to another object to its right.

23. Use the Close view button to close the report preview. This button is active only

when a report is displayed on the Preview page.

24. Use the Show First Page navigation button to move to the first page of the report

preview. This button is active only when a report is displayed on the Preview page.

25. Use the Show Previous Page navigation button to move to the previous page of the report preview. This button is active only when a report is displayed on the Preview

page. This button is active only when a report is displayed on the Preview page.


Page 15

Step Action

26. Use the Show Next Page navigation button to move to the next page of the report preview. This button is active only when a report is displayed on the Preview page.

27. Use the Show Last Page navigation button to move to the last page of the report


28. Use the Stop navigation button to stop the system from scrolling through the report


29. This concludes the "Understanding Toolbar Buttons" topic.

End of Procedure.

Understanding the Supplementary Toolbars The Supplementary toolbars give you access to many advanced reporting features available in

Crystal Reports.

This topic describes the functions of the buttons on two supplementary toolbars, Insert Tools and

Expert Tools. The Expert Tools toolbar provides quick access to many advanced tools. The Insert Tools toolbar provides quick access to many report-enhancing features.

Procedure

Training Guide


Page 16

Step Action

1. First, you will learn about the buttons on the Insert Tools toolbar. You can access

this toolbar by selecting View, Toolbars from the menu and then, clicking the

Insert Tools option.

Use the Insert Text Object button to add a text object to your report.

2. Use the Insert Group button to open the Insert Group dialog box, enabling you to

organize similar data together in the report.

3. Use the Insert Summary button to open the Insert Summary dialog box, enabling you to assign a summary operation for a selected field.

4. Use the Insert Cross-Tab button to open the Cross-Tab dialog box, enabling you to

insert a cross-tab object in the report. Cross-tabs display data in a summarized

format by using rows and columns similar to a spreadsheet.

5. Use the Insert OLAP Grid button to insert an OLAP grid object in the report.

6. Use the Insert Subreport button to open the Insert Subreport dialog box, enabling you to create and insert a second report inside the main report.

7. Use the Insert Line button to insert a line in the report. The standard arrow cursor

changes into a pencil cursor when this feature is active.

8. Use the Insert Box button to insert a box in the report. When you click the Insert

Box button, the standard arrow cursor changes to a pencil cursor.

9. Use the Insert Picture button to insert an image into the report. When you click this button, the Open dialog box appears. Select the desired image file and click the

Open button. The standard arrow cursor changes to the Insert cursor. Move the

Insert cursor to the appropriate section in the report and click the left mouse button.

10. Now, you will learn about the Expert Tools toolbar. You can access this toolbar by clicking the Expert Tools option in the Toolbars dialog box.

11. Use the Database Expert button to open the Database Expert dialog box, enabling

you to create, display, update, and delete links between databases.

12. Use the Group Expert button to create, modify, and delete groups.

13. Use the Group Sort Expert button to open the Group Sort Expert dialog box. It

enables you to identify the top or bottom groups in the report and sort on summary information. You can also identify top or bottom percentages.

14. Use the Record Sort Expert button to automatically arrange report elements to fit

best inside the report window.

15. Use the Select Expert button to open the Choose Field dialog box, enabling you to

create a criteria expression from a field to add to your report.

16. Use the Section Expert button to open the Section Expert dialog box, enabling you to format a specific section of the report.

17. Use the Formula Workshop button to open the Formula Workshop dialog box. The

Formula Workshop enables you to create or modify any type of formula used in

Crystal Reports, such as Report Custom functions, Repository Custom functions, Formula fields, and SQL Expression fields.


Page 17

Step Action

18. Use the OLAP Report Settings button to activate the OLAP Report Creation wizard.

19. Use the Template Expert button to open the Template Expert dialog box, enabling

you to select a predefined style for the report. When you select a style, all the

formatted features of that style are applied to the report.

20. Use the Format button to open the Format Editor dialog box, enabling you to

modify object properties. The element you want to modify must be selected in the report for this button to be available.

21. Use the Hyperlink button to open the Hyperlink tab of the Format Editor dialog

box. It enables you to create a hyperlink from the selected object to a Web site, a

file, an email address, or a report object in another report.

22. Use the Highlighting button to activate the Highlighting Expert dialog box. It enables you to apply conditional formatting to all types of report fields, such as

Number, Currency, String, Boolean, Date, Time, and DateTime.

23. This concludes the "Understanding the Supplementary Toolbars" topic.

End of Procedure.

Understanding Crystal Cursors The Crystal Reports cursor changes during certain report operations to ensure that you are

performing the procedure correctly.

This topic describes the various cursors used in Crystal Reports.

Procedure

Training Guide


Page 18

Step Action

1. The cursors in Crystal Reports display available features during report creation.

Cursors in Crystal Reports are also referred to as pointer shapes. The main cursors in

Crystal Reports and the conditions under which they appear are listed in the graphic.


Page 19

Step Action

2. For example, if you position the cursor between report sections, it changes from an arrow to a section cursor to denote that the section can be resized. The sizing cursor

changes back to an arrow when you move off the boundary line between sections.

3. In summary, Crystal Reports uses changing cursors, or pointer shapes, to denote the

availability of certain features, such as sizing and moving, when you are working in a report.

End of Procedure.

Creating Crystal Reports

Generating formatted output in Crystal involves two steps. First,you create and save queries in PeopleSoft Query, and then you create report definitions in Crystal to format the fields (columns)

used in the queries. After you have created and saved the report definitions, you can print reports

that are easy to read and understand. You can generate reports in a variety of different formats, including ASCII files, Microsoft Word documents, and spreadsheets.

Crystal report definitions do not usually contain actual data from your database, so each time you

use the reports, PeopleSoft Query retrieves the most recent data from your database. This ensures that your printed reports always contain up-to-date information. However, if you want to track

changes over time, you can choose to save data with a report.

Upon completion of this lesson, you will be able to:

• Create a report.

• Create mailing labels. • Create a report template.

• Create a report using a template.

Training Guide


Page 20

Creating Basic Reports You can create a report in Crystal Reports if a report template or report does not exist that fits your needs. Creating your own report in Crystal enables you to use a query from PeopleSoft and

customize the field layouts and formatting.

The basis for any report in Crystal Reports is a PeopleSoft query. Instead of manually entering data into a report, the system retrieves it through the PeopleSoft query that you link to the report.

In this topic, the Accounting Department requests a Resource Amount report that includes Project ID, Activity, Analysis Type, Resource Type, Resource Category, Currency Code, and

Resource Amount. You need to layout fields on the report based on the

PCQ1500_ACTIVITY_BASED_COSTING query. You will add a report title and format the fields within the report, as required. Additionally, you will add date and page number fields to the

report.

Procedure

Step Action

1. The Crystal Reports window is displayed for you. Create a new document.

Click the New button.


Page 21

Step Action

2. You can create a Crystal Report using the predefined report templates in the Report Expert. In this example, you will start by creating a blank report.

Click the As a Blank Report option.

3. Click the OK button.

Training Guide


Page 22

Step Action

4. The Database Expert dialog box appears. You use this dialog box to search for and

select the PeopleSoft query that you are going to use for the report.

Click the expand button.

5. You can click the expand icon next to the ODBC folder or double-click the ODBC folder label to open it.



Page 23

Step Action

6. Use the ODBC (RDO) dialog box to connect to the required data source. Locate the PeopleSoft PeopleTools option.

Click the vertical scrollbar.

7. Click an entry in the list.

8. Click the Next > button.

9. You are prompted to log on to PeopleSoft the first time you create a new report or

run a report in Crystal Reports. The User ID and Password fields are case-sensitive in the application.

Enter the desired information into the User ID field. Enter "VP1".

10. Click in the Password field.

11. Enter the desired information into the Password field. Enter "VP1".

12. Click in the Database field.

Training Guide


Page 24

Step Action

13. Specify the database name. For the purpose of this example, use the F1B880US

database.

Enter the desired information into the Database field. Enter "F1B880US".

Step Action

14. The connection to the database has been made. Next, select a query that you will use

to retrieve data for the report.


15. Users have access to different queries depending on their security level. This dialog

box contains a list of all the PeopleSoft queries from which you can create a report

based on your access.


16. You can double-click a query name to select it, or click the query name and then

click the Add button to select it. In this example, you want to use the

PCQ1500_ACTIVITY_BASED_COSTING query.

Click an entry in the list.


Page 25

Step Action

17. Click the Add button.


Step Action

19. The Crystal Reports window is divided into two major components, the Design page and the Preview page.

The Design page is where the majority of report creation takes place. PeopleSoft

recommends that you perform most operations from the Design page because it is easier to work with representations of the data on this page instead of the actual data

displayed on the Preview page.

20. The Design page consists of five major sections: Report Header, Page Header,

Details, Report Footer, and Page Footer. Each section serves a specific purpose in the creation of a finished report.

The page components are labeled to designate various sections of your report. Where you place objects in these sections is determined by where you want them to

appear in the printed report. From this page, you can perform various tasks such as

specifying sort options, creating groups, and formatting fields.

Training Guide


Page 26

Step Action

21. Information placed in the Report Header section of the Design page appears only

once at the top of the first page of the report. This section is typically for the report

title, company logo, and any other information you want only at the beginning of the report.

22. Information placed in the Page Header section of the Design page appears at the

top of each page of the report and follows the Report Header information on the first

page. This section typically contains header information, such as column headings.

23. Information placed in the Details section of the Design page appears in the main body of the report after the Page Header information. The majority of the report data

should be placed here.

24. Information placed in the Report Footer section of the Design page appears at the

bottom of the last page and follows the last detail row. This section typically holds information you want only at the end of the report, such as summary information.

25. Information placed in the Page Footer section of the Design page appears at the

bottom of each page of the report and follows the Report Footer information on the

last page. This section typically contains footer information, such as page numbers.


Page 27

Step Action

26. A connection to the selected query has been made. The next step is to add fields to the Crystal Reports document.

Click the Field Explorer button.

Step Action

27. Open the Database Fields folder.


28. Open the PCQ1500_ACTIVITY_BASED_COSTING folder.


Training Guide


Page 28

Step Action

29. The application displays the fields for this table. You select the fields for the report,

using one of the following methods:

• Double-click the field name and move the Insert cursor to the Details section of

the Design page. Click the mouse to insert the field.

• Click and drag the field name to the Details section. Release the mouse to insert

the field. • Select a field, click the Insert to Report button, and move the Insert cursor to the

Details section of the Design page. Finally, click the mouse to insert the field.

30. For the first field, you will use the Insert to Report button to add the field to the

report.

Click the ACTIVITY_TYPE list item.

31. Click the Insert to Report button.

32. Move the cursor to the highlighted area of the report in the Details section, and click

the mouse to position the field.

33. Use the double-click method to add the second field to the report.

Double-click the ANALYSIS_TYPE list item.

34. Move the cursor to the highlighted area of the report in the Details section and click the mouse to position the field.

35. For the purposes of this example, the remaining fields will be entered for you to avoid repetition.

36. Notice that all the fields have been added.

Click the Close button.

37. The fields are now placed on the report. When you place a field in the Details

section on the report, the system automatically adds a column heading to the Page

Header section. Save the report.

Click the Save button.


Page 29

Step Action

38. Enter the desired information into the File name field. Enter "vendor".

39. Click the Save button.

Training Guide


Page 30

Step Action

40. After you create the template on the Design page of the report and link to the query,

you click the Refresh button to preview the report. Crystal Reports opens

PeopleSoft Query and runs the report containing data retrieved from the query.

Click the Refresh button.


Page 31

Step Action

41. The results are displayed on the Preview page.

The Preview page enables you to view the finished product prior to printing so that

you can see the results of the formatting, sorting, and grouping from the Design

page. The Design and Preview pages are interconnected, so whatever changes you make on one page are reflected on the other.

42. The Preview page of the report window consists of four major sections: Group

Tree View, Report View, Date Age Indicator, and Navigation buttons. Each

section serves a specific purpose in the viewing of a finished report. This page does not appear until Crystal retrieves data from PeopleSoft Query. The report data then

appears on the Preview page.

43. If data is grouped in the report, the group headings appear in the Group Tree View.

The group tree view also serves as a navigation option. To move to a group within the report, click the group heading in this view and the corresponding data appears

on the right (the Report View). You can toggle this option on and off, using the

Group Tree button on the toolbar.

44. In the Report View, the system displays each report page one page at a time. Use the navigation buttons to move through the report.

45. The Data Age Indicator displays the date that the data was last retrieved. If the

report data was retrieved today, the indicator displays Today and the time of

retrieval. This allows the user to determine if a refresh is required to retrieve the

most current data for reporting accuracy.

46. The navigation buttons move you through the report pages on the Preview panel.

Training Guide


Page 32

Step Action

47. Displayed between the buttons is the current page number with respect to the last

page you viewed.

48. Once you create and save a report, you can update the data at any time. To update

the data in a report, click the Refresh button. Once again, Crystal Reports links to PeopleSoft query, this time to rerun the query associated with the report. In this

manner, you can create updated Crystal reports from the initial report.

49. You successfully created a Crystal report.

End of Procedure.

Creating Mailing Labels You can create various types of labels, including mailing labels, from the data in your PeopleSoft

database, using Crystal Reports.

In this topic, the Service Department has requested the Customer Mailing Labels report that can

be used to send correspondence to all the customers. You will use the Mailing Labels Report

Creation Wizard to create mailing labels for the customers. The data source is the LABEL query, which is based on the CUST_ADDRESS record.

Procedure


Page 33

Step Action

1. Begin by creating a new report.


Step Action

2. Click the Mail Label list item.


Training Guide


Page 34

Step Action

4. The Mailing Labels Report Creation Wizard dialog box enables you to define the

label type and select the data that will appear on your report. This dialog box enables

you to select the query to create mailing labels, select fields for the labels, define custom label formats, and define criteria to filter the data so that only a subset of that

data displays on the report. You first select a query to use for the mailing labels.

Click the horizontal scrollbar.

5. Click the vertical scrollbar.

6. You want to use the LABEL query for the report.



8. Click the Next > button.


Page 35

Step Action

9. After selecting the query, you need to select the fields that you want to display on the mailing labels. To select individual fields, you highlight the field name and click

the Add button. To select all the fields in one step, you use the Add All button. In

this example, you will select all the fields.

Click the Add All button.

10. The fields have been selected. Next, you need to define the mailing label type.

Click the Next > button.

11. Crystal Reports has included templates of some of the most popular Avery labels.

The Label Size and Page Margins fields on this page are set by default to the

correct values for the Avery label selected. If you select User-Defined Label in the Mailing Label Type field, you need to enter values that match the label type you

are using.

12. For this example, you are going to use the Address (Avery 5262) template.

Click the Mailing Label Type list.


14. Next, specify how the labels should appear when printed.

Click the Across Then Down option.

15. The label information has been selected. Next, you can optionally create a criteria expression.

Click the Next > button.

16. This step is optional and enables you to select certain rows based on selected

criteria. To do this, you select a field, add it, and specify the criteria that will filter the data so that only a subset of that data displays on the mailing label report. For

this example, you will not select any criteria.

Click the Finish button.

Training Guide


Page 36

Step Action

17. The results appear on the Preview page. Save the report.



Page 37

Step Action

18. Enter the desired information into the File name field. Enter "Mailing Labels".


20. In this topic, you created Avery mailing labels containing address information from

the PeopleSoft database. If you are not using an Avery label, you have the option to define parameters for a custom layout.

End of Procedure.

Creating a Basic Crystal Report Template Creating a report from a template ensures continuity in design across the reports you create. Besides, it saves the time in creating and formatting the reports.

Templates help ensure that every report you produce follows the same reporting standards. These standards can include the placement of the PeopleSoft Training logo, date, page number, and

changes to the font size and style of the report.

Consider this scenario: You want to create a large number of standardized reports in Crystal

Reports. To help save time in the future, you decide to create a report template with fields

common to all of your company's standardized reports, such as Page Number, Report Title, and

Date. You need to save the template as Report_Template.

Procedure

Training Guide


Page 38

Step Action




Page 39

Step Action

2. Click the As a Blank Report option.


Training Guide


Page 40

Step Action

4. This is a report template. Therefore, you do not want to connect to a specific

Peoplesoft Query at this time.

Click the Cancel button.


Page 41

Step Action

5. Click the Field Explorer button.

6. The fields that you want to add to the template are called Special Fields in Crystal.


7. The fields that you want to add to this template are: Page Number, Report Title,

and Data Date. To add a field, you can double-click and place it in the template,

click and drag it to the template, or click the field and click the Insert to Report button. In this example, you will use the Insert to Report button.

Click the Page Number list item.


9. Click the Page Footer section to place the Page Number field in the footer of the report template.

10. Click the Report Title list item.

Training Guide


Page 42

Step Action


12. Click the Report Header section to place the Report Title field in the header of the

report template.

13. Notice that the title is aligned to the left.

Click the Align Center button.

14. The last field you want to add is the Data Date field. This is the date in which the

data was last refreshed on the report.

Click the Data Date list item.



Page 43

Step Action

16. Click the Report Header section to place the Data Date field in the header of the report template.

Step Action

17. Next, add some text fields to the report to better describe what will be displayed.

Click the Insert Text Object button.

18. Click the Report Header section to place the text object next to the Data Date field.

19. Enter the desired information into the field. Enter "Report Date".

20. Click the Insert Text Object button.

21. Click the Page Footer section to place the text object next to the Page Number field.

22. Enter the desired information into the field. Enter "Page Number".

Training Guide


Page 44

Step Action

23. You have inserted the required fields into the report template. Now, close the Field

Explorer dialog box.


24. You next want to view what the report will look like with these fields applied to it.

Click the Print Preview button.

25. Notice the date displayed in the top right corner. Scroll down to see the page

number.


26. Notice the page number in the bottom right corner.

27. Save the template so that it can be used by others to create reports.


Step Action

28. Enter the desired information into the File name field. Enter "Report_Template".


Page 45

Step Action


30. You successfully created a report template.

End of Procedure.

Creating a Crystal Report Document From a Template When you create a group of reports to be displayed together, using a template makes for a more

professional product. Using a template also helps ensure consistent formatting for a report. For

example, your organization might want every report to include a company logo, date, and copyright statement.

In this topic, you want to create a report that lists all the business units. You will use the template Report_Template, which has some standard formatting applied to it, and import the data source

table into this template. You will be using the BUSINESS_UNIT_LIST query as the data source.

Procedure

Training Guide


Page 46

Step Action

1. The first step in creating a document from a template is to open the template.

Click the Open button.

Step Action

2. You can open the file in either of the following ways:

• Select the report name and then click the Open button. • Double-click the report name.

In this example, you will use the first option to open the report template.

Click the Report_Template list item.

3. Click the Open button.


Page 47

Step Action

4. The template is opened. The next step is to import the data source table APY_0010_BUSINESS_UNIT_LIST. Before importing the data, view the Field

Explorer to check the status of the database fields.


5. Note that the Database Fields cannot be opened because there is no query and there

are no fields to insert. You need to import data from the database.

Click the Database menu.

6. Click the Database Expert... menu.

Training Guide


Page 48

Step Action

7. Open the Create New Connection folder.


8. Open the ODBC (RDO) folder.



Page 49

Step Action

9. The ODBC (RDO) dialog box appears. Locate the PeopleSoft PeopleTools option to connect to it.



11. Click the Finish button.

Training Guide


Page 50

Step Action

12. You are prompted to enter the password to connect to the database.

Click in the Password field.

13. Enter the desired information into the Password field. Enter "VP1".



Page 51

Step Action

15. The Database Expert dialog box displays the list of the existing queries. Locate the APY_0010_BUSINESS_UNIT_LIST query.





Training Guide


Page 52

Step Action

19. Click the expand button.

20. Click the expand button.

21. Next, insert the following fields in this report: BUSINESS_UNIT,

VOUCHER_NUM_OPTION, BUSINESS_UNIT_GL, and TOL_AMT_CD.

Double-click the BUSINESS_UNIT list item.

22. Click the Details section of the report to place the field.

23. The BUSINESS_UNIT field has been added. For the purpose of this exercise, the

remaining fields will be added for you to avoid repetition.

24. Notice that the fields have been added.



Page 53

Step Action

25. Next, enter summary information for the report. Summary information can include the date when the report was created and also the report title. Such information is

helpful to anyone who refers to the report.

Click the File menu.

26. Click the Summary Info... menu.

Step Action

27. Enter the desired information into the Comments field. Enter "Created:

02/26/2004".

28. Click in the Title field.

29. Enter the desired information into the Title field. Enter "Business Unit List".


Training Guide


Page 54

Step Action

31. Next, view the results.



Page 55

Step Action

32. Now that the report is complete, you need to save it with a different name so that you do not override the generic template.


33. Click the Save As... menu.

Training Guide


Page 56

Step Action

34. Enter the desired information into the File name field. Enter "Business Unit List".


36. You successfully created a report from an existing report template.

End of Procedure.

Formatting Crystal Reports

Once you create a basic Crystal report, you have a number of options to format it. Formatting is used in reports to add emphasis or provide clarity.

Upon completion of this lesson, you will be able to: • Insert report titles.

• Insert dates.

• Insert page numbers.

• Insert text objects. • Format text boxes.

• Draw boxes.

• Draw lines. • Suppress duplicate rows in a report.

• Use colors.

• Use graphics.


Page 57

Inserting Report Titles Report titles typically identify the content or purpose of a report. Titles may also contain the

department or group being reported, the time frame, such as month, week, or year, being reported,

and the word report at the end of the title.

In this topic, you want to make the vendor report easier to read by adding a report title.

Procedure

Step Action

1. To add a report title, you need to insert a special field. You can open the Field

Explorer dialog box by using the Insert menu or the toolbar button. In this

example, you will use the toolbar button.


2. The Field Explorer dialog box contains a list of the fields available to add to the

report.


Training Guide


Page 58

Step Action

3. There are three ways you can add a field to a report: double-click it and place it in

the report, click and drag it to the report, or click the field and click the Insert to

Report button. In this example, you will double-click the field.

Double-click the Report Title list item.


Page 59

Step Action

4. Click the Report Header section of the report to place the title in the header of the report.

5. Notice that the title is aligned to the left.


6. Click the Bold button.

7. You need to assign a title to the report for it to display on the Preview page. You

use the Summary Info dialog box to assign a title to a report.


8. Click the Summary Info... menu.

Training Guide


Page 60

Step Action

9. You use the Document Properties dialog box to manage your reports. The

Comments section is used to track internal documentation of your report, such as

the date, purpose, and modifications made to the report. For this example, you will enter only the report title.

Click in the Title field.

10. Enter the desired information into the Title field. Enter "Vendor List".



Page 61

Step Action

12. Click the Close button.



14. Notice the title at the top of the page. You can format this title as required. The

report title is updated each time the report is run.

15. You successfully inserted a report title in a Crystal report.

End of Procedure.

Inserting Dates Dates are used with reports to help identify when the report was run, printed, or used. Crystal

Reports allows you to select the date format and place the date in the header or footer of the report or page.

In this topic, you want to make the vendor report easier to reference by adding a date field.

Procedure

Training Guide


Page 62

Step Action

1. To add a date field, you need to insert a special field. You can open the Field

Explorer dialog box by using either the Insert menu or the toolbar button. In this




Page 63

Step Action

2. The Field Explorer dialog box contains a list of the fields available to add to the report.


3. There are several types of date fields that can be inserted, such as Data Date, File

Creation Date, Modification Date, and Print Date.

In this example, you are going to use the Data Date, which is the date in which the

data was last refreshed on the report.

Training Guide


Page 64

Step Action

4. There are three ways you can add a field to a report: double-click it and place it in

the report, click and drag it to the report, or click the field and click the Insert to


Double-click the Data Date list item.


Page 65

Step Action

5. Click the Report Header section of the report to place the date field in the header of the report.




8. Notice the date field in the report. The date in the report is updated each time the

report is run. The date is based on the system date on your computer.

9. You successfully inserted a date field in a Crystal report.

End of Procedure.

Inserting Page Numbers Page numbers are used to organize reports and to identify parts of a report. Crystal Reports

allows you to select the page number format and place the page number in the header or footer of the report or page.

In this topic, you want to make the vendor report easier to reference by adding page numbers.

Procedure

Training Guide


Page 66

Step Action

1. To add page numbers, you need to insert a special field. You can open the Field

Explorer dialog box by using the Insert menu or the toolbar button. In this



2. The Field Explorer dialog box contains a list of the fields available to add to the

report.



Page 67

Step Action

3. There are three ways you can add a field to a report: double-click it and place it in the report, click and drag it to the report, or click the field and click the Insert to


Double-click the Page Number list item.

Training Guide


Page 68

Step Action

4. Click the Page Footer section of the report to place the Page Number field in the

footer of each page.




7. You need to scroll down the page to view the page footer section of the report.


8. Notice the page number at the bottom right part of the report. The page numbers are updated each time the report is run.

9. You successfully inserted a page number in the Crystal report.

End of Procedure.

Inserting Text Objects Inserting text objects enables you to add text fields to a report. A text object can contain words, special fields, or a combination of both. For example, you can create a text object in your report

that contains the text Date: and the Print Date field. If you run the report on January 1, 1999, the


Page 69

text object reads Date: January 1, 1999. The text Date will never change and the date January 1, 1999 may change based on the data at the time the report is printed.

In this topic, you want to make the Vendor List report easier to read by adding text objects that describe the date and page number fields. You will also centrally align the text in these fields.

Procedure

Step Action

1. To insert a text object, you can use the Insert menu or the toolbar button. In this



2. First, you are going to insert a text box to describe the date field on the report.

Click the Report Header section of the report to place the text box in the header of

the report.

3. Enter the desired information into the field. Enter "Report Date".

Training Guide


Page 70

Step Action

4. The text appears left justified by default.


5. Next, you are going to insert a text box to describe the page number field on the report.


6. Click the Page Footer section of the report to place the text box in the footer of each page.

7. Enter the desired information into the field. Enter "Page Number".

8. The text appears left justified by default.




10. Notice the text to the left of the date field.


Page 71

Step Action

11. You need to scroll down to view the section of the report that contains the page numbers.


12. Notice the text to the left of the page number field.

13. You successfully inserted a text object in a Crystal report.

End of Procedure.

Formatting Text Boxes Text boxes are generic fields inserted on a report such as report titles and page numbers. You can

format text boxes in a number of ways to enhance their appearance in a report. The formatting options described in this topic apply to all text boxes in a report, regardless of type.

In this topic, you want to make the Business Unit List report easier to read by formatting the text objects added to the report. To do that, you will make the following changes to the report:

• The Report Title field: Change the font style to Bold, size to 14, and centralize it.

• Page headers: Make them bold and increase their font size by one point. • The Report Date text field: Make it bold and italicized.

• The Data Date field: Ensure that the format is System Default Short format and make it bold.

• The Page Number field: Change the number format to System Default Number format.

Finally, you will preview the report.

Training Guide


Page 72

Procedure

Step Action

1. The Business Unit List report is displayed. First, you are going to format the report

title.

Click the Report Title object.


Page 73

Step Action

2. You want to change the font style and size for the title. Many of the formatting options can be applied using either the Format menu or the toolbar buttons.

Click the Format menu.

3. Click the Format Field... menu.

Training Guide


Page 74

Step Action

4. You use the Common tab to align and rotate the field. You do not need these

options for this example.

Click the Font tab.

5. You use the Font tab to change the font type, style, size, color, and effects. For this example, you want to change the style to Bold.

Click the Style list.


7. Next, you want to change the font size to 14.

Click the Size list.




Page 75

Step Action

10. Notice that the title has been modified. However, it is not completely visible. You can adjust the size of the report header and the size of the text box by clicking and

dragging. For the purpose of this exercise, this will be done for you to save time.

Step Action

11. Notice that the Report Title field has been expanded. Next, you want to make the column headings on the report bold and one size larger. You will use toolbar buttons

to do this.

Click the BUSINESS_UNIT object.

Training Guide


Page 76

Step Action

12. Click the Bold button.

13. Click the Increase Font Size button.

14. For the purpose of this exercise, all the other column headings will be updated for

you to avoid repetition.


Page 77

Step Action

15. You next want to format the Report Date text box by applying bold, italics, and an 11 point size.

Click the Report Date object.

Training Guide


Page 78

Step Action

16. You will use the Format Editor dialog box to accomplish this task.

Click the Format button.


Page 79

Step Action

17. Click the Font tab.

18. Click the Style list.


20. Click the Size list.



Training Guide


Page 80

Step Action

23. Next, you want to format the date field.

Click the Data Date object.


Page 81

Step Action

24. Click the Format button.

Training Guide


Page 82

Step Action

25. You use the Date tab to select the format for the date. You can select one of the displayed options or customize the format. A sample of what the date will look like

is displayed at the bottom of the tab. For this example, you want to keep the System

Default Short Format.

Click the OK button.

Step Action

26. You are not changing the number format of the date field, but you want to add bold

to the field.

Click the Bold button.


Page 83

Step Action

27. Next, you want to format the Page Number field.

Click the Page Number object.

Training Guide


Page 84

Step Action

28. Click the Format button.


Page 85

Step Action

29. You use the Number tab to select a format for a number. You can select one of the

displayed options or customize the format. For this example, you want to use the

System Default Number Format.



Step Action



32. Notice the formatting applied to the Report Title, Report Date, and Data Date

fields.

33. You need to scroll down to view the Page Number field.


34. Notice the formatting applied to the Page Number field.

Training Guide


Page 86

Step Action

35. You successfully formatted text boxes on a Crystal report.

End of Procedure.

Drawing Boxes Boxes are used in reports to add emphasis or provide clarity. This feature helps you create

professional looking reports.

In this topic, you want to make the Business Unit List report easier to read by drawing a box around the report title.

Procedure

Step Action

1. The Business Unit List file is displayed. The drawing tools can be found on the Insert Tools toolbar.

Click the Insert Box button.


Page 87

Step Action

2. You use your mouse by clicking and dragging to draw the box around an object.

Press the left mouse button and drag the mouse to the desired location.

3. Release the mouse button.

Training Guide


Page 88

Step Action



5. Notice the box around the report title.

6. You successfully drew a box on a report.

End of Procedure.

Drawing Lines Lines are used in reports to add emphasis or provide clarity. This feature helps you to create

professional looking reports.

In this topic, you want to make the Business Unit List report easier to read by drawing a line under the column headings in the report.

Procedure


Page 89

Step Action

1. The Business Unit List report is displayed for you. You first need to remove the underline for each heading. Begin by removing the underline for the

BUSINESS_UNIT field.

Click the BUSINESS_UNIT object.

Training Guide


Page 90

Step Action

2. Click the Underline button.

3. The underline has been removed. For the purposes of this example, the balance of

the fields will be done for you to avoid repetition.

4. The underline below the rest of the fields has been removed for you. Now, add the line below these fields.

Click the Insert Line button.


Page 91

Step Action

5. You use your mouse by clicking and dragging to draw the box around an object.

Press the left mouse button and drag the mouse to the desired location.

Training Guide


Page 92

Step Action

6. Release the mouse button.


Page 93

Step Action



8. Notice the line below the column headings.

9. You successfully drew a line on a report.

End of Procedure.

Suppressing Duplicates In Crystal Reports, you can suppress report sections (headers, details, and footers), rows and

columns that either do not contain a value or contain specific values. You use the Suppress If

Duplicated option to suppress duplicate values in a report. This option hides the duplicate values,

if they exist, and prints the first value only.

In this topic, you have to print the vendor report. The vendor report displays the resources and

amount spent for each activity and analysis type. You want to hide the analysis type, if it appears

multiple number of times against an activity. To do this, you will use the Suppress If Duplicated

option.

Procedure

Step Action

1. The vendor report is displayed. Notice that the analysis type ACT appears multiple number of times.

Training Guide


Page 94

Step Action

2. To suppress the duplicate values, navigate to the Design page.

Click the Design tab.


Page 95

Step Action

3. Click the ANALYSIS_TYPE object.

Training Guide


Page 96

Step Action

4. You use the Format Editor dialog box to suppress the duplicate occurrences of a field.



Step Action

6. The Format Editor dialog box enables you to change the style, type, size, color,

and appearance of text and report fields. In this example, you do not need to use the options on the Paragraph tab.

Click the Common tab.

7. Use the Suppress If Duplicated check box if you do not want the report to display field values that are duplicated. The field values will not appear on the Preview

page, nor will they print.

Click the Suppress If Duplicated option.


Page 97

Step Action


Step Action

9. Next, view the report.


Training Guide


Page 98

Step Action

10. You are prompted to confirm whether you want to refresh the report data.


11. Notice that only the first value ACT appears in the ANALYSIS_TYPE column. The

duplicate values have been suppressed.


Page 99

Step Action

12. Click the vertical scrollbar.

13. Notice that the duplicate values have been suppressed for the other analysis types also.

14. You successfully suppressed the duplicate values in a report. You use the Suppress

If Duplicated option in the Format Editor dialog box.

End of Procedure.

Using Colors Crystal Reports enables you to modify your report with colors. Colors add dimension to your

report that enables your readers to better comprehend the results.

In this topic, you will modify the Address Report. Change the Postal Code output color to red.

Procedure

Training Guide


Page 100

Step Action

1. Begin by selecting the field to be modified.

Click the Postal Code object.

2. Notice that all Postal Code fields on the report are now shaded. By selecting a data field, any formatting changes can be applied to every instance of the data field on

the report.



4. Use the Format Editor dialog box to edit field properties in a Crystal Report.

5. The Paragraph tab enables you to modify the paragraph properties of the field.


Page 101

Step Action

6. Click the Font tab.

7. Click the Color list.

Training Guide


Page 102

Step Action

8. Click the red object.


Page 103

Step Action


Step Action

10. Notice all instances of the Postal Code are now in the selected color. When you print

this report to a color printer, the Postal Code will now display in red.


11. You successfully updated colors in a report.

End of Procedure.

Using Graphics Graphics add a whole new dimension to your report presentation. Graphics help you better

analyze almost every type of report output. Crystal Reports has several standard graphics templates that you can incorporate into your reports.

Consider this scenario: your company is evaluating locations for a new office building and would like to determine how many employees are in each state. Show the state to employee information

by adding a new bar graph to your existing address report.

Training Guide


Page 104

Procedure

Step Action

1. Begin by adding a new bar graph.

Click the Insert menu.

2. Click the Chart... menu.

3. The Chart Expert dialog box enables you to create charts inside your Crystal

report.

4. Use the Type tab to select the chart type. Crystal Reports provides a number of useful chart designs.

In this example, the Bar chart type is already selected.


Page 105

Step Action

5. Click the Data tab.

6. Use the Data tab to select data elements from your report, which are used to define the chart.

7. Click the PS_ADDRESSES.EMPLID list item.


9. Click the PS_ADDRESSES.STATE list item.



Training Guide


Page 106

Step Action

12. This chart represents the number of employees per state.

13. You successfully inserted a chart into a Crystal report.

End of Procedure.

Advanced Crystal Report Options

This lesson describes the procedures you follow for organizing data in Crystal Reports. The

process of organizing data in Crystal Reports is different from that in PeopleSoft Query. In

Crystal Reports, you sort data to place values in a logical order and group data to organize data

into groups. This method of organizing data eases the process of finding information in a report.

This lesson also covers the common summary operations, use of running totals, and the creation

of cross-tabs for a report.

Upon completion of this lesson, you will be able to:

• Sort data in a report. • Group data in a report.

• Sort data within groups in a report.

• Use summary operations in a report.

• Summarize data by use the Running Totals feature in a report. • Create cross-tabs in a report.


Page 107

Sorting Data Sorting data in a report arranges data logically, which improves readability and data analysis.

Crystal Reports provides the options to change the original sort order output to either an

ascending or descending order.

In this topic, your manager has asked you to provide the Asset-cost report. The report should

display the cost of assets in the descending order. You will first open the Asset_cost report, and then use the Record Sort Expert to sort the assets in the descending order of their cost.

Procedure

Step Action

1. The Crystal Reports window is displayed. Begin by opening the Asset_cost report.


Training Guide


Page 108

Step Action



4. The Asset_cost report is displayed. Notice that the assets are listed according to their

Business Units. You need to arrange them in the descending order of cost.


Page 109

Step Action

5. You use the Record Sort Order dialog box to specify the sort details. You can access this dialog box by using one of the following options:

• Select the Report menu, and then click the Sort Records option.

• Click the Sort Order button on the toolbar.

In this example, you will use the Report menu.

Click the Report menu.

6. Click the Record Sort Expert... menu.

Training Guide


Page 110

Step Action

7. The Record Sort Order dialog box appears. You use this dialog box to sort data

within a group. To sort the fields, you select the field in the Report Fields group

box. The report fields are not completely visible.


8. In this example, you want to sort the COST field in the descending order.


9. You use the Add and Remove buttons to insert and delete the fields for the sort.

Click the Add button.

10. You specify the direction for the sort by using the Sort Direction options,

Ascending or Descending. The Ascending order option sorts data alphabetically from A to Z or numerically from lowest to highest. The Descending order option

sorts data opposite of ascending sorts, so that alphabetical data is organized from Z

to A, and numerical data is organized from highest to lowest.


Page 111

Step Action

11. For this example, you want to arrange the assets in the descending order of their costs.

Click the Descending option.


Step Action

13. You can perform multiple sorts in Crystal Reports. However, the first sort you

assign takes precedence over any subsequent sorts. Also, the sorts you create in Crystal Reports take precedence over the sorts assigned to the query.

14. You successfully sorted data in a report.

End of Procedure.

Grouping Data Grouping data in a report means organizing like data together. This makes the process of locating data efficient.

In this topic, your manager has asked you to provide details of the amount spent in vendor travel. To do this, you need to modify the vendor report by grouping the data by resource type, and then

view the resource amount against the TRAVL resource type.

Training Guide


Page 112

Procedure

Step Action

1. Begin by opening the vendor report.



Page 113

Step Action



Training Guide


Page 114

Step Action

4. Before grouping the data, view how the report looks.


5. Currently, the report data is arranged by the analysis type. Within each analysis type, there are multiple resource types. You need to group the data by resource types to

view the amount spent on each resource type.

6. You use the Insert Group dialog box to group data in a report. You can use the

Insert menu or the Insert Group button on the toolbar to display this dialog box. For this example, you will use the Insert menu.


7. Click the Group... menu.

8. You use the Insert Group dialog box to group data together. The first drop-down

box enables you to select the report field to be grouped. Valid values are based on

the table accessed in the query used for the report.


Page 115

Step Action

9. For this example, you want to group the list by RESOURCE_TYPE.

Click the records will be sorted and grouped by list.


11. Use the second drop-down box to select a direction to order the groups. The

direction can be ascending, descending, original, or specified.

Click the records will be sorted and grouped by list.

12. You can select one of the following orders for sorting:

• Ascending order groups data either from A to Z or lowest to highest.

• Descending order groups data either from Z to A or highest to lowest. • Specified order enables you to define an order for the fields. A second tab appears

when you select specified.

• Original order maintains the order of the fields from PeopleSoft Query.

For this example, retain the default order.


Training Guide


Page 116

Step Action

13. Click the Options tab.

14. You select the Customize Group Name Field option to modify the name assigned

to the grouping of fields on the report. If you use the default, the name assigned will

be the field name from PeopleSoft Query. In this example, you will use the default name.

15. Select the Keep Group Together check box to keep groups together on the same

page of the printed report. This feature prevents grouped data from splitting across

page breaks. In this example, you will not use this feature.

16. Select the Repeat Group Header On Each Page check box to have Crystal Reports print a new group header at the top of each page of the report. This feature is useful

when grouped data appears on multiple pages. If this feature is turned off, a group

header appears only at the top of the first page.

In this example, you will not use this feature.



Page 117

Step Action

18. Groups have been added to the report. Notice that there is a list of resource types in the Group Tree View section of the page. You can click a resource type to view the

required details.

In this exercise, you need to view resource amount details for the TRAVL resource type.

Double-click the TRAVL list item.

Step Action

19. Review the resource amount details for the TRAVL resource type.

20. You successfully grouped data in Crystal Reports. Once you assign the sort and group options in a report, you can sort data within the groups.

End of Procedure.

Sorting Data Within Groups After you assign the initial sort and group options to a report, you have the option to sort data within the groups.

In this topic, you have already grouped the data in the vendor report by resource type. Now, within those groups, you would like to display the analysis type alphabetically and sort the report

data according to analysis type.

Training Guide


Page 118

Procedure

Step Action

1. The vendor report, with the data grouped by the resource type, is displayed. To sort

the data within the groups, open the Record Sort Order dialog box.

Click the Record Sort Expert button.


Page 119

Step Action

2. Use the Record Sort Order dialog box to specify sort details. The field names are not completely visible.


3. In this example, you want to sort the data within each group by the analysis type.

Select the ANALYSIS_TYPE field.



5. The ANALYSIS_TYPE field appears in the Sort Fields box.


6. Notice that the data is now sorted within the resource type groups by analysis type. You can use the navigation buttons to quickly view different pages in the report.

7. You successfully sorted data within the groups in a Crystal report.

End of Procedure.

Training Guide


Page 120

Using Summary Operations Summary operations enable you to view rows of data from a group or the entire report as a single value. This function is useful when you want to comprehend the data in a report or group.

You can use the Summary operations to perform such tasks as counting the values in a group,

calculating the sum or average of values, and finding the minimum or maximum value in a group.

In this topic, you want to use the AMOUNTDUE query to create a report that displays the

balance amount for the customers of each business unit. The report should also display the total

balance amount for each business unit. You decide to use the Summary feature of Crystal Reports to display this detail in the report.

Procedure

Step Action




Page 121

Step Action

2. Click the As a Blank Report option.


Training Guide


Page 122

Step Action

4. You want to use the AMOUNTDUE query. Locate the query in the QUERY list.






Page 123

Step Action

8. Next, you need to insert fields in the report.


Training Guide


Page 124

Step Action

9. Open the Database Fields folder.


10. Open the AMOUNTDUE folder.


11. Add the BUSINESS_UNIT field to the report.

Double-click the BUSINESS_UNIT list item.

12. Click the Details section of the report to place the field.

13. The BUSINESS_UNIT field has been added to the report. For the purpose of this

example, the balance of the fields will be added for you to avoid repetition.

14. Notice that the fields have been added.



Page 125

Step Action

15. Next, preview the report.


16. You now need to insert the summary operation. To do that, navigate back to the

Design page.

Click the Design tab.

Step Action

17. You want to calculate summary for the balance amount.

Click the BAL_AMT object.

Training Guide


Page 126

Step Action

18. You can use the Insert menu or the Insert Summary toolbar button to open the

Insert Summary dialog box. In this example, you will use the menu.


19. Click the Summary... menu.

20. You use the Insert Summary dialog box to count the values in a group, calculate

the sum or average of values, and find the minimum or maximum value in a group.

21. The Choose the field to summarize drop-down list displays the fields from the report. Valid values are based on the table accessed in the query linked to the report.

You can specify the field that you want to summarize.

22. The Calculate this summary drop-down list contains the common summary

operations in Crystal Reports. For this example, you want the sum of the balance amount for each business unit. Therefore, you will retain the default summary

operation.


Page 127

Step Action

23. The Summary Location drop-down list specifies the location in the report where the summary will be displayed. For this example, you can accept the default location

of Report Footer. Now, specify the group.

Click the Insert Group... button.

24. You use the records will be sorted and grouped by drop-down list to designate on which field to sort and group the data. In this example, you want to group by the

BUSINESS_UNIT field, so you will use the default.

25. You use the second records will be sorted and grouped by drop-down list to select

a sort order. For this example, retain the default ascending order.

Training Guide


Page 128

Step Action



Page 129

Step Action


Step Action

28. The summary field has been added. You can move the position of this field by

clicking and dragging it to the desired location. For the purpose of this example, this summary field has been placed correctly for you to save time.

Add a text description field in front of this field.


29. Click next to the summary field to place the text box.

Training Guide


Page 130

Step Action

30. Enter the desired information into the field. Enter "Total Balance Amt".


Page 131

Step Action



Step Action


33. Notice that summary lines have been added to the report.

Training Guide


Page 132

Step Action

34. Now, save the report.



Page 133

Step Action

35. Enter the desired information into the File name field. Enter "Amount Due".


37. You successfully applied a summary operation.

End of Procedure.

Using Running Totals Running Totals are totals that are displayed on a record by record basis. They are a flexible and

powerful way to create specialized summaries and continually incrementing totals.

Running Totals total all records in a report or a group up to and including the current record. For

example, if your first three records have values of 2, 4, and 7, the running total for each of the

three records would be as follows: 0 + 2 = 2

2 + 4 = 6

6 + 7 = 13

This feature totals each page and carries that total to the next page total until the Grand Total is

reached on the last page.

In this topic, you want to modify the vendor report to display the Total Cost column for each

resource. The total cost should be calculated each time the activity changes. You will use the

Running Total feature of Crystal Reports to calculate total amounts.

Training Guide


Page 134

Procedure

Step Action




Page 135

Step Action



Training Guide


Page 136

Step Action

4. The vendor report is displayed. You can access the Running Totals feature from the

Field Explorer dialog box.



Page 137

Step Action

5. The Running Total Fields list displays the names of all the running total fields in your report. Currently, there are no running totals in the report. You can create a

running total by selecting the Running Total Field option, and then clicking the

New button.

Click the Running Total Fields object.

6. Click the New button.

7. Use the Create Running Total Field dialog box to specify the details for your

running total. To add a new running total field, add a name, field to summarize, and

the Evaluate and Reset criteria.

Training Guide


Page 138

Step Action

8. First, specify a name for the running total.

Enter the desired information into the Running Total Name field. Enter "Total

Cost".

9. Next, define the field to be summarized. The report fields are not completely visible.


10. You want to calculate the totals for the RESOURCE_AMOUNT field.



12. You use the Type of summary field to define the operation you want to perform. In

this example, you want to calculate sum totals of the RESOURCE_AMOUNT field. Therefore, retain the default selection.

13. You use the Evaluate section to define when the running total will execute. For

example, you could use the On change of field option if you wanted the running

total to be executed each time the ANALYSIS_TYPE field changed.

In this example, you want to calculate a new running total for the resource amount

with each new row, so you will use the default option.


Page 139

Step Action

14. The default selection Never in the Reset section indicates that you are running total that never resets, and continues throughout the report. Retain the default option for

this exercise.


15. The Total Cost field appears highlighted in the Running Totals Fields list. You

need to add this field to the report. You insert running total fields by dragging them into the report, or by selecting them and then clicking Insert to Report button.

Step Action


17. Click the Details section of the report to place the running totals field to the right of

data fields.

Training Guide


Page 140

Step Action

18. Notice that the Total Cost field has been added to the report. Each running total

field added to the report has a check mark added in front of its name in the Field

Explorer.

Running total fields are prefixed by the # sign on the report.




Step Action

20. Notice that on the report, each row in the running total column displays the current

record value added to the previous values. This total continues, unbroken, through the report.

21. You successfully calculated running totals for a field in a report.

End of Procedure.


Page 141

Creating Cross-Tabs Cross-tabs display data in a summarized format by using rows and columns similar to a

spreadsheet. Using cross-tabs makes it easy to view a large amount of data in a compact space

inside your report, and therefore makes it easier to analyze totals. A cross-tab object is a grid that returns values based on the row and column criteria that you specify.

Crystal Reports offers two ways to use cross-tabs: 1) Create a report and use a cross-tab object at the end of the report to summarize the information.

2) Create a cross-tab report where all pertinent information in the report is inside the cross-tab

object.

In a cross-tab object, the number displayed at the intersection of a row and a column represents a

summary of the values that meet the row and the column criteria.

In this topic, you are required to provide resource amount details based on the vendor report. You

decide to create a cross-tab object in the vendor report to summarize the data in a cross-tab

format. You will need to open the vendor report, and create a format that displays how much

amount has been spent for resources in each activity.

Procedure

Training Guide


Page 142

Step Action



Step Action




Page 143

Step Action

4. Open the Format Cross-Tab dialog box. You can open the dialog box by selecting Insert, Cross-Tab from the menu bar, or by clicking the Insert Cross-Tab button

on the supplementary toolbar. For this example, use the menu option.


5. Click the Cross-Tab... menu.

6. You use the Cross-Tab Expert dialog box to specify which fields represent the row,

column, and summarized fields. You can add these fields in two ways: • Highlight the field and click the appropriate button.

• Highlight the field and drag it to the appropriate box.

Training Guide


Page 144

Step Action

7. The Available Fields list box displays the report fields and database fields that you

can add to the cross-tab object. Add the required fields to the appropriate columns.

The report fields are not completely visible.


8. For this example, you want ACTIVITY_TYPE in the columns, RESOURCE_CATEGORY in the rows, and RESOURCE_AMOUNT as the

summarized field. Begin by adding ACTIVITY_TYPE to the Columns section.

Click the Add button.

9. Next, add the RESOURCE_CATEGORY field to the Rows section.



11. Finally, add the RESOURCE_AMOUNT field to the Summarized Fields section.



Page 145

Step Action


13. For summarized fields, the Change Summary option is available to change the summary settings. if required.

Click the Change Summary... button.

Step Action

14. You can use the Edit Summary dialog box to change the summary type of the cross-tab's summarized fields. For this example, you want to retain the current

settings.


Training Guide


Page 146

Step Action

15. Click the Style tab.

16. You use the Style tab to add predefined styles to the grid.


17. Click the Customize Style tab.

18. You use the Customize Style tab to create and add customized styles to the grid. In

this example, you want to display cell margins to make the table look neat.

Click the Show Cell Margins option.



Page 147

Step Action

20. Click the Report Footer section of the report to place the cross-tab object at the end of the report.

21. The cross-tab object appears in the Report Footer section of the Design page. The

cross-tab object contains a grid, based on the parameters you specified. Crystal

Reports automatically adds an additional row and column for totals. You can also modify the cross-tab object by resizing and formatting the fields in either Design or

Preview mode.



23. The cross tab was placed at the end of the report, so you need to navigate to the last

page to see it.

Click the Show Last Page button.

24. The report now includes a cross-tab object. In this object, the row variables

represent resource categories and column variables represent activity types. You can view the totals at the bottom of the report.


Training Guide


Page 148

Step Action

25. Now, save the report.


26. You successfully inserted a cross-tab object in a Crystal report.

End of Procedure.

crystal reports for fscm 9.0 created on 2/4/2010 10:10:00 am training material... · training guide...

Documents