Info

Converts Number, Currency, or Date types to String For converting String and Boolean types to Number For converting Number, Currency, Date, Time, or Boolean to text For spelling out numbers or currency values (for example, 101 is One hundred and one) In addition to these, there are also functions for converting DateTime strings. You'll find them in the Formula Editor, under the heading Additional Functions. These functions will accept a DateTime string and return a Date field, a Time field, or...

Understanding the Crystal Decisions Crystal ReportsEngine Namespace

If you have worked with Visual Basic and Crystal Reports before, you are probably familiar with the functionality within the Crystal Reports Engine. The Crystal Reports Engine provides a low-level interface into the report itself and allows developers to control most aspects of the report programmatically. Within Visual Studio .NET, the CrystalDecisions.CrystalReports.Engine namespace is used to expose this functionality. If you are integrating your reports in a simple, view-only application,...

Drilling into Report Details

If you are working with a report that has groups inserted, you can drill down within the viewer to show the detailed records that make up that group. By default, these details are visible anyway, as shown in Figure 4-25. When you drill down into a group, a separate tab is opened within the preview window, showing only the group you have selected. For summary reports, you may want to hide the details and allow users to drill down if they need more information. This provides an easy way to cut...

Relational Databases

By far, the most popular data access method is through a native or ODBC connection to a relational database. The retail version of Crystal Reports that you would buy in a store ships native drivers for the most popular RDBMS, including DB 2, Informix, Oracle, and Sybase, among others. Most of these native drivers require that the standard database client be installed and configured before they can be used. Again, these drivers are not available with Crystal Reports .NET, so you will need to...

Enableusedummydata On Crystal Report

EnableDrillDown property, 145 EnableHideForDrillDown property, AreaFormat class, 295 EnableKeepTogether property AreaFormat class, 295 SectionFormat class, 296 EnableNewPageAfter property AreaFormat class, 295 SectionFormat class, 297 EnableNewPageBefore property AreaFormat class, 295 SectionFormat class, 297 EnablePrintAtBottomOfPage property AreaFormat class, 295 SectionFormat class, 297 EnableResetPageNumberAfter property AreaFormat class, 295 SectionFormat class, 297...

String Functions

Trims all spaces from the left-hand side of a string Trims all spaces from the right-hand side of a string Converts a string to all uppercase characters Converts a string to all lowercase characters Boolean (determines whether a string is numeric or not) Converts different types of fields to string

Working with Subreports

Within Crystal Reports .NET, multiple subreports can be combined into one main report, which allows you to create information-rich reports from multiple sources and display this information side by side. Both linked and unlinked subreports are available in the code download, found in the solution Open and run this solution from Visual Studio .NET and choose the report from the drop-down box at the top right of the window. To run the unlinked report, you will need access to the Northwind sample...

Drilling Down on Subreports

Multiple subreports can be inserted into a main report and provide a way of combining disparate information on a single report. A subreport within a Crystal Report is actually a report in its own right, with its own page numbering, sections, and other information. There are a number of subreport events that can be used as users drill through a report with subreports, including name of the subreport that was drilled into page number that the subreport is on location in the viewer where the...

Subreports

A subreport in Crystal Reports .NET cannot be located. When editing a subreport within Crystal Reports .NET (by right-clicking the subreport and selecting Edit Subreport), a tab is added to the bottom of the page. Previous versions of Crystal Reports would open a tab at the top of the page. You can use these tabs to navigate between different subreports that exist within your main report. A report with a subreport runs With subreports, performance can be a problem if your slowly or...

Using Viewer Events

Viewer events provide the ability to track when different events are fired from the browser for instance, when the user navigates through the pages of the report or when they refresh the report. These events can then be used to fire other code within your application. Although all of the different events have their own unique properties and methods, they all inherit a common property called Handled that is a Boolean value used to determine whether the event was fired and subsequently handled....

Conditional Formatting for Multiple Outcome Properties

With properties that can have a multiple outcome, the conditional formatting formula needs to be a bit different. In our previous Boolean example, only two things could happen either the field was suppressed or it wasn't. With multiple-outcome formulas dependent on the setting of a property, any number of things could happen. Using the same condition as above, if our value was over 10,000, we could have changed the field change color but to which color When working with multiple-outcome...

