Crystal Reports

Crystal Reports is a third party package that you use to design and generate presentation quality reports. PACE Advanced Reporting is integrated with Crystal Reports Version 11.5 (XI Service Pack 2). Crystal Reports 2008 (12.0.0.683) is also certified for use with Advanced Reporting.

Crystal Reports Overview

Crystal Reports reads database connection information from the PACE Server on which the job runs. To get started, install and configure any necessary database client software. Crystal Reports must be installed on the Server to run the reports, and on each Client machine to open and view report results. Eagle recommends using a 32-bit environment since Crystal Reports is a 32 bit application that uses 32 bit drivers. There are two ways in which you can create Crystal Reports:

Using the Crystal Reports application to create stand alone Crystal Reports in .RPT format. Register only the .RPT file. You do not have to install the Add In to create a stand alone report.
In a Visual Basic.NET format using Visual Basic. Crystal Reports.Net Version 11.5 is supported for use with Visual Basic.NET reports. To create reports using Visual Basic.NET with a Crystal Report embedded, register only the DLL.

You cannot have rules for Crystal Reports combined with rules for Data Dynamic Active Reports in an Advanced Report Profile. When developing an Advanced Report, use Crystal Reports to:

Change the data source on the fly. While creating a Crystal Report, you can connect to either an Oracle or SQL database directly and select a data source that exists on the Local machine. When the report is moved to a server and run, PACE changes the data source to the one that exists on the server. This is helpful when TNS names are different on the developer’s machine and the server.
Use parameters that were created in Crystal Reports. If the Crystal Report has the Parameter field, the value can be passed as an option value from an Advanced Report.
Provide internal options for report types where you need to pass the Entity, Effective Date, Begin Date, and End Date. This is especially helpful for reports created against the Data Mart database. PACE provides four internal options: *Report Entities, *Begin Date, *End Date, and *Effective Date. These are marked with an asterisk and a different color. These options are internal and non editable.

Create Stand Alone Crystal Reports

To create standalone Crystal Reports in .RPT format using Crystal Reports:

Start Crystal Reports.
On the Crystal Reports Start Page, select Blank Report under the New Reports section.
The Database Expert dialog box appears, displaying available data sources. The options depend on the data access components you select during installation.
Identify the data source.
There are two types of database drivers, which act as a gateway between Crystal Reports and a specific type of database or data access technology. When creating the report, you select a driver and the location of the data. When the report is executed and the data is loaded from the database to the report, the driver performs the query.
Direct access drivers. This type of driver allows you to report from a specific type of database. For example, if you select the Microsoft Access direct access driver, you are prompted for the filename of the Access MDB file. If you are using the Oracle direct access driver, you are prompted for a server name.
Indirect access drivers. This type of driver allows you to connect indirectly to an actual data source, for example, an OLE DB. They are built to read the data.
Once you specify the data source and location, you see the Crystal Reports Designer window, allowing you to design and test the reports.
Initially, the Design tab is the only tab on the report. When you select Print Preview to preview the report with data, the Preview tab appears. The Design tab is divided into five sections:
Report Header (RH)
Page Header (PH)
Details (D)
Report Footer (RF)
Page Footer (PF)
Select the Short Section Names check box on the Options dialog box to view the Report Header, Page Header, Details, Report Footer, and Page Footer section names as RH, PH, D, RF, and PF. To disable this option, click Options on the File menu.
Draw and arrange the data fields on the report. For more details on how to create Crystal Reports based on an Access database and OLE DB, see Creating Reports Based on an Access Database and Creating Crystal Reports Based on OLE DB (ADO).

Use Crystal Reports 2008 and Crystal Report Viewer

Crystal Reports 2008 is certified for use with Advanced Reporting. You can use either Crystal 2008 RPT files or Visual Basic.NET DLLs when using Crystal 2008 in Advanced Reports. To develop Crystal DLLs using Crystal 2008, you must install the Visual Studio 2008 add-in. Crystal 2008 is not supported with Visual Studio 2005.

Crystal 2008 must be installed on the server machine to run the reports with Crystal. Crystal 2008 must also be installed on all the client boxes to view the report results. Use the Crystal Report Viewer to open and view both Crystal 2008 and Crystal XI reports. The Crystal Report Viewer is displayed as shown below.

If Crystal XI is installed on the client box, you can still open Crystal XI and Crystal 2008 reports. However, they will open in the original Advanced Report viewer.

Create Reports Based on an Access Database

Before you create an Advanced Report Designer in Crystal Reports based on an Access database, you must create the PACE components for the design, such as OLAP Report, Advanced Report Rule, and Advanced Report Profile. After creating these components, you can create a Crystal Report.

To create a report based on an Access database:

