Info

Crypto Ultimatum

Top Ways To Earn Money From Cryptocurrencies

Get Instant Access

Database Fields

Database fields can be inserted from any of the tables, views, or stored procedures that appear in your report. Database fields are shown on your report using the notation of TableName.FieldName. Once a database field has been inserted into your report, a red checkmark will appear beside the field in the Field Explorer to indicate it has been used.

If you are trying to insert a field from a stored procedure and you can't see it listed in the Field Explorer, this is because by default Crystal Reports .NET will show you the tables and views only within your data source. To display stored procedures, right-click in the Report Designer. From the menu, select Designer | Default Settings and then select the Database tab. There you will find a number of check boxes for the different types of data items you can add to your report, including stored procedures.

Text Objects

Text objects are used in a report for typing text that will appear in that report, such as the column headings and comments. To insert a text object, right-click on top of your Report Designer in any section, and then select Insert ^ Text Object from the menu. This will insert a text object into your report in edit mode. You can type text directly into the text object. When you are finished, click anywhere outside the text object to get out of edit mode.

If you want to edit a text object already in place on your report, simply double-click the field to put it back into edit mode. If you have a large amount of text that you need to put into a text object (like an existing form letter), switch to the edit view and right-click directly on top of the text object. In the menu that appears, you will have the option to browse for and import a text file directly into the text object. You can also format a text object with tab stops, alignment options, and even line spacing by right-clicking directly on top of the object and selecting Format from the right-click menu.

Special Fields

Special fields within a Crystal Report are predefined fields that serve a specific function within your Report Designer. Examples of these special fields include page numbers, print dates, and data dates. A complete list of these special fields has been included below:

Special Field Name

Description

Print Date

The date when the report was printed

Print Time

The time when the report was printed

Modification Date

The date of the last modification to the report

Modification Time

The time of the last modification to the report

Data Date

The date when the data was read from the database

Data Time

The time when the data was read from the database

Table continued on following page

Special Field Name

Description

Record Number

An internal, sequential record number assigned to all records returned to the report

Page Number

Page number

Group Number

An internal, sequential number assigned to all groups

Total Page Count

The total page count

Report Title

The report title, as saved in the report file's Summary Information

Report Comments

The comments entered in the Summary Information

Record Selection Formula

The Record Selection Formula used by the report

Group Selection Formula

The group selection formula used by the report

File Path and Name

The full path and filename of the report file

File Author

The author of the report from the report file's Summary Information

File Creation Date

The date the report file was created

Page N of M

Where N is the current page and M is the total page count

Summary Fields

Earlier in the chapter, we looked at creating a report using the Standard Expert. One of the tabs in the expert was for Total, where a summary field could be inserted into your report. Summary fields are usually associated with groups or a grand total in your report and can be inserted into your report by right-clicking anywhere within the Report Designer and selecting Insert ^ Summary from the menu that appears. This opens the dialog shown in Figure 2-27.

At this point, you may also notice that the right-click menu includes an option for Subtotal; subtotals and summary fields are similar, but a subtotal refers specifically to a sum, whereas a summary field could be a sum, an average, or a standard deviation.

Formula Fields

Crystal Reports .NET features a rich formula language that has evolved over the years as a powerful way to add complex calculations to reports. Formula fields appear in curly braces and are prefixed by the @ symbol; a formula used within another formula would look like this:

Formula fields are created using the integrated Formula Editor. To see the editor, right-click Formula Fields in the Field Explorer, select New, enter a name, and then click OK to open the dialog shown in Figure 2-28.

Figure 2-27

When working with formula fields, you have a choice of two different types of syntax: Crystal Syntax or Basic Syntax. If you have worked with Crystal Reports before, you will probably be familiar with Crystal Syntax. It was the original formula language available with Crystal Reports and is still used for Record Selection Formulas and conditional formatting.

Basic Syntax was introduced to eliminate the need to learn a second formula syntax. The syntax, functions, and control structures are similar to Visual Basic, which many developers are familiar with, and it is easy for developers to create formulas using a language that is familiar to them.

