Understanding Field Objects

As described in a previous hour, 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. In this hour, you will be introduced 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 6.1 shows the sample Crystal Report created in the last hour with the Field Explorer activated and docked on the left side of the screen. As previously mentioned, this can be docked on either side of the designer or at the bottom the screen. Alternatively the Field Explorer can freely float over any part of the design window by simply dragging and dropping it.

Figure 6.1. Crystal Reports Designer with the Field Explorer docked on the left side.

graphics/06fig01.jpg

The next seven sections introduce the different types of fields accessible from the Field Explorer and provide some introductory ideas on where they might be used in a report. Subsequent hours in the book will 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:

  • Fields that are being used in the report or fields that have been used by other fields (for example, formulas) being used in the report will be highlighted with a green check mark in front of them.

  • The buttons along the top of the Field Explorer (Insert, Browse, New, Edit, Rename, and Delete) will be enabled or disabled based on the availability of the selected Field type.

  • Detailed report field formatting, positioning, and resizing will be covered in Hour 8, "Fundamentals of Report Formatting."

Accessing Database Fields

The 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, and so on that you previously selected as data sources for this report (discussed in Hours 3 5). 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 in to 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.

graphics/lightbulb_icon.gif

If you are uncertain of exactly which fields to add to your report because of ambiguous (for example, WERKS, MENGE, WONKY) 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 will drop the first of the multiple chosen fields in the selected location on the report and will place 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 will be placed one line down and the placement algorithm will continue.

Accessing Formula Fields

Formula fields provide a means to add derived fields (that is, those not directly available in your database) into your Crystal Reports, such as a calculation. They also provide your business users (report consumers) with additional views of data. 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 (Chap5.rpt) from Hour 5 would include

  • Days Until Shipped (A date formula determining the difference between the two database fields Order Date and Ship Date)

  • Next Years Sales Projection (A numeric formula that multiples the database field Last Years Sales by 110%)

  • Custom Name Field to include the first letter of a customer contact's First Name (a database field) concatenated with a space and the contact's last name (another database field)

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 (for example, DaysUntilShipped from the Hour 5 sample report) will appear 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 functionality available through the right-click menu or Field Explorer action button and then selecting the location.

graphics/lightbulb_icon.gif

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 Hours 10, "Understanding and Implementing Formulas," and 17, "Using Formulas and Custom Functions to Implement Complex Business Logic."


If a new formula is required, it can be created directly from the Field Explorer by using the New toolbar button. You will be prompted to name the new formula and then select the method of creation. This dialog is displayed in Figure 6.2.

Figure 6.2. The Formula Name dialog.

graphics/06fig02.jpg

Using the Xtreme Sample Database and the sample report created in Hour 5 (chap5.rpt), one simple formula we 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, implement the following steps:

  1. Highlight the Formula Fields branch of the Field Explorer tree.

  2. Select New using either the New button or by right-clicking and selecting New from the fly-out menu.

  3. Enter the Formula Name Full Name in the Formula Creation dialog and select the Formula Editor using the Use Editor button.

  4. Scroll down in the Report Fields window (the top left window in the main frame) to locate and open the Customer table. Select the Contact First Name field by double-clicking on it. The field will be displayed in the main Formula Editing window.

  5. Add a space after the Contact First Name field and then type in + " " +. This will act to concatenate the two fields together and also add a space between the first name and the last name.

  6. Scroll down in the Report Fields window (the top left window in the main frame) to locate and open the Customer table. Select the Contact Last Name field by double-clicking on it. The field will be displayed in the main Formula Editing window.

  7. When you have confirmed that the main formula window looks exactly like that shown in Figure 6.3, save the Full Name formula by clicking the Save button and then closing the main Formula Editor window.

    Figure 6.3. The Formula Editor with a String concatenation formula created.

    graphics/06fig03.jpg

By selecting Save in the Formula Editor, we 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 on to the report beside the Customer Name.

Accessing SQL Expression Fields

The 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 will appear 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 Expression using the Insert into Report button or action on the right-click menu and selecting the location.

SQL Expressions are created in the same Formula Editor as formulas but use Structured Query Language (SQL) statements (see Hour 22 for more information) 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 control over report processing, such as pushing data processing to the database server instead of the Crystal Reports engine because this is often most efficient.

graphics/bookpencil_icon.gif

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 Hour 22, "Optimizing SQL Queries."


Accessing Parameter Fields