Open a new Crystal Report.
On the Crystal Report Start page, click Blank Report under the New Reports section.
You see the Database Expert dialog box.
Expand the Create New Connection folder and click OK.
You see the Access/Excel (DAO) dialog box.
From the Database Type drop-down list, select Access and click Finish.
Expand the table node of the .MDB connection to see the list of tables.
Select all the tables you want for the report and click OK.
You see the Crystal Report Designer dialog box appears.
On the View menu, click Field Explorer.
You see the Field Explorer window, displaying all the tables with the fields you selected. You can expand the Database Fields node in Field Explorer to see the fields in the table.
Right-click the field and select Browse Data.
You see the subset of the values for the field and field type and size.
Drag the fields you want to appear on the report.
Click the Preview tab to preview the report.
Click the Design tab to return to the design section.
If you see x$# characters instead of field names, click Options on the File menu. On the Layout tab, click Show Field Names.
Save the report file with the .RPT extension.

Create Crystal Reports Based on OLE DB (ADO)

To create a report based on OLE DB:

Open a new Crystal Report.
In the New Reports section, click Blank Report.
You see the Database Expert dialog box.
Select OLE DB (ADO) and click OK.
You see the OLE DB dialog box.
From the Provider list, select a provider and click Next.
The OLE BD dialog box prompts you for connection information.
Provide the connection information, including Service, User ID, and Password, and then click Next.
Click Finish to connect to the database.
You see the Database Expert dialog box.
Select all the tables you want for the report and click OK.
You see the Crystal Report Designer window.
On the View menu, click Field Exporter.
The Field Explorer pane displays all of the tables with the fields that you selected.
Drag the fields you want on your report.
Click the Preview tab to preview the report.
Save your report.

Create Crystal Reports in Visual Basic.Net

To create a Crystal Report in Visual Basic:

Start Visual Studio.
Click New Project.
You see the New Project window. If you successfully install the Add In, Eagle Projects > PACE Advanced Reports is selected by default
Select the folder and project name. Click OK.
You see the Eagle PACE Reporting Wizard dialog box.
Select Crystal Reports.NET and click Finish.
You see the Crystal Reports Gallery dialog box.
Select As a Blank Report.
The Crystal Report is added to the project and you see the Microsoft Visual Studio window.
On the Tools menu, click Eagle Wizard.
You see the PACE Reports View dialog box.
Select the Available check box that corresponds to the report you want to make available to the project and click OK.
On the Tools menu, click Connect to Database.
You see the Database Expert dialog box.
Select a data source and click OK.
Select the tables for the report and click OK.
Drag the fields you want on the report.
Save your report.

Use the Crystal Reports Configuration Utility

The Crystal Reports Configuration utility, CrystalConfigurationUtility.exe, is provided with Eagle PACE V10.0. Use this utility to update *.config files to bind previous versions of Crystal Reports to the version currently being used. This is helpful when you have developed reports in previous versions of Crystal Reports, but later upgraded to a newer version. By mapping versions in the configuration files using this utility, you can still run your old RPT/DLL files no matter which version of Crystal Reports you are running.

Before using this utility, make sure you have terminal service into the box, or log into the physical machine.

To run the Crystal Reports Configuration utility:

Go to the \\server\dotnet folder.
Double click CrystalConfigurationUtility.exe.
The *.config files in the \\server\dotnet folder are updated. These configuration files include AdvReportTestUtility.exe.config, asmautoreg.exe.config, crregister.exe.config, ecat.exe.config, and render.exe.config.
When you run this utility, it gets the current version of Crystal Reports from assembly and updates the new version and MAX version to that version in the configuration file. The MIN version is updated to the lowest version of Crystal XI.

Working with Crystal Reports

Direct Data Base Connections

There are direct Data Base connections available for .Net Crystal Reports. If a report is designed from a SQL database, then override the DatabaseName variable value to the actual Database (DATAMART) Name. By default the database value is pace_master.

An example of the code from pacereportsfactory.vb follows:

<Assembly: PaceReportsAttribute("CrystalReports",
"CRY_DLL_SQL_DB")>
Public Class PaceReportsFactory
Implements Eagle.PaceReportsEx.IPaceReportFactory
'if connect to SQL server, Developer should override with the actual Database name being used by report.

Dim DatabaseName As String = "pace_master"

Using Text Objects

You can insert two fields in a text object instead of adding both fields as separate objects. This allows you to control the formatting of both fields by making changes to only one object.

When you insert fields in a text object, the fields are automatically trimmed (they do not have any extra white space on either side). This is useful when you want data like Last Name and First Name to appear with a comma and a space between them. For this, right-click in the section and click Insert Text Object. As you move the cursor over the report, an object frame appears. Drag the fields to the text object. Double-click inside the text object to select it for editing.

Add Summary Information

