لطفا منتظر باشید ...
Report Design Basics
There are some basic concepts we need to get out of the way before we move on to more advanced topics. We are just going to run through the basics of how a report is put together and the options we have for controlling this framework.
Report Sections
A Crystal Report comprises a number of different sections, as shown in the following screenshot. Each of these sections has a set of properties associated with it as well as a default behavior. For example, the default behavior of the Page Header section is that it will appear on the top of every page of your report.The sections of your report are clearly marked within the Report Designer by both a section name and number. This number doesn't mean much to us now, but a little later when we want to programmatically control report sections, this notation will come in handy. To view the properties for each of these sections, right-click on your report and select Format Section to open the dialog shown below.
You can choose to suppress a section if you don't want it to appear, or change it's appearance by adjusting the color or size of the section, based on your needs. Here is a rundown of the basic sections that may be contained within a report, as well as their default behavior.
Report Section
Description
Report HeaderAppears on the top of the very first page of the report, and is usually suppressed by default. Can be used to indicate the start of a new report or used as a cover sheet.
Report FooterAppears on the bottom of the very last page of the report, is shown by default, and can be used to summarize the report (number of records, print date, and so on).
Page HeaderAppears on the top of each page and can be used for column headings, the report title, page count, among other items.
Page FooterAppears on the bottom of each page and can be used to display page numbers, print dates, times, and so on.
Group HeaderAppears at the head of each group and is usually used to display the group name.
Group FooterAppears at the end of a group of records and is usually used to display the group name, subtotals, or summaries.
Details SectionOne for each record in your report, used to display columns of information, and may be expanded to larger sections of fields, or to create forms.
You can insert multiple sections into your report in a scenario where you might want to have two different page headers, or separate content out for ease of use and design.
You can control all of these sections through the use of conditional formatting, which we will touch on in Chapter 7, Formulas and Logic.
Report Formatting
Given the complex report requirements given to developers, reports can look like anything you can imagine - from a simple invoice to reports that match a preprinted form. To be honest, the majority of time spent developing a report will be in the report formatting. You probably already know where the report's data is coming from, and the record selection you wish to apply - all that remains is putting it together.There are a number of different levels where formatting can occur - you can apply some attributes by section (such as the background color, or behavior) but the majority of formatting is performed at the object level. Each object within your report will have a unique set of attributes that you can change to control the color, size, font, number, and date formats.While you can set these for a number of objects at once (by selecting multiple objects with Ctrl+left-click or a lasso, sometimes also called a stretch-box), Crystal Reports.NET does not support the concept of grouping or classing objects together, to make setting global properties easier.If you are using your report with web-based applications, Crystal Reports.NET does allow you to apply attributes from a style sheet to your report. For more information on how this works, check out Chapter 4: Report Integration for Web-Based Applications.
Field Objects
Field objects within Crystal Reports.NET contain the majority of your report content. From database fields that display records, to text objects that describe each column, to the summary fields that provide the totals, any report is basically just a collection of field objects (and a little formatting).There are eight different types of field objects that can be added to your report and they are available from the Field Explorer that is opened by default when you open the Report Designer within .NET (if it's not in view, remember it can be opened by pressing Ctrl-Alt-T).
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 and 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.
Important
If you are trying to insert a field from a stored procedure and you can't see it listed in the Field Explorer to add to your report, this is because by default Crystal Reports.NET will only show you the tables and views within your data source. To display stored procedures, right-click in the Report Designer and from the menu, select Designer | Default Settings and select the Database tab. There you will find a number of checkboxes 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 your 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 select Insert | Text Object from the menu. This will insert a text object onto your report in edit mode. You can type text directly into the text object and 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 pre-defined 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 DateThe date when the report was printed
Print TimeThe time when the report was printed
Modification DateThe date of the last modification to the report
Modification TimeThe time of the last modification to the report
Data DateThe date when the data was read from the database
Data TimeThe time when the data was read from the database
Record NumberAn internal, sequential record number assigned to all records returned to the report
Page NumberPage number
Group NumberAn internal, sequential number assigned to all groups
Total Page CountThe total page count
Report TitleThe report title, as saved in the report file's Summary Information
Report CommentsThe comments entered in the Summary Information
Record Selection FormulaThe Record Selection Formula used by the report
Group Selection FormulaThe group selection formula used by the report
File Path and NameThe full path and filename of the report file
File AuthorThe author of the report from the report file's Summary Information
File Creation DateThe date the report file was created
Page N of MWhere 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 and 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.At this point, you may also notice that the right-click menu includes an option for Subtotal - (subtotals and summary fields are similar) but whereas a subtotal refers specifically to a sum, a summary field could be a sum, average, or 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:
{@SalesTax} + {@InvoiceTotal}
Formula fields are created using the integrated Formula Editor. To see the editor, right-click on Formula Fields in the Field Explorer, select New, enter a name, and click OK.
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 7, 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 being used with 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 on the Parameter Fields section of the Field Explorer and select New, which will open the following dialog:
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: whether it is a discrete value, or range, 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, 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 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 EnterInvoiceId 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.InvoiceId} = {?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, then 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 a SQL Expression field ensures that your calculation will be performed on the database server itself, and you have access to all of the database functions of SQL.
To create a SQL Expression right-click on SQL Expression Fields in Field Explorer, select New, enter a name, and select OK.
Once you have created a 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 Chapters 3 and 4, 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.
Sorting and Grouping
Earlier in the chapter, we looked at adding sorting and grouping to your report through the Standard Expert. You can also add these features to your report without having to go back into the expert. To start, make sure you understand the difference between what Crystal refers to as sorting and grouping. Sorting in this case usually refers to record-level sorting - for example, if you had a simple listing report with a number of columns, you could sort the values to put them in date order, for example.Inserting a group is similar, in that the records are re-ordered, but they are also put into their own groups based on a field you have specified. In the example from before, a group could be inserted on a date and each day would have it's own group, with the related records listed as a member of that group. (As a side note, the default grouping option for dates is "by day", but you can also change that to a weekly, monthly, or quarterly interval.)To add simple record-level sorting to your report, right-click within the Report Designer and from the menu, select Report | Sort Records. A dialog will appear and allow you to select the fields for sorting.
An important note is that groups will always take precedence over record-level sorting, so they will always appear in this dialog first and cannot be removed here. It only makes sense, as you could create a group on one field (like a date) and then within the group, specify a record-level sort on an amount field, for example.For inserting groups into your report, you can right-click on the report, select Insert | Group, and select a field to serve as the basis for your groups.
Each distinct record stored within that field will produce a separate group, using the options you have specified in this dialog. A default group name will appear for each group, or you can specify a field or formula for the group name. A common example is where you are grouping on a state abbreviation (for instance, NY) and want to display the full state name (New York). You could either specify another field that contained the full state name, or create an if ... then formula to print it for you - although I wouldn't recommend it for this example, as the formula would have to be 50 lines long for every state in the US, let alone the rest of the world!
Record Selection and Filtering
Record selections are important in a report's design, and filtering is used to hone the content in to exactly what the user needs. It used to be that legacy reports would be hundreds of pages long, and users had to flip through them to find the data they needed. With record selection, we can pinpoint the required information and present it to the user in an easy-to-read format.You have already had a look at record selections with the Standard Expert, and there is not much more to say concerning the record selection operators themselves. The real power of record selection comes with actually getting your hands dirty and writing your own Record Selection Formula. For Crystal Reports.NET users who were not developers, this was always quite difficult, but for developers it opens up a world of possibilities.When working with the Record Selection Formula in your report, it is important to note that the formula itself has no output. For example, if you were writing a formula to appear on your report, you would actually want some result at the end, whether it is a simple sum or complex statistical calculation.For the Record Selection Formula, all we want returned is a Boolean variable. When the Record Selection Formula is applied to a record, either a True or False value is returned to us. If the condition is met and a True value is returned, the record is retrieved and will appear in the report. If the value returned is False, the record will be discarded and will not appear in the report.
As far as best practices for Record Selection Formulas are concerned, they can be as long and complex as required, but brevity is recommended.You should also double-check your Record Selection Formula for any potential bottlenecks when the report is processed. For example, if you were to create a Record Selection Formula to bring back a list of companies where their invoice total was over $10,000, it might look something like this:
{@InvoiceTotal} > 10000
But when you run your report, you notice that the report takes an extraordinary long time to run. What is happening is that this record selection relies on a formula field ({@InvoiceTotal}) that needs to be calculated before record selection can occur. So in this instance, every single record is being retrieved from the database, the InvoiceTotal formula is being calculated and then record selection is being applied locally.Try to write Record Selection Formulas that take advantage of the data server or platform you are working with and don't be afraid to use a custom SQL statement as the data source for your report if things get too difficult. If you already know SQL, make the most of what you know to create optimized queries from your data and move on to other things.