Migrating from Visual Basic

The Upgrade Wizard is a tool provided by Microsoft as part of the Visual Studio .NET package to assist us with migrating existing applications to the .NET Framework. Unfortunately, the wizard is more targeted at upgrading the Visual Basic components, which do not include the reports that may have been included in the application. To invoke the wizard, start Visual Studio .NET, select Open Project, and open an existing Visual Basic 6 application. This will launch the Visual Basic Upgrade Wizard....

A

For exponents, used for calculating squares (for example, 3A2 would return 9) Open operators.rpt in the Report Designer, and create a new formula by right-clicking Formula fields. By using the negate operator, we could calculate a value representing the number of items returned to the company from an order The negate function is also useful when working with financial information, in which a negative amount may indicate a credit. For formulas and logic that need to return a True or False value,...

C

Cached reports, binding to Report Viewer, 166-168 calculations. See formulas Calendar1stHalf function, Basic Syntax, 263 Calendar1stQtr function, Basic Syntax, 263 Calendar2ndHalf function, Basic Syntax, 263 Calendar2ndQtr function, Basic Syntax, 263 Calendar3rdQtr function, Basic Syntax, 263 Calendar4thQtr function, Basic Syntax, 263 caret (A), exponentiation in Basic Syntax, 258 Case clause, Basic Syntax, 267 case sensitivity of database server, 217 CBool function, Basic Syntax, 259 CCur...

Formatting Cross Tabs

Controlling the column and column sizes within a cross-tab is accomplished by changing the field widths and heights of the objects within the columns or rows. To change the field sizes within your cross-tab, locate the field you want to change and click to select it. This should put a single handle on each side of the field object you can resize the field by dragging these blue handles. As you resize one field in a column or row, the entire column or row is resized. Crystal Reports includes a...

Using Running Totals

Running totals provide an at-a-glance look at cumulative values in your report and display a running summary beside each record. With each release of Crystal Reports, running totals have grown in functionality, and they can now be used for a wide range of summary and analysis tasks. Running totals also feature a flexible evaluation and reset function that makes complex analysis easier. By using running totals in your report, you can quickly give users the information they need without having to...

Basic Syntax

In Chapter 8,Formulas and Logic, we looked at the differences between Basic Syntax and Crystal Syntax and created formulas using both. This appendix has been put together as a handy, in-depth reference of the differences between the two and provides listings of the functions and operators in each syntax. This list is by no means exhaustive, but it hopefully provides you with some idea of the main and most-used syntax in cases where Basic and Crystal Syntax differ. To change between Crystal and...

What Is Basic Syntax

With the introduction of Crystal Reports 7.0 came Basic syntax. This closely resembled Visual Basic code by using similar functions and operators but with the ability to access all of the Crystal-specific functions and features. Over time, the two syntaxes have grown closer together with the Crystal version having gone farther to reach its Basic cousin. Which syntax you choose depends upon your background and experience. If you're a dyed-in-the-wool Crystal Reports developer, the chances are...

Refresh Events

The ReportRefresh event has no arguments other than the inherited Handled property. It can be used to build metrics on how often a report is run or refreshed, and to pass information to users about the report before they launch a refresh, as shown here Private Sub source As Object, ByVal MyEvent As Handles CrystalReportViewerl.ReportRefresh MsgBox Please be advised this report takes up to 2 minutes to run. End Sub Refresh events are also key to improving application and data performance if your...

Printing and Exporting

Within the Crystal Reports Engine, there are a number of different ways you can produce report output, even without the Crystal Report Viewer. This functionality provides an easy way for you to print directly from your application or print batches of reports without any user intervention. We are going to start looking at this type of functionality with a simple print application by building on the sample application we are working with. To print your report from your application, the...

Printing a Report

To print a report, there is a simple PrintReport method that will invoke a standard Windows printer to select where you would like to print your report, how many copies, and other functions. To add this code to your custom viewer, drag and drop a button onto your form, and name it Print_Button. Change the Text property to Print. Double-click the Print button you have dropped onto your form and enter the following code in its Click event This will open a standard Windows print that will allow...

Inserting a New Group

