Creating a Report

As a reminder, no Report Wizard will be used for this example. Instead we're going to create this Sales Report from scratch.

If necessary, refer to the last email sent to Bob for the report requirements.

1. Select a report template. After opening Crystal Reports, click on the New button. Then in the Crystal Reports Gallery dialog box, choose As a Blank Report and click OK, as shown in Figure 5.3.

   Figure 5.3. The Crystal Reports Gallery with As a Blank Report chosen.

   

2. Select an appropriate data source. From the Database Expert dialog that comes up, in the Available Datasources list, browse to Create New Connection, ODBC. As soon as you choose ODBC, the ODBC (RDO) dialog will pop up. Scroll until you find the Xtreme Sample Database 9 as in Figure 5.4. Select it and click Finish. (There are no other settings to get this database working, so you can ignore the Next button. We will discuss these additional options in a later hour.)

   Figure 5.4. The ODBC (RDO) dialog box showing the selected Xtreme Sample Database.

   

3. Select the appropriate tables. After choosing the appropriate database to connect to, we need to select the tables for this report. Move down in the left list box and expand the Tables item. Choose the Customer, Orders, and Employees tables by using the right-arrow (>) button, as seen in Figure 5.5.

   Figure 5.5. The Data tab from the Database Expert dialog box showing three tables added to the report.

   

   

   Remember you can choose each table separately and click the arrow button or hold down the Ctrl key to select all tables that you want and then press the arrow button only once. Also, if you want to select several tables in a row, the Shift key will help you with that.

   
   

4. Link tables from the database. Move to the next tab in the Database Expert dialog by either clicking the OK button or selecting the Links tab. Notice that all the tables have already been linked. Crystal Reports attempts to link tables using similar field names and sizes whenever possible.

   

   You can enlarge this dialog by using the stretch markers so that you can increase the display area and see more tables at once. The next time you enter this dialog, your adjusted size will be remembered.

   
   

   We don't need to make any changes at this point, so just press the OK button.

5. Create logical groups of data. Next on our requirements list is to group the records by company name. In order to accomplish this, choose Insert, Group. When the Insert Group dialog appears, scroll down the first list box until Customer Name under the Customer table is available. Select it, as shown in Figure 5.6, and then click OK.

   Figure 5.6. The Common tab of the Insert Group dialog box with the Customer Name field selected.

   

   Notice that in the Design view of the report, two new sections become available called Group Header #1 and Group Footer #1. Within Group Header #1, the Group Name #1 field is also automatically added.

   

   One of the powerful features in Crystal Reports is that you build the structure of the report without necessarily adding all the detail fields first. For example, even though we have not placed any objects on the report yet, we can still go ahead and add a group to the report.

   If, however, we were to have some objects on the report already, they would appear in the Report Objects node within the list box, which would appear before the other available fields. This is for ease of use and accessibility.

   
   

6. Add summary values to the report. Now that the report information is grouped, add a summary on the orders. Choose Insert, Summary to get the Insert Summary dialog box to appear. In this dialog box, select in the field to summarize the list box to the Order Amount field. The Orders table is pretty far down the list, so keep scrolling. Next, because we plan on finding out how much each customer has ordered, the summary operation in the second list box needs to be changed from Maximum to Sum. Last, because this summary is per customer, we will need to change the location of the summary. Instead of placing it in the Report Footer (where it would summarize all amounts into one grand total), change the Summary Location list box to show Group #1, as shown in Figure 5.7. Click OK.

   Figure 5.7. The Insert Summary dialog box with Order Amount summed by Customer Name selected.

   

   Notice that in the Design view, in the Group Footer #1, the Sum of Order Amount field has been added.

   

   Although we have not formally added the detail records to this report, they are being read in as needed. You don't actually have to spend time on detail information if the request is for the summary numbers.

   
   

7. Add detail records to the report. To view the records of concern for this report, let's put the records into the report so we can track them. First, let's bring up the Field Explorer so that we can use it to add the fields to the report. To do this, choose View, Field Explorer. In the Field Explorer that becomes available, open the Database Fields item and then the Orders item to expose our fields that we'd like to add. Select each field separately and drag it to the Design tab using your mouse. Place them side-by-side in the Details Section: Order ID, Order Amount, Order Date, and Ship Date. To leave some space for the status field that we will add later, add the last field (Ship Via) further to the right of Ship Date, as shown in Figure 5.8.

   Figure 5.8. The Design Window with all fields added.

   

8. Add a parameter to the report. We have been asked to run this report for X employee. To insert a parameter field, go to the Field Explorer and choose Parameter Fields. Right-click and choose New. In the Create Parameter Field dialog box, enter the information required. For the name of the parameter, enter Employee Name. For the prompting text that the end user will see, ask a question such as, "Which employee would you like to view?" Instead of assuming that the user will type the correct spelling, we can set some defaults for them. To do this, click on the Set Default Values button. In the Set Default Values dialog box that appears, we can choose to select data directly from our database. In this case, we want to get the fist names of our employees. In the Browse Table list box, choose the Employee table and then in the Browse Field list box, choose First Name.

   Notice that the list of first names appears in the Select or Enter Value to Add list box. By clicking on the double-arrow (>>) button, that list will be brought in as default values as in Figure 5.9. Click OK to this and the previous dialog box.

   Figure 5.9. The Set Default Values dialog box with employees' first names added to the defaults.

   

