Print Crystal Classroom Material

Crystal Reports I0 – Table of Contents Exercise 1: Starting the Crystal Reports Program, Exploring the Environment and Database Expert ........................................................................................................................................ 2 Exercise 2: Accessing Data Sources, Record Selection Criteria and Formatting Features. .. 11 Exercise 3: Date/Time Selection Expert and Setting Print Date and Time............................. 27 Exercise 4: Organizing: Grouping, Sorting, and Summarizing .............................................. 33 Exercise 5: Formatting, Sorting, Creating Hyperlinks, Text Objects and Adding Supplementary Objects: Lines, Boxes and Images................................................................. 45 Exercise 6: Creating a Mailing Label Report from the Report wizard..................................... 52 Exercise 7: Smart Linking (Creating Links Between Tables).................................................. 62 Exercise 8: Creating Formula Fields ...................................................................................... 64 Exercise 9: Highlighting, Conditional Formatting, and Alert Monitoring................................... 96 Exercise 10: Formatting Sections within Sections Expert .................................................... 122 Exercise 11: Using Group Layout Charts and Creating Top N Reports ............................... 134 Exercise 12: Review (Optional) ............................................................................................ 152 Exercise 13: Creating an Employee Salary Report .............................................................. 154 Exercise 14: Specified Order Grouping ................................................................................ 165 Exercise 15: Cross-Tab Wizard Data Summary.................................................................. 175 Exercise 16: Grouping Data Hierarchically........................................................................... 186 Exercise 17: Underlay an Image as Watermark Background............................................... 192 Exercise 18: Form Letters .................................................................................................... 199 Exercise 19: Variables.......................................................................................................... 211 Exercise 20: Parameters ...................................................................................................... 216 Exercise 21: Exporting a Crystal Report............................................................................... 226 Exercise 22: Crystal Enterprise ............................................................................................ 229

This documentation contains confidential and proprietary business information of Saudi Aramco. This document, including copies of all or any part thereof, may not be released or disclosed to persons other than Saudi Aramco employees except as necessary for business purposes of Saudi Aramco under an appropriate confidentiality agreement. Saudi Aramco

Saudi Aramco Training

5/15/2007

updated on

03/31/2007

1

Crystal Reports I0 - Step by Step Exercises Exercise 1: Starting the Crystal Reports Program, Exploring the Environment and Database Expert

The objective of this exercise is to familiarize end users with the start up process of Crystal Reports from the Start button on the user system. The following items will be highlighted: • The Main Components of the Design Window. • Exploring the Toolbars . •

Connect ODBC Extreme Sample data base.

•

Placing Fields on the Report

•

Managing Resources with Explorers

•

Changing System default Setting: File → Options → Layout

Step by Step Activities Starting Crystal Reports

1. To start Crystal Reports, follow the menu path: Start → Programs → Crystal Reports Tools → Crystal Reports 10

This documentation contains confidential and proprietary business information of Saudi Aramco. This document, including copies of all or any part thereof, may not be released or disclosed to persons other than Saudi Aramco employees except as necessary for business purposes of Saudi Aramco under an appropriate confidentiality agreement. Saudi Aramco

Saudi Aramco Training

5/15/2007

updated on

03/31/2007

2

The Welcome to Crystal Reports window is displayed:

2. Click in the As a Blank Report radio button. 3. Click on the OK button. The following “Database Expert” dialog box is displayed:

Note:

Users may select the Data Source Type from one of the followings:

Saudi Aramco Training

5/15/2007

Crystal Reports10

3

•

To choose the Data Source Type, click on ODBC data source to expand it.

•

To choose the Data Source Type from SAP BW Query, SAP R/3 Info Sets or SAP Table, Cluster or Function..

4. In the Data Expert window, click on the + sign to the left of Create New Connections as shown below:

5. Click on the + sign to the left of ODBC to expand it. The following dialog box is displayed:

5. Select Xtreme Sample Database 10 to expand it . 6. Click on Finish 4 Saudi Aramco Training

button. 5/15/2007

Crystal Reports10

The following screen is displayed:

7. Click on the + sign to the left of Tables . 8. Double-click on the Customer table Note: Users may select the table then press on > to take it to the Selected Tables Pane. The following screen is displayed:

Note:

Saudi Aramco Training

Users may also drag and drop the fields to the selected fields area. 5/15/2007

Crystal Reports10

5

9. Click on OK button. The following Design screen is displayed:

Note:

6 Saudi Aramco Training

The display of your design screen with/without grids depends on the settings of your Layout. This can be changed from File → Options → Layout as shown below:

5/15/2007

Crystal Reports10

Note:

It is up to the user’s need if he/she needs the grid on or off.

Note:

The Design tab is automatically revealed with five default areas.

Note:

The Field Explorer window displays with the Design grid in the background. The field Explorer contains the fields that belong to the selected table as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

7

Note:

You can drag and drop the Supplementary Toolbar at the bottom left of the Design Grid.

The Design Window Includes : Report Header

Items Placed on the Report Header print only on the first page of the report. Often the name of the report, the print date, company logo, or “Title page” information is included in the Report Header.

Page Header

Objects in the Page Header print at the top of every page of the report. Column Headings, print dates and page numbers are common entries in the Page Header.

Details

Place the data fields on which you want to report in this area. This information (database fields and text) prints for every record selected on the report.

Report Footer

Items placed here print only on the last page of the report. Grand Totals, total number of pages, and other Summary information are often placed here.

Page Footer

Objects placed here print at the bottom of every page. Page numbers are often placed in the Page footer.

button on the Application toolbar above the screen 10. Click on the Field Explorer if it is not displayed by default:

8 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

Notice the Field Explorer with the Report Fields and Report Sections tabs added to the left of the designer screen. Notice also that some of the icons on the application toolbar above are inactive. They will get activated once you drag and drop the table fields to the Details section in the design screen.

11. Expand the + to the left of Database Fields then expand the Customer table to display all the fields as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

9

12. Drag and drop the first field Customer ID to the Details section to activate the inactive buttons. To help you discover the application, drag and drop customer ID in the Details section of the design screen. Notice that the database of customer ID is in the details while the column header is automatically created in the Page header.

The following screen is displayed with the active buttons:

Supplementary Toolbar

Note:

Insert Text

Insert group

Database Expert

Insert Summary

Group Expert

Group sort expert

To continue next exercise, please delete the customer ID in Details Section by selecting it and clicking on Delete on the keyboard.

Insert CrossTab

Record sort expert

10 Saudi Aramco Training

OLAP grid

Select expert

SubReport

Section expert

Formula workshop

5/15/2007

Insert Line

OLAP design wizard

Insert Box

Template Expert

Picture

Format

Chart

Insert Hyperlink

Map

Highlighting

Crystal Reports10

Exercise 2: Accessing Data Sources, Record Selection Criteria and Formatting Features. Table: Customer Fields: Customer Name, Region, City, Last Year’s Sales The objective of this exercise is to familiarize users with the followings: • Field Explorer, Database Fields, and Special Fields • Menus and Toolbars • Design Window/ Preview window • Moving, Aligning, and Resizing Objects • Formatting Fields • Inserting a Report Title. • Placing Fields on the Report • Selecting Data Fields for the Design View.

Step by step Activities 1. 2.

To go to your database from the design screen click on Database Expert button. To enhance and format a Crystal Report, expand from Create New Connections expand ODBC(RDO) if it is not already expanded then expand Extreme Sample Database → Tables and double click on the Customer table as shown in the following screen:

Saudi Aramco Training

5/15/2007

Crystal Reports10

11

2. Click on OK button. The following screen is displayed:

Note:

3.

To show or hide the grid and other display options in the design window, go to: File → Options → Layout where the user can activate or deactivate the options he may like.

From the Field Explorer dialog box, expand the Database Fields by clicking on the + sign to the left of the item.

The following “Field Explorer” dialog box is displayed:

Note:

If the Field Explorer dialog box is not shown, users may follow the menu path to display it: View → Field Explorer or click on Field Explorer button.

12 Saudi Aramco Training

5/15/2007

Crystal Reports10

4.

Click on the + sign to the left of Customer table to expand it.

The following dialog box is displayed:

Note:

To select non- adjacent multiple fields, click on Ctrl, then click on the fields to select them, then drag and drop in the Details section as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

13

Note:

5.

The fields can also be dragged to the design window. For the purpose of this report we are going to drag and drop: Customer name, Region, City, and Last Year’s Sales in the details section..

Click on the Customer Name then drag and drop it to the details section in the design window to the left side. Note:

Once the field is clicked on and dragged, the shape of the mouse

changes to an arrow and a rectangle shown.

6.

running with It as

Repeat this step with Region, City, and Last Year’s Sales. Note:

Ensure that the fields are not on top of each other.

14 Saudi Aramco Training

5/15/2007

Crystal Reports10

7. To resize the fields in the Page Header section to accommodate the size of data headers, click on the Customer Name in the Page Header section then click on the Shift button on the keyboard. While holding on Shift click on Left arrow on the keyboard many times to minimize space or Shift + Right arrow to increase space until the space is reasonably adjusted. The following screen is displayed:

8.

Repeat the previous step with all fields.

9. To format the Last Year’s Sales field to print with no decimal places, with a thousands separator (,) and with a floating dollar sign, click on Last Year’s Sales in the Details section, right click and select Format Field from the drop down menu as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

15

The following “Format Editor” dialog box is displayed:

10. Click on the Number default.

16 Saudi Aramco Training

tab on the Format Editor box if it is not shown by

5/15/2007

Crystal Reports10

The following dialog box is displayed:

11. Click on Customize

Saudi Aramco Training

button.

5/15/2007

Crystal Reports10

17

The following “Custom Style” dialog box is displayed:

12. Click on the Decimals drop down arrow and select 1 (No decimals) as shown below:

Note:

The “Thousands Separator” check box is selected by default.

13. To set the dollar sign as Floating, click on the Currency Symbol tab .

18 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

14. Check in the Enable Currency check box. Note:

“Floating” radio button is selected by default as shown below:

15. Click on Ok. Saudi Aramco Training

5/15/2007

Crystal Reports10

19

16. Click on OK. 17. To preview the report and see the results of the design, click on the Print Preview button. The following screen is displayed:

Note:

The date/Time the data was refreshed on, is shown on the status bar to the left of report page number.

18. To toggle between the Preview screen and the Design screen, click on the Design tab

on the top left of the design screen.

19. To format the Region column header, click on the Region field in the details section and right click. 20. Select Format Text from the drop down list. 21. In the Format Editor box, click on the Font tab as shown below:

22. To change the Font color, click on the drop down arrow to the right of Color, then select blue. Click on the Style and select Bold then click on OK .

20 Saudi Aramco Training

5/15/2007

Crystal Reports10

23. To rearrange the columns in a different order: Click on the Customer Name in the Details then click on Shift to select the Customer Name in the Page header section 24. Approach the mouse until its shape becomes cross arrows and drag the two fields and drop them to the right of City as shown below:

Note:

To set the right space for the right data field size, right click On Details in the gray area on the left and select Fit Section from the drop down list as shown below:

25. Repeat the previous steps 23-24 to place Region at the left side in the Details and Page Header sections as shown below: Saudi Aramco Training

5/15/2007

Crystal Reports10

21

The Following screen is displayed:

26. To create a title, click on the Text Object button

or follow the menu path:

Insert → Text Object as shown below:

22 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

The mouse is linked to a rectangular shape or dotted line that is moving along.

