Understanding Field Objects
| As described in Chapter 1, the Field Explorer displays a tree view of data fields in your report. It shows database fields, formula fields, SQL expression fields, parameter fields, running total fields, group name fields, and special system fields that you have defined for use in your report. This chapter introduces you to all the standard field types available in Crystal Reports. To activate the Field Explorer, either select it from the View menu or click on the Field Explorer button in the Crystal Reports Standard toolbar. Figure 2.1 shows the sample Crystal Report created in the last chapter with the Field Explorer activated and docked on the right side of the screen. As previously mentioned, this can be docked on either side of the designer or at the bottom of the screen. Alternatively, the Field Explorer can freely float over any part of the design window by simply dragging and dropping it. Figure 2.1. Crystal Reports Designer with the Field Explorer docked on the right side. The next seven sections introduce the different types of fields accessible from the Field Explorer and provide ideas on where they might be used in a report. Subsequent chapters in the book cover some advanced uses of these types of fields. Before moving on to explore these different types of fields, here are some common traits shared by all field types:
Accessing Database FieldsThe Database Fields branch of the Field Explorer tree is used to add database fields to your report. The fields that can be added to your report are those from standard database tables, views, stored procedures, synonyms, and system tables. To add additional tables or other data sources to your report, you would use the Database Expert under the Database menu. To insert the database fields that are available from the Field Explorer into your report, either click and drag them into the desired location on the report or select them, click the Insert to Report button (or Insert to Report action from the right-click menu), and then select the desired location on the report for the highlighted fields. Tip If you are uncertain of exactly which fields to add to your report because of ambiguous (for example, WERKS, MENGE, LEAFS) or similar (for example, District, Region, Locale, Division) field names, you might be able to determine the appropriate field by selecting the respective field and using the Browse button (or the Browse action from the right-click menu) to view the data type and sample values of data from the table. Multiple fields can be highlighted simultaneously in the Field Explorer and placed in the report designer window at once. Crystal Reports drops the first of the multiple chosen fields in the selected location on the report and places the subsequent fields in order to the right of the initial field. If the report's layout runs out of real estate on the right side of the report, the subsequent fields are placed one line down and the placement algorithm continues. Accessing Formula FieldsFormula fields provide a means to add derived fields (that is, those not directly available in your database) such as a calculation into your Crystal Reports. Crystal Reports treats derived formula fields in exactly the same manner as it does original database fields. Some examples of where formulas might be used on the sample report from Chapter 1 would include the following:
The formula fields branch of the Field Explorer tree is used to add existing or new formula fields to a report. A listing of previously created formulas appears in this part of the Field Explorer tree. Once created, existing formulas are added to the report by either clicking and dragging and dropping or by selecting the formula and using the Insert functionalityavailable through the right-click menu or Field Explorer action buttonand then selecting the location. Tip Both simple and complex formulas can be created on any type of field including numeric, date, string, Boolean, or memo fields. This is explored in Chapters 11, "Using Record Selections and Alerts for Interactive Reporting," and 13, "Using Formulas and Custom Functions." If a new formula is required, it can be created directly from the Field Explorer by using the New toolbar button. You are prompted to name the new formula and then select the method of creation. This dialog is displayed in Figure 2.2. Figure 2.2. The Formula Name dialog requires specification of a formula name. Using the Xtreme Sample Database and the sample report created in Chapter 1 (chap1Wizard.rpt), one simple formula you might want to add is a Full Name field that comprises both the first and last name of the customer's contact person (Contact First Name and Contact Last Name in the Customer sample table). To perform this task, perform the following steps:
By selecting Save in the Formula Editor, you return to the Field Explorer and the new formula, Full Name, is now available to be placed on the report. Finish this section by placing the Full Name Formula Field onto the report beside the Customer Name. Accessing SQL Expression FieldsThe SQL Expression Fields branch of the Field Explorer tree is used to add existing or new SQL Expression fields to a report. A listing of previously created SQL Expressions appears in this part of the Field Explorer tree. Once created, existing SQL Expressions are added to the report by either clicking and dragging and dropping or by selecting the SQL Expressionusing the Insert into Report button or action on the right-click menuand selecting the location. SQL Expressions are created in the same Formula Editor as formulas but use Structured Query Language (SQL) statements (rather than the formula syntax). SQL Expressions are used in cases where report-processing efficiency is critical. Using SQL expressions can give report designers greater report processing performance by pushing data processing to the database server instead of the Crystal Reports engine as this is generally most efficient. Note The SQL syntax created in SQL Expressions must be appropriate to the source database. Different databases support various syntactical versions of SQL and even diverse degrees of functionality. This is explored in a document called "Using SQL Queries in Crystal Reports" that is available from the Downloads section of www.usingcrystal.com. Accessing Parameter FieldsParameter fields provide a means to create dynamic reports and provide your business users with an interactive method of driving the report content or layout they view. When a Crystal Report contains parameters, it requests certain pieces of information from the business user before processing. The involved Crystal Report can then use those inputted parameters to filter the data that is presented or even suppress entire report sections. Some examples of where parameters might be used include
The Parameter Fields branch of the Field Explorer tree is used to add existing or new parameter fields to your report. A listing of previously created parameters appears in this part of the Field Explorer tree. Once created, Parameter fields are added to the report by either clicking and dragging and dropping or by selecting the Parameter Fieldusing the Insert into Report button or action on the right-click menuand selecting the location. If a new parameter is required, it can also be created directly from the Field Explorer by using the New toolbar button. You are prompted to name the new parameter and enter some supporting information. This dialog is displayed in Figure 2.4. Figure 2.4. The Create New Parameter dialog enables you to specify a parameter name and supporting parameter type information.
At this point, it is only important to note the location of this field type. Implementing Running Total FieldsRunning total fields provide a means to incrementally calculate a total on a report as the records are processed. In contrast to the summary fields you will learn about later in the book, running total fields enable you to control how a total is calculated, when it is reset, and when it is displayed. Some examples in which running total fields might be used include
The Running Total Fields branch of the Field Explorer tree is used to add existing or new running total fields to your report. A listing of previously created running totals appears in this part of the Field Explorer tree. Once created, existing running total fields are added to the report by either clicking and dragging and dropping or by selecting the Running Total Fieldusing the Insert into Report button or action on the right-click menuand selecting the location. If a new running total is required, it can be created directly from the Field Explorer by using the New toolbar button. You are prompted to name the new running total. Select the field to calculate the running total on, the type of running total (for example, sum, average, variance, and so on), and some other supporting information about when the running total is to be evaluated and reset as shown in Figure 2.5. Figure 2.5. The Create Running Total Field dialog enables you to specify a Running Total Name and its supporting information. In the sample Customer Order Listing report from Chapter 1, an interesting running total to add would be one on the average order amount over time within each country. This running total tells senior sales management whether the average order size for each country is increasing or decreasing over time. To create this running total, follow these steps:
The completed Running Total dialog is shown in Figure 2.6 Figure 2.6. The Create Running Total Field dialog with Average Order Size running total information entered. After the running total has been created, it only needs to be dragged onto the report in the appropriate section. In this example, the appropriate section is the Detail section to show a changing average order size for every order. The Updated Sample Customer Order report is shown in Figure 2.7. Notice the changing average order size being calculated for each record. This type of report can now provide increasing value to senior sales management. Figure 2.7. A sample Orders report with Running Average Total on Order Size for each sales rep. Tip It's not necessary to place running total fields exclusively in the Details Section of your reports. By placing running total fields in different sections of your report, you can receive very interesting results. For example, if you place a running total in a Group Footer section, the running total displays the selected running total up to and including the current group. This can be very useful when analyzing average order size over time and grouping by month or quarter (for example, where you are only interested in some form of aggregated running total). As highlighted in the Running Total dialog, it is possible to both evaluate and reset the running total fields based on four different options. The first three are self-explanatoryfor each record, on the change of a specified field, or on the change of a specified group. The last option, using a formula, is a powerful and flexible option that should be more fully explored after reviewing Chapter 13, "Using Formulas and Custom Functions," on formula creation. In its simplest description, this option enables the creation of a conditional running total or the reset of that running total based on the results of a formula you have created. Tip A good use of this conditional summing is the creation of a running total that calculates the sum of all orders, but only evaluates (or sums in this case) the running total when the total order amount on a given record is greater than a certain amount (for example, 1,000). This running total, in effect, would provide a running total of only large orders so that business analysts can determine the percentage of revenue derived from large orders. Another common usage of this functionality is for financial statements (such as income statements) where a number of General Ledger transactions compose the rows retrieved from the database and different Running Totals are used to conditionally add the associated transaction value to their total if and only if certain conditions are met (for example, a certain account code is associated with the transaction value). The resulting running totals are then placed in a report to present financial statementoriented information such as Total Revenue, Operating Costs, Investment Income, Taxes, and so on. Using Group Name FieldsGroup Name fields only exist in a report after you have specified one or more groups to add to your report. You will read about that functionality later in this chapter. Group Name fields are created at the same time you add a Grouping to a report. Once created, existing Group Name fields are added to the report by either clicking and dragging and dropping or by selecting the Group Nameusing the Insert into Report button or action on the right-click menuand selecting the location. Special FieldsThe special fields in the Field Explorer are a number of system fields that Crystal Reports provides. These system fields and a brief description of each are presented in Table 2.1. The fields that were new to version 10 are suffixed with a *10 and the fields new to version XI are suffixed with *XI.
These special fields are added to the report by either clicking and dragging and dropping or by selecting the Special Fieldusing the Insert into Report button or action on the right-click menuand selecting the location. |
For detailed information on parameter creation and use as a means to filter report information, 
EAN: 2147483647
Pages: 365