Summary information allows you to add the title, author, subject and comments about the report.

To add summary information:

Click Summary Info on the File menu.
Enter the information in the text boxes and click OK.
You see the title in the Report Header section.
Select the Field Explorer on the Design tab to see the same title appear as a report title.
Select Report Title from the special fields.
Drag it to the Report Header area and click Review.
The title appears in the Report Header section.
Right-click and select Format Field. Change the font and color of the title.

Select Specific Data

To select specific data in Crystal Reports, you create selection formulas in the Select Expert dialog box. For example, to prevent the report from displaying Security Description values of TOTAL, complete the following procedure:

Click the Design tab.
Select the Security Description field.
Right-click the field, and select Select Expert.
You see the Select Expert dialog box.
Select is not equal to from the drop-down list.
Select the TOTALvalue from the drop-down list and click OK.
Click the Preview tab.
You are asked whether to use Saved Data or Refresh Data.
Select Refresh Data.
The report now shows new data.

Group Report Data

To group data in Crystal Report:

On the Insert menu, click Insert Group.
You see the Insert Group dialog box.
Select the group and the sorting order for the report data, and then click OK.

Sort Records

To sort records in Crystal Reports:

Click the Preview tab.
On the Report menu, click Record Sort Expert.
You see the Record Sort Expert dialog box.
Select the field you want to sort in ascending or descending order and click OK.

Insert a Logo

To insert a logo in your report:

Click the Design tab.
On the Insert menu, click Insert Picture.
You see the Open dialog box.
Select the logo.
Once the object frame appears, drag the logo where you want it to appear.
Click the Preview tab to view the report with the logo.

Create Summary Fields

Summary fields allow you to create formulas.

To create a summary field:

Go to the Field Explorer on the Design tab.
Right-click the Formula Field, and select New.
You see the Formula Name dialog box.
In the Name field, type a name for the formula and click OK.
You see the Formula Workshop window. You can define the formula on this window.
Click Save to save the formula and close the window.
If there is an error in the syntax, a prompt appears. Use the new Formula Field like any other field on the report.

Add a Chart to the Report

To add a chart to the report:

On the Insert menu, click Chart.
You see a chart frame.
Drag this object to the report.
Once you place the object on the report you see the Chart Expert dialog box.
Select the fields for both X and Y axes for the Chart. Click the Type tab to select the report type.
Click OK to display the chart control on the report.
Click the Preview tab to view the chart in the report.

Create a Subreport

To create a Subreport:

On the Insert menu, click Subreport.
You see the Insert Subreport dialog box.
Select Choose an existing report and click OK.
Drag the Subreport frame to the report.
In this example, the Profile From Date and Profile To Date are coming from another report through a Subreport.

Add a Word Document or Other File into a Report

To add a Word document or another file into your report:

On the Insert menu, click OLE Object.
Select the file type and select either the Create New or Create from File option.
Select the file that you want to load into the Crystal Report.

Sort the Report

To sort your report by one or more fields:

On the Report menu, click Report Sort Expert.
You see the Record Sort Expert dialog box.
Add the fields you want the report to be sorted by and click OK.

Use Advanced Report Options from PACE

PACE supports Parameter fields that are created in a Crystal Report. PACE also provides internal options for report types. There are four internal options: *Report Entities, *Begin Date, *End Date, and *Effective Date.

The internal options are non-editable. They appear in a different color (blue) than the other options and display an asterisk (*). PACE also supports user defined options for Crystal Reports.

Create Parameter Field in Crystal Report

To create the Parameter Field in Crystal Report:

Go to Field Explorer on the Design tab and select New.
You see the Create New Parameter dialog box.
Click in the Value column, and select a field. Click OK.
Define the formula to include the Parameter Fields.
If the report is using *Report Entities or any other parameter that expects more than one value, create the Parameter field with the Allow Multiple Values set to True.
If you have an option for multiple values for Entity_ID, go to the Design tab and right-click the Entity ID field.
You see the Select Expert dialog box.
Select Formula from the drop-down list. Enter the following formula in the drop-down list on the right: {FUND_MASTER.ENTITY_ID} = ({?Report Entities}), and then click OK.
Save and register the report on the server so that the report becomes available from PACE.
Create a new Advanced Report Rule and select this report for the Designer. The Options section of the Advanced Report Rule displays three options you can map to the values so that they can be passed to the report during run time.
The first two parameters are mapped to internal options. The third parameter is mapped to a regular option. If this rule is used in an Advanced Report, the Entities and Effective Date are passed from the report and used as values for ParamEntity and ParamDate Parameter fields. For Status, the default values specified in the status option are passed to the report. You can override values from both PACE and Portal. But if you create the Crystal Report as a DLL via Visual Studio .NET 2005, you can pass option parameters.