9. Connect the parameter to the report's selection criteria (filter). The new parameter field has not been connected to any actual data values yet. This is noted by the lack of a green check mark applied to the parameter item's icon in the Field Explorer. We need to add it to the Selection Criteria within the report to tell the report to only grab data for a given employee, based on the value that the business user will select in the parameter's prompt. Choose Report, Select Expert to see the Choose Field dialog box. In this dialog box, scroll down and select the Employee's First Name field. Click OK to get to the Select Expert dialog box. In Select Expert, change the list box to Is Equal To. By doing so, notice that another list box opens. From that list box, choose the first item {?Employee Name}, as shown in Figure 5.10. This is the parameter field that we just created. This will allow us to get the information from the user instead of hard-coding it ourselves. Click OK to continue.

   Figure 5.10. The Select Expert dialog box with the Employee Name parameter field connected to the database.

   

10. View the report. Before going any further, take a look at the report as it stands so far with some data. By Choosing Report, Refresh Report Data, we can access the parameter's prompting dialog to choose an employee. Choose Anne and click OK. A report with meaningful data is returned in the Preview tab like Figure 5.11.

    Figure 5.11. The resulting report based on steps 1 through 11 in the Preview tab.

    

11. Save the report. File, Save will bring up the Save dialog. Provide a suitable name for the report, such as Chap5.rpt.

    

    If you are concerned about losing work between conscious saves of a report, Crystal Reports has an autosave feature that you can enable. You can set this option by navigating to File, Options, and then selecting the Reporting tab. The Autosave Reports After option can be set and the length of time in minutes between saves can be specified in the edit box to the right of this option.

    
    

12. Implement the requested logic flow for the report. In order to check whether the order has been shipped on time, we must compare the ship date with the order date for each order. To accomplish this, we will need to create a formula. In the Field Explorer, right-click on Formula Fields and choose New. In the Formula Name dialog box, name the formula Days Until Shipped and then choose the Use Expert button. This open the Formula Workshop's Formula Expert dialog box.

    

    If this is your first time seeing the Formula Workshop dialog box, you will undoubtedly notice that the dialog box is not quite large enough to hold all the information that we need. By simply using the maximize button at the top, you can resize this dialog's window to its maximum height and width. The next time you come back to it, your settings will be remembered.

    
    

13. Add a formula. Because we need to perform some math on the date fields, we will review the requirement. In this case, we don't want to include weekends or holidays in our math because couriers don't work those days. Instead of writing a complex formula, we're going to use a supplied function that is available in the Repository. In the Custom Function Supplying Logic list, open the Repository Custom Functions item and navigate to Crystal Repository, Crystal, Date, cdDateDiffSkipHolidays and select the object by single-clicking on it. Notice that the dialog fills out nicely with a lot of information regarding this custom function. The only values that you need to enter are in the Value list boxes under the Function Arguments section. For startDateTime, choose Orders.Order Date; and for endDateTime, choose Orders.Ship Date, as shown in Figure 5.12. Click on the Save and Close button in the top left of the dialog.

    Figure 5.12. The Formula Workshop's Formula Expert with all arguments filled in.

    

    

    We will examine and explain the Crystal Repository in a later hour. We are only introducing the idea of sharing complex logic at this point.

    
    

14. Close and save the formula. Closing and saving this formula in one step allows Crystal Reports to automate some processes for you. It will now ask if you want to save this report. Choose Yes for this and all subsequent messages. Then, place the formula field that appears in the Field Explorer on to the report between the Ship Date and the Ship Via field in the Details section.

15. Left Justify the formula field by choosing the Align Left button in the Formatting toolbar so that the numbers don't overwrite the courier info to the right.

    

    The automation of error checking, as well as insertion of the Custom Function in to the report, is what those messages are about. The real beneficial feature here is that the cdDateDiffSkipHolidays function actually requires the use of other custom functions but we're not burdened with having know the details of this. Crystal Reports will ask if you want them all at once. We do, so we just select Yes. All the functions are brought into the report.

    
    

16. Format the report. Use the Report Explorer to accomplish this. Select View, Report Explorer to turn on this viewer. In the Report Explorer dialog box, navigate the listing to get to the Details section and choose the DaysUntilShippped1 object. Right-click on it and choose Highlighting Expert. In the Highlighting Expert dialog box, click on the New button. By editing the item values in the Item Editor side of this dialog box, we can add a red color. To accomplish this, change the second list box item to say Is Not Equal To. This will give us the logic of "If this field is not equal to 0, then." Now we must set what to do if this is the case. To accomplish this, change the Font Color to Red, as shown in Figure 5.13, and click OK to continue.

    Figure 5.13. The Highlight Expert with the late condition applied.

    

17. View and Save the final report. Review what your report looks like in the Preview tab and save it again as Chap5.RPT so that we can use it in later hours.