To insert a new group into your report, right-click any blank area within your report and select Insert Group. This opens the dialog shown in Figure 3-9. The first choice you will need to make is which field you want to group on. This can be a database field, a formula field, or an SQL expression. Use the drop-down list provided to select a field. The fields that have been inserted onto your report will appear at the top of the list, and the fields from the database tables that have been added...

Working with Areas and Sections

Another often-used class is the ReportDefinition class, which is used to retrieve all of the areas, sections, and report objects shown in your report. An area within the context of a Crystal Report corresponds to the types of sections we talked about earlier in Chapter 2, Getting Started with Crystal Reports .NET. There are seven different types of areas, including You may remember from Chapter 2 that all of these different types of areas can also have multiple sections within them Details A,...

Crystal DecisionsWeb Namespace

The CrystalDecisions.Web namespace contains all of the classes that relate to functions available within the Web Forms Viewer itself. The following table illustrates the different classes that are available, as well as their use in Web applications. Contains the properties, methods, and events relating to the CrystalReportViewer and viewing reports. Note some properties of this class are inherited from CrystalReportViewerBase. Contains properties for setting the target browser edition, database...

Adding a Data Source to Your Report

Locate the type of datasource you would like to add to your report and expand the data source folder. Depending on what type of datasource you select, an additional dialog may open. For example, if you select ODBC RDO , a second dialog will open and prompt you for an ODBC data source. Likewise, if you select Access Excel DAO under More Data Sources, a second dialog will open and allow you to select a spreadsheet or database to report from. In this instance, we are working with the ODBC data...

Page Navigation and Zoom

To start, you probably will want to know what page you're are on at some point. Luckily for us, the Crystal Report Viewer has a simple method called GetCurrentPageNumber that allows us to get the page number of the page we are currently viewing. In the custom viewer we are working with, we are going to place a label on the form we'll call it PageNo_Label that contains the page number. Initially, we'll set this to Page 1 using the Text property, but after that, this can be set dynamically using...

Setting the Data Source Location

Another handy feature is the ability to change the database location of your reports. You can design a report on your test database, for example, and then later point it to a production database where your data resides. To change the database location of your report, select Database Set Location to open the dialog shown in Figure 3-7. Change the location rjf the dala source by selecting the current database or table and choosing the database or table to replace it with. Then click Replace. fs A...

Printing Your Report

Now, if you have already done some report integration with Windows applications, you may have noticed that the Web Forms Viewer is missing one very important icon the Print button. When a Crystal Report is viewed on the Web, it is actually rendered in static HTML 3.2 or HTML 4.0, with all of the report elements represented in HTML code and syntax. This makes things difficult when it comes time to print your report. In a case where you were just using the plain old viewer with little or no...

Creating the Setup Project

Firstly, just as in the section on Windows deployment, we need a simple Web reporting application to deploy, and one has been included for you in the downloadable files for this chapter in a project named ch10_web_app. This application consists of a single Web Form that has the Web version of the Crystal Report Viewer embedded and allows you to preview the same Employee Listing report that we looked at when working with the sample Windows application earlier. To see this application working,...

Using the Crystal Report Viewer for Web Forms

Tablelayoutpanel

For report files that live externally to your application for instance, as a standalone report file, created with either this or a previous version of Crystal Reports , there is not much to creating a simple preview form for your report. We are going to walk through that process in the following section. Earlier we created a new project called web_viewer_basic and within that project there should be a default Web Form WebForm1.aspx that was created when you created the project. To start, we...

Creating Formulas with Crystal Syntax

Over the past few releases, Crystal syntax and Basic syntax have moved closer together through the development of similar functions and operators. In the next section, we'll have a look at some of the remaining differences. We'll be using a project called CrystalSyntax_basic to look at this syntax. This project is available in the download code from www.wrox.com, or you can build it from scratch by following the instructions. If you decide to build it yourself, create a Windows application now...

Verifying Database Structures

As your database structures evolve and change, reports you have created from these structures may no longer work because of different field names, types, and so on. To ensure that the changes made in the database are reflected and accounted for in your existing reports, you will need to verify the database that they were created from after any changes by selecting Database Verify Database. If you have databases or tables in your report that are not used, you may receive the message Verify files...