27. Release the mouse and drop the Text Object in the center of the Report Header section. 28. To Activate the text Object for editing: click outside the text object then double click inside it: 29. Type in the Title “My First Report” as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

23

29. To increase the font size to 20 points, click outside the Text Object box, then click on the Text Object title. 30. Right click and select Format Text. The following “Format Editor” dialog box is displayed:

24 Saudi Aramco Training

5/15/2007

Crystal Reports10

31. Click on the Font tab and the click on the Size to select 20 from the drop down list. 32. To add a border and drop down shadow to the border, in the Format Editor box, click on the Border tab. 33. In the Line style area, select single for left, right, top and bottom. 34. Check in the Drop Shadow check box . 35. To change the font color, click on the Font tab and select the color. 36. Click on the OK button. 37. To center the title, click on the title, then approach click on the Align Center button . 39. To show the Special Fields in the Field Explorer select Print Date and drag it to the top of the report and drag Page Number to the Page Footer the bottom of each page.

40. Click on the + sign to expand the Special Fields as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

25

The following “Field Explorer” dialog box is displayed:

41. Click on the Print date and drag it to the left of the report title in the Report Header. 42. Click on the Page Number and drag it to the right of the Page Footer as shown:

43. To save the report, click on the Save Note:

button.

Crystal usually names your first report as Report1.rpt by default.

44. Click on Save to continue.

26 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 3: Date/Time Selection Expert and Setting Print Date and Time Table: Employee Fields: First Name, Last Name, Hire Date, Employee Position, Birth Date.

Set Print Date and Time: Objectives: If you have used the special field Print Date or Print Time on your report, but you do not want them to display the actual date and time of the printing, you can set them to specific values using the Set Print Date and Time dialog box. Step by Step Activities: 1. Create a report showing the staff: First Name, Last Name, Hire Date and Position… using drag and drop method from the field list to the Details section. 2. Format the Hire Date to appear as MM/DD/YY with no time displayed. To do this, select Hire Date From the Details Section then → Right click → Format field → Select the Format type with no time from the list…a good formatting type might be (03/01/1999) as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

27

3. Click on OK. 4. Preview the report…(There are 15 records in this report). 5. Define a Record Selection to show only those employees whose Hire Date was in the time period: LastYearYTD (0 records): To do this, select Hire Date from Details Section → Select Expert → Select (Is in the Period) from the drop down field on the left → On the right drop down field, select LastYearYTD as shown below:

Note:

Don’t delete this selection.

6. Click on OK. 7. Change the Print Date to December 1, 1994 and preview the report again (5 records): (Report Æ Set Print Date/Time) as shown below:

The following dialog box is displayed:

28 Saudi Aramco Training

5/15/2007

Crystal Reports10

8. Click in Other radio button and enter 12/01/1994 in the Date field, then click on the OK button. (5 records)

Saudi Aramco Training

5/15/2007

Crystal Reports10

29

The following screen is displayed:

9. Remove the selection criterion to display all 15 employees. To do this, Go to Select Expert

→ Select the Field → Delete.

The following screen is displayed:

30 Saudi Aramco Training

5/15/2007

Crystal Reports10

10. Show all employees Over Age 35 (2 records). To do this go to Selection Expert

→New→Birth Date→ Less than → 12/01/1971 as shown below:

Note:

Delete this selection before step 11

11. List all Sales Representatives hired between 03/01/1991 and 12/31/1992. To do this, ensure you Drag employee’s Position to the Details section in the design screen → Right click → Select Expert Representative (6 Records) as shown below:

→ Equals to → Sales

Note: KEEP THIS SELECTION, PLEASE. Saudi Aramco Training

5/15/2007

Crystal Reports10

31

12. Then make another New Selection to restrict on Hire date: Select Expert → Hire Date → Between 03/01/1991 and 12/31/1992. (4) records of Sales Representative as shown below:

13. Save the report

32 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 4: Organizing: Grouping, Sorting, and Summarizing Table: Customer Fields: Region, City, Customer Name, Last Year’s Sales It is important to sort your date in a variety of ways to meet your queries. In this exercise we are going to practice how to sort in Ascending and Descending order, how to Group data, and how to create a Summary report: Subtotal, Grand total , Count, and Percentage of Total. • • • • •

Sorting Grouping Summarizing Count % of Total

: Sub -Total and Grand Total

Step by Step Activities To sort your data Ascending, or Descending click on Record Sort Expert

Note:

Saudi Aramco Training

:

Select the field from the Available Fields List → Drag and Drop to the right side in the Sort Fields List or by selecting

5/15/2007

Crystal Reports10

33

→

the field and clicking on Take to the right arrow OK

1. Sort the records in your report to print in Ascending Order by Region and City then by Last Year’s Sales in Descending order, (Click on Record Sort Expert → Select Region ( by default it is set in Ascending order) 2. Repeat the same with City as ascending then Last year’s Sales as Descending Order as shown below:

3. Remove the Sort order by selecting Records Sort Expert

→ to reverse

the activity by clicking on the item and selecting take to the left arrow

.

4. To Group your records by City, click on Insert from the menu barÆ Group or click on the Group Expert button .

34 Saudi Aramco Training

5/15/2007

Crystal Reports10

5. Select City from the upper drop down List in the Report Fields:

6. In the lower drop down list, select in ascending order (alphabetical order). tab, check in Keep Group Together and Repeat 7. In the Options Group Header On Each Page.

8. Click on OK 9. Preview the report 10. Redefine: (Delete or Change the Existing Group) to be based on Region not City. (See the note below): Note: There are two ways to change the definition of a group: (1) Delete the Group and Define a New One. (2) Modify (Change) the Existing Group. Saudi Aramco Training

5/15/2007

Crystal Reports10

35

10. Right-click on the gray area of the Group Header Section on the left of the Design tab and select Change Group…as shown:

11. Remove the Region field and Column Heading in the Page Header Section (not the Group) from your report and reposition fields if needed. 12. Preview the report 13. Save the report 14. Add Country as a Group in Ascending Order: → Country → Options → check in both Keep Group ( Insert → Group Together and Repeat Group Header on each page → OK) 15. Change the color of the Country Group#2 Name field to Red by Right Click on the Country Group#2 Name → Format field) → Font tab → Color → Red → OK). 16. Preview the report. It is in hierarchical form, Region first then Country… see note, please: on the toolbar to see that the Note: Click Record Sort Order grouping order is Region then Country. Or Select Group right click in the gray area on the left → Change Group as shown:

36 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

17. Reorder the groups so that the records are grouped by Country then by Region. Note:

To reorder groups, do it from design gray area on the Left as shown on step 18 below :

18. (Drag Group Header # 2 (the group you want to move and drop it over Group Header #1 in its new location) as shown below:

19. Preview

the report.

20. Click on the Save

Saudi Aramco Training

button or press CTRL-S.

5/15/2007

Crystal Reports10

37

21. Insert a Grand Total of Last Year’s Sales. To do this, Select Last year’s Sales from Details section → Insert → Summary →Sum → Grand Total → OK) Note:

Grand Total will appear in the Report Footer. See last page of the report.

22. Insert the following Summary fields using (Insert Æ Summary on groups already created:

): Based

(a) Count of the Companies (Customer Name) in each Region; format to include no decimal places. To do this, (Insert → Summary → Select Count from Calculate this Summary field drop down list → Customer Name→ select Country-A group from Summary location field) → OK ..as shown below:

Note:

Your count will appear in Group Footer #2

B. To find out the Subtotal of each Region’s Sales for Last Year’s Sales: Insert Summary → Sum → Last year’s Sales → Customer. Region-A group → OK). Note: Users may select the field they are basing their calculations on from the Details section to save them the first three steps which will default automatically) Note: Result will appear in Group Footer # 1. of the report. 23. To format the Subtotal to include a dollar sign and no decimal places:

38 Saudi Aramco Training

5/15/2007

Crystal Reports10