Which language you use depends on what facet of Crystal Reports you are working with. As I mentioned earlier, the record and group selection formulas within Crystal Reports are written using Crystal Syntax exclusively, so you are going to have to learn a little bit anyway. For formulas that will appear on your report, you have a choice of using either Crystal or Basic Syntax. (You can't mix the two in one formula, but you can mix the two different types of formulas in one report.) A drop-down list in the Formula Editor controls the syntax, and you can switch between the two if required.

Formulas are covered in length in Chapter 8, "Formulas and Logic," but keep in mind you may see the Formula Editor appear in other places throughout this book; it is also used to create Record Selection Formulas and perform conditional formatting, among other things.

Parameter Fields

Parameter fields within Crystal Reports .NET are used to prompt the user to enter information when the report is run. Parameters can be used in a number of different ways, from simple data entry (like entering the name of a user to be displayed on the report) to record selection (to filter the content of a report).

Parameter fields are designated using curly braces and are prefixed by a question mark, so a parameter field in use in a formula might look something like this:

If {?EnterCountry} = "USA" then "North America"

To insert a parameter field into your report, right-click the Parameter Fields section of the Field Explorer and select New, which will open the dialog shown in Figure 2-29.

For simple parameters, you will need to give your parameter a name — the question mark prefix will be added for you — and specify some prompting text and a field type for your parameter. By default, parameter fields are set to be strings, but there are actually several different types available including:

Boolean

Currency

Date

Date Time

Number

String

Time

You will also need to determine what type of values you want to be entered, such as a discrete value or a range of values, among others things.

Once you have created your formula field and inserted it into your report, Crystal Reports .NET will display a default dialog prompting the details just entered (shown in Figure 2-30) whenever your report is previewed. Most developers find this is a bit too generic for their own use and prefer to create their own interface with their own forms, including drop-down boxes, and so on. However, if you are not too concerned about how the prompt appears, this is all you need.

The parameter field can now be used just like any other field in your report: You can place it on your report and use it in formulas. To use a parameter field with record selection, you will first need to create a parameter field to accept the input and then to set the record selection to be equal to this parameter field. For example, if you were going to prompt the user for an invoice number to reprint an invoice, you would probably want to create a parameter field called Enterlnvoiceld and set the type to be numeric.

From that point, you would need to alter the record selection to use this parameter field. In this instance, the Record Selection Formula might look something like this:

{Orders.Invoiceld} = {?EnterInvoiceId}

When the report is refreshed, the user will be prompted to enter an invoice ID, which in turn will be passed to the SQL statement and used to retrieve the records for the report.

An import concept to remember is that the Record Selection Formula must always return a Boolean value. If the value returned is True, the record will be returned to the report. If the value is False, it will just move on to the next record.

SQL Expression Fields

In order to make the most of your database server, Crystal Reports .NET allows you to use SQL Expression instead of (or in addition to) Crystal Formulas. Using an SQL Expression field ensures that your calculation will be performed on the database server itself and gives you access to all of the database functions of SQL.

To create a SQL Expression, right-click SQL Expression Fields in Field Explorer, select New, enter a name, and select OK.

Once you have created an SQL expression field using the SQL Expression Editor, you can drag the field from the Field Explorer onto your report, and it will behave just like any other database field.

Unbound Fields

Another definite enhancement to Crystal Reports .NET is the ability to use unbound fields — that is, fields that are not tied to a specific data source. Using unbound fields, you can create a generic report and then programmatically set the content of the fields at run time. This is similar to how Crystal Reports used TTX text files in the past to hold the field structure for a data source, but Crystal Reports .NET points these fields to a dataset.

There are seven different types of unbound fields that you can add to your report:

Boolean

Currency

Date

Date Time

Number

String

Time

When you drag an unbound field onto your report design, it behaves like a placeholder. When we talk about integration for Windows and Web Applications in Chapter 4, "Report Integration for Windows-Based Applications," and Chapter 5, "Report Integration for Web-Based Applications," you will learn how to bind data from your project with this field and display the same data in your report. For now, you need to know that until run time, these fields will look and act like formula fields (right down to an @ prefix on each), but they are actually unbound fields that will be used later.

Was this article helpful?

0 0

Post a comment