Customizing Report Fields at Run Time

Another handy feature that is often overlooked is the ability to customize the report fields at run time. This feature makes it possible to create generic reports with blank formula fields laid out on the report design and then specify which database fields should be used to fill these fields at run time. But, before we can dive in to learning this technique, we will need to create a new project within our solution. To create a new project, select File New Project and, in this instance, create...

Crystal Decisions Knowledge Base

Crystal Decisions has recently revamped its support Web site with a new comprehensive search engine that allows you to seach multiple categories of documents, including the Knowledge Base. The Crystal Decisions Knowledge Base provides a comprehensive selection of articles across the range of Crystal Decisions products and is updated twice a week with over 100 documents relating to Crystal Reports .NET. To search for articles related to Crystal Reports .NET, search on the following keywords...

Binding to an Untyped Report

When working with Crystal Reports .NET, you can add individual report files to your project and use and reference them to print reports from your application. Taking this a step further, you could also use these reports as components, which is where we start to look at typing. As mentioned earlier, when integrating reports into an application, we can either use strongly typed reports or untyped reports. If you have been working with Visual Studio .NET for any length of time, you have probably...

Working with SQL Commands and Expressions

In addition to reporting directly from database tables, views, and stored procedures, you also have the ability to use custom SQL commands as the datasource for your report. Using this method, you can create a virtual table that contains all of the fields you want to use. This functionality offers a flexible alternative to using Crystal Reports' own database and linking functionality, and can help you reuse the investment you have made in other report tools, or existing SQL statements. SQL...

Creating a Parameter Field

The first step in creating a parameter field is specifying a name for that field. Once you have created your parameter field, Crystal Reports will enclose this field name in curly brackets, preceded by a question mark to indicate it is a parameter field ParamFieldName . You can also enter prompting text that will appear whenever the Parameter Field dialog appears, as shown in Figure 3-45. Prompting text should help the user understand what to enter in the value field of the dialog for example,...

Understanding Conditional Formatting

There are two different kinds of conditional formatting available within Crystal Reports, based on the two different kinds of properties available for Crystal Reports elements. The first deals with Boolean properties, which include things like Suppress and Can Grow, and have an on off state. The second deals with properties that can have multiple outcomes, like font or section colors. While the concepts behind using these two different types of properties are similar, the formulas behind them...

Differences from Basic Syntax

To start with, a Crystal syntax formula does not require the Formula tag at the end to output the formula results. This is the statement from the Local_Customer_Flag formula field in the customer sales report. You can access this field by opening the report in Visual Studio .NET, selecting View Other Windows Document Outline from the main toolbar, and, in the tree that appears, selecting Formula Fields, right-clicking local_customer_flag, and then selecting Edit. Following is a Basic syntax...

Creating a Report from an Ado Net Dataset

The CustomerOrders dataset for the database object we have built will be populated when the application is started. When designing a report based on this dataset, we cannot browse the content of any of the fields as the dataset hasn't been populated at this point, but if you do have any questions about what type of content the fields could contain, view the database to find out, or use a viewing application like the one we have just built. To create a report based on an ADO .NET dataset, start...

Application Data

Most applications today are developed on an underlying database or other data source, and Crystal Reports .NET can use this application data as the source for your reports, as we saw in the last chapter. Probably the most compelling reason to use application data is that the logic from the application can be reused in your report. For example, if you have a data-bound grid that displays a number of orders together with calculated fields for order totals, sales tax, shipping, and so on, you...

Binding by Report Name

To bind a report using the report name, all you need to do is set the ReportSource property, either through the property page or through the form's code. You will want to add this to the form's Load method, which can be accessed by double-clicking the form we are working with. To set the ReportSource property, we need to specify the report file name and path, as shown here Because we have already added the report to our application, we could also bind the report by creating a report object,...

Isbn Is 0-764-55370-0

Customizing the Appearance and Layout of the Report Viewer 173 Chapter 6 Creating XML Report Web Services_193 XML Report Web Services Overview 194 What Are XML Report Web Services 194 How Would I Use an XML Report Web Service 196 Creating XML Report Web Services 196 Creating Basic Report Web Services 197 Creating Report Web Services with Multiple Reports 201 Utilizing the Generic Report Web Service 202 Consuming XML Report Web Services 204 External Report Web Service 205 Internal Report Web...