(Select Last Years Sales to the right of Group Footer # 1 → Right Click → Format Field) → As shown below:

24. Click on Customize

button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

39

25. In the Number tab → Select Decimal:1 from the drop down list.

Tab → Enable Currency Symbol by 26. In the Currency Symbol checking it in → Select Floating dollar sign if it is not selected by default → OK→ OK.

C. To find out a Percentage Summary for each Region’s Sales (based On Country total) for last Year Sales, and format it to use 4 decimal places: (Insert Summary → Sum → Last Year’s Sales → Customer. Region-A → Check in Show a Percentage of ) → Group #1 Sum of Last Year’s Sales → OK) as shown:

40 Saudi Aramco Training

5/15/2007

Crystal Reports10

Note :

Result will appear over the previous Group Footer #2 so, from design screen drag it to the right a little or below in a second line.

Note:

To format the percentage to 4 decimal: ( right click on Last Years Sales % Summary to the right of Group Footer # 1 → Format field →Customize → Number → Decimal 4).

Note:

Saudi Aramco Training

You can format the decimal places from the Preview screen. Select any % from the % column in the preview screen, then click on the Increase as shown below: decimal Places

5/15/2007

Crystal Reports10

41

Increase decimal Places

Decrease Decimal

27. To find out a Percentage Summary for each Country’s Sales for last Year’s Sales based on the Entire report or (Grand total); and format it to 4 decimal places, follow this menu path: (Insert Summary → Sum → Last year’s Sales → Customer. Country A → Check in Show a Percentage of ) → Grand total: Sum of Last Year’s Sales → OK). Please see section C: above. 28. To format to 4 decimal: ( Right click on Last Years Sales % Summary to the right of Group Footer # 2 → Format field

42 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

The following dialog box is displayed:

29. Click on Customize

Saudi Aramco Training

button:

5/15/2007

Crystal Reports10

43

The following dialog box is displayed:

30. Click on the drop down menu to the right of Decimals field. Then select 1.0000 which means that there are 4 decimal places added to the number.

31. Create a text object as a label for each of the summary fields; make it stand out: and place it to the left of each To do this click on the Text Object button Summary that you have created already for : *Count of Companies per region *Subtotal *% of each region’s sales *% of each country’s sales As shown below in the rectangles on the design screen below:

18. Save the report. 44 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 5: Formatting, Sorting, Creating Hyperlinks, Text Objects and Adding Supplementary Objects: Lines, Boxes and Images. Table: Customer Fields: Country, City, Customer Name, Last Year’s Sales Step by Step Activities: 1. To Format column headers and change point size to 12 and bold: Select all Column Headers → Right Click → Format Object. Then click on the Font tab to change it to Bold and Size 12.

2. Sort the report in Ascending order by country and city: To do this click on Record Sort Expert button then from the Available fields section, select the fields you want to sort and take them to the right in the Sort Fields section → OK as shown below:

Note:

Sort defaults as Ascending. To Change to Descending Click in the Descending radio button → OK 3. Format Last Year’s Sales with no decimal places, with a thousands separator and a floating currency symbol ($) 4. This is the same as you did it in Exercise 2 Steps 9-14. In Brief: Saudi Aramco Training

5/15/2007

Crystal Reports10

45

Right-click Last Year’s Sales and select Format Field… In the Number tab click on Customize Switch to the Currency Symbol tab In the Currency Symbol field ensure Enable Currency Symbol is checked and floating radio button is selected. As shown

.

5. Click OK twice 6. Preview the report Note:

The currency symbol is attached to the sales figure with no space between them.

7. Click OK twice 8. Preview the report 9. Save the report 10. Open the Field Explorer *(it is already open in version 10). If it disappeared button. for one reason or another click on the Field Explorer 11. Under Special Fields at the end of Field Explorer components drag and drop the Print Date and Print Time into the Report Header. To do this expand the

46 Saudi Aramco Training

5/15/2007

Crystal Reports10

Special Fields then drag and drop the necessary fields in the specified sections:

12. Format the date to print like Monday, February 2, 2004 To do this select Print Date in the Report Header → Right click → Format field → select Monday, March 1, 1999 from the list as shown below:

13. Print Page N of M at the bottom right hand corner of each page (in the Page Footer). Saudi Aramco Training

5/15/2007

Crystal Reports10

47

14. Rotate Country and Last Year’s Sales column headers by 90 degrees. To do this select Country → right click → Format Text then from the dialog box that displays select 900 from the Text rotations field options.

Note:

The text needs more resizing vertically to appear clearly as

shown:

15. Click on Insert Text Object button on the toolbar and place it in the Report Header 16. Type Home Page in the text object as shown below: 17. In Design view highlight the Home Page text object: highlight Text Object → Right click → Format Text → hyperlink . Note:

Or Just click on the Hyperlink format Editor.

Icon without going to the

18. Click in A Website on the Internet radio button as shown below:

48 Saudi Aramco Training

5/15/2007

Crystal Reports10

19. In the Website Address enter http://home.aramco.com.sa 20. Click OK 21. Preview the report and test the link 22. Click the Insert Box button to (draw a box around each country) on the Supplementary toolbar or click Insert Æ Box by drawing a box around the country in the details section as shown below:

Note:

If the Supplementary toolbar showing click on: View Æ Toolbars → Insert Tools…as shown:

is not

23. A pencil cursor appears. Use the tip of the pencil to draw a box around each country 24. Click the Insert Picture button on the Supplementary toolbar Saudi Aramco Training

5/15/2007

Crystal Reports10

49

To do this go to Insert Picture Note:

→ and past it in the Page Header section.

If the image is too large, you need to resize it to fit in the area you specify.

25. Go to the following location My Document → My Pictures 26. Insert any picture in the Report Header as shown below:

27. Place a colored border around it with a drop shadow. Select the picture → Right click → Format Graphic → Border

To draw a border around the picture with drop down shadow: select Border from Format Editor → Click on the sides of the border to select type → Then check in the With Drop Shadow box as shown below: 50 Saudi Aramco Training

5/15/2007

Crystal Reports10

28. Preview The following screen is displayed:

29. Save the report

Saudi Aramco Training

5/15/2007

Crystal Reports10

51

Exercise 6: Creating a Mailing Label Report from the Report wizard Table: Customer Fields: Text Objects database The Mailing Label report wizard guides you through the creation of a report that prints labels. It will help you visualize the size of the label and format it as necessary to print the label type you choose. The Mail Label wizard will create a report where each record is printed in a rectangular area rather than in rows. Crystal Reports is still using bands, but the bands are narrower and can repeat across the page in columnar format. The Mail Label wizard can be used for any report that requires this type of formatting and not only for label creation. It might be used to create postcards, name tags, and so on.

Step by Step Activities: 1.

Open Crystal Reports, choose Using the Report Wizard from the welcome dialog box, and click OK. If Crystal Reports is already open, choose File | New, or click the New button on the toolbar. The following Screen is displayed:

18. Click in Using the Report Wizard the click on OK.

52 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

3. Select Mail Label and click on OK button.

The following Screen is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

53

4. Open the Tables folder. Move the Customer table to the Selected Tables list, and click Next. The following screen is displayed:

5. In the Fields screen, move Contact First Name, Contact Last Name, Customer Name, Address1, Address2, City, Country, and Postal Code to the Fields to Display list and click Next. The following screen is displayed:

6. From the Label screen, choose Address/Shipping (Avery 5163) as the 54 Saudi Aramco Training

5/15/2007

Crystal Reports10

Mailing Label Type. Note : The are a lot of Mailing Report Types to select from:

7. Click on Finish button. Note:

Saudi Aramco Training

The report will be processed and then displayed in the Preview window. The Mail Label wizard puts each field in a separate detail section so that they are stacked one on top of the other. Obviously that is not what we really want our mailing label to look like, but it is a good start.

5/15/2007

Crystal Reports10

55

The following screen is displayed:

Note:

There are address2 gaps in some of the labels. To find out whose arrdess2 was not supplied in the database, count how many lines are in each group and you will discover the difference.

8. To Find out the gaps, you can Delete Address2 and insert a new one in a Text Object

in Details E.

Note:

In exercise 18 the gap or blank lines issue will be discussed in details.

56 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

9. To suppress gaps in the addresses select all the Address 2 in the Details E→ Right click → Format Text as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

57

10. From the Common Tab→ Check in Suppress Embedded Fields Blank Lines → Click on OK …

11. Finally, create a title to the report. Insert a Text Object in the Page Header : a) Before Typing a title: “Company Contact Information” in the text object we have to unsuppress Report Header and Page Header. b) Format the title as follows: bold, 12, red, underline c) Make sure the all objects are aligned. See step 13 below. To unsuppress Report Header and Page Header ,right click the left side of the design screen in the gray area of the Report Header and Page Header → Don’t Suppress as shown below:

in the Page Header and enter the title” 13. Next, drag and drop a Text object Company Contact Information”. 14. Preview 15. Insert two more text objects in the Report Header: Details are on page 60 below. Note:

Follow the above procedure in step # 3 to unsuppress report Header and drag and drop Text Objects and the Special Fields. Your Report will look like this:

58 Saudi Aramco Training

5/15/2007

Crystal Reports10

Saudi Aramco Training

5/15/2007

Crystal Reports10

59

a) First text object should say “Printed: “ followed by the Print Date. b) Make the Print Date 12 point and bold c) The second text object should say “Last Modified: “ followed by the Modification Date and Modification Time 16. Preview : 17. To print each person’s information on a separate page select New Page After from Section Expert. To do this: Right click on the Details on the gray area of the design screen → Section Expert. The following screen is displayed:

18. Check in New Page After → Ok

60 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is the final output:

19. Save the report.

Saudi Aramco Training

5/15/2007

Crystal Reports10

61

Exercise 7: Smart Linking (Creating Links Between Tables) Tables: Customer, Orders, Orders_Details, Fields: Customer Name, Order ID, Order Amount Linking Tables When users use more than one table in a report, they must tell Crystal Reports how to link the tables. Tables are usually linked in Crystal Reports in the same way that they are linked in the underlying database. A field from one table is linked to a field in another table that contains identical field values. For example, the sample data contains an Employee table that has an Employee ID field. It also has an Employee Addresses table that has an Employee ID field. Employee can be linked to Employee Addresses using the Employee ID field. Links are represented by lines drawn between the linked fields. Users can create links for the XTREME sample data by clicking the Link button. Links can also be created by selecting a field in one table, pressing and holding down the mouse button, and then selecting a field in another table:

Note: For more Information about Smart Linking, Please read Crystal Reports Help.

62 Saudi Aramco Training

5/15/2007

Crystal Reports10

Step by Step Activities:

1. You should be on the Links tab with Customer, Orders, and Orders_Detail displayed. Click the Clear Links button, and notice that the links are removed. (If you are asked whether you are sure you want to remove the links, click Yes.) 2. Select By Name in the Auto-Link panel, and then click the Link button. Notice that the links reappear. 3. Clear the links again, and then manually create a link from Customer.Customer ID to Orders.Customer ID. 4. Remove all the links again, and then re-create them using the Auto-Link By Name option. 5. Click OK to close the Database Expert. The Design tab will display with a blank report. 6. Save the report.

Saudi Aramco Training

5/15/2007

Crystal Reports10

63

Exercise 8: Creating Formula Fields Table: Customer, Orders Fields: Order ID, Order date, Order amount In this exercise, you will learn some techniques for working with different type of formulas including the use of functions and operators. Step by Step Activities: Part A: Using a Number Formula 1. Create a new report based on the Customer and Orders table of the Xtreme Sample Database 10. Follow the menu path: From the Database Expert Screen → Make New Connections → Xtreme Sample Database 10 → Tables → Customer → Drag Customer table and Orders table to the Selected Tables: area as shown below:

2. Click on OK button. 3. Add the following fields from the Orders table to the Details section of the report: Order ID, Order Date and Order Amount as shown below: 64 Saudi Aramco Training

5/15/2007

Crystal Reports10

4. Format the Order ID to show without the thousand separator. 5. Preview the report by clicking on Preview

Saudi Aramco Training

5/15/2007

button.

Crystal Reports10

65

The following screen is displayed:

Note:

if you think there are Zeros in your database, then you can suppress the zeroes by selecting the Order Amounts in the Details section → right click → Format Field → Customize

→ Numbers → Check in Suppress if zero Value as shown below:

66 Saudi Aramco Training

5/15/2007

Crystal Reports10

6. Format the Order Date to show only the date portion (do not show time). From the design screen in the details section, select Order ID → right click

7. Select Format Field as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

67

The following screen is displayed:

8. Click on Customize

button.

68 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

9. Uncheck Thousands Separator check box and click on OK button → Click on OK again.

Saudi Aramco Training

5/15/2007

Crystal Reports10

69

The following screen is displayed:

Note:

The comma is no longer existing in the Order ID.

10. Format the Order Date to show only the date portion (without time). From the design screen in the Details section, select Order Date → right click → Format field as shown below:

70 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

11. Select the preferred date format that you like. For the purpose of this exercise select 03/01/1999 as shown. 12. Group the data by Customer Name. 13. From the design screen click on Insert group

icon.

The following screen is displayed:

14. Click on the drop down arrow in the upper field and select Customer Name from the list as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

71

Note:

By default grouping is in Ascending Order.

15. Click on OK button. The following Group Name is inserted in Group Header#1 and shown in the design view:

72 Saudi Aramco Training

5/15/2007

Crystal Reports10

Note: whenever a group is created , the system creates a group name in the Group Header section and created a Group Footer section as well. 16. Create a formula and call it Total Order / Customer that calculates the Sum of Order Amounts for each customer.

17. In the Formula Editor expand Functions → Expand Summary → double click on Sum (fld) → double click on Order Amount from the Report Fields List → , (comma) → Customer Name Field. The formula will look like this:

Note:

It is a good idea to check if the syntax of the formula is correct or not before saving and closing the Formula Editor. To check the syntax, click on the Check button. The following little box is diaplayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

73

18. To have more space in you design screen, go to File → Printer Setup → Paper Size → Landscape

19. Click on OK button.

74 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

. 20. Click on the Preview

tab.

The following screen is displayed:

Note:

Saudi Aramco Training

The Customer name grouping is in bold font by default.

5/15/2007

Crystal Reports10

75

21. Save the report as “Using Formulas Part A – Begin.rpt”. Your report should look like the following:

22. From the View Menu, select the Field Explorer displayed.

option, if it is not

23. Right-click on Formula Fields and select the New… option as shown below:

76 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

24. In the Formula Name field, type Order Plus Tax and click on Use Editor. 25. Click on the OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

77

The following screen is displayed:

Note:

There are five sections by default in the Formula Workshop, Formula Editor. The first section on the left is for the server Custom Functions; The next one contains the Report Fields that are already selected on your report. However. You can include any field that is not added in the design of the report, from the Extreme Sample Database 10 (ODBC RDO) tables that you have already selected from the Database Expert. The Functions pane is to include the functions you want to create the formula. The Operators pane also help you include the necessary Operators to create your formula. The bottom pane is where you enter and edit your formula.

26. Type the following formula in the Formula Workshop editor: {Orders.Order Amount} * 1.09

78 Saudi Aramco Training

