Trademarks Shoptech Software, the Shoptech Software logo, E2 Shop System and the E2 Shop System logo are trademarks or registered trademarks of Shoptech Software. All other names mentioned herein may be trademarks of their respective owners. Product specifications and program conditions are subject to change without notice.
Acknowledgements: Shoptech Software acknowledges that portions of the content included in this manual were excerpted from the following source: Business Objects Crystal Reports XI R1/R2 Report Design I Fundamentals of Reports Design - Learner's Guide RD110R2 Revision A, Vancouver, British Columbia, Canada, April 2006 Business Objects Crystal Reports XI R1/R2 Report Design II Business Reporting Solutions - Learner's Guide RD210R2 Revision A, Vancouver, British Columbia, Canada, May 2006
i
Table of Contents Introduction
1
What is E2 Shop System?
1
What is Crystal Reports?
1
Purpose of This Course
2
Prerequisites
2
Computer Setup
2
Hardware
2
Software
2
Course Objectives
2
Lesson 1: E2 Shop System Database Concepts
4
Defining database concepts
4
Introduction
4
Database Glossary
4
E2 Shop System Databases
5
Blsdata.mdb or E2 Database on SQL Server
5
ReportDB.mdb
5
SQL Statements
5
Advanced Searches and Hot Spots
7
Introduction
7
Searching in the E2 Shop System
7
Advanced Search Wizard
8
Accessing the Standard Hot Spots
11
Running a Standard Hot Spot
14
Create Your Own Hot Spot
18
Advanced Search, Hot Spot, or Report?
21
Exercise 1
22
Lesson 2: Planning a Report
23
Planning and developing a report prototype
23
Introduction
23
Report prototype layouts
23
Developing a report prototype
23
Report Requirement Checklist
23
Lesson 3: Creating a Report
26
Creating a Report
26
Before you Begin
26 ii
Table of Contents The Design Button
27
Introduction
29
Methods of Creating a Report
30
Accessing the Data and Adding Tables
31
The Design Environment -The Report Artist's Palette
32
Toolbars
33
Crystal Report Options
33
Inserting Objects on a Report
34
Previewing the Report
35
Save the Report
35
Exercise 2
36
Lesson 4: Customizing a Report
37
Positioning Objects
37
Sizing Objects and Sections
39
Sizing Objects
39
Sizing Sections
39
Formatting objects
40
Common tab
40
Border tab
41
Font tab
42
Number tab
42
Adding Text Objects
43
Grouping Data in the Report
44
Sorting the Report
45
Adding Summary Fields
46
Adding Special Fields
48
Exercise 3
51
Lesson 5: Manipulating Data and Running a report in E2SS Adding Formulae
52 52
To Create a Formula
52
Conditional Formatting
54
Selecting Records
55
Parameters (Prompts)
58
Creating a Parameter
58
Running Your Report From Within E2 Shop System iii
60
Table of Contents Creating a Hot Spot
60
Exercise 4
62
Lesson 6: Modifying existing E2 Shop System Reports
63
Introduction
63
Packing List
64
More on the ReportDB.mdb
64
Linking additional tables
66
Invoice
69 Embedding a logo
69
Job Label
70
Modify the Job Label report Job Traveler
70 72
Sub-reports
72
Bar Codes
73
Appendix A: Using Three Tables in Advanced Searches...
74
Apppendix B: Crystal Reports Support Guidelines
75
When Modifying E2 Shop System Reports
75
To Access Crystal Reports Knowledgebase
76
Appendix C: Adding a Table Link to Crystal Report (MS Access)
77
Appendix D: Adding a Table Link to a Crystal Report (SQL Server)
81
Appendix F: Label Types
86
iv
Advanced Database and Crystal Reports
Introduction What is E2 Shop System? The E2 Shop System from Shoptech Software enables shop owners to effectively control all aspects of shop activity, while simultaneously and effectively managing profitability. Whether it's your front office or shop floor that you're concerned about, the fully integrated E2 Shop System takes the uncertainty out of shop management. Development of the E2 Shop System dates back to 1984. Our shop specific software was developed from an actual shop experience, real-life shop situations, "shop know how" and shop needs. Both shop owners and shop users participated in the creation of this unique software. Today, shop owners and shop users continue to play a vital role in transforming suggestions into product enhancements. The E2 Shop System includes features found in no other software product of its kind. The features we have included make our system incredibly comprehensive, yet user-friendly. You will have the information you need in a relevant, consistent, and informative format in a matter of seconds. Because our system is 100% Windows-based, it is incredibly easy to learn and use. "Point and Click" convenience appears throughout the system, virtually eliminating the need to rely on a keyboard. The E2 Shop System gives you the power, flexibility and speed to access all the information you need to manage your shop. . . profitably.
What is Crystal Reports? Crystal Reports® XI from Business Objects, allows you to access, format, and deliver information to drive better decision making - with the flexibility and power necessary to provide your internal and external users with the information they need, when they need it, while minimizing IT overhead. A proven, award-winning reporting standard, Crystal Reports XI addresses all aspects of the reporting process, allowing you to: = Transform any data into highly formatted reports = Securely deliver reports over the web for end-user interaction = Manage and integrate reports into your environment Crystal Reports XI delivers powerful authoring, extensive distribution, and flexible integration. It is the most proven reporting tool; with billions of Crystal reports (.rpt files) used in Fortune 1000 companies around the world. Crystal Reports allows you to create flexible, feature-rich reports and then integrate them into web and Windows applications. You can access and format data, and embed reporting into Java, .NET, and COM applications using a comprehensive set of software developer kits (SDKs). Crystal Reports is the de facto reporting standard sold by over 500 independent software vendors. And it is embedded in leading software from Microsoft, SAP, Borland, BEA, PeopleSoft, and IBM. 1 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Purpose of This Course We encourage you to take a class presented by Business Objects to learn how to use Crystal Reports and all of its various features since you are able to use Crystal to do more than just report against your E2 database. For more information about these classes please see the Business Objects website (www.businessobjects.com). These classes use the Xtreme MS Access database that installs with Crystal Repots (C:\Program Files\Business Objects\Crystal Reports 11\Samples\en\Databases\Xtreme.mdb). While this database is for a manufacturing company, Xtreme Mountain Bikes, it is nowhere near as complex as the database used by E2. Therefore we felt that it is important for our customers to not only learn about Crystal, but even more importantly using Crystal with E2 Shop System. This way, an instructor familiar with E2 and the underlying database can lead you through the course and answer your questions.
Prerequisites = E2 Shop System Working Knowledge class or other recorded web based training offered by Shoptech Software = Experience with Microsoft Windows operating system and Windows based applications = An understanding of Relational Database Management Systems (RDBMS) is helpful, but not necessary
Computer Setup Hardware The minimum hardware requirements for Crystal: = = = =
P3 700MHZ 512 MB RAM, 1 GB Recommended 3 GB available hard drive space CD-ROM or DVD drive
Software The software required for this course: = An operating system: Microsoft 2000, 2003, XP = E2 Shop System 7.1 (User Id - Supervisor and Password - Pass) = Crystal Reports XI Release 2 Professional Edition
Course Objectives During this Advanced Database/Crystal Reports course, users will learn how data is stored in the E2 Shop System and the basics of working with Crystal Reports version XI. Through lecture, discussion and class participation students should expect to learn: = E2 Shop System Database concepts = Advanced Searches and Hot Spots = Planning a report 2 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = = = = = =
Creating a report Adding data to a report Organizing and formatting a report Creating basic formulas Applying conditional reporting Modifying existing standard reports provided with the E2 Shop System
3 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Lesson 1: E2 Shop System Database Concepts Before you begin writing reports against the E2 Shop System database, it is helpful to have a good understanding of database concepts and how the E2 Shop System data is stored. In this lesson you will learn about: = Defining database concepts = Advanced Searches and Hot Spots
Defining database concepts Introduction You need to know where your information is stored and in what form before you can work out your reporting requirements.
Database Glossary By understanding basic database concepts, you will be better prepared to understand what is needed when planning a report.
Term
Definition
Database
A database is a bank of data that provides the information in a report.
Table
A table is a "container" of information that can hold many pieces of data, such as records. Tables are stored in databases. It is composed of columns (fields) and rows (records).
Record
In a database, a record is a complete unit of related information. Each record is made up of one or more fields, and each field can hold one piece of data (known as a value).
Field
A field is the basic building block of a record. A field can be empty (NULL) or contain a value.
Relational database
A relational database contains tables that can be linked together based on the relationship between the fields in two or more tables.
Link
A link is a field that is common to two or more tables and that serves as a connecting point between those two tables. Linking means that records are matched up from one database with those from the other(s) and ensure that all the data in each row of multiple tables refers to the same customer (transaction, invoice, and so on) on a report.
Report
A report is an organized presentation of data. A comprehensive, customized, and attractive report can provide management with the information it needs to run an organization effectively.
4 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
E2 Shop System Databases There are 2 databases that can be used to write queries and reports against the things stored within E2.
Blsdata.mdb or E2 Database on SQL Server This database is your live (production) database. It houses your Customers, Vendors, Parts, Quotes, Orders, Invoices, etc. When a report is run from within E2, this database is queried. In this course we will learn how to create new queries and reports using this database, as well as linking tables in this database into existing reports.
ReportDB.mdb The standard reports provided with the E2 Shop System use the ReportDB.mdb database as a template. In the standard reports, the E2 user, prior to actually running the report, sets all filtering options. When the user clicks the Print Single … or Generate Report button, the E2 application gathers the data required for the report and in some cases makes calculations using that data. This report data is saved in a MS Access database that is then temporarily stored in the C:\Documents and Settings\\Local Settings\Temp folder. This data only exists while the report is running. The report itself was written using the template database called ReportDB.mdb. This database contains a small amount of Sample data and can be opened using MS Access to view its contents. Your company's data will never be visible in this database. Therefore it's only purpose is to be used when designing and previewing the reports you modify. If you create a report from scratch, you will not be able to use the ReportDB.mdb. In some cases you may find fields additional to the ones already on the report in tables that you can use from the ReportDB.mdb, but do not be surprised if you add a field from the ReportDB.mdb and do not see any results when you run the report because there may be abandoned fields in the table.
SQL Statements SQL is an acronym for the term "Structured Query Language". Advanced Searches, Hot Spots, and even Crystal Reports use a form of SQL to gather information from the required database. Most SQL statements will being with the words SELECT, UPDATE, INSERT, or DELETE. The underlying programming of the E2 Shop System interface actually uses these types of statements to add new customers, edit orders, or delete invoices as you click the buttons or type values into the fields you see on the screen. An example of a SQL statement that could be run in the E2 Shop System is: SELECT * FROM CUSTCODE All SQL statements must contain 1 vital element; the table(s) that contain the required information. The name of the table usually follows the word FROM (the sections of a SQL statement are often referred to as clauses); in this case it is the CUSTCODE table. This table stores the majority of the information pertaining to your customers. The SELECT clause contains the columns to be included in the result set. In this case the asterisk (*) indicates that we want to return all
5 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports of the fields (columns) in the table. Running this statement will return something like the following:
All 61 columns in this table and all of the rows are returned in the results. If we indicate one or more of the field names in the SELECT clause, then only the values in the stated column(s) is returned. For example; SELECT CUSTCODE, CUSTNAME, SALESID, CURRENCYCODE FROM CUSTCODE returns:
By adding an ORDER BY clause, you can control the order in which the results are displayed. An example of this would be, SELECT CUSTCODE, CUSTNAME, SALESID, CURRENCYCODE FROM CUSTCODE ORDER BY SALESID ASC. This will return the following:
The results are no longer in alphabetical order, but in ascending order by the Salesperson's id.
6 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports You can further control the results of your query by filtering the results so that only the desired records are returned. This is achieved by adding a WHERE clause to the SQL statement. For example, the statement SELECT CUSTCODE, CUSTNAME, SALESID, CURRENCYCODE FROM CUSTCODE WHERE CURRENCYCODE = 'CANADA' ORDER BY SALESID ASC returns:
Now the results display the requested fields filtered by the customers whose currency code is Canada in ascending order by the Salesperson's id.
Advanced Searches and Hot Spots Introduction Advanced Searches and Hot Spots are queries that you can write to view the data stored in the E2 database. Learning how to write the queries will help you learn where data is stored and give you greater understanding of how E2 and Crystal Reports can work together.
Searching in the E2 Shop System First let's look at the basic searching functionality programmed into the E2 Shop System.
1 2 3 4
Open the E2 Shop System from the icon on your desktop. Log in as Supervisor with the password of PASS. Select the ABC Company from the list of available companies. Go to Orders | Order Entry.
5 At the bottom of this window, you can see that there are some standard search options that can be selected by choosing one of the option buttons like Part Number or Job Notes, etc. By default All and the Order Status of Open are selected for you.
7 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
6 By choosing one of the option buttons you can filter your search results to the orders that meet the desired criteria. Let's select the Sales ID option. Click the Search button.
7 The window that opens is a prompt. It allows you to enter a Sales ID to limit your search results. Enter HOUSE into this field. Click OK.
8 The results returned are only the open orders that have the Sales ID of House.
Advanced Search Wizard For both Advanced Searches and Hot Spots the E2 Shop System includes a tool called a wizard that can be used to build your SQL statements without requiring you to know how to write SQL. The wizard has 2 different names depending on how it is accessed from Advanced Searches or Hot Spots, but it functions the same in each. Besides the standard searches you can also perform Advanced Searches by selecting the Advanced Search option. When selected, the drop down list below the option will open to show any existing Advanced Searches and the selection. Let's create an Advanced Search to show Orders entered by a specific user.
1 Select the Advanced Search option and then select from the menu.
8 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
2 The Advanced Search Wizard displays.
3 There are 4 main areas to this window. The Available Tables drop down lists all of the searchable tables. Because we are searching for Orders, the Orders table is listed. If we were searching for Shipments, we might see DelTicket there instead. Leave Orders in this field and press the Tab key. This will populate some of the other lists for you.
4 The Related Tables area displays tables that also contain information relating to your Orders. The Comments column describes the information that is stored in each table. The Available Fields area contains a list of all of the fields you can use to build your search. Unlike a basic SQL statement discussed earlier, the columns displayed in the results are not based on your search, but on programming. This means that the fields we select will be used for linking to other tables, filtering records, and sorting the results. This will be different when we get to Hot Spots.
5 From the list of Available Fields, select Customer Code and then click the single arrow pointing to the right to move this field into the Selected Fields area.
6 Next, select Entered By UserID and click the single arrow again.
9 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
7 This is all we will add at this time. Click the Next button.
8 This window allows you to set filtering criteria. Click the Next button if you do not want to filter the records. To set filtering criteria first pick the field you want to filter by. Use the Available Criteria drop down to choose the type of comparison you want to make; like Equal to or Greater Than, etc. Then, set the value that you want the query to evaluate. This can be a values selected from the table, hard coded into the search, or typed in by the user when the search is executed.
9 From the Criteria Fields drop down list, select Orders.Entered By UserID.
10 In the Available Criteria, select Equal to. 11 Leave the criteria applied as And. 12 Select the option button by Prompt the user for a search value so that you can enter the user id that was used to create the order.
13 This window shows you the prompt that you will see when you execute the search. You can change this text if you wish. Then click OK.
14 Click Add Criteria to include this filtering. You must click the Add Criteria button after setting each thing that you will filter out.
10 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
15 Click Next.
16 Use this window to set your sorting for the search, give your search a name and description and make any final adjustment to the SQL statement that will run. In the Sorting Options drop down list, select CustomerCode.
17 Enter the text ORDER CREATOR in the "What code do you want for your query?" field.
18 Type Search to find Orders entered by a specific user in the "What description do you want for your query?" field. Notice how similar the SQL statement here is to the ones we discussed earlier in this manual. You can use the check box to copy this query to ALL users if you wish.
19 Click Finish. 20 Your search will now run and the prompt for the User Id should appear. Type GREG in the field and then click OK.
21 Edit a few of the existing orders to verify that Greg was the user that created the order.
22 Try the Advanced Search again. This time enter SUPERVISOR and then click OK. You should see a fewer number of orders in the list than when you ran it for Greg.
Accessing the Standard Hot Spots The E2 Shop System includes 38 built in Hot Spots. For most users Hot Spots can be accessed from the Hot Spots menu or by pressing the F3 key on the keyboard from anywhere in the E2 Shop System or Quick View. If the list of Hot Spots is empty or does not contain the Hot Spot you want to run then you will need to set them up or have your E2 Shop System administrator do it for you. A user that has security access to the File | System Maintenance | User Maintenance area of the E2 Shop System will be able to interact with and create new hot spots.
1 2 3 4
Log into the E2 Shop System as Supervisor. Go to File | System Maintenance | User Maintenance. Edit the user Supervisor. In the grid at the bottom of the screen, select a column in the row containing the ABC Company. 11 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
5 Click the Security button. 6 Verify that the Supervisor user id has to access the User Maintenance area.
7 Click OK. 8 There is a Hot Spots button here where you can create Hot Spots and give other users access to them. Click on the Hot Spots button.
There’s already an entry in this grid. The Hot Spot Type for this entry indicates it’s an Advanced Search. This is the Search we created in the previous section. By clicking twice in the SQL Statement/Application Path column you could manually edit this Search to add additional filtering or sorting if necessary. You could also copy this search to another user if required. On this window you could also create a new Hot Spot manually by entering a code in the Hot Spot column, description in the Description column, and the SQL statement in the SQL Statement/Application Path column. This should only be attempted by someone familiar with writing SQL statements or with the assistance of Shoptech Software support.
9 For now, let's set up access to the 38 Standard Hot Spots for the Supervisor user id. Make sure that the From A User ID option is selected.
12 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
10 Click the Copy button.
11 The pane on the left contains a list of your active User IDs, plus an additional ID of DEFAULT. This ID stores the 38 standard Hot Spots and cannot be edited or removed. While holding down the Shift key, highlight the first (EMPLCLOCKEDIN) and the last Hot Spot (TRIGGERS) to select all of the Hot Spots in the list. You can use the CTRL key to highlight individual Hot Spots and only copy a few of the Hot Spots to the user.
12 Click OK.
13 14 15 16
Select Yes to copy all of these Hot Spots to the Supervisor User ID. Click OK to exit the Hot Spots Maintenance window. Click OK again to close the Update User window. Click Close to close the User Maintenance window. Repeat these steps to copy the Hot Spots to other users.
13 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Running a Standard Hot Spot Now that you have given yourself access to the standard Hot Spots, there are 2 ways to actually run these queries.
1 Click the Hot Spots menu to run some of the Hot Spots available to your User ID.
2 From this menu, select All Currently Open Jobs to run the query.
The results window in this case will show you the open Job Number, the part being made, its description, the due date of the job and any comments. From here you can export the results to a CSV file, which would be editable in Microsoft Excel; or HTML and XML, which are both web page formats. You can Email the results to a user in the E2 Shop System or other external email address. You can print the results to a printer or to a PDF writer if one is installed. You can change the appearance of the results window. Changes you make here will be remembered so the next time you run the Hot Spot the columns will appear the same.
14 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports To change the width of a column:
1 Position your mouse on the line that separates the column heading for Part Description and Due Date.
2 You cursor should become a two-headed arrow. Hold down the left mouse button to drag the line to the left or right.
3 When you have completed the adjustment, release the mouse. 4 Click the Close button. 5 Run the All Currently Open Jobs Hot Spot again from the Hot Spots menu to see the change you have made.
6 You can also right click on the selected column and choose Autosize Column. The column width will automatically change to the width of the widest value in the results set. Use the Hide Column selection to make the column not appear in the results. To change a caption of a column heading:
1 Right click on the column heading for Part Number.
2 Select Edit Column Heading from the menu.
3 Change the text Part Number to Part No. in the "Enter a new caption…" field.
4 Click OK. The column heading is now Part No. instead of Part Number.
15 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports To move a column:
1 Run the Past Due Jobs hot spot.
2 You can reorder the columns by holding down the left mouse button with a column selected and then dragging the column to a new location. The red arrows indicate where the column will be positioned when you release the mouse. Move the Due Date in front of Comments.
3 Notice the double line that is between the Part Description and the Quantity Ordered columns. This line, called a split, allows you to use the thumb tab at the bottom of the grid to see the column(s) to the far left in this case without causing the columns to the right of the split to move off the window.
4 To move a column to the other side of the split, select the Job Number column and then right click the column heading. Select Move Column to Other Split.
5 You may have to click the thumb tab at the bottom of the right side of the split to see the change.
16 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports To change Advanced Settings:
1 Right click on a column heading. 2 Select Advanced Settings from the menu.
Column Formatting: This option allows you to change the format of the values in the column as Date, Date Time, Currency, Whole Number, Custom or No Format. Column Justification: Use this option to change the position of the values within the column to be left justified, centered, or right justified. Column Sorting: Change the sorting with this option to sort all of the results in the entire grid based on this column in Ascending or Descending order. Column Totals: This option allows you to add the values (numeric) in a column and display the total on the last row of the grid. Column Merging: This option allows you to combine cells in a column that are next to each other and contain the same values together. The Due Date column is merged already. Column Jumps: By setting a jump, you can select a cell in the grid and use the Details button to "jump" to the specified area of QuickView. Select one of the job numbers in the grid and click the Details button to access Job Status for that specific job. The Hot Spots main menu list gives you access the first 30 Hot Spots in your entire list of available Hot Spots. The menu items displayed in this list are the Descriptions of each Hot Spot. This list will only hold 30 Hot Spots. If a Hot Spot you create is not in this list and you would like for it to appear, then you should modify the Hot Spot code so that it is listed at the beginning of the grid.
17 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports The second way to run a Hot Spot, especially if the one you want does not display in the menu, is to select All Hot Spots from the Hot Spots menu.
The Hot Spots window will display all Hot Spots that return values, require user entered prompts, or point to applications. Queries that return no results will not be in the list. To run one, just select it and click the Details button or if you want to email the results, click the Email button.
Create Your Own Hot Spot Shoptech has included a substantial number of common queries you can run from Hot Spots. However, you may find that you need something we have not provided or that you need to modify or customize a query we did provide. You can click on the Edit Hot Spots button to create new or modify existing Hot Spots. Scenario: Recently your company has had an alarming number of Customer Returns. You are worried that you are losing a great deal of money and would like to know how much these rework jobs have cost you over a certain period of time. You decide that a Hot Spot would be a good way to gather this information. You would like to see the customer, order number, original job number, rework job number, quantity to rework, the status of the job, and unit price this year to date.
1 Go to Hot Spots | All Hot Spots and click the Edit Hot Spots button.
18 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
2 If you know the exact SQL statement you want to use, scroll to an empty line, enter a code, description, and the SQL statement for your Hot Spot manually. If you do not know the SQL statement, click the New Hot Spot button.
3 The Query Wizard works just like the Advanced Search Wizard we used to create our Advanced Search except that this time we will need to decide which table to start with since the Available Tables field is not populated for us. We need information from the order so that would be a good table for a start. Select Orders in the Available Tables drop-down list and press the Tab key.
4 In our scenario we said we want to see the order number and the customer. Select OrderNumber and CustomerCode and then click the single arrow pointing to the right to move these fields to the Selected Fields pane.
5 We also want to see fields from the OrderDet table. Use the Available Tables again to select OrderDet and then press the Tab key.
6 From the list of Available Fields, select Due Date, Job Number, Master Job Number, Part Number, Quantity Ordered, Status, Unit Price, and Work Code. Then click Next.
19 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
7 Since we are using two tables in our Hot Spot query, we must "Join" them. In other words, we need to tell E2 what data the tables have in common. In this case the Orders table and the OrderDet table have the Order Number in common. Find the Order Number field in both panes and make sure they are highlighted. Click the button and then Next.
8 In our scenario, we also stated we want to see Rework Jobs from the beginning of the year. These are the criteria we want to place on our Query. In the Criteria Fields drop down list select OrderDet.WorkCode. A Rework Job in the E2 Shop System is indicated by jobs that have the work code of "REWORK". So, we only want to see jobs that have the work code of "REWORK". In the Available Criteria drop down, select Equal To. In the Search for specific value field select REWORK and then click the Add Criteria button.
9 We also want to limit our results to jobs that are or were due this year. Repeat step 8, but select OrderDet.DueDate, greater than or equal to, and enter the date January 01 of this year as MM/DD/YY. Click the Add Criteria button.
10 You could continue to add additional criteria as hard values or even as prompts. We do not require any other criteria so click the Next button.
11 On this window, let's set our sorting options to Order Number. 12 Enter the code REWORK COSTS and the description "Rework job costs from Jan this year".
20 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
13 You should now see the entire SQL statement. If necessary you could make changes to the statement here. The order of the fields in the SELECT clause will be the order of the columns in the result window when you run the Hot Spot. You can use the Copy check box if you want to copy this query to all of your other users. For now click Finish.
14 On the Hot Spots Maintenance window, locate the REWORK COSTS hot spot we just created and then highlight one of the cells within this row. Click the Test Hot Spot button. If the SQL statement runs successfully, you should get the message that the query ran successfully. It may or may not return any results depending on the query and the data in the database.
15 There is one more field our scenario required. The OrderDet table does not contain a field that stores the total price for the job. We will need to edit the query manually to add a field that will show the quantity multiplied by the price. Double click in the SQL Statement\Application path field to make the field editable (yellow). In the SELECT clause after "OrderDet.UnitPrice," enter the following text: (ORDERDET.QTYORDERED * ORDERDET.UNITPRICE) AS TotalCost. You must add this manually because the wizard will not allow you to perform calculations.
16 Tab off the field to ensure the changes are committed to the database. Test the Hot Spot again to make sure that it runs successfully.
17 Click OK. 18 From the list of Hot Spots, locate and select the Rework job costs from Jan this year Hot Spot and then click the Details button.
Advanced Search, Hot Spot, or Report? How do you determine which type of query to create? You will need to answer the question; "What do I want to do with the results?" If you need to be able to interact with the results, but not print them, then an Advanced Search would be appropriate. You could take a screen shot of the results or you could multi-tag the results and click the print button, but the primary reason to create an Advanced Search is because you can edit any of the items in the result set. 21 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports Use a Hot Spot if you want to quickly query your database and print or email the resulting list. You can create jumps to Quick View from the results, but cannot edit the results in the E2 Shop System. When printed or exported, the results have little to no formatting, but can be easily modified using Word, Excel, or other 3rd party programs. Create a Crystal report when formatting of the results is required. Because of its friendly interface, Crystal Reports can help when you need data from many tables. The Query Wizards only allow you to join two tables. There are also many functions in Crystal Reports that will allow you to simply create complex formulas for evaluating the results.
Exercise 1 Your Scenario: To go along with finding out how much all the rework jobs have cost you, you want to find out how much scrap is getting reported. You know that you can run the Time Tracking Summary report to get some information about the scrap reported by your employees. However, at a glance you would like to find out which employees are scrapping parts over a period of time. Create a Hot Spot from the TimeTicketDet table that shows the employee's code and name, the job number, number of pieces scrapped, step number, reason code, and the work center. To limit the results to a meaningful number of records, prompt the user for a date range.
Remember that you do not want to see every time ticket record, just the one's with scrapped parts. (TimeTicketDet.PiecesScrapped >0)
The result should look something like the picture below:
22 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Lesson 2: Planning a Report You need to understand the basic concepts of report design, so you can plan what information you want to include on your report. In this lesson you will learn about: = Planning and developing a report prototype
Planning and developing a report prototype Introduction As a starting point in the report design process, you should outline the information you want the report to provide. After completing this unit, you will be able to: = Determine the layout and content of a report = Develop a prototype of a report
Report prototype layouts By taking a systems-oriented approach to reporting and developing a prototype, you will produce clearer and more readable reports. With the paper prototype in hand, you can put your full effort into learning and using the program, rather than trying to design and learn at the same time. For more details on the report planning process, see the Crystal Reports User's Guide.
Developing a report prototype To develop a report prototype gather the information on which the report will be based. This stage of the report planning process, referred to as requirements gathering, consists of the report designer asking the report requestor a series of targeted questions, sometimes in the form of a checklist. After determining the outline of the report request, you develop the prototype by: = Sketching the report on a blank piece of paper, usually the size the report will be printed on. = Filling in a form. Sketching the report helps if you need to see the report visually; however, a form with all the categories listed enables you to develop the prototype without missing a section. As well, the form is more professional than a quicklysketched visual and can be used as a sign-off point in a report request.
Report Requirement Checklist You can use the following checklist as a template for creating your own form. = Define the overall purpose of the report. The purpose statement helps you focus on your primary needs, and it gives the report both a staring point and a goal. = Define the readers of the report. Plan the report so it includes the information each user is looking for. 23 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = Define the data used in the report. What type of data source will you need to connect to? = Determine the report title. Write out a working title for the report. You may decide to change it later, but at least you will have a title to use when creating the prototype report. = Define header and footer identifying information. The body should contain all the data needed to fulfill the statement of purpose you wrote for the report. It should also contain all of the data needed by the various users that you have identified. = Determine if the data is organized into groups. How? By customer? By date? By hierarchy? Or by other criteria? = Determine if the data is sorted based on record or group values. Data can be sorted based on a specific record or on a group. = Determine if the data exists or if it needs to be calculated. Some report information can be drawn directly from data fields; other information will have to be calculated based on data field values. = Determine if the report is to contain only specific records or groups. You can base a report on all records in a given database, or on a limited set of records from the database. = Determine if the data needs to be summarized. Do you want to total, average, count, or determine the maximum or minimum value included in all the values in any column on the report? Do you want to include a grand total at the bottom of a selected column? = Define how to identify key information through formatting options. What formatting options can be used to highlight key data on a report?
24 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
25 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Lesson 3: Creating a Report Creating a basic report using Crystal Reports will help you turn raw data into usable information. In this lesson you will learn about: = = = = = =
Creating a report Accessing the data and Adding tables The design environment - the report artist's palette Inserting database objects on a report Previewing a report Saving a report
Creating a Report Scenario You have been asked to create a report that shows which parts were sold the most by your company. This information is available in the database used with the E2 Shop System. The report should include a title, company logo, the print date labeled at the top of the report, and the page number labeled at the bottom of the report. We will need to see the Order Number, Part Number, Part Description, Quantity, and the Sale Price. We would like to be able to limit the results of the report to only show the Top 10 movers within a certain time frame. The parts should only be top-level assemblies.
Before you Begin The E2 Shop System comes with 475 reports that can be run from various areas of the application. It is very possible that E2 already includes the report you want to create. To satisfy the scenario above there are 2 existing reports that could be run that would give similar results. Log into the E2 Shop System with the Supervisor User ID and “pass” as the password.
26 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports Go to Orders | Order Entry Summary and select the Part Number Breakdown report. Generate this report to the screen.
E2SS V 7.1 uses the Crystal XI report viewer, which allows users to interact with the report when it is onscreen. Notice that when initially generated, the report is sorted by Part Number. The data displayed is based on the orders that have been entered.
The Design Button Use the Design button to change the sorting and printing options on this report:
1 Click on the Design button. You will see a list of the Fields available in the report on the left, and the fields on which the report is sorted on the right.
2 Using the arrow buttons between the two fields, remove the PartNo field from the list on the right and move it back to the list on the left.
3 Select the Qty field from the list on the left and move it to the list on the right. 27 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
4 Highlight the Qty field in the list on the right and then set the Sort Order drop down list to Descending. Double click the Next button and then click Finish.
The report is now sorted by quantity, so that the highest quantity is at the top and the least at the bottom. Next, let's run the Sales Summary report. This data comes from the invoices you’ve entered.
28 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports Notice that this report sorts by Total Sales. It also includes parts that are not actually in Estimation or Inventory Items like UPDATE and TEST. As you can see you may be able to get the information you need without creating a new report. We will look at modifying existing reports in a later lesson.
Introduction In order to create a report, you must first connect to the database that contains the data you need. From our scenario, we know we want our report to show parts, quantities, and prices. From the Part Breakdown reports we just ran, the Order Entry Summary and Sales Summary, we now know that we could get this type of information from Orders or Invoices depending on who asked us to write the report, the lead engineer or the bookkeeper. We are going to use Orders so, let’s take a look at one!
1 Go to Orders | Order Entry. 2 In the Order Number field, type 4440 and then click the Edit button.
3 In E2, an Order is made up of related information stored across several tables; Orders, OrderDet, OrderRouting, Releases, JobReq, JobMaterials, OrderFiles, and ContactNotes, to name a few. It is the report writer's job to know which tables store the information needed in the report. In this case we will use the OrderDet table, because this table stores the job related information as you see it in the grid, namely Part Number(s), Qty(s). Ordered, and Unit Price(s).
29 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Methods of Creating a Report When you launch Crystal Reports, the Start Page displays.
The Start Page includes links to recent reports you've opened, report creation methods, the Help system, a search function, sample reports, and an update service. You can create a new report with Crystal Reports using these methods: = Use one of the Report Wizards provided. A user who is unfamiliar with Crystal Reports, or someone who is familiar but is rushed, will find it easier to design a report using one of the Wizards, sometimes referred to as Experts. You step through the report building process of a specific type of report when using an Expert. = Construct the report manually, piece by piece. You can custom design a report using this method by starting with a blank report and building the elements of the report from scratch. = Create a report from another report. Another quick method to build a report is to create it from an existing report. This existing report can be used as a template since it may already contain much of the information and formatting you need. After you open the report you want to use as a template, save it to a new file using the Save As command on the File menu. The Resources area contains dynamic content from Business Objects.
30 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports You can easily turn off the Resources section, by deselecting the Show Online Resources checkbox in the Start Page section. After you select the type of report you want to build from the Start Page, we will start with a blank report, choose the data source you will use to create the report.
Accessing the Data and Adding Tables While you can use the Standard Report Wizard link to start your report, we will start with a Blank Report so that we can build the report as we go.
1 Click the Blank Report link from the Start Page. 2 Next we need to select the data source we are going to use for our top selling parts report.
3 From the list of Available Data Sources, click the + button to expand the Create New Connection branch.
4 E2SS supports two possible database types; MS Access (the default platform) or MS SQL Server (Enterprise Edition). In this class, we will use an MS Access database. See Appendix D for information on linking SQL Server databases to your reports. Expand Access/Excel (DAO).
5 Clear the Secure Logon checkbox, since E2's database does not normally require a password. 31 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
6 Click the Browse (…) button to the right of the Database Name field. Browse to C:\APPS\E2\BLSWIN32\DAT\ABC\BLSDATA.MDB and click the Open button. Then click Finish.
7 Expand the Tables branch to locate the OrderDet table. Highlight OrderDet. Click the arrow pointing to the right to move the OrderDet table to the Selected Tables column.
8 Now click OK. 9 This window is referred to as the Design Environment. It is your report palette.
The Design Environment -The Report Artist's Palette Down the left side of the Design window you will see the report sections: = Report Header - Any object placed into this section will appear and print at the top of the first page of the report. It is ideal for the Report Title, the date the report was printed, or perhaps information about how the report was filtered. = Page Header - Objects placed in this section appear at the top of each page of the report. Field labels, report titles and page numbers are often added to this section. = Details - This section is used for the body of the report, and is printed once per record. The bulk of the report data appears in this section. 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 - This section of the report prints near the bottom of the last page of the report. This section is ideal for grand totals and charts. = Page Footer - Placing objects in this section will cause them to print at the bottom of each page of the report. Page numbers and the file path to the report are good things to put in this section.
32 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = Group Header(s) and Group Footer(s) - We do not see these sections now because they are only available when the report is grouped. Objects placed in either of these sections will either print at the beginning of the section (above the Details) or at the end of the section (after the Details). Using your mouse you are able to drag database fields, formulas, text objects, pictures, etc into different sections of the report.
Toolbars There are five toolbars that are turned on by default. To turn on or off the various toolbars, go to View | Toolbars from the menu bar. Toolbars.bmp These toolbars are dockable so they can be moved around and can even be floated out in the work space. However the buttons themselves cannot be changed. = Standard - This toolbar contains the common buttons like New Report, Save, Print, Cut, Copy, and Paste. = Formatting - Use this toolbar for changing font colors, sizes and styles among other things. = Insert Tools - The buttons in this toolbar allow you to insert items like text, group, and summary fields; pictures and charts. = Expert Tools - These buttons allow you to quickly open Crystal Report's Experts. The experts can be used to set sort orders, add grouping, and create formulas. = Navigation Tools - The refresh and page navigation buttons are located in this toolbar.
Crystal Report Options Crystal Reports allows you to set options for your reports which Crystal will remember so that each time you launch Crystal you do not have to change them. To access these options go to File | Options.
33 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports Use the tabs on this window to set your preferences. There are a few that we would like to point out that may make your life easier. On the Layout tab, check the Short Section Names checkbox to give yourself more work area for your report. This will change the section names from Report Header to RH and Page Footer to PF. On the Database tab, you can uncheck the Automatic Smart Linking to prevent tables from being linked undesirably. You can use the Fields tab to set the default number of decimal places on numeric field types, numeric field types, date formatting options (mm/dd/yy or mm/dd/yyyy), or your default currency symbol. From the Fonts tab you can set default font colors, sizes, and styles for various field types.
Inserting Objects on a Report In the Standard toolbar click on the Field Explorer button.
We will use this pane to add fields, formulas and other objects to our report. Click the + next to Database Fields to see the tables we have added to the report. We can expand this branch as well to see the fields in this table. We have only added the OrderDet table for now. Using your mouse, drag and drop the OrderNo, PartNo, PartDescrip, QtyOrdered, and UnitPrice fields into the Details section.
Believe it or not, you have just written a report. It does not meet our scenario's requirements yet, but it is a report. Notice that database objects added to the Details section are automatically given labels in the page header section. Your report may look slightly different if you changed any of the font options.
34 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Previewing the Report Let's see what our report looks like. Click the Refresh button.
You should see that some Order numbers are duplicated, as are some of the Part numbers and their descriptions. Also on some of the orders, Unit Prices for the same part numbers are more than they are on other orders. Do not worry too much about formatting the report during the early stages, because it is likely that you will add a new field to the report and have to change fonts or colors or something to match the objects already on the report.
Save the Report It is recommended that you keep the reports you write and modify for E2 in a common location. Shoptech has provided a location within the E2 Shop System folder structure for modified reports. Saving reports here will allow you to more easily locate them in the future. We recommend the …\blswin32\source\reports\replace folder. You should also give your report a name that will help identify the report's purpose.
1 To save this report, click the Save button in the Standard toolbar. 2 The Save As window will open.
3 In the Save in drop down menu, select C:\APPS\blswin32\source\reports\replace folder. In the File name field change the file name to Top Ten Parts.rpt and then click the Save button.
35 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Exercise 2 In this exercise we will begin work on a report that we will complete through the other exercises included in this course. You have been asked to create a report to be used as a Winter Holiday Thank You letter by your company. The information required for this report is available in the database used with the E2 Shop System. The report should include the Customer's full company name, the Customer's AP Contact, and the Customer's Year to Date sales. The report should include some text thanking the customer for their business and wishing them a Happy Holiday. The report can also include holiday related clip art and should only cover half of the page so that 1 letter per page can be printed on an A4 page of card stock.
1 Create a new report using the Blank Report expert. 2 Add the Billing and CustCode tables from the C:\APPS\blswin32\DAT\ABC\blsdata.mdb file.
3 Link the CustCode table to the Billing table by dragging CustCode.CustCode to Billing.CustCode.
4 Add the CustCode, CustName, and APContact fields from the CustCode table to the Details section of the report.
5 Add the InvoiceNo, InvDate and InvoiceTotal fields from the Billing table to the Details section of the report.
6 Now, preview the report. 7 Save the report to the Replace folder as Holiday Letter.rpt. Excercise1.bmp
8 Using the Database Expert, change the linking option to get results similar to that above.
9 Try the other linking options to see how it affects the results of your report.
36 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Lesson 4: Customizing a Report Now that you have created the report and added some data objects to it, we will learn how to customize the report. In this lesson you will learn about: = = = = = = = =
Positioning objects Sizing objects and sections Formatting objects Adding text objects Grouping data in the report Sorting the report Adding Summary fields Adding Special fields
Positioning Objects You can use your mouse or keyboard to easily reposition any objects that you have added to your report.
1 In the Top 10 Parts.rpt on the Design tab, hold down the left mouse button until the pointer changes to a four-headed arrow. Drag the UnitPrice field to the right hand edge of the report. Notice that when you move the field, the label in the Page Header moves with it.
2 Click on the QtyOrdered field and then using the arrow keys on the keyboard, move the field to the right, so that it is next to the UnitPrice field.
3 Click on the label for QtyOrdered, and then move this label to the left. Notice that the label moves independently of the field and that this has broken the link between the label and the field so that now if you move the field the label no longer moves. You can re-link the field to the label by re-aligning the sides of the field and label. Now, when the field is moved the label moves with it again.
4 To select multiple fields you can hold the CTRL key, click on each field that you want to select, and then position each object. To remove the selection, simply click in any empty space on the report. If you have selected multiple objects and need to de-select just one of them, while still holding down the CTRL key, click the desired selected object and it will become de-selected.
5 You can also perform what is called the marquee select or "lasso" method. Hold down the left mouse button and as you move the pointer, you should see a rectangular outline. Any object that this rectangle touches will be selected. You do not have to surround the object, just touch it.
6 If you want to select all of the objects in a section, you can right click the section, and then choose Select all Section Objects.
37 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
7 When you have multiple objects selected, notice that one of the objects will have little squares along each side. These squares or "handles" indicate that this field is the "main" object. Right click on the selected fields. From the resulting menu, select Align | Tops.
This will align the selected fields along the top edge of the "main" object. Experiment with alignments using the other menu options. Be careful that you do not stack fields on top of each other. Crystal includes some tools to help you position objects on your report. You are welcome to experiment with these options to find the settings you prefer. These settings can be changed by accessing the File | Options window on the Layout tab.
The Snap To Grid, Gridlines, Guidelines, and Rulers can all help you align the objects that you have added to a report. Take a few minutes to try some of the options to see how they affect your report. Crystal reports also supports using the Windows standard functions of Cut, Copy, and Paste. These functions are quite handy when you need to summarize a field within a group and also as a grand total. Be careful when attempting to move fields while previewing the report. You may find that you have moved the field into another section of the report. It is a good idea to use the Design tab when moving objects around on the report.
38 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Sizing Objects and Sections Sizing Objects In many cases, once you have previewed your report you may find that data fields do not show all of the data in them because the field is too narrow or you may find that for visual purposes you would like to have some additional spacing between sections of the report. You can resize objects based on height, width, or size in relation to other objects.
1 Select the label for OrderNo on your report. Position your mouse pointer over the handle on the right side of this field. Your pointer will become a two-headed arrow this time. Now drag the edge of this field to the left or right to make the size smaller or larger respectively.
2 Using one of the selection methods discussed previously, select several objects. Remember the one with the handles is the main object. Drag one of the handles on this object. The size of the other selected fields will expand or shrink based on how much you adjust the main object.
3 With several fields selected, right click the mouse on the main object. From the resulting menu, select Size | Same Size.
This will resize the secondary objects (the ones without handles) to be the same height and width as the main field. Working from the preview tab can be helpful when adjusting the size of objects on your report because you can see a sample of the data and adjust the size according to the amount of data or text that the field contains.
Sizing Sections Depending on the audience for your report, you may find that it is helpful to have more space between records or sections of your report. You may also decide to leave a section empty. The section will still print even if it is empty, so Crystal allows you to adjust the size of each section.
39 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
1 On the Design tab, position your pointer over the double line that acts as a boundary between the Details section and the Report Footer.
2 When the cursor changes to the vertical double arrow, click and drag this line down the page.
3 Preview the report to see how your report is affected. You are not able to adjust the size of the sections from the report preview.
Formatting objects In this section we will learn about the tools available to us in Crystal that really change the way the report appears. This is your opportunity to express your creativity and aesthetic sensibilities. The most basic method for formatting an object on a report is to use the functions in the Formatting toolbar.
1 Select the QtyOrdered field in the Details section. 2 The formatting toolbar will show you how this field is currently formatted; font, size, alignment, color, etc.
3 Select a different font style, make it Bold and Centered, and finally change the text color to Red.
4 Preview the report. You can also format more than one object at a time using the Format Editor.
1 Select all of the labels in the Page Header section. 2 Right click your mouse on one of these labels. 3 Select Format Objects from the pop-up menu to open the Format Editor. Using the Format Editor is a more complex method of formatting. Some of the more frequently used features of the Format Editor are: = = = =
Common Border Font Number
Common tab On the Common tab, you can suppress the field, keep the field together if a record spans more than one page, control borders of the field, limit the number 40 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports of lines the field can print, rotate and align text, add Tool Tip text, and lock the size and position of the selected objects.
Border tab On the Border tab, you can select line styles, drop shadows, and the color for the border and the background. Clicking Background opens a second color selection list for you to choose a fill color for the text object.
41 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Font tab On the Font tab, your options include changing the font, style, size, and color. You can also add a strikeout (a line through text) and underline effects. The sample box shows you the results of your choices.
Number tab If you select a numeric field to format, the Format Editor offers these options on the Number tab.
= System Default Number Format This option checks the settings in your Windows control panel and uses those settings for your numeric fields. = Currency symbol This option enables you to display a currency symbol and its type, if applicable.
42 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = Customize button Under the Customize button, you can set various options: =
Suppress if Zero This option functions similarly to a spreadsheet. If the value to be printed is "0" (zero) then no value is shown.
=
Decimals This option enables you to include decimals or not.
=
Rounding This option enables you to round a number to the specified number of decimal places or to the nearest whole number if no number of decimal places is indicated.
=
Negatives This option offers several different ways to display negative values.
=
Decimal Separator This option enables you to specify what character to print as a decimal separator.
=
Thousands Separator This option enables you to turn the thousands separator on and off. The input box allows you to specify what that separator is.
=
Leading Zero This option enables you to turn leading zeroes on and off as well as specify the format.
On several of the tabs you may notice the Formula button. You can use this button to apply a condition based on a formula for which the indicated formatting applies. For example, if the QtyOrdered is greater than 1000, make the text red.
1 On the Font tab set the Font style to Comic Sans MS, 12 pt., Bold, and Blue.
2 Preview the report. 3 Take a few moments to try some of the options on the other tabs. From this point onward, your report may vary from the report created by the instructor or the screen captures in the manual.
Adding Text Objects You may need to add additional text to a report. Headings, descriptive labels, and Report Titles are examples of some of the text fields that may need to be added to your report. To add a title to your report:
1 In the Insert Tools toolbar, click the Insert Text button. 2 Your mouse pointer will become a large plus sign (+). The empty text box is actually attached to your pointer. When you left click the next time, the attached text box will be placed in that spot.
3 Position your pointer in the Report Header section and then left-click to place the text box. 43 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
4 Your cursor should be flashing in the text box. 5 Type Top 10 Parts. 6 When you have finished typing, left-click in a blank area of the report to accept the text you have typed.
7 Using the information you have learned in the previous section, format this text to make it appear like a report title. To position your text in the center of the page header, size the field from the left edge to the right edge of the report, and then use the appropriate alignment button to center the text within the box.
8 To edit the text you have already typed, right-click on the text box. 9 Select Edit Text from the menu. 10 Your cursor will now be flashing in the text box again. Edit the text as necessary.
11 Click in a blank area to accept the changes. 12 Preview the report. Your report should resemble something like this.
Grouping Data in the Report As we preview our report now it is difficult to tell exactly how many of part ABC123 have been sold, because this part appears on multiple rows throughout the report. One way that we can get each order for a particular part listed together is to group the report.
1. In the Expert Toolbar, click the Group Expert button.
2 There are 2 panes on this expert window. The Available Fields pane shows Report Fields (objects already on the report) and Table fields (all of the Fields available in the table). The Group By pane will show groupings on the report. 44 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
3 Select the OrderDet.PartNo field, and then click the button with the single arrow (>) pointing to the right.
4 This groups the report by Part Number and sorts those parts in ascending order as indicated by the "A". This means that Part Numbers that begin with numbers will print first in ascending order 1, 2, 3… then Parts that begin with letters A, B, C…You can use the Options button to use a different sort order. We will look at sorting records in a later section.
5 Click OK. 6 You should now see that 2 new sections have been added to the report called Group Header1 (GH1) and Group Footer1 (GF1) and a field that Crystal inserted for us called a Group Name field.
7 Preview the report. 8 You should now see the report grouped by Part Number.
Sorting the Report As we have seen with the grouping we added which sorted parts in ascending order, by sorting the records in our report we can get the report to list the parts in a particular order. This will make the data in the report more meaningful to us. When we preview the report now the parts are sorted in ascending order, but the order information within the group does not appear to have any sorting at all. Let's add some.
1 Click on the Record Sort Expert button. 2 Again we see two panes, the left pane displays Available Fields and the pane on the right shows how the report is currently sorted. In this case the expert shows us that there is grouping on the report which we cannot change using this expert.
3 Select the OrderDet.OrderNo field from the Available Fields pane and then click the right pointing arrow (>). 45 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
4 Select the option button for Descending. 5 Click OK. 6 Preview the report. Now you should see the highest order number at the top of the list within each part number group.
7 This would be a good time to save your report again. Click the Save button.
8 Take a few minutes to make some additional changes. Remove the sort you just added for the OrderNo. Add sorting on the QtyOrdered field in Ascending order as well as sorting on the UnitPrice field in Descending order. How does this change the report?
Adding Summary Fields A summary field in a report is basically a total of one of the fields. If the field type is numeric you could add those numbers together as a Sum. If the field is a string (letters and/or numbers), you could count the number of records. You can insert summary fields on either the Design or Preview tabs. The options available when inserting a summary field varies depending on the type of data you are summarizing.
46 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Summarizing a text or date field: = = = = = = = =
Summarizing a numeric field:
= Sum = Average = Sample variance = Sample standard deviation = Maximum = Minimum = Count and Distinct count = Correlation with = Covariance with = Median = Mode = Nth largest, N is: = Nth smallest, N is: = Pth percentile, P is: = Pth most frequent, P is: = Population variance = Population standard deviation = Weighted average Select the OrderNo field in the Details section.
Maximum Minimum Count Distinct count Nth Largest Nth Smallest Mode Nth most frequent
1 2 Right-click the mouse. 3 Select Insert | Summary.
4 Set the Calculate this summary field to Count. 5 Set the Summary location to Group #1:OrderDet.PartNo and then click OK.
6 This will add a summary field into the Group Footer Section. 7 Click into a blank area of the report so that no object is selected.
47 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
8 From the menu bar, select Insert | Summary.
9 10 11 12 13
Set the Choose field to summarize to OrderDet.QtyOrdered. Set the Calculate this summary to Sum. Set the Summary Location to Group #1:OrderDet.PartNo. This will add another summary field to the Group Footer section. Preview the report.
14 The reason we summarized both fields is to demonstrate that we have not truly defined what our report should show. It is fairly safe to assume that for a Top 10 Parts sold report we would like to see the total based on the quantity ordered. But if you think about it, we could count the number of orders for each part. This would tell us which parts are ordered the most frequently.
15 Add some text field labels for the two summary fields we just added.
Adding Special Fields Crystal includes many special pre-built functions that will make the life of the report writer a little simpler. These special fields can help reduce the time it takes you to make your report presentable. After this section you will be able to: = Insert special fields = Format special fields 48 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = Embed a special field into a text object Some of the more commonly used Special fields are: = Data Date: prints the date the data was last refreshed on a report with saved data. = Date Time: prints the time the data was last refreshed on a report with saved data. = = = = =
File Creation Date: prints the date the report file was first created. File Path and Name: prints the directory path of the report file. Modification Date: prints the date the report was last modified. Modification Time: prints the time the report was last modified. Page N of M: prints the current page number of the total number of pages.
= = = =
Page Number: prints the current page number. Print Date: prints the current date as per your computer's clock. Print Time: prints the current time as per your computer's clock. Report Comments: prints the contents of the Comments area in the Document Properties in the File | Summary Info menu.
= Report Title: prints the contents of the Title area in the Document Properties dialog box in the File | Summary Info menu. = Total Page Count: prints the total number of pages in the report.
1 From the Field Explorer, expand the branch for Special Fields.
2 Drag and drop the Print Date field into the Report Header section of the report.
3 Drag and drop the Page Number field into the Page Footer section. 4 Preview the report. You should see today's date at the top of the first page of the report and the page number at the bottom of each page.
5 Right click on the Print Date field, select Format Field from the menu. 6 Select a date format that you prefer. 49 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
7 8 9 10
Click OK. Insert a text label with the text "Date:" to the left of the Print Date field. Carefully drag the Print Date field into the Date: text box. It should look something like this:
Embedding data or special fields into text field boxes is a handy feature that allows you to preserve alignment and allows you to format 1 field instead of 2.
11 12 13 14
Add a label for the page number. Format these fields according to your sense of style. Save and preview the report. Take a few minutes to fix up the Top Ten Parts report using the lessons we have learned so far.
50 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Exercise 3 1 Open the Holiday Letter.rpt that you created in Exercise 1. 2 Group the report by CustCode.CustCode. 3 Summarize the Billing.InvoiceTotal as a Sum in the Group Footer section.
4 5 6 7
Increase the size of the Group Header section. Insert a Text object in the Group Header with the text "Dear ,". Embed the CustCode.APContact field into the "Dear ," text box. Insert a second text field containing a holiday greeting. Be sure to include your company name, text for the customer's name, and text for the total of their invoices.
8 Embed the CustCode.CustName and the Sum of Billing.InvoiceTotal fields into your text message.
9 Format the text fields to have a holiday look. Do not worry about all of the extra text right now, we will deal with it in a later exercise..
10 Insert a holiday picture into the center of the Group Footer section from the C:\APPS\blswin32\source\reports\replace\exercise images folder.
51 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
Lesson 5: Manipulating Data and Running a report in E2 Shop System Now that our report displays data and we have learned how to format the report to make the report look a certain way, let's learn how to manipulate the data. In this lesson you will learn about: = = = =
Adding formulae Selecting records Parameters (Prompts) Running your report from within the E2 Shop System
Adding Formulae In many cases, the data you want to appear on your report already exists in the database. Sometimes, however, your report requires data that is derived by manipulating data in an existing field in the database. In this case, you would use a formula to manipulate the data, and a formula field to display the manipulated data. You can think of a formula as a small piece of computer programming code that processes and prints its results on your report. It will print the result wherever you place the formula field. = = = = = =
Typical uses for a formula include: Calculating a percentage of a number Extracting a single character from a string field Combining a text string with a number or date field Finding the difference between two date fields Performing conditional logic
To Create a Formula We have two fields already on our report that are ideal for performing a calculation. Multiplying the QtyOrdered and UnitPrice fields would tell us how much we sold the part for on each order. To make this calculation, we will need to create a new formula.
1 Open the Top Ten Parts.rpt. 2 Make some room in the Details section on the right hand side for another field.
3 In the Field Explorer, right click on the branch for Formula Fields and then select New Formula from the menu. You can also highlight the Formula Field branch and then click the New Formula button.
4 Enter a name for the formula - OrderPrice. 5 Click OK. The Formula Workshop window consists of five panes. Each pane contains a different element useful in formula writing.
52 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
A - This pane can be used to show you formulas that already exist in the report. B - This pane contains Report fields (those already used in the report) and Database fields (those available in the selected database(s). Double click a field to add it to the formula. C - Within this pane you can select from a wider variety of functions. See Crystal's Help to learn more about using Functions. D - Common operators are available in this pane; +, -, >, =, etc. E - As you build the formula, the syntax for it will appear in this pane. Use the // button to make comments about the formula. This is helpful when someone else needs to make changes to your report.
1 In pane E, type //Calculate the price of the parts on the order. Press Enter to move your cursor to the next line.
2 Expand the Report Fields branch. 3 Double click on OrderDet.QtyOrdered. 4 Expand Operator | Arithmetic, and then double click on the multiply symbol.
5 Under Report Fields again, double click on OrderDet.UnitPrice. 6 This should result in the following formula {OrderDet.QtyOrdered} * {OrderDet.UnitPrice}. Database fields are indicated by the values within the braces; {tablename.fieldname}.
7 Check the syntax of your report by clicking the x+2 button. If no errors are found, click the Save and Close button. If you receive an error, you will need to correct the formula.
8 Drag and drop the @OrderPrice formula into the Details section of your report.
53 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
9 Save and preview the report.
Conditional Formatting Besides using formulas as fields on the report, you can also use formulas to put conditional formatting on fields in the report. For example, in our Top 10 report, let's make the OrderPrice yellow when the price is greater that $1000.00.
1 2 3 4 5
Right click on the @OrderPrice field in the Details section of the report. From the menu, select the Format field. Navigate to the Border tab. Click on the x+2 button to the right of the background checkbox. The Formula Workshop window will open. Pane E will be pre-populated with some comments. These comments will show you the syntax to use to produce the colors that are available.
6 In pane E, type the following text If {@OrderPrice} > 1000 then crYellow.
7 Check the formula, then click the Save and Close button if no errors are found.
8 Click OK on the Format Editor window. 9 Save and preview the report. Notice the prices greater than $1000.00 are yellow but the other values are black and you can no longer read them. This is because we did not tell the formula what to do with values that are less than or equal to $1000.00.
10 To correct this, right click on the @OrderPrice field again and then select Format field from the menu.
11 Click the Border tab and then the x+2 button by the background checkbox to edit conditional formula again.
12 Add the text else crNoColor to the end of the formula. 13 Check the formula. 14 Click the Save and Close button.
54 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
15 Save and preview the report.
Now our report shows prices greater than $1000.00 with a yellow background and prices less than or equal to $1000.00 with no background. There are thousands of formulas that can be written using Crystal Reports. For more information about formulas, the functions, and operators please refer to the Help included with Crystal Reports. Refer to Appendix A for Shoptech's Support Guidelines for Crystal reports.
Selecting Records In our original scenario, we wanted to limit the records in our report in two ways. We can accomplish this by only selecting certain records from the database. First we will limit the report to only show top level assembiles. To do this, we need to understand how an assembly is indicated in E2 Shop System.
1 Login to E2 Shop System as Supervisor. 2 Go to Orders | Order Entry and create a New order. 3 Select any customer from the drop down list.
55 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
4 In the first row of the grid in the Part No field, type DJ2-9761. Enter the Qty Ordered of 5. Click the Process button. On the Line Item Quantity Specification window, click the Zero Out Grid button so that we will be making the components rather than posting them from stock. Click the OK All button. Once the order appears in the list, click the Edit button.
You should notice that there are more parts listed now than the one part DJ29761 that you entered. Each part listed on the order represents a job. Notice that there is a job number on each row of the grid. Scroll to the right to locate the column titled Master Job Number. This field will be blank for the first part on the order. This tells us that the first job is the master job, and the others are sub-assembly jobs. In our report we can tell Crystal to only display jobs for which the Master Job number is blank.
1 Within Crystal Reports, click the Select Expert button. 2 On the Choose Field window, select the MasterJobNo field and then click OK.
3 Click the drop down list to see the possible options. = Is any value This option selects all records, meaning no selection at all. = Is equal to This option enables you to specify one specific value as the criteria, so that only matching records are included on the report; for example only records from California (CA).
56 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports = Is one of This option enables you to specify a series of values as the criteria, so that only records matching on of these fill be included on the report; for example, only records from CA, OH, or NY. = Is not one of This option enabled you to exclude a series of values as the criteria, so that those matching records will be excluded on the report; for example, not records from CA, OH, or NY. = Is greater than or less than This option enables you to indicate a specific value that records must be above or below in order to be included on the report; for example, only records with sales greater than or over $50,000.00 for the last year. = Is greater than or equal to or less than or equal to This option enables you to include the beginning and end values of the selection criteria. = Is between This option enables you to select records that have a value falling between or matchin on of the two specified values; is inclusive and includes the end values; for example, only records with last year's sales between $10,000 and $20,000, including $10,000 and $20,000. = Is not between This option enables you to exclude records that have a value falling between or matching one of the two specified values; is inclusive and excludes the end values; for example, excludes records with last year's sales between $10,000 and $20,000, including $10,000 and $20,000. = Starts with This option enables you to indicate characters(s) or value(s) that each data field must begin with in order to pass the selection criteria; for example, only customers whose names begin with the letter "A". = Is like This option permits DOS wildcard characters (? and *) to specify criteria that must be met; for example, crystal, comical, and critical could all be selected using "is like c*". = Is not like This option permits DOS wildcard characters (? and *) to specify criteria that must be excluded; for example, using "is not like c*" would exclude crystal, comical, and critical. = In this period This option enables you to specify a date range in which records must fall in order to be included on the report and is only available if a date field is chosen; with this option, a scroll list of all Crystal Reports date ranges is made available. = Is not in this period This option enables you to specify a date range in which records must not fall in order to be included on the report and is only available if a date field is chosen; with this option, a scroll list of all Crystal Report date ranges is made available. = Formula This option expands the dialog box where you enter your criteria. If you 57 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports are familiar with the Crystal Formula language, you can enter you formula here.
4 Select is equal to from the menu. 5 Since we want only Master jobs, then we want records where the Master Job Number is blank, so in the text field press the space bar once.
6 Click OK. 7 Save and preview the report. 8 Find part DJ2-9761 in the results. Notice that none of the other jobs on that order appear in the report.
Parameters (Prompts) Parameters prompt the user of a report to enter information. You will use the information entered to limit the results displayed in your report. You can think of a parameter as a set of questions that the user will need to answer before the report is actually generated. In our example, we would like to limit the results in our report based on the date the order was entered. The user will be able to enter the beginning and end of a date range; one day, one week, 3 months, 2 years and so on.
Creating a Parameter There are 2 steps in creating parameters; first you will create the parameter and then you will tell Crystal how to apply the information that was entered by the user.
1 In the Field Explorer, right click on the Parameter Fields branch. 2 Select New from the menu.
3 In the Name field type Begin Date. 4 Set the Type to Date since we want the user to enter a date value in the prompt.
5 Click OK. 6 Create a second similar parameter called End Date with the same type. Click OK. 58 Shoptech, Inc. - Advanced Training
Advanced Database and Crystal Reports
7 In order to compare these parameters to data in our database we will need to add an additional table into our report. Click the Database Expert button.
8 Under your Current Connections locate the Orders table and move it to the list on the right.
9 Click the Links tab. 10 We will link the Orders table to the OrderDet table on the OrderNo. To do this select the OrderNo field in the Orders table and drag it to the OrderNo field in the OrderDet table. NOTE: We will discuss linking in further detail in a later lesson.
11 Click OK. 12 For testing purposes, let's drag the Orders.DateEnt field into the Details section of the report and preview it. Notice that orders from many different years appear in the results.
13 To apply the parameters we will need to set Record Selection based on a formula. Go to Report | Selection Formulas | Record from the menu.
14 You should see that the filtering we applied based on the Master Job Number is already here. Click your mouse at the end of the line and press the Enter key to go to the next line.
15 Since we need to apply additional filtering type the word and. 16 Double click on the DateEnt field from the Orders table to insert that field into the formula.
17 Type >= and then double click the Begin Date parameter; {?Begin Date}.
18 Repeat step 15 and 16 on a new line. 19 Type