P

Packaging and Deployment Wizard, 311-312 PageContentHeight property, PrintOptions class, 281 PageContentWidth property, PrintOptions class, 281 PageFooter area, 294 PageHeader area, 294 Page_Init event, 158, 162, 163-164, 170 Page_Load event, 178, 184 PageMargins property, PrintOptions class, 281 PageNofM function, Basic Syntax, 266 PageNumber function, Basic Syntax, 266 PageNumberToBeginSearch property, Search event, 142 pages breaking between areas, 295 between groups, 75 displaying in...

Conditional Formatting for Boolean Properties

With these properties, there is either a True or False status. If a field has the Suppress property checked, the property is True, and it will be suppressed. If not, the property is False, and the field is shown. When working with conditional formatting on these types of properties, all we need to do is specify a condition. If that condition is True, then the property will be set to True. For the following walkthrough of conditional formatting, open the Customer Sales report customer_sales.rpt...

Setting Default Properties

By default, Crystal Reports .NET will have a number of properties preset. These include the font and formatting for fields in your report, the page size, margins and layout, and so forth. For more control over the reports you create, you can actually change these default properties. One of the most common scenarios is that Crystal Reports .NET defaults the font to Times New Roman and sets a specific font size for different types of fields. Your standard report template may be in Arial, so you...

Binding to a Strongly Typed Report

Finally, you can choose to add a strongly typed report component, which probably has the simplest binding method of all. First of all, add the report that you wish to bind to your project. In our case, this will be sales_graph.rpt. To create a strongly typed ReportDocument component, drag the ReportDocument component onto your Web Form. The code for this Web Form is available in the code download for the chapter as web_viewer_stronglytyped. This will open the dialog shown in Figure 5-12 and...

Determining Run Time File Requirements

With the main components of your application added to the setup project and all of the report files as well, you would now need to determine which run-time files are required to make those same reports work on the target machine. When looking at the run-time file requirements for Crystal Reports, there are a number of considerations, including the data source for your report and any drivers in use and export formats you wish to support, among other things. There are four different types of...

Basic Syntax Coding Conventions

The structure used by Basic syntax in Crystal Reports closely resembles the structure used in Visual Basic, but there are a few slight differences. Open operators.rpt from the sample code, right-click Formula Fields in the Field Explorer, and select New. Enter LearnSyntax in the dialog as the name of the formula. To start with, field names are enclosed in braces and use the naming convention of tablename. fieldname so enter the following formula that would calculate extended price in...

Viewer Methods

String Picture Crystal Report

When working with the CrystalReportViewer, we have a number of methods available to us, which will allow us to integrate specific viewer functions into our application. As we move through this section, keep in mind that these methods can be used to create your own look and feel for the report preview window. Create a new Web Form, which we'll call web_viewer_methods. Again, the code for this application is included with the download code. Drag a CrystalReportViewer onto this form. Include the...

Symbols And Numerics

amp ampersand , concatenation operator, 261, 342 at sign , prefixing formulas, 251, 256 backslash , integer divide in Basic Syntax, 258 a caret , exponentiation in Basic Syntax, 258 colon , preceding REM statement, 257 curly brackets enclosing field names in Basic Syntax, 256 enclosing parameter field, 56, 101 equal to operator Basic Syntax, 259 joins, 223 gt greater than operator Basic Syntax, 259 joins, 223 gt greater than or equal to operator Basic Syntax, 259 joins, 223 lt less than...

Usa

Current Page No 1 Total Page No 1 Zoom Factor Page Width Current Page No 1 Total Page No 1 Zoom Factor Page Width Don't forget that, when you do pass the table and field name to the Report Engine, we will need to use the standard Crystal Reports format for displaying database fields with the convention of tablename.fieldname, enclosed in curly braces for example, Customer.Country . And, you can also use this same technique to control Grouping, Sorting, and more anywhere a formula field can be...

Control Structures

Crystal Reports supports a number of control structures that can control branching within a formula. lf Then statements provide an easy method for controlling branching within your formula text. lf Then statements can work on the basis of a single condition, for instance If Customer.Country USA Then Formula Local Customer In the customer sales report example that's included with this chapter customer_sales.rpt , we can create a Formula Field that will assess if the value in the Country field is...

Creating Record Selection Formulas

Record selection is a key component of most reports, for it is here that the results are filtered to show only the information required. In Chapter 2, Getting Started with Crystal Reports .NET, we had a first look at simple record selection and some of the operators that can be used to filter your report. Now that we're working with Crystal syntax, we can look at writing these formulas ourselves. First, all record selection formulas have to be written in Crystal syntax. There is no option to...

Crystal Developers Journal

Crystal Developers Journal CDJ is an independent publication written to help end users and developers learn advanced techniques for making the most of the tools available from Crystal Decisions, including Crystal Reports, Crystal Analysis, and Crystal Enterprise. The Web site includes original articles and relevant articles from other Web sites, and you can learn advanced techniques for designing reports and integrating reporting capabilities into your own applications or environment. Learn how...

Setting a Database Alias

Aliases are used when you need to reference a table in a report more than once. A common example would be where you had an employee table with a supervisor ID that was also the employee ID of the supervisor. To get Crystal Reports .NET to reference the same table, you would need to add it to the report a second time, and give it an alias like EmployeeSupervisor. To set a database alias, open the Database Expert by right-clicking and selecting Database Add Remove Database. In the window on the...

Understanding the Crystal Decisions Windows Forms Namespace

The CrystalDecisions.Windows.Forms namespace consists of a number of classes that provide functionality specific to viewing reports. As you look through the classes below, you can easily map each back to some function within the viewer itself. In the section immediately following, we are going to look at each of these classes in depth and learn what can be done with each. Contains the properties, methods, and events relating to the Crystal ReportViewer and viewing reports. DrillEventArgs and...

Optimizing Report Performance

If you have worked with reporting applications before, there is a usually a bit of time spent on optimizing report performance users are not happy with reports that run for three minutes, let alone three hours. Over the years, there have been significant enhancements within the Crystal Print Engine that have improved performance and cut down on processing time, but the majority of poor report performance does not lie within the Report Designer but rather in how the report is designed and in the...

Myreport.printoptions.printername

In this case, we are going to print one copy of pages 1 to 999 from our report to your default printer. If you double-click the command button you have added to the form, you can add the method call to the code view, and it would look like this Private Sub Print_Button_Click ByVal sender As System.Object, ByVal e As System.EventArgs Handles Print_Button.Click Dim myReport As New employee_listing myReport.PrintToPrinter 1, True, 1, 999 End Sub In this case, we used 999, assuming that would be a...

Adding the Report Viewer to a Windows Form

Earlier in the chapter, we quickly dragged the Report Viewer onto a form and previewed your first report the good news is that there is not much more involved in adding the Report Viewer to a form in your application. The Crystal Report Viewer is available from the Windows Forms Toolbox and you can drag it directly onto your form or draw the viewer on your form to the size required. You can easily add the Crystal Report Viewer to an existing form to display a report side-by-side with other...

Working with Report Objects

Within the sections of your report, there are a number of report objects. You are probably already familiar with these objects since they are the fields, graphs, and cross-tab objects among others that appear on your report. Each of the particular object types within your report has its own unique formatting properties and may also share common formatting options with other types of objects as well. To determine what type of object you are working with and subsequently understand what options...

Web Forms Viewer

Drilling down into the group tree cannot be done after using the ShowGroupTree method. If you use the ShowGroupTree method, a bug in Crystal Reports .NET will not allow you to drill down into the group tree. This issue has been tracked by Crystal Decisions and should be fixed in future releases of the product. The quality of images in the Web Forms Viewer needs to improve. When working with reports that contain graphs and other pictures in the Web Form, the default resolution is 96 dpi. This...

TopNGroup Sorting

TopN Group Sorting is a function of Crystal Reports used to sort groups according to a summary field that has been created based on that group. This function is often used to determine something like the top 20 customers that is, Top N, where N is 20 or the top or bottom five products, and it can also be used to order groups based on a summary field. Before you can use TopN Group Sorting in your report, you need to make sure that you have two things inserted onto your report a group and a...