5/15/2007

Crystal Reports10

From the Report Fields pane, expand the Orders table fields list and double click on the Oder Amount field.

Note:

The Order Amount Field will drop in the Formula Editor pane as shown below:

27. To add a 9 % per cent to the Order Amount, click on * Multiply sign followed by the number you want. For this example it is 1.09 Your Formula Workshop screen should look like the following:

Saudi Aramco Training

5/15/2007

Crystal Reports10

79

Note:

it would be a good practice to check the syntax of your formula if it is correct or not by clicking on Check button or press on ALT+ C.

28. Click on the Save and close Workshop editor.

button to close the Formula

29. Drag the newly created formula Order Plus Tax from the Field Explorer to the Details section of your report to the right of the Order Amount filed. Your report should look like the following:

30. Click on Preview

80 Saudi Aramco Training

tab.

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

The following screen is displayed with a new column with the Header Order Plus Tax added to the report as a result of the formula dragged and dropped in the Details section.

31. Define a formula and call it Total Order Plus Tax / Customer that calculates the sum of Order Amount Plus Tax for each customer

32. From the Functions menu, expand Summary → double click Sum {fld}. Then from the Report Fields in the Formula Editor, double click on the Order Plus Tax Formula as shown below: Saudi Aramco Training

5/15/2007

Crystal Reports10

81

33. Enter a , (comma) → double click on Customer Name from the Report Fields list

34. Define a Text Object and place it in the Group Footer. The text object should display two sentences as follows -The Total Order Amount for {Customer Name} is $ ……Total Order Amount/Customer (Formula) 82 Saudi Aramco Training

5/15/2007

Crystal Reports10

-The Total Order Amount Plus Tax for {Customer Name} is $ …Total Order Amount Plus Tax/Customer (formula) 35. To create a Text Object, click on the Text Object button and drop it in the Group Footer section. To Resize and Enlarge it to accommodate two sentences of 0.350 in height and 6 inch in length, click on the Text Object → right click → Select Size and Position → in Height enter 0.350 and in Length enter 6 as shown below:

Note:

To enter/edit text in the Text Object, double click inside the Text Object, and start entering text and spaces. You need also to drag and drop database fields/ formulas from the Field Expert in the Text object. All the fields that are enclosed in { } are dragged and dropped from the Field Explorer Fields. Formulas are also enclosed in { } and start with @ as shown: {@Total Order /Customer} :

36. Enter the following text in the text Object: The total Order Amount for {Customer Name} is {@Total Order /Customer} The Total Order Amount Plus Tax for {Customer Name} is {@Total Order Plus Tax/ Customer}

Saudi Aramco Training

5/15/2007

Crystal Reports10

83

Preview the report and the following screen is displayed:

37. Save the report as “Using Formulas Part A – End.rpt”.

84 Saudi Aramco Training

5/15/2007

Crystal Reports10

Part B: Using String Formulas 1. Continuing with the report in Part A above, save the report as “Using Formulas Part B – Begin.rpt”. 2. Right-click on Formula Fields and select the New… option. (See steps 18-21 above for reference)

3. In the Formula Name field, type First Letter of Customer Name 4. Click on the Use Editor

button.

5. Expand the Report Fields list and double click on Customer Name as shown below:

6. Type the following formula in the Formula Workshop editor: {Customer.Customer Name}[1] Note:

Subscript [ ] should have a value that grips how many characters out of the customer name. you can put a range [1 to 3 ] to grip 3 characters out of the customer name. Subscript is used to extract one or more characters from a text string or to extract an element from an array. Example: LNAME [1] Returns "S", where LNAME = "Smith". {Customer. Country. CODE} [1 to 3]

Saudi Aramco Training

5/15/2007

Crystal Reports10

85

Returns "966", where Postal Code = "966 3 874 1519". Note:

Space is counted in the subscript extraction.

Your Formula Workshop screen should look like the following:

7. Click on the Save and close Workshop editor.

button to close the Formula

8. We will now use this formula to group the data first on this formula and then on the complete Customer Name. Click on the Group Expert button. 9. Double-click on the First Letter of Customer Name formula to add grouping by this field. Use the arrow keys at the top to re-arrange the groups so the @First Letter of Customer Name appears first then the Customer.Customer Name. Your Group Expert screen should look similar to the following:

10. Click on the OK button. 86 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

How the grouping is done first: by the first letter of the customer name and then by the full customer name. There are two groups created so far. One group for the Customer Name then a group on the Formula (first Letter of Customer name).

11. Preview the report.

Saudi Aramco Training

5/15/2007

Crystal Reports10

87

The following screen is displayed:

11. Save the report as “Using Formulas Part B – End.rpt”.

Part C: Using Date Formulas 1. Continuing with the report in Part B above, save the report as “Using Formulas Part C – Begin.rpt”. 2. Right-click on Formula Fields and select the New… option.

3. In the Formula Name field, type Order Month. 4. Click on Use Editor

88 Saudi Aramco Training

button..

5/15/2007

Crystal Reports10

5. Type the following formula in the Formula Workshop editor: Monthname(Month({Orders.Order Date})) as shown below: a.

From the Functions pane expand Functions → Date and Time → Month name → Month as shown below:

b.

Double click on Monthname [Month] this returns

c.

With the insert still blinking in the (I), double click on Month [x] this returns

d.

With the insert point still blinking in the (I) double click on Order date from the Report Fields pane

e.

This returns

Your Formula Workshop screen should look like the following:

6. Click on the Save and close button to close the Formula Workshop editor. Saudi Aramco Training

5/15/2007

Crystal Reports10

89

7. Drag the newly created formula Order Month from the Field Explorer to the Details section of your report to the right of the Order Plus Tax field. Your report should look like the following:

8. Right-click on Formula Fields and select the New… option. 9. In the Formula Name field, type Process Time.

10. Click on the Use Editor button. 11. Type the following formula in the Formula Workshop editor: {Orders.Ship Date} - {Orders.Order Date} Note:

The sign __ between the two fields of the formula means minus.

90 Saudi Aramco Training

5/15/2007

Crystal Reports10

The two fields are taken from the Orders table from the Report Fields pane. Your Formula Workshop screen should look like the following:

12. Click on the Save and Close Workshop editor.

button to close the Formula

13. Drag the newly created formula Process Time from the Field Explorer to the Details section of your report to the right of the Order Month filed The following screen is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

91

14. Format this field to show with No Decimals places by selecting the Process Time database in the preview screen, then click on Decrease Decimals twice in the right of Formatting tool bar.. Your report should look like the following:

15. Save the report as “Using Formulas Part C – End.rpt”.

Part D: Using Boolean Formulas 1. Continuing with the report in Part C above, save the report as “Using Formulas Part D – Begin.rpt”. 2. Right-click on Formula Fields and select the New… option. 3. In the Formula Name field, type Apply Discount.

92 Saudi Aramco Training

5/15/2007

Crystal Reports10

4. Click on the Use Editor

button.

5. Create the following formula in the Formula Workshop editor: {Orders.Order Amount} > 5000 To create the formula, double click the Order Amount field from the Orders Table from the Report fields List in the Formula Workshop. → Enter > 5000 Your Formula Workshop screen should look like the following:

6. Click on the Save and close button to close the Formula Workshop editor. 7. Drag the newly created formula Apply Discount from the Field Explorer to the Details section of your report to the right of the Order Month filed.

Saudi Aramco Training

5/15/2007

Crystal Reports10

93

The following screen is displayed:

8. Format the field to show “Yes” or “No” values. To do this select your Apply Discount formula in the Details section, → Right click → Format Field The following dialog box is displayed:

94 Saudi Aramco Training

5/15/2007

Crystal Reports10

9. Select Yes/No from the list → Ok Your report should look like the following:

10. Save the report as “Using Formulas Part D – End.rpt”.

Saudi Aramco Training

5/15/2007

Crystal Reports10

95

Exercise 9: Highlighting, Conditional Formatting, and Alert Monitoring Table: Customer, Orders Fields: Customer Name, Order Amount, Last Year’s Sales In this exercise, you will learn how to use the Highlighting Expert, Create Conditional Formatting and Trigger Alert Monitoring to show certain trends or deviations in your reports.

Step by Step Activities Part A: Using the Highlighting Expert 1. Create a new report based on the Customer and Orders tables of the Xtreme Sample Database 10. 2. Add the following fields to the Details section of the report: Customer Name from the Customer table and Order Amount from the Orders table. Delete the column headings from the Page Header. 3. Group the data by Order Date (for each month) and format the group name to show the full month name and year like February 2003. Click on the Insert Group button → Select Order Date from the Order Table List from the drop down menu from When the report is printed, the records will be sorted and grouped by: as shown below:

4. From the Design screen, click on Preview

button.

The following screen will be displayed: 96 Saudi Aramco Training

5/15/2007

Crystal Reports10

5. In the Report Header section, add a title to your report such as “Monthly Customer Order Amounts”. Format the title as you like. 6. Click on Text Object button and place it in the center of Report Header section as a title. Format it to bold, 14 point font size and blue ,double line boarder with shadow around it . 7. To draw a border around the title, select the tile → right click → Format text → Border as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

97

8. Select Double line style for all sides of the border and check in the Drop Shadow box as shown below:

9. Click on OK button. 98 Saudi Aramco Training

5/15/2007

Crystal Reports10

10. Make sure to double click inside the Text Object, highlight the title and click on button. the Align Center 11. Preview. The following screen is displayed:

12. To format the group name to show the full month name and year like February 2003, Click on the Group # 1 Name from the Group Header # 1 Section on the design Screen → right click → Format field The following screen is displayed:

13. Select a date Like February 2003. In this case select March 1999.

Saudi Aramco Training

5/15/2007

Crystal Reports10

99

14. Save the report as “Highlighting – Begin.rpt”. Your report should look like the following:

15. To highlight the sales amount differently based on the amount value, start by rightclicking on the Order Amount field in your report and select the Highlighting Expert

button.

100 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

16. Click on the New

button.

The following screen is displayed:

17. In the Value of field, leave the option to: this field. 18. In the second field (Operator/Condition), select: is less than or equal to. Saudi Aramco Training

5/15/2007

Crystal Reports10

101

19. In the Value field, type: 100.00. 20. In the Font Style field, select: Bold. 21. In the Font Color field, select: Red as shown below:

22. Click on the New button to add another value. 23. In the Value of field, leave the option to this field. 24. In the Condition field, select is greater than. 25. In the Value field, type 5,000.00. 26. In the Font style field, select Bold, Green font color.

102 Saudi Aramco Training

5/15/2007

Crystal Reports10

Your Highlighting Expert window should look like the following:

27. Click on the OK button. Note:

Please check how the Order Amount values are formatted (Highlighted) differently based on the Order Amount value. Your report should look like the following:

28. Save the report as “Highlighting – End.rpt”. Saudi Aramco Training

5/15/2007

Crystal Reports10

103

Part B: Using Conditional Formatting 1. Continuing with the report you created in Part A above, click the Undo button or go to Highlighting Expert and remove your entries. We will now use conditional formatting to achieve the same highlighting effects. 2. Save the report as “Conditional Formatting – Begin.rpt”. 3. To highlight the sales amount differently based on the amount value, start by right-clicking on the Order Amount field in your report and select the Format Field… option. 4. Click on the Font tab. 5. Click on the Conditional Formatting button next to the Color option as shown below:

6. From the Operator pane in the Formula Workshop Editor expand Control Structures and double click on If x then y else z as shown below:

104 Saudi Aramco Training

5/15/2007

Crystal Reports10

7. In the Formula Editor, place the insert point of the mouse after IF, then double click on the Order Amount field from the Report Fields List as shown below:

After that, enter <= 100 →Following then enter crRed In the second line continue as shown below: else if {Orders.Order Amount} > 5000 then crGreen In the third line continue as show below. else DefaultAttribute as shown:

if {Orders.Order Amount}<=100 then red else if {Orders.Order Amount}>5000 then green else defaultattribute

Note:

you can manually type the color (red) or (crred.) “cr” means Crystal or search for it using the search tool show below:

8. Click on Find

as

button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

105

The following dialog box is displayed:

9.

In the Find what field enter color. Note:

Users have to decide on the option they are searching for whether it is a Field, a Function, an Operator etc. Colors are Functions therefore the Functions radio button is selected. Users may look for defaultattribute function in the same way as shown below:

106 Saudi Aramco Training

5/15/2007

Crystal Reports10

Your formula should look like the following:

10. Click on Save and close

button.

The following dialog box is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

107

Note:

Please notice how the conditional formula button is changed. The pencil is diagonal…which means there is a formula attached to this button.

11. Click on OK button. The following screen is displayed:

12. Save the report as “ Conditional Formatting- End.rpt”.

Part c: Alert monitoring In this exercise, you will learn how to use the Alerts feature of Crystal Reports to display some notification messages to users based on some criteria you set in your reports. If the values displayed are below the threshold you predefined as a target, the system will pop up a message screen urging you to take action. 1. Make sure you delete any Conditional Formatting Formula you created in section b of this exercise as shown below:

2. From the design screen, right click on the Order Amount → Format Field → Font → Click on the Conditional formula button to the right of Color: 3. Highlight the formula in the formula editor and click on Delete on the keyboard as shown below:

108 Saudi Aramco Training

5/15/2007

Crystal Reports10

4.

Delete the first group you created on Order Date before.

5. To do so, In the gray area on the left, of the Group #1 Name in the Design screen, right click and select Delete Group as shown below:

6.

From the Customer table, add the following fields in the Details section of the report: City and Last Year’s Sales as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

109

7. Group the data by Country as shown:

8. Apply a record selection on Country to show customers from USA only.

110 Saudi Aramco Training

5/15/2007

Crystal Reports10

To do this click on Selection Expert below.

button and select Country as shown

9. Click on OK button. The following Select expert dialog box is displayed:

10. Click on the drop down arrow below Customer. Country then select is one of operator as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

111

11. In the second drop down menu to the right, type USA and click enter on the keyboard or click on Add button as shown below:

12. Click on OK button. The following screen is displayed:

13. Click on Refresh Data button. 14. To group data by Region, from the design screen click on Insert Group button as shown below:

112 Saudi Aramco Training

5/15/2007

Crystal Reports10

14. Select Region from the When the report is printed, the records will be sorted and grouped by above.

Saudi Aramco Training

5/15/2007

Crystal Reports10

113

The following screen is displayed:

15. There is a lot of repetition under Customer Name. To remove repetition, select Customer Name in the details section, right click → Format Field → Common → Check in the Suppress if Duplicated Display String box as shown below:

114 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

16. Ensure that the Highlighting Condition on Order amount that you created before is deleted. To do so, select Order Amount from the Details section → right click → Highlight Expert as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

115

The following screen is displayed:

17. Click on Remove 18. Click on OK button.

button twice.

The following screen is displayed:

116 Saudi Aramco Training

5/15/2007

Crystal Reports10

19. To create an Alert Monitoring to warn about Sales below 5000 follow the menu path: From the design screen in the Details section, select Last Year’s Sales → Report → Alerts → Create or Modify Alerts as shown below:

The following Dialog box is displayed:

20. Click on New

Saudi Aramco Training

button. The following dialog box is displayed:

5/15/2007

Crystal Reports10

117

21. In the Alert Name field, type Sales Warning. 22. In the Message field, type Sales below 5000. 23. Make sure the Enabled checkbox is selected. 24. Click on the Condition button. The following screen is displayed:

25. Enter the following formula in the Formula Workshop Editor: {Customer.Last Year's Sales}<=5000

118 Saudi Aramco Training

5/15/2007

Crystal Reports10

Your Formula Workshop window should look like the following:

26. Click on the Save and close Workshop.

button to close the Formula

27. Click on the OK button. 28. Click on the Report Refresh button. The below Alert window appears as there are some records that satisfy the alert condition:

29. Click on the View Records to see the records causing the alert. 30. To highlight this Alert result in red font color, select Last years Sales from Details section → right click and select Highlighting Expert

Saudi Aramco Training

5/15/2007

Crystal Reports10

119

31. Click on New

button.

32. Enter the following parameters that are reflected on the dialog box below and click OK. The following screen is displayed:

33. Click on Refresh

button.

The following screen is displayed:

34. Click on View Records

120 Saudi Aramco Training

button

5/15/2007

Crystal Reports10

The following screen is displayed:

35. Save the report as “Report Alerts – End.rpt”.

Saudi Aramco Training

5/15/2007

Crystal Reports10

121

Exercise 10: Formatting Sections within Sections Expert Table: Customer Fields: Country, Region, Customer Name, Last Years Sales

A report consists of several sections, including the Report Header, Page Header, Group Header, Details, Group Footer, Page Footer, and Report Footer. Each report section is made up of a series of lines. When a text-based object is placed in a section, it is placed on a line in such a way that the text is aligned to the baseline. The line's height is then adjusted by the printer driver so that it is high enough to accommodate the object. •

If a report contains many objects, suppressing sections may require fewer conditional formulas.

•

Not all settings and features can be formatted conditionally. By suppressing sections, however, you can make any formatting changes you want.

•

You may want to provide completely different types of information for people viewing the accessible version of the report. For example, you may want to split visual and audio objects into two different sections and conditionally suppress them based on the parameter value the user chooses.

Design Tab Areas When you first begin creating a report, Crystal Reports automatically creates five areas in the Design tab. •

Report Header This section is generally used for the report title and other information you want to appear at the beginning of the report. It can also be used for charts and cross-tabs that include data for the entire report.

•

Page Header This section is generally used for information that you want to appear at the top of each page. This can include such things as chapter names, the name of the document, and other similar information. This section can also be used to display field titles above the fields on a report.

•

Details This section is used for the body of the report, and is printed once per record. The bulk of the report data generally appears in this section.

•

Report Footer

122 Saudi Aramco Training

5/15/2007

Crystal Reports10

This section is used for information you want to appear only once at the end of the report (such as grand totals) and for charts and cross-tabs that include data for the entire report. •

Page Footer This section usually contains the page number and any other information you want to appear on the bottom of each page.

If a group, summary, or subtotal is added to the report, the program creates two additional sections: •

Group Header This section typically holds the group name field, and can be used to display charts or cross-tabs that include data specific to the group. It is printed once at the beginning of a group.

•

Group Footer This section generally holds the summary value, if any, and can be used to display charts or cross-tabs. It is printed once at the end of a group.

When a group, summary, or subtotal is added, the Group Header area appears directly above the Details area and the Group Footer area appears directly below the Details area. If you set up additional groups, the program creates new group areas between the Details area and the existing Group Header and Group Footer area(s). Like the original areas, each of these newly added areas can contain one or more sections. By default, they each contain a single section.

Saudi Aramco Training

5/15/2007

Crystal Reports10

123

Step by Step Activities: 1. Group Ascending by Country then Sort Ascending by Region and Customer Name. To do so, click on Insert Group → Select Country from the upper drop down list, as shown:

2. To sort Region and Customer Name, click on the Records Sort Expert button. Select Region, Customer Name and add them to the Sort Fields in the right using the > arrow, then Press OK as shown below:

124 Saudi Aramco Training

5/15/2007

Crystal Reports10

3. Preview the report and the following screen is displayed:

4. Include a Summary showing Total Sales per country To do this click on InsertÆ SummaryÆLast Year’s SalesÆSum → Group#1 Country… as shown below:

5. Click on the OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

125

The following screen is displayed:

6. Make sure each country prints on a separate page. To do so go to the gray area on the Left of Group Header #1 ÆRight ClickÆSection Expert as shown below:

126 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

7. Check in New Page Before and click on OK button. The following screen is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

127

Note:

8.

Each Country Group is printed on a separate page. New Page After option allows a blank page at the end, While New Page Before allows a blank page in the front.

Select only records for France and England. Select Country in Details sectionÆ (Select Expert) ÆFrance/England →Add or Enter → OK.

9. Click on Save

Is one of

button.

10. Shade the background of every other line in the Detail section Tip: use the Remainder and RecordNumber functions. To do so click on the Section Expert button or right click from the gray area on the left Æ Section Expert → Select Details Æ ColorÆ Check in the Background colorÆ Silver as shown below:

128 Saudi Aramco Training

5/15/2007

Crystal Reports10

11. Click on the Formula button to the right of Background and enter the formula with the following two functions: A. Remainder is found under Functions → Math.. as shown:

B. RecordNumber is found under Functions → Print State as shown:

Note:

Saudi Aramco Training

Concept of Remainder Function depends on the division of First record by Second record . 1/2= ? remainder; 2/2= no remainder… If the remainder = 0 then white, if there is a remainder 3/2=1.5 then silver.

5/15/2007

Crystal Reports10

129

Note

For the purpose of this exercise we are going to apply Remainder in highlighting records greater than 0 remainder in Silver else no color.

This is how your formula looks in the formula editor:

12. Uncheck the Print After from the Group Header #1 Section. The following screen is displayed:

Note:

Country field in the details section can now be deleted because a group is already created.

13. Format the Group Footer to print with a red color background if the country’s total sales were under $600,000. To do so select Group Footer #1 in the Section Expert ÆColorÆ Check in the Background Color Æ Select Red as shown:

130 Saudi Aramco Training

5/15/2007

Crystal Reports10

14. Then click on the Formula

to add the following formula in the formula editor:

if Sum ({Customer.Last Year's Sales},{Customer.Country})<=600000 then crred else blue 15. Preview the report The following screen is displayed:

16. Remove the selection condition so that all countries are shown in the report by going to Select Expert and pressing on Delete twice :

.

Saudi Aramco Training

5/15/2007

Crystal Reports10

131

The following screen is displayed:

17. Remove all Background colors from the report. To do so reverse the activity or suppress Group Footer #1 Section as shown:

132 Saudi Aramco Training

5/15/2007

Crystal Reports10

18. Format the report so only the summary information is displayed and drilldown is available. To do so hide the details section from the gray area on the left of Details section as shown:

Note:

You have two options either to hide the details information and there is a possibility of drilling down details…or suppress details section but in this case you are not allowed to view details.

This is how your design should look when the Group Footer # 1 and details sections are suppressed. Saudi Aramco Training

5/15/2007

Crystal Reports10

133

Exercise 11: Using Group Layout Charts and Creating Top N Reports Table: Customer

Fields: Include Region (delete it later) , City, Customer Name, Last Year’s Sales Group LayOut Charts The Group layout is a simplified layout in which you show a summary on change of field for topics such as Country. Note:

In order to create a chart using the Group layout, you must have at least one group and at least one summary field for that group.

Step by Step Activities: 1. Design a report showing: Region, City, Customer Name and Last Year’s Sales. Your design should look like this:

2. Group by Region. To do so select Region → Insert Group → Options as shown below:

134 Saudi Aramco Training

5/15/2007

Crystal Reports10

3. Sort Ascending

by City and Customer Name as shown:

4. Set a record selection to show the Regions FL, CA, OH, PA… to do so, select Regions in the Details section → Select Expert → Is one of → FL, CA, OH, PA (adding them one by one) and clicking on Enter on the keyboard each time as shown:

5. Include in your report the following Summary fields : ƒ Count of customers: To do so select Customer Name from the Details → Count → Customer Name section then click on Insert Summary → Customer. Region A → OK as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

135

136 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

ƒ

Subtotal of Last Year’s Sales (by region): To do so select last Year’s Sales from the Details section then click on Insert Summary → Last Year’s sales → Sum → Customer .Region A → OK ..as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

137

The following screen is displayed:

ƒ

Subtotal of Last Year’s Sales as a Percentage of Total .To do select last Year’s Sales from the Details Section then click on Insert Summary Æ SumÆ last Year’s SalesÆ Customer Region A>Check in Show as a Percentage of check box ÆGrand Total : Sum of last Year’s Sales → OK ..as shown:

138 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

The % result in Group Footer #1 comes above Sum of Last Year’s Sales in the design screen. Please make sure you press the down arrow twice.

6. Hide the Details section. To do so, select the details in the gray area on the left of the design screen→ Right Click → Hide (Drill down OK) as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

139

The following screen is displayed with the details section hidden:

7. Create a Pie Chart comparing Total Sales (Sum of Customer last Year’s Sales) for each Region: to do so click on (Insert Æ Chart ) as shown:

8. Select Data tab . 140 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

9. Give your chart the title “Sales by Region”: To do so, click on Text → Title → enter text → OK or Highlight the chart by clicking on the white area around the chart → right click → Chart Options → Title as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

141

The following screen is displayed:

10. Preview your report

142 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

11. Now change the record selection to show a different set of regions. For example, you can include NJ. To do so click on the Select Expert → Add NJ from the list → OK as shown:

The following dialog box is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

143

12. Click on Refresh Data button. The following screen is displayed with the newly added region reflected on the chart.

13. Drilldown on different chart values 14. Delete the Chart by clicking on the white area of the chart and clicking on delete button on the keyboard. 15. Delete Selections. 16. Show Details Section by right clicking on the Details section on the left side and selecting Show from the list.

144 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

17. Apply the Top N criteria to show the top-selling three regions for each country with the remaining records in a group called Others. To do so click on Group Sort Expert select Top N ..as shown:

Saudi Aramco Training

button. Under For this Group Sort

5/15/2007

Crystal Reports10

145

18. Under based on option below, select Sum of Customer Last Year’s Sales as shown

19. In the Where N is: enter the value 3 as shown:

146 Saudi Aramco Training

5/15/2007

Crystal Reports10

20. Check in Include Others, with the Name: as shown:

21. Click on OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

147

The following screen is displayed:

Note:

The result above represents the top three values in the Sum of Last Year’s Sales.

22. Add a title to the report – North American Sales: Top 3 Regions by Country 23. Preview. 24. Save. 25. Remove the Top N settings. To do so click on Group Sort Expert

148 Saudi Aramco Training

5/15/2007

button.

Crystal Reports10

26. Under 27. Click on OK button.

for this Group sort select No Sort .

The following screen is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

149

28. Change the report to show only those groups whose sales exceed $300,000. To do so click on Last Year’s Sales in the details section of the design Screen → Select Expert as shown:

29. In the drop down list select is Greater than or Equal to and enter 300000 in the second field as shown:

150 Saudi Aramco Training

5/15/2007

Crystal Reports10

30. Click on OK. The following screen is displayed:

31. Save the report

Saudi Aramco Training

5/15/2007

Crystal Reports10

151

Exercise 12: Review (Optional) Tables: Customer, Orders Fields: Order ID, Order Date, and Order Amount Step by Step Activities: 1. Group on Customer Name 2. Enter a formula to convert all customer name to uppercase

and drag it in place of Customer name in Details Section.. Formula: Uppercase ({Customer.Customer Name}) 3. Suppress all duplicates in the Customer names.

4. Select only records from BC region and only records with Order Amount > 0 5. Delete the previous selection 6. Format Order ID to eliminate the commas. 152 Saudi Aramco Training

5/15/2007

Crystal Reports10

7. Format the Order Date to only display the date (no time). 8. Insert a Subtotal on Order Amount per Customer 9. Insert a Grand Total: Insert Summary → Order. Order Amount → Sum → Grand Total. 10. Sort on Order Date (ascending) and then on Order ID (ascending) 11. Insert a title on the report Customer Order Report and format it with a border and drop shadow. 12. Insert the Print Date and Time in a text object with the word “Printed:” 13. Format the Subtotal to have a line above the number. 14. Preview 15. Save This is how the report design looks:

This is how the Preview looks:

Saudi Aramco Training

5/15/2007

Crystal Reports10

153

Exercise 13: Creating an Employee Salary Report Table: Employee Details Section: Text Object: First initial. Last Name. Then outside the text Object add the following formulas :Years of Service, Age, Salary Increase by certain % Step by Step Activities: 1.

In a Text Object in Details section, drag and drop the Formula named First Initial followed by Last Name .

Text Object

Note:

Employee Name: (in the Text Object → combine the first letter of the first Name and last name of each employee). The formula is {Employee. First Name}[1]..as shown:

154 Saudi Aramco Training

5/15/2007

Crystal Reports10

2. Click on Formula in the Field Explorer → Right Click → New formula → Name it: First Initial → select Use Editor as shown:

3. In the Text Object that you created already, drag and drop the formula that you named @ First Initial → Space → Period → followed by <Employee Last Name> field from database fields in the Field Explorer.

Saudi Aramco Training

5/15/2007

Crystal Reports10

155

This is how your design screen looks after finishing step 3 above:

4. Years of Service: format with No decimal. To create a formula that calculates Years of Service, right click on Formula Fields in the Fields Explorer → New → Call it Years of Service → Use Editor..as shown:

Enter the formula in the Formula Editor:

Note:

Year is a Function that can be found under Date and Time.

5. After saving and closing the formula Editor, drag the Years of Service in the Details just outside the Text box you created before. 6. To format the years without decimals, select the results column from the preview screen, then click on Decrease Decimals toolbar twice as shown:

156 Saudi Aramco Training

5/15/2007

option from the formatting

Crystal Reports10

7. Hire Date: display only the year (not date). To display only Year, create a formula and call it Hire Year as shown:

Note:

Another method to format Hire date by right Clicking on Hire date, → Format field → Customize → Select Date and Time and select Date from Order Field. Click on the Date tab and Select None for Month and Day. For the Year select 1999.

8. After saving and closing your formula, drag and drop it in the details just to the right of Years of service. Saudi Aramco Training

5/15/2007

Crystal Reports10

157

9. Preview The following screen is displayed:

Note:

The Hire Year above needs formatting. It shouldn’t include commas or decimals.

9. Click on the Hire Year result in the Preview screen, and click on the comma on the formatting toolbar

and click twice on Decrease Decimals

158 Saudi Aramco Training

button.

5/15/2007

Crystal Reports10

The following screen is displayed:

10. Age Formula: (calculated in years). To Calculate Age, create formula and call it Age as follows:

Note:

The blue font that you see while creating the formula represents a function or operator syntax by the system.

11. Drag and drop Age formula in the Details section as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

159

12. Preview the report. The following screen is displayed:

Note:

160 Saudi Aramco Training

The purpose of this report is to add 5.5% to the employee salary as of next year (2007) if his current salary is 50000 or less. All others will receive 2 % if their salaries are above 50000.

5/15/2007

Crystal Reports10

13. Let’s presume that 2006 is the Current Salary: an increase of 5.5% for all employees whose 2006 salary was 50000 or less. All others will receive a 2% salary increase To add 5.5 to those employees whose current salaries are 50000 or less, we have to create a formula and call it Salary Increase and this formula will read as follows:

14. Drag and drop the X-1 2007 Salary formula to the Details section to the right of current 2006 Salary to help create a comparison. The following screen is displayed:

Note:

You can edit column headers by double clicking the headers name.

15. let’s check and proof who received 5.5% and who received 2%. To do this we have to create a formula and call it % which read as follows:

Saudi Aramco Training

5/15/2007

Crystal Reports10

161

16. Drag and drop this formula to the right of 2007 Salary in the details section. The following screen is displayed:

17. Let’s Insert a Grand Total for 2006 Salary : Insert>summary→ Sum → 2006 Salary and Grand total for 2007 Salary at the bottom of the report: To do so just select current salary or just Salary as it is called in the database → Sum → 2007 Salary Formula as shown: Insert Summary

162 Saudi Aramco Training

5/15/2007

Crystal Reports10

18. Click on OK 19. Select 2007 Salary in the details, and click on Insert Summary as shown:

→ Sum →

20. Add a title using Text Object , 21. Add print date and time to your report with appropriate formatting: Using Special Fields from the Field Expert at the end: Just drag and drop the item to the section desired:

Saudi Aramco Training

5/15/2007

Crystal Reports10

163

Note:

This is how it looks when you drag special fields to the Report Header section:

22. Preview and Save.

164 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 14: Specified Order Grouping Table: Customer Fields: drag and drop the following fields in the details section of the design screen: Region, City, Customer Name, Last Year’s Sales

Grouping data Grouped data is data that is sorted and broken up into meaningful groups. In a customer list, for example, a group might consist of all those customers living in the same Zip Code, or in the same Region. In a sales report, a group might consist of all the orders placed by the same customer, or all of the orders generated by a particular sales representative. Group and sort direction When data is grouped, four sort and group direction options are available. Direction refers to the order in which the values are displayed. •

Ascending Ascending order means smallest to largest (1 to 9, A to Z, False to True). The program sorts the records in ascending order and then begins a new group whenever the value changes.

•

Descending Descending order means largest to smallest (9 to 1, Z to A, True to False). The program sorts the records in descending order and then begins a new group whenever the value changes.

•

Specified order :

Saudi Aramco Training

5/15/2007

Crystal Reports10

165

Specified order is a user-defined order. The program places each record into the custom group you specify, leaving the records in each group in original order or it sorts them in ascending or descending order, depending on your instructions. •

Original Original order is the order the data was originally saved in the database. The program leaves the records in the order in which they appear in their originating database table, and begins a new group whenever the value changes in the group field you select.

Step by step Activities: 1. Include a group by Country : to do so click on Insert Group Country. 2. Select only Canada and USA : To do so, click on Select Expert New → select Country from the drop down list → OK ..as shown:

→ button →

The following dialog box is displayed:

3. From the first drop down list, select is one of and the next field, select France → Add, then select England → Add → OK as shown: 166 Saudi Aramco Training

5/15/2007

Crystal Reports10

Note:

Look at the Navigation pane on the left of the design screen to see how your selection looks.

→ Select 4. Sort the Region alphabetically : Click on Record Sort Expert Region from the Available Fields List → Take it to the right pane using take to the right arrow. 5. Add a Specified Grouping by Region to include the following To do so, select Region from details section → Insert Group → from the When the report is printed, the records will be sorted and grouped by list, select → In specified Order as shown:

The following dialog box is displayed:

6. Click on New Saudi Aramco Training

button. 5/15/2007

Crystal Reports10

167

The following dialog box is displayed:

7. In Group Name field, enter the first name: Western Sales Territory and select is one of from the drop down list as shown:

8. In the field to the right of is one of

either select the Regions CA, ID, NV and BC one by one from the drop down list or type them and using enter after each entry as shown:

168 Saudi Aramco Training

5/15/2007

Crystal Reports10

9. Click on OK. 10. Click on New again and repeat steps 6 to 9 to enter the second Region group name Eastern Sales Territory and the list of regions: FL, OH, ON, and PA.

Saudi Aramco Training

5/15/2007

Crystal Reports10

169

The following dialog box is displayed:

11. Click on Others

tab.

The following dialog box is displayed:

12. In the field below Put all Others together, with the name: Unassigned Regions as shown above. 13. OK 14. Preview

170 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

15. Insert a Group subtotal for the Region group and a Group subtotal for each Country group. To do so, select Last Year’s Sales in the Details section of the design view → Insert Summary

→ the following dialog box is displayed:

16. Select as shown in the screen above the click on OK. Saudi Aramco Training

5/15/2007

Crystal Reports10

171

17. To find out the a group subtotal for each country group, do the same thing but the Summary Location option, select Group # 1 Customer. Country.. as shown:

18. Preview. 19. Save 20. To delete the specified grouping, right click on the gray area on the left of Group # 2 Name → right click → and choose Delete Group as shown:

172 Saudi Aramco Training

5/15/2007

Crystal Reports10

Or click on the Section Expert button → select the section to suppress → Suppress (No Drill down) shown:

21. Click on OK . The section that you suppressed will not print with the report. 22. To suppress Group Header, right click on the gray area to the left of Page Header → Section Expert → Check in Suppress on the right side as shown:

Saudi Aramco Training

5/15/2007

Crystal Reports10

173

23. Click on OK. The following screen is displayed:

Note:

174 Saudi Aramco Training

The hashed lines represent suppression of data.

5/15/2007

Crystal Reports10

Exercise 15: Cross-Tab Wizard Data Summary In this exercise, you will learn how to use Cross-Tabs Wizard as an efficient and concise way to summarize large data. 1. From the File menu, select the New Æ Cross-Tab Report as shown

Note:

and

Make sure you select .

2. Click on OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

175

3. On the Data screen, select the Customer, Orders, Order Details and Product and Product Type tables from the Xtreme Sample Database 10 as shown below:

4. Click on the Next button.

176 Saudi Aramco Training

5/15/2007

Crystal Reports10

5. On the Link screen, check to make sure that the Product table is linked back to the Customer table through the Orders and Orders Details tables as shown below:

6. Click on the Next button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

177

The following screen is displayed:

7. On the Cross-Tab screen, drag the Customer Country fields from the Customer table to the Rows area or select the table and click on . 8. Drag the Product Name field from the Product table to the Columns area. 9. Drag the Order Amount field from the Orders table to the Summary Fields area.

178 Saudi Aramco Training

5/15/2007

Crystal Reports10

10. Leave the default summary operation as Sum. Your completed Cross-Tab screen should look like the below:

11. Click on the Next button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

179

12. On the Chart screen, select Pie Chart and leave the default charting options as suggested by the Wizard as shown below:

13. Click on the Next button.

180 Saudi Aramco Training

5/15/2007

Crystal Reports10

14. Optional: On the Record Selection screen, do a record selection on the Customer.Country to restrict it to the four countries: Austria, Canada, Costa Rica, Spain and USA as shown below:

15. On the Record Selection screen, do a record selection on the Product-Name to restrict it to the four countries: Nicros, Vespar Comfort ATB Saddle, Extreme Adult Helmet, Extreme Youth Helmet as shown below:

Saudi Aramco Training

5/15/2007

Crystal Reports10

181

16. Click on the Next button.

182 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

Note:

On the Grid Style screen, leave the default Original style.

17. Click on the Finish button. The Cross-Tab is displayed in the Report Header. 18. Refresh your report.

Saudi Aramco Training

5/15/2007

Crystal Reports10

183

The following screen is displayed:

184 Saudi Aramco Training

5/15/2007

Crystal Reports10

19. Save the report as “Cross-Tabs – Start.rpt”.

20. To Pivot the table; i.e. swap the rows and columns, right-click on your Cross-Tab table and select the Pivot Cross-Tab option. Note how the Countries are shown on the Columns and the Product Classes are shown on the Rows:

21. Optional: To re-invoke the Cross-Tab Expert (Wizard), right-click on you CrossTab table and select the Cross-Tab Expert… option. 22. Optional: To change the formatting of the Cross-Tab, right-click on you CrossTab table and select the Format Cross-Tab… option. 23. Save the report as “Cross-Tabs – End.rpt”.

Saudi Aramco Training

5/15/2007

Crystal Reports10

185

Exercise 16: Grouping Data Hierarchically Table: Employee

Fields: Last Name, First Name in a text Object, Position Grouping data hierarchically You have the option to group data in a report to show hierarchical relationships. When you group data hierarchically, you sort information based on the relationship between two fields. For example, if you want to show the hierarchical structure of a department, you could group data by employee ID and specify the hierarchy using the data field listing whom the employee reports to. Step by Step Activities: 1. Design a report showing Last Name, First Name in a text box into the Details section and remove the column headings: as shown

186 Saudi Aramco Training

5/15/2007

Crystal Reports10

2. Insert the Position field into the Details section and remove the column heading as shown:

3. Insert a Group on Employee ID. Customize the Group Name to display as Last Name : Note:

In the Insert Group dialog box, select the field to be the basis of your hierarchy.

For example, if you want to view the hierarchical structure of a company's employees, select the employee ID data field. If you want to view the hierarchical structure of region sales offices, select office names. To do this Insert Group → Common→ Employee ID → Options → Check in Customize Group Name Field: select Employee Last Name from Choose from Existing Field after selecting the radio button and → Ok.. as shown as shown

Saudi Aramco Training

5/15/2007

Crystal Reports10

187

Note:

Select in ascending order. By default, the group header of the report will display the value of the field you are grouping on.

Note:

Click the Options tab and select the Customize Group Name Field check box if you want to show a different value in the group header. For example, if you grouped by {Employee.EmployeeID}, at each change of

188 Saudi Aramco Training

5/15/2007

Crystal Reports10

a group you will see the corresponding employee ID. If you want to display a different value (employee name instead of employee ID), customize the group name field by choosing an alternate data field, or creating a formula. 4. Click Report Æ Hierarchical Grouping Options as shown

Saudi Aramco Training

5/15/2007

Crystal Reports10

189

The following dialog box is displayed:

Note:

In the Available Groups list, select the group you want to organize hierarchically.

5. Check in Sort Data Hierarchically 6. Instance ID should be Employee ID. Note: The Instance ID Field and Parent ID Field must be of the same data type. For example, if the Instance ID Field holds string data, then the Parent ID Field must also hold string data 7. Select Supervisor ID as the Parent ID field in the Hierarchical Options dialog box. Note:

In the Parent ID Field list, select the field by which you want the Instance ID Field organized. For instance, for a company hierarchical report, you might select the data field listing the supervisor to whom the employee reports.

8. Set a group indentation of 0.5 as shown:

190 Saudi Aramco Training

5/15/2007

Crystal Reports10

Employee ID

Note:

In the Group Indent field, enter the amount you want to indent for each subgroup.

9. Preview and Save. The following screen is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

191

Exercise 17: Underlay an Image as Watermark Background Table: Orders

Fields: Order ID, Order Amount, Shipped, Payment Received. Underlay The ability of an object (a bitmap, a graph, and so on) to print beneath multiple sections which follow the section in which it was placed. For example, you can place a bitmap in one section, format the section to underlay the following sections and then expand the bitmap so it appears as a background for the entire page of your report. Step by Step Activities:

1. Remove all column headings as shown:

1. Prepare a washed out image via PowerPoint or any graphic tool. In PowerPoint follow the menu path Insert → Picture → From File and place it in the page header

section as shown:

2. To format the picture to watermark, right click on the picture and select Format Picture as shown:

192 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

3. Select Washout in the Color field and click OK.

Saudi Aramco Training

5/15/2007

Crystal Reports10

193

The following screen is displayed:

4. Right Click on the picture → Save as Picture on Desktop as shown:

5. From Crystal Reports, Insert Picture → Place it on the Page Header section of the design screen. Your picture should look like this: 194 Saudi Aramco Training

5/15/2007

Crystal Reports10

6. From the design screen, on the gray left area of the Page Header right click → Section Expert.

Saudi Aramco Training

5/15/2007

Crystal Reports10

195

The following dialog box is displayed:

Note:

Users may get to the Section Expert from the design screen by clicking on Section Expert

button.

7. While the Page Header is selected, check in the Underlay Following Section and click OK as shown:

196 Saudi Aramco Training

5/15/2007

Crystal Reports10

Saudi Aramco Training

5/15/2007

Crystal Reports10

197

The following screen is displayed:

8. Click on Preview tab to display the background then click on Design tab. 9. Expand the picture so it can be a background for the full page. 10. save

Note:

Column Headers can be moved to Report Header for better results.

198 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 18: Form Letters Table: Customer Fields: In a text Object in Details A Section: Customer Name, Address1, Address2, City, Region, Postal Code Creating a form letter using a text object The following section shows you how to create a form letter. You are going to use a text object to create a form letter. The form letter you create will be tied to a database table so that each letter will be customized with company information from a different record.

Step by Step Activities:

ƒ

1. Create a Text Object in the Details section with the following information as shown below: ƒ Customer Name: From the Fields Explorer, drag and drop Customer Name to the top left hand corner of the text object rectangle as shown: Customer Name

step 2

Step 1 ƒ

Address 1

Step 3

Saudi Aramco Training

Step 4

5/15/2007

Crystal Reports10

199

ƒ

Address 2

Step 5

Step 6 ƒ

City, Region

Step 9

Step 11

Step 10

Step 12 •

Postal Code

2. To type a letter, we need another details section created. Right-click on the gray area on the left of Details section and select Insert Section Below as shown:

200 Saudi Aramco Training

5/15/2007

Crystal Reports10

Saudi Aramco Training

5/15/2007

Crystal Reports10

201

The following screen is displayed with the new details section:

3. Create a Text box Object in section Details (b) with the following Thank You message: As shown below: Dear : We would like to thank . With total sales of $ , played a significant part in making this last year a successful one. Sincerely,

Note: Note:

all data in { } is drag and drop from the Field ` Explorer list. all customers will receive this message.

4. Create a third Details section (C). See steps 2-3. As shown:

5. Create a Text Object Box in section Details c with an Encouraging Message as follows: Dear : 202 Saudi Aramco Training

5/15/2007

Crystal Reports10

Our sales support team will be contacting . We’re sure that together we can improve upon last year’s sales of $ . Sincerely, As shown:

6. Preview 7. Save Note:

Every customer will receive both messages.

8. Make sure that you send the Thank You message to distributors with sales greater than $25,000. To show Results, we have to create a formula to suppress Sales amount less than 25000 $. To do this, From Details B Section on the gray area : Right click→ Section Expert → To the right of Suppress → click on Formula sign suppress <= 25000 dollars.

to

9. In the formula Editor enter this formula:

10. Save and Close 11. OK Note:

Only customers whose sales are greater than 25000 will receive this message.

12. Repeat this from Section Expert to suppress values greater than 25000 $ 13. Click on Section Expert button

Saudi Aramco Training

5/15/2007

Crystal Reports10

203

The following screen is displayed:

14. Select Details C and to the right of Suppress click on the Formula button. As shown: Note: It would be a good idea to check in the Suppress ( No drill-Down).

204 Saudi Aramco Training

5/15/2007

Crystal Reports10

Note:

It would be a good idea to check in the Suppress ( No drill-Down) box.

15. Enter the following formula in the Formula editor:

16. Save and Close 17. OK Note:

Only customers with sales less than or equal to 25000 will receive this message.

18. Preview the report

Saudi Aramco Training

5/15/2007

Crystal Reports10

205

The following Preview screen is displayed:

19. Go to the Section Expert button and select the Details area. 20. Turn on the New Page After check box to print each customer message on a separate page as shown:

206 Saudi Aramco Training

5/15/2007

Crystal Reports10

22. Click on OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

207

The following screen is displayed:

23. Click OK 24. Preview 25. Note that some Address 2 fields are blank (have a Null value). Use a condition so that the Address 2 field will not print if it is blank: Highlight the Text Object in Details A Section → Right Click → Format Text as shown:

208 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

26. Check in Suppress Embedded Field Blank Lines as shown above. 27. Click OK

Saudi Aramco Training

5/15/2007

Crystal Reports10

209

The following screen is displayed:

Note:

All blank gaps in the address label have disappeared.

28. Save the report

210 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 19: Variables

Table: Product Fields: Product ID, Product Name, Price(SRP)

Objectives

Variables in Formulas: (Crystal syntax) This section describes the key components of variables and shows you how to create variables and assign values to them. Variables overview A variable represents a specific data item, or value, and acts as a placeholder for that value. When a formula encounters a variable, the formula searches for the value of the variable and uses it in the formula. Unlike a constant value, which is fixed and unchanging, a variable can be repeatedly assigned different values. You assign a value to a variable and the variable maintains the value until you later assign a new value. Because of this flexibility, it is necessary for you to declare variables before you use them so that Crystal Reports is aware of them and understands how you intend to use them. Example If you wanted to report on customers by area code, you could create a variable that extracts the area code from a customer fax number. The following is an example of a variable called areaCode: Local StringVar areaCode; areaCode := {Customer.Fax} [1 To 3]; The first line of the variable example is the variable declaration; it gives the variable a Scope (local) name (areaCode) and type ( StringVar). The database field {Customer.Fax} is a String field and [1 To 3] extracts the first three characters from its current value. The variable areaCode is then assigned this value. Note, the declaration lines should end with a semicolon ;

Saudi Aramco Training

5/15/2007

Crystal Reports10

211

Variable Declaration Scope:

Data Type

Variable Name Any name of your choice.

Example: Global

Stringvar

Plocation ;

Global stringVar plocation; plocation:= left(ToText({Product.Product ID}),1); If plocation= "1" then "Chicago" else If plocation= "2" then "New York" else If plocation= "3" then "Vancouver" Else "Others"

The variable scopes available are: •

Global The variable is available to formulas throughout the entire current report.

Global NumberVar Amount; Declares a global variable named Amount that can hold any value of an integer or 212 Saudi Aramco Training

5/15/2007

Crystal Reports10

decimal data type. This variable would be available to formulas throughout the current report

•

Shared The variable can be shared with a subreport as well as the entire current report.

•

Local The variable is specific and can only be used in the formula in which it is defined

Step by Step Activities:

1. The first character of the Product ID determines the location of the product as follows: If Product ID starts with 1 location is Chicago If Product ID starts with 2 location is New York If Product ID starts with 3 location is Vancouver If Product ID starts with 4 location is Riyadh 2. Edit product ID to show no commas. 3. Create a formula using a global variable plocation to test the first character of Product ID: to do so create a variable formula in the Formula Fields under Field Explorer: As shown.

4. The formula should look like the following in the formula editor:

Saudi Aramco Training

5/15/2007

Crystal Reports10

213

Note:

Use this function to obtain a certain number of characters from the left end of a text string. For instance, you could use the Left function to obtain just the area code from the values in a field containing phone numbers.

Example: . Left ({customer.FAX}, 3) Retrieves the area code of each fax number when the first three characters of the Fax field contains the area code.

214 Saudi Aramco Training

5/15/2007

Crystal Reports10

This is how the report looks after preview:

Saudi Aramco Training

5/15/2007

Crystal Reports10

215

Exercise 20: Parameters Table: Customer Fields: Customer Name, Last Year’s Sales Step by Step Activities:

1. From the Field Explorer, Highlight [?] Parameter Fields as shown:

2. Click New

button.

The following screen is displayed:

3. Fill in the Parameter fields as reflected in the dialog box above. 4. In the Parameter Field Name enter SalesAbove 5. Prompting text : Enter Sales Above Amount 216 Saudi Aramco Training

5/15/2007

Crystal Reports10

6. Value type: Number 7. Options: Discrete value 8. Click OK The following Field Explorer has a new parameter added to it.

9. To activate this parameter we have to define the value of this parameter in the Select Expert . 10. Select Last Year’s Sales in the details on the design screen → Select Expert button The following dialog box is displayed:

11. Select the data in the fields as shown above 12. Select is greater than from the first list on the left. 13. From the next field on the right select { ?SalesAbove} then click on OK. 14. Click OK

Saudi Aramco Training

5/15/2007

Crystal Reports10

217

15. Preview 16. Click on Refresh button to preview the report with the following values for the parameter field: 20000, 50000, and 100000 The following dialog box is displayed:

17. Select the Prompt for new parameter values radio button. 18. Click on OK button. 218 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following dialog box is displayed:

19. In the Discrete Value, enter 50000 and click on OK.

Saudi Aramco Training

5/15/2007

Crystal Reports10

219

The following screen is displayed:

20. Add a title to your report such as Customer Listing – sales Over Followed by [?]SalesAbove parameter so that it reflects the change in the parameter value as shown:

21. Preview 22. Save 23. Add a string parameter RegionValue that will allow you to determine which region to display, in addition to the SalesAbove parameter as shown:

220 Saudi Aramco Training

5/15/2007

Crystal Reports10

24. In the Prompting Text above enter : Which Region. 25. In the Value type select String. 26. Click on Default Values

button.

The following dialog box is displayed:

Saudi Aramco Training

5/15/2007

Crystal Reports10

221

27. In the Browse Table field make sure it is Customer by default. 28. In the Browse Field select Region from the list. 29. You can take all regions by their full name or filter using the minimum maximum Length Limit. For the sake of the exercise check in the Length Limit Check box and enter 2 in the Min Length and 2 in the Max Length then click on the Take to the right arrow

button:

The following screen is displayed:

30. Click on OK button. 31. Click on OK button. 32. To activate the [?]RegionValue parameter, it should be defined in the select expert first. 33. Click on Select Expert ?RegionValue as shown:

222 Saudi Aramco Training

button → New → Region → is equal to →

5/15/2007

Crystal Reports10

34. Click on OK button. 35. Adjust the title of the report so that it now displays Customer Listing – Sales over ?SalesAbove for ?RegionValue by dragging and dropping parameters as shown:

36. Preview 37. Refresh 38. Prompt

39. Click on OK button.

Saudi Aramco Training

5/15/2007

Crystal Reports10

223

The following dialog box displays two parameters to use

40. Select ?RegionValue for CA ?SalesAbove 10000 as shown above:

224 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

41. Try Dublin, Eastern Province, Lazio, and Warsaw > 50000 42. Save the report.

Saudi Aramco Training

5/15/2007

Crystal Reports10

225

Exercise 21: Exporting a Crystal Report Export command Use the Export command to export your report in one of several popular word processing, database, and spreadsheet formats, in HTML or ODBC format, or in one of a number of standard data interchange formats. For example, you can export your report in Microsoft Excel format and then open it in Excel as a spreadsheet file. You can export to a disk file, to an application, to a Lotus Notes database, to an Exchange Folder, or to email. Step by Step Activities 1. Open any existing crystal report. 2. Select the Export Note:

button from the Standard toolbar or click File Æ Export You have several formats to choose from such as PDF

, CSV The following dialog box is displayed:

, Excel

, Text

, XML

etc.

3. Select a format type from the Format drop-down list Choose Text: Note: The are a lot of format options . Crystal Reports provides you with many different export format types. They include: •

Adobe Format (PDF)

•

Crystal Reports (RPT)

•

HTML 3.2

•

HTML 4.0

226 Saudi Aramco Training

5/15/2007

Crystal Reports10

•

MS® Excel 97-2000

•

MS® Excel 97-2000 (Data only)

•

MS® Word

•

ODBC

•

Record style (columns no spaces)

•

Record style (columns with spaces)

•

Report Definition

•

Rich Text Format

•

Separated Values (CSV)

•

Tab-separated text

•

Text

•

XML

.

4. Select a destination type from the Destination drop-down list Choose Disk File. Note:

There a lot of possible destinations for your report. Select the destination you want from this list. You can export to: •

An application

•

A disk file

•

An Exchange folder

•

Lotus Notes database

•

Microsoft Mail

Saudi Aramco Training

5/15/2007

Crystal Reports10

227

5. Click OK 6. Enter any additional information you are prompted for and save

228 Saudi Aramco Training

5/15/2007

Crystal Reports10

Exercise 22: Crystal Enterprise Crystal Enterprise (Optional Demo) Demo: (Because of access restriction to Crystal Enterprise)

Crystal Enterprise (CE) is a web-based report management tool that is used to manage the access and delivery of Crystal Reports. Crystal Enterprise features include: • • • •

Folder management so all users can share reports across the enterprise Web administration to manage viewing, accessing and running reports Publishing of reports using the Report Publishing Wizard Scheduling of reports

Activities: saving a report on the Crystal Enterprise Server a. After designing the report on the Crystal reports design go to File → Save as, and → select Enterprise from the Save as dialog box as shown below: b. Select your folder to save the report in.

c. In the File name, enter the report name as shown: Saudi Aramco Training

5/15/2007

Crystal Reports10

229

d. Click on the Save button

230 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

e. To display the report from the Enterprise server, Click on the Internet explorer button Start the Internet Explorer and access the website http://crystald.aramco.com.sa ( Development environment).

Saudi Aramco Training

5/15/2007

Crystal Reports10

231

The following Crystal Enterprise Report Solution web page is displayed:

f. Click on the Management Console

232 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following Log On screen is displayed:

g. Enter use Internet password and ensure the Authentication Type is LDAP h. To display the report in different format: HTML or ActiveX, Advanced HTML or Java click on Preferences tab.

Saudi Aramco Training

5/15/2007

Crystal Reports10

233

The following screen is displayed:

i.

From the Viewer field, select DHTML the click on OK.

234 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

j.

Click on Folders icon as shown below:

k. The following screen is displayed with all the folders you are authorized to see.

Saudi Aramco Training

5/15/2007

Crystal Reports10

235

l.

Click on your user your folder. In the example above it is the instructor’s ID (allanamk).

236 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following screen is displayed:

m. Click on the report name to display: For the purpose of this Exercise I will click on : My Second Web Report

Saudi Aramco Training

5/15/2007

Crystal Reports10

237

The following screen is displayed:

n.

Click on Preview button

238 Saudi Aramco Training

5/15/2007

Crystal Reports10

The following Crystal Report’s Viewer screen is displayed:

o. Click on Go to Next Page

Saudi Aramco Training

button

5/15/2007

Crystal Reports10

239

The following screen is displayed:

p.

Click on the URL address and right click to copy. Follow the menu path to send the link by Email: File → Send → Link by email

Note:

This is a development environment Production access is through

http://crystal.aramco.com.sa/ as shown below:

240 Saudi Aramco Training

5/15/2007

Crystal Reports10

http://reporter.aramco.com.sa as shown below

- Click on Management Console. -Enter your Internet ID and password to logon

Thank You for Your Patience Instructor’s tel 8741519 [email protected]

Saudi Aramco Training

5/15/2007

Crystal Reports10

241