Parameter 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

  • A region parameter on a sales report

  • A profit center on a financial report

  • Beginning and ending dates on a transactional report

  • A department on an HR salary listing report

  • A salesperson name on a customer order listing report, as in the sample report from Hour 5

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 (for example, Employee Name in Hour 5's sample report) will appear 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 Field using the Insert into Report button or action on the right-click menu and 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 will be prompted to name the new parameter and enter some supporting information. This dialog is displayed in Figure 6.4.

Figure 6.4. The Create Parameter Field dialog.

graphics/06fig04.jpg

Detailed information on parameter creation and utilization as a means to filter report information is covered in Hour 12, "Implementing Parameters for Dynamic Reporting." At this point, it is only important to note the location of this field type.

Running Total Fields

Running 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

  • Running Total of Web Site Hits over Multiple Days/Weeks/Months/etc

  • Running Total of Sales Expenses over Weeks in a Quarter or Fiscal Year

  • Running Total of Average Order Amount over Time

  • Running Total of Employee Count over Time

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 will appear 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 Field using the Insert into Report button or action on the right-click menu and 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 will be 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 6.5.

Figure 6.5. The Create Running Total Field dialog.

graphics/06fig05.jpg

In our sample Customer Order Listing report from Hour 5, an interesting running total to add would be one on the average order amount over time for each sales rep. This running total will tell senior sales management if the average order size for each sales rep is increasing or decreasing over time. To create this running total:

  1. Prepare the sample report from Hour 5 for this new field by deleting the Ship Via title and associated database field to clear some room. Select these two fields in either the Design or Preview tab of Crystal Reports and press the Delete key. Also, sort the data by ascending date by accessing the Record Sorting Expert from either the Report menu or the Record Sort icon on the Expert Tools toolbar, and then selecting Order Date as a secondary sort order after Customer Name.

  2. Highlight the Running Total Fields branch of the Field Explorer tree.

  3. Select New using either the New toolbar button or by right-clicking and selecting New from the pop-up menu. This will bring up the dialog shown in Figure 6.5.

  4. Enter the name Avg Order Size for the Running Total Name.

  5. Select the Order Amount field from the Order Table as the Field to summarize by highlighting it in the field selection window and clicking on the Select button (>).

  6. Because we desire an average, select this from the Type of Summary drop-down box.

  7. We want to calculate the average order amount for each order, so select the For Each Record option in the Evaluate section.

  8. Because we want to calculate this for each salesperson, select the Reset On Change of Group option and select the Customer Name group in the Reset section and click OK to finish.

The completed Running Total dialog is shown in Figure 6.6.

Figure 6.6. The Edit Running Total Field dialog.

graphics/06fig06.jpg

After the running total has been created, it only needs to be dragged on to 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 6.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 6.7. A sample Orders report with Running Average Total on Order Size for each sales rep.

graphics/06fig07.jpg

graphics/lightbulb_icon.gif

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 will display 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-explanatory for 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 completing Hours 10 and 17 on formula creation. In its simplest description however, 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. 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.

Group Name Fields

Group Name fields will only exist in a report after you have specified one or more groups to add to your report. We will cover that functionality later in this hour. 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 Name using the Insert into Report button or action on the right-click menu and selecting the location.

Special Fields

The special fields provided in the Field Explorer are a number of system fields that Crystal Reports provides. These system fields and a brief description are provided in Table 6.1.

Table 6.1. Fields Available in Crystal Reports V9

Field

Description

Valid Locations on Report

Data Date

The date the data in your report was last retrieved.

Anywhere

Data Time

The time the data in your report was last retrieved.

Anywhere

File Author

The author of the report. This is set in Document Properties (File\Summary Info in the menu).

Anywhere

File Creation Date

The date the report was created.

Anywhere

File Path and Name

The file path and name for the report.

Anywhere

Group Number

An automatically created group numbering field.

Group Header or Group Footer sections only

Group Selection Formula

The current report's group selection formula. This is created by using the Select Expert covered in Hour 7.

Anywhere

Modification Date

Date that the report was last modified (in any way).

Anywhere

Modification Time

Time that the report was last modified (in any way).

Anywhere

Page N of M

Indicates Current Page on report relative to total number of pages.

Anywhere

Page Number

The Current Page number.

Anywhere

Print Date

Either the current date or a date specified in the Set Print Date and Time dialog under the Reports\Set Print and Date Time option.

Anywhere

Print Time

Either the current time or a time specified in the Set Print Date and Time dialog under the Reports\Set Print and Date Time option.

Anywhere

Record Number

An automatically created number that counts the records in the detail section of your report.

Details Section

Record Selection Formula

The current report's record selection formula. This is created by using the Select Expert covered in Hour 7.

Anywhere

Report Comments

Comments summarizing the report. This is set in Document Properties (File\Summary Info in the menu).

Anywhere but only the first 256 characters will be printed.

Report Title

The title of the report set in the Document Properties dialog (File\Summary Info in the menu).

Anywhere

Total Page Count

The total number of pages for this report.

Anywhere

These special fields are added to the report by either clicking and dragging and dropping or by selecting the Special Field using the Insert into Report button or action on the right-click menu and selecting the location.



Sams Teach Yourself Crystal Reports 9 in 24 Hours
Sams Teach Yourself Crystal Reports 9 in 24 Hours
ISBN: B003D7JUVW
EAN: N/A
Year: 2005
Pages: 230

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net