SYSPRO Reporting Services > Crystal Reports

Crystal Reports

Crystal Reports is a powerful and flexible tool that enables you to design both simple and complex or specialized reports. You can use formulas, cross-tabs, sub-reports and conditional formatting to customize reports.

Report designing in Crystal Reports is a specialized and extremely wide area. This section covers only the basic report designing concepts. For detailed information and reference guides to Crystal Reports, please refer to the Crystal Reports Online Help.

Launching Crystal Reports

Crystal Reports is embedded in SYSPRO Reporting Services. The full reporting, customization and design capabilities of Crystal Reports is available when you select the Design Report function in SYSPRO Reporting Services.

Basic requirements

Ensure that the Report Designer option is enabled for the the operator code and that Crystal Designer is installed on the operator's workstation.

Launch options

You can launch Crystal Reports in two ways:

  1. Select the Design Report option in SYSPRO Reporting Services:

    • From the toolbar in the Report Designer window.

    • From the Edit menu in the Report Designer window.

    • From the menu that is displayed when you right-click on a report template in the Report Designer window.

    • From the menu that is displayed when you right-click on the report name in the SRS main menu.

    • From the Setup menu in the SRS main menu.

  2. Select Crystal Reports from Windows Explorer. This displays the Start Page tab which includes general information regarding the SYSPRO add-in programs and reporting.

    From this page you can:

    • Open a blank report.

    • Select a report creation wizard program.

    • Open a recently used report.

[Note]

If you don't open Crystal Reports using the Design Report option, then you will be unable to save the report automatically to the SYSPRO application server and you will be unable to use the SYSPRO Add-Ins menu option or the SYSPRO toolbar.

SYSPRO Add-ins

Crystal Designer has been customized to save report templates automatically on the SYSPRO application server. This customization was done as SYSPRO Add-ins which are available from the Add-Ins menu in Crystal Reports and the SYSPRO toolbar in Crystal Reports.

There are two options available from the Add-ins menu in Crystal Reports:

Option Description
Save Report This saves the report template using the existing report template name.
Save Report For

This gives you the option of saving the report for all companies (system-wide) or a specific company, group, operator or role.

When you use this option, a new report template is created on the SYSPRO application server.

Report templates are automatically saved to the \Base\ReportingCustomized\ReportTemplate folder on the SYSPRO application folder.

You cannot use the Save option from the File menu in Crystal Reports to create a report template for different levels. You can only do this by using the SYSPRO add-in program Save Report For.

If you open a report template using the Design Report option from within SYSPRO Reporting Services, then:

  • You can either use the Save option from the File menu in Crystal Reports or the Save Report option in the SYSPRO Add-ins menu. Both these methods save the template, using the same template name, automatically on the SYSPRO application server. This applies to system-wide and level-specific report templates.

  • You can use the Save Report For option in the SYSPRO Add-ins menu to create a report template for a different level, for example to create a report template specific to an operator group.

If you open a report template using Crystal Reports from Windows Explorer, then:

  • The Save option from the File menu in Crystal Reports does not automatically save the report template to the SYSPRO application server. If you select this option, then you need to select a folder location on your workstation and provide a file name for the report template. The report template is then saved on your workstation.

  • You cannot use the Save Report SYSPRO Add-ins program. The program returns a warning message and the template is not saved. It does not matter if the template is system-wide or level-specific.

  • You cannot use the Save Report For SYSPRO Add-ins program to create a level-specific report template.

If you did not use the Design Report option in SYSPRO Reporting Services to open or create the report template (or you used one of the report creation wizard programs in Crystal Reports to create the report) then you need to do the following:

  1. Use the Save option from the File menu in Crystal Reports to save the report template to a location on your workstation.

  2. Copy the report template to the \Base\ReportingCustomized\ReportTemplate folder on the SYSPRO application folder.

  3. Use the Add other menu item option in SYSPRO Reporting Services to add this report to the SRS menu.

[Note]

Ensure that you receive the Report saved confirmation message before you close Crystal Reports. This ensures that the report was saved to the SYSPRO application server.

Report Design Environment

Field Explorer

You use the Field Explorer pane to insert, modify or delete fields on the Design and Preview panes of Crystal Reports. This pane is available by default when you open Crystal Reports, from the Field Explorer option in the View menu, or from the from the Standard toolbar.

The following groups are available in the tree view of the Field Explorer pane:

  • Database Fields

  • Formula Fields

  • SQL Expression Fields

  • Parameter Fields

  • Running Total Fields

  • Group Name Fields

  • Special Fields.

Report Explorer

You use the Report Explorer pane to view report objects that already exist on the report. This pane is available:

  • From the Report Explorer option (Crystal Reports - View - Report Explorer).

  • From the Standard toolbar.

The content of the Report Explorer pane represents the content of the report in a treeview. The root node is the report itself, while the first-level nodes represent the report's sections. Within each section the report's fields and objects are listed. Any item you select in the treeview is selected in either the Design or the Preview panes.

All the actions you can perform on a selected field or object are available from the menu that is displayed when you right-click on the field or object.

[Note]

  1. You cannot add additional field or report objects in the Report Explorer pane, but you can delete existing fields and objects.

  2. The Report Explorer pane is a docking pane which can be placed anywhere on the Crystal Reports for SYSPRO window. You can also pin or auto-hide the docking pane.

Design Pane

The Design pane is the pane in Crystal Reports which you use the most. This pane is available:

  • By default when you open Crystal Reports.

  • From the Design option (Crystal Reports - View - Design).

  • By selecting the Design tab.

[Note]

You cannot hide the Design pane.

The Design pane displays the report area and the report sections are clearly marked. Fields can be dragged and dropped from the Field Explorer pane into the Design pane.

The Design pane provides a very efficient environment for designing a report, because you work with a visible data representation and not with the data itself. You can therefore add and delete fields and objects, move them around, set up complex formulas, and more, without using any additional resources needed to select the data.

When you first begin creating a report, Crystal Reports automatically creates five default sections in the Design pane:

  • Report Header

  • Page Header

  • Details

  • Report Footer

  • Page Footer.

You can add additional sections using the Section Expert program. When you insert groups, summaries or subtotals, the program creates the following additional sections:

  • Group Header

  • Group Footer.

If you set up additional groups, the program creates new group areas between the Details section and the existing Group Header and Group Footer section(s).

Preview Pane

You use the Preview pane to display the report using the actual data. This pane is available:

  • From the Preview option (Crystal Reports - View - Preview).

  • From the Print Preview option (Crystal Reports - View - Print Preview).

  • From the Standard toolbar.

  • By selecting the Preview tab.

The program gathers the data, performs the necessary calculations and displays the report. You can review the formatting of the report and see the actual results of all the summaries, grouping, record selections and formula calculations.

When you are using a SYSPRO business object as the data source, then only the sample data, as defined in the XML OUT schema file, is displayed in the Preview pane. When you are using a Crystal driver, then the actual data is displayed in the Preview pane.

The program works by default in the following way:

  • The first time you select to display the report in the Preview pane, the program retrieves data from the data source and saves the data with the report.

  • The next time you select to display the report in the Preview pane, the program uses the saved data unless you specifically refresh it or add a field that requires the program to retrieve new data.

The date and the time of the last time the data was refreshed are displayed in the bottom status bar of the Preview pane. You can refresh the report data at any time.

  • Groups

    You use the Groups pane to display a hierarchical treeview of all the groups and subgroups in the report using the actual data. You can drill down into the group levels to see how the grouping on the report is displayed. This pane is available from the navigation pane in the Preview pane.

    You use the Preview Panel option from the View menu to show or hide this navigation pane.

    If you select a group in the Groups pane, the Preview pane automatically navigates to the detail of that group in the report. You can therefore also use this functionality as a navigation tool for larger reports.

    [Note]

    When you are using a SYSPRO business object as the data source, then only the sample data, as defined in the XML OUT schema file, is displayed in the Preview pane.

  • Parameters

    You use the Parameters pane to interactively format and filter report data by changing parameter values. This pane is available from the navigation pane in the Preview pane.

    You use the Preview Panel option from the View menu to show or hide this navigation pane.

    You select the option Show on (Viewer) Panel in the Value Options pane of the Create New Parameter window if you want to display the parameter within the Parameters pane. You can set this option to the following options:

    • Do not show

    • Editable

    • Read only.

    If you have selected that the parameters must be displayed in the Parameters pane, but are read-only, then you can only see the parameter value, but you cannot interactively enter new parameter values.

    [Note]

    • Data parameters are differentiated from non-data parameters with a database icon. When you modify the value of a data parameter, the report refreshes and accesses the database to make the corresponding changes. When you modify the value of a non-data parameter, the report is formatted or filtered based on saved data within the report. It does not access the database.

    • When you are using a SYSPRO business object as the data source, there are no parameters for the report.

  • Find

    You use the Find pane to search the report for specific words or phrases. This pane is available from the navigation pane in the Preview pane.

    You use the Preview Panel option from the View menu to show or hide this navigation pane.

    You enter the word in the search box provided and select the magnifying glass (or Enter) to start the search.

    If you select the drop-down menu option next to the search box, then you can also select further search options:

    • Match Case

    • Match Whole Word Only

    The search results are listed in the pane. Included in the search results are the page number and the group information. If you double-click on the individual search result, then the specific data record is highlighted in the report.

    [Note]

    When you are using a SYSPRO business object as the data source, then only the sample data, as defined in the XML OUT schema file, is displayed in the Preview pane.

Wizards

You can use a number of wizards in Crystal Reports that can help you create reports. You follow step-by-step instructions by following the windows of these wizards.

There are four Report Creation wizards:

  • Standard Report Creation Wizard

  • Cross-tab Report Creation Wizard

  • Mailing Labels Report Creation Wizard

  • OLAP Report Creation Wizard.

These wizards are available from the File > New menu in Crystal Reports.

When you create a report using one of these wizards, then you cannot use the SYSPRO Add-ins program to save the report template to the SYSPRO application server. You therefore do not need to launch Crystal Reports through the Design Report option in SYSPRO Reporting Services. You can launch Crystal Reports 2008 for SYSPRO from the Windows menu and then follow the following steps:

  1. Use the Save option from the File menu in Crystal Reports to save the report template to a location on your workstation.

  2. Copy the report template to the ..\Base\ReportingCustomized\ReportTemplate folder on the SYSPRO application folder.

  3. Use the Add other menu item option in SYSPRO Reporting Services to add this report to the SRS menu.

Standard Report Creation Wizard

The Standard Report Creation Wizard is the most generic of the wizards. It can assist you in creating and defining basic report components. The wizard displays the following windows:

  1. The Data window: You use this window to select data sources and tables.

  2. The Links window: This window displays the links that the wizard created automatically. You can change these links if required. This window is only displays when the data source has more than one table or when you use more than one data source.

  3. The Fields window: You use this window to select the fields you want to display on the report.

    [Note]

    You can use the Browse Data option to view the actual data of a selected field.

  4. The Grouping window: You use this window to select fields to group by and to select the sort direction of the groups. This is not mandatory. The default sort order is in ascending order.

  5. The Summaries window: You use this window to select fields that you want to summarize within the groups. This is not mandatory. If you have selected a field, then the default type of summary is Sum.

  6. The Group Sorting window: You use this window to select the group that will be sorted and the kind of group ordering you require. You can select one of the following:

    • None

    • Top 5 groups

    • Bottom 5 groups.

    This window is only displayed if you have specified a group in the Grouping window and a summary in the Summaries window.

    If you have selected a group ordering, then you can select the summary value that you want to use to compare the values for the top or bottom groups.

  7. The Chart window: You use this window to insert a graph or chart into a report. This window is only displayed when you have specified a group in the Grouping window and a summary in the Summaries window.

  8. The Record Selection window: You use this window to select the fields you want to use to filter the information on. This is not mandatory.

  9. The Template window: You use this window to select a formatting template to apply to the record. This is not mandatory.

If you start with a blank template, then you can use the following programs to define the same components:

  • Database Expert

  • Insert Group

  • Group Expert

  • Group Sort Expert

  • Insert Summary

  • Record Sort Expert

  • Chart Expert

  • Select Expert

  • Template Expert.

Cross-Tab Report Creation Wizard

The Cross-Tab Report Creation Wizard guides you through the creation of a report in which the data is displayed as a cross-tab object. It helps you create and format the cross-tab object itself.

A cross-tab object is a grid that presents data in compact rows and columns. It consists of the following elements:

  • Rows

  • Columns

  • Summary fields - these fields are found at the intersection of rows and columns and represent a summary (sum, count, etc.) of those records that meet the row and the column criteria.

  • At the end (or beginning) of each row is a total for that row.

  • At the bottom (or top) of each column is a total for that column.

  • At the intersection of the total row and the total column is a grand total.

[Note]

In order to print a cross-tab report, you must enter at least one summarized field in the cross-tab object.

Database Expert

Crystal Reports have a variety of data sources available for reporting. The first step in designing any type of Crystal Reports template is to define the data source.

The data sources are managed using the Database Expert program in Crystal Reports. This program is available:

  • From the Database Expert option (Crystal Reports > Database > Database Expert).

  • From the menu that is displayed when you right-click on Database Fields in the Field Explorer pane (the Database Expert option).

  • From the Experts toolbar.

The Database Expert provides an integrated tree view of all data sources you can use with Crystal Reports. In the Database Expert, you can select from the following as a data source for your report:

  • A currently connected data source.

  • A data source that has been added to your Favorites folder.

  • A recently accessed data source (the Database Expert automatically maintains a list of such data sources for you).

  • An existing data source (for example, a data file residing locally, or an ODBC data source that has already been set up).

In the Database Expert, you can also specify links between database tables when you have selected more than one table for your report. The Database Expert is made up of two tabs: the Data tab and the Links tab.

Some of the popular choices for data sources include:

  • Access/Excel (DAO): This option lets you connect to a supported database type (Access, dBASE, Excel, Lotus).

  • Database Files: This option shows a list of standard PC databases that reside locally.

  • ODBC (RDO): This option shows a list of ODBC data sources you have already configured for use. This includes using Transoft ODBC drivers to connect to a SYSPRO CISAM data source. You can also connect to any SQL Server database.

  • OLAP: This option opens the OLAP Connection Browser so you can choose an OLAP cube as a data source.

  • OLE DB (ADO): This option shows a list of OLE DB providers available to use. You can use the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client 10.0 provider to connect to your SYSPRO SQL database.

  • XML and Web Services: This option allows you to connect to an XML and XSD file, including the SYSPRO schema files.

You need to understand what is required for the selected data source, including:

  • If you want to create a report based on CISAM data files as the data source, then you need to ensure that you have created the data source based on Transoft ODBC drivers. You install these drivers from the SYSPRO installation DVD. You also require licenses to use these drivers.

  • If you want to create a report connecting directly to a SQL Server database, then you need to know the SQL Server details, including the authentication type and login information. You can either use an ODBC (RDO) connection or an OLE DB (ADO) connection to the SQL database.

  • If you want to create a report based on XML files, you need to know the location path of the XML and relevant XSD files. When you create a report based on a business object, you use an XML data source.

    [Note]

    When you are designing reports based on SYSPRO business objects, you need to ensure that you understand the XML structure and hierarchy. The XML data that the business object returns may contain nested hierarchies and aggregated values. You should not link other data sources to the business object's XML data source, as unexpected or no results may be returned.

  • If you want to create a report based on an Excel spreadsheet or Access database, you need to know the location path to these data files, including the authentication information.

  • The XML data source (for a template that connects to any existing report in SYSPRO's Financial Report Writer program) always uses the following two files:

    • ..\Base\Schemas\gen_fin_report_writer_out.xml

    • ..\Base\Schemas\gen_fin_report_writer_out.xsd.

Once the data source has been created and the data tables added to Database Fields in the Field Explorer pane, you can drag data fields to the report design area.

If you are using an ODBC (RDO) connection to a SQL database and you do not want the user to enter a user id and password every time the report is run, then you can:

  • Select the Trusted Connection option when you create the ODBC (RDO) connection to a SQL database.

  • You can enter a connection string on the Data Source Selection window. Examples of the connection string are

     SQL Authentication: Driver={SQL
    Server};Server=YourSQLServer;Database=YourSQLDatabase;UID=
    YourSQLLogin;PWD=YourPassword 
    (When SQL Server is set up to use SQL Authentication)

     NT Authentication: Driver={SQL
    Server};Server=YourSQLServer;Database=YourSQLDatabase;Trusted_Connection=Yes
    (When SQL Server is set up to use NT Authentication).

If you are using an OLE DB (ADO) connection to the SQL database, and you do not want the user to enter a user id and password every time the report is run, then you can select the Integrated Security option when you create the data source connection to the SQL database.

[Note]

Reports based on Crystal data sources are restricted to the report creator's workstation. If other users need to run this report, then you need to create and configure data sources on each of these user workstations.

Table links

When you use more than one table in a data source, then the tables must be linked. When you link, you are using a field that is common to both tables. Crystal Reports uses the link to match up records from one table with those from the other.

The Database Expert program will attempt auto linking (smart linking) of tables (by key fields) by default or you can select the Link option in the Database Expert - Links tab to auto-link the tables in the selected data source. The default join type is normally an inner join.

You use the Link Options window to specify:

  • join types

  • enforced join options

  • link types.

[Note]

You open the Link Options window by selecting Link Options in the Links tab of the Database Expert program or by selecting Link Options from the menu that is displayed when you right-click on an existing link in the Links tab.

When there is more than one link, Crystal Reports needs to know in what order it should process the links. By default, the processing order matches the order in which the links appear on the Links tab.

You use the Order Links option in the Links tab to arrange the links in the order you require them to be processed.

[Note]

The order in which the links are processed may result in different data sets returned for use in the report. The link order also has a significant effect on performance.

Data types of the fields that you use in the links must be compatible. For example, you cannot link a numeric field with a string field.

Link relationships

When you link records from one table to another table, the records will typically fall under one of two relationship types:

  1. One-to-one relationship:

    In a one-to-one relationship between records in two linked tables, for every record in the primary table there is only one matching record in the lookup table (based on the linked fields). For example, the ArCustomer table in the SYSPRO database can be linked to the ArCustomerBal table based on the Customer field in each table. The ArCustomer table contains information about the company's customers and the ArCustomerBal table contains the latest balances for each customer. There is only one record for each customer in each of these tables. Therefore, if the ArCustomer table is linked to the ArCustomerBal table, only one record will be found in the ArCustomerBal table for each record in theArCustomer table. This is a one-to-one relationship.

  2. One-to-many relationship:

    In a one-to-many relationship between records in two linked tables, for every record in the primary table, there may be more than one matching record in the lookup table, based on the linked fields. In the SYSPRO database, the ArCustomer table can be linked to the SorMaster table based on the Customer field in each table. The ArCustomer table contains information about the company's customers and the SorMaster table contains information about the sales orders placed by customers. Since customers can place more than one order, there may be more than one record in the SorMaster table for each customer record in the ArCustomer table. This is a one-to-many relationship.

Join Types

The following table explains the different join types and the number of rows returned in the result set. This table uses a link between the ArCustomer master table as the primary table (From table) and the SorMaster table as the lookup table (To table).

[Note]

Take note of the following background information regarding these two tables:

  • The tables are linked on ArCustomer.Customer = SorMaster.Customer.

  • There are 23 rows in the ArCustomer table and 171 rows in the SorMaster table.

  • There are 10 orders in the SorMaster table which are not linked to a valid customer. This results in 161 sales orders with valid customers.

  • There are 4 customers in the ArCustomer master table which have no orders in the SorMaster table.

Join Type

Number of rows in the result set

Details

Inner Join

161 rows

This is the default type of join. This join returns rows when there is at least one match in both tables on the linked fields. In other words, it returns all the rows in which the linked field value in both tables is an exact match.

This type of join returns all the sales orders that are linked to valid customers. Customers with no sales orders and sales orders not linked to valid customers are not included in the result set.

Left Outer Join

165 rows

(161 sales orders linked to valid customers + 4 customers with no sales orders)

The result set from this join includes:

  • all the rows in which the linked field value in both tables is an exact match and

  • it also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup (right) table.

In other words, it returns all rows from the left table, even if there are no matches in the right table.

This type of join returns all the sales orders that are linked to valid customers, and it also returns rows for the customers where there are no sales orders.

Right Outer Join

171 rows

(161 sales orders linked to valid customers + 10 sales orders not linked to a valid customer)

The result set from this join includes:

  • all the rows in which the linked field value in both tables is an exact match and

  • it also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary (left) table.

In other words, it returns all rows from the right table, even if there are no matches in the left table.

This type of join returns all the sales orders that are linked to valid customers, as well as the sales orders that are not linked to a valid customer.

[Note]

Theoretically this should not happen as you cannot add a sales order without using a valid customer, and the results of this type of join can be used to indicate invalid or corrupt data. Exceptions to this may be supply chain transfers.

Full Outer Join

175 rows

(161 sales orders linked to valid customers + 4 customers with no sales orders + 10 sales orders not linked to a valid customer)

The result set from this join (bidirectional outer join) includes:

  • all the rows in which the linked field value in both tables is an exact match and

  • it also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup (right) table and

  • it also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary (left) table.

In other words, it returns all rows from both tables.

This type of join returns all the sales orders in the SorMaster table as well as the 4 customer rows with no sales orders.

[Note]

When you link two tables, you link from one table to another table. The from table is used as a primary (left) table, while the to table acts as a lookup (right) table where rows are looked up by the primary table. In a simple link, Crystal Reports examines the first record in the primary table and finds all matching records in the lookup table. Once all matches have been found in the lookup table for the first record in the primary table, all matches in the lookup table for the next record in the primary table are found.

Enforce Join

The Enforce Join option you select can only affect the number of rows returned in the result set if you do not use fields from both the tables in the join relationship in the report. The following table explains the effect of this option on the number of rows in the result set. This table uses a link between the ArCustomer master table as the primary table (From table) and the SorMaster table as the lookup table (To table). If you use fields from both the ArCustomer and the SorMaster table in your report, then the Enforce Join option has no effect.

[Note]

Take note of the following background information regarding these two tables:

  • The tables are linked on ArCustomer.Customer = SorMaster.Customer.

  • There are 23 rows in the ArCustomer table and 171 rows in the SorMaster table.

  • There are 10 orders in the SorMaster table which are not linked to a valid customer. This results in 161 sales orders with valid customers.

  • There are 4 customers in the ArCustomer master table which have no orders in the SorMaster table.

Type of Join

Enforce Join option

Using only fields from ArCustomer on your report

Using only fields from SorMaster on your report

Using fields from both tables on your report

Inner Join

Not Enforced

23 rows

171 rows

161 rows

 

Enforced From

23 rows

161 rows

161 rows

 

Enforced To

161 rows

171 rows

161 rows

 

Enforced Both

161 rows

161 rows

161 rows

Left Outer Join

Not Enforced

23 rows

171 rows

165 rows

 

Enforced From

23 rows

165 rows

165 rows

 

Enforced To

165 rows

171rows

165 rows

 

Enforced Both

165 rows

165 rows

165 rows

Right Outer Join

Not Enforced

23 rows

171 rows

171 rows

 

Enforced From

23 rows

171 rows

171 rows

 

Enforced To

171 rows

171 rows

171 rows

 

Enforced Both

171 rows

171 rows

171 rows

Full Outer Join

Not Enforced

23 rows

171 rows

175 rows

 

Enforced From

23 rows

175 rows

175 rows

 

Enforced To

175 rows

171 rows

175 rows

 

Enforced Both

175 rows

175 rows

175 rows

Enforce Join

Details

Not Enforced

When you select this option, the join you've created is used only if it's explicitly required by the Select statement, in other words only if you use fields from the specific table. You can create reports based on the selected tables without restriction (that is, without enforcement based on other tables).

In the example above, if you only use fields from the ArCustomer table in the report, then the type of join you use to the SorMaster table is ignored. The results are the same as if there was only one table in the data source.

[Note]

This is the default option.

Enforced From

When you select this option, and you use fields from the to table (the lookup table), the join is enforced.

In the example above, if you only use fields from the SorMaster table in the report, then the join is enforced. If you only use fields from the ArCustomer table in the report, then the join is not enforced.

Enforced To

When you select this option, and you use fields from the from table (the primary table), then the join is enforced.

In the example above, if you only use fields from the ArCustomer table in the report, then the join is enforced. If you only use fields from the SorMaster table in the report, then the join is not enforced.

Enforced Both

When you select this option, if you use fields from either the from table (the primary table) or the to table (the lookup table), then the join is enforced.

In the example above, if you only use fields from the ArCustomer table or you use only fields from the SorMaster table in the report, then the join is enforced.

Link Types

The following table explains the result sets from the different link types you can use together with the join types and the Enforce Join option:

Link Type

Details

=

The result set from an Equal link includes all the records where the linked field value in both tables is an exact match.

>

The result set from a Greater Than link includes all records in which the linked field value from the primary table is greater than the linked field value in the lookup table.

>=

The result set from a Greater Than Or Equal link includes all records in which the linked field value in the primary table is greater than or equal to the linked field value in the lookup table.

<

The result set from a Less Than link includes all records in which the linked field value in the primary table is less than the linked field value in the lookup table.

<=

The result set from a Less Than Or Equal link includes all records in which the linked field value in the primary table is less than or equal to the linked field value in the lookup table.

!=

The result set from a Not Equal link includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table.

[Note]

This type of link can be used to find possible combinations of items when a table is joined to itself (a self-join).

Report Objects and Experts

You can insert and position various types of report objects into a report. These types include:

  • Fields from data sources

  • Text

  • Groups

  • Summary fields

  • Running totals

  • Parameters

  • Formulas

  • Charts

  • Subreports

  • Lines

  • Boxes

  • Pictures

  • Flash objects

  • Special fields

  • BLOB (Binary Large Object) fields

  • Hyperlinks

  • OLE objects and

  • Template field objects.

There are a few general methods of inserting objects into a report:

  • Dragging the object from the Field Explorer pane to the position in the Design pane of the report.

  • Selecting the option Insert to Report from the menu that is displayed when you right-click on the object in the Field Explorer pane.

  • Selecting the object from the Insert menu.

  • Using the Insert toolbar.

A green tick appears next to fields that have been selected for the report.

Different Expert programs are available to assist you in adding, maintaining and deleting these report objects. Some of these Expert programs include:

  • Select Expert

  • Formula Expert

  • Group Expert

  • Chart Expert

  • Record Sort Expert

  • Group Sort Expert

  • Format Editor

  • Section Expert

  • Highlighting Expert

  • Template Expert

Text objects

You can insert text objects in most areas of a report. Text objects are used to:

  • Insert titles.

  • Label summaries and other data.

  • Combining database fields.

  • Insert text on the report.

After inserting the text object, you can resize and move the text object to the desired position on the report. You use the Format Editor program to format text objects.

Groups

  • Insert Groups

    You use the Insert Group program to group the data in the report without summarizing the data in the groups. This program is available:

    • From the Group option (Crystal Reports > Insert > Group).

    • From the Insert toolbar.

    • From the Insert Summary program (the Insert Group option).

    You can group by any report object that is available to the report, including report fields, data source fields and existing formulas.

    You can sort the groups in four different directions:

    • The default sort direction is ascending order.

    • In descending order.

    • In specified order: You can set up custom groups and use these freeform groups as the sort order. You define Named Groups and specify the individual values that belong in each group.

    • In original order.

    [Note]

    When you are designing reports based on SYSPRO business objects, you need to ensure that you understand the XML structure and hierarchy. If the XML returned by the business object is already in the correct sequence, then you can use the sort direction In original order. This will improve performance.

    You use Named Groups (custom groups) as the sort order when:

    • The field you want to group on does not exist on the report.

    • The field exists, but you do not want to group on the values in that field. You can manually assign values to custom groups.

    • The field exists, but you want to select specific values or ranges of values for each group.

    You can also select the option Use a Formula as Group Sort Order. The Conditional Formula icon becomes available and the sort order list is not available anymore. You can define a conditional group sort order based on a formula.

    You can customize the group name field in two ways:

    • You can select an existing field name.

    • You can use a formula that returns a string value as a group name.

    [Note]

    The group name formula is always applied to the group header and not to the group footer.

    You can select to keep the groups from breaking across pages and define where page breaks must be inserted.

    When you insert a group:

    1. The first group appears directly above and below the Details section in the Design tab.

    2. The second group appears between the Details section and the first group.

    3. The third group appears between the Details section and the second group, and so forth.

    When you look at the Design tab, the Group Header for the first group is the top group header, and the Group Footer for the first group is the bottom group footer.

    Once you have grouped the data, you can sort the records within the groups, using the Record Sort Expert program, to further organize the information.

  • Group Expert

    You use the Group Expert program to specify how fields are grouped on the report. You can also use this program to reposition groups and to edit the criteria used for triggering and sorting groups. This program is available:

    • From the Group Expert option (Crystal Reports - Report - Group Expert).

    • From the Experts toolbar.

    You use the Browse Data option to browse the data of the field that is selected in the Available Fields pane. The Options option opens the Change Group Options window which you can use to change the fields to group by and to change the sorting of the groups.

    [Note]

    The Change Group Options can also be opened by selecting the Change Group option from the menu that is displayed when you right-click in the Group Header section in the Design tab.

    The Group By pane reflects the group footer order of groups; the bottom group in the list is the first group, the group next to the bottom is the second group, and so forth.

Summaries

You use the Insert Summary program in Crystal Reports to summarize any field that is available to the report, including existing formulas and groups. This program is available:

  • From the Summary option (Crystal Reports - Insert - Summary).

  • From the menu that is displayed when you right-click on a field in the Details section of the Design pane (the Insert Summary option).

  • From the Insert toolbar.

Summaries can be used for a variety of tasks, including:

  • To count the number of values in a group.

  • To calculate the sum, average, standard deviation or variance value in a group.

  • To identify the minimum or maximum value in a group.

  • To add report totals.

When you insert a summary in the report, you need to define the following:

  • The field to summarize.

  • The type of summary.

  • The location of the summary.

Summary totals can be inserted by default only to the Report Footer section of the Design pane. After you have created group levels, you can add summaries to these group levels. You cannot insert a summary in the following sections:

  • Page Header

  • Details

  • Page Footer.

Optionally, you can also select the Insert Group option to add a group level and you can select the Add to all group levels option to apply the summary to all the existing group levels. You can also calculate the percentage total of one group within a broader grouping.

You can drill down to the individual data records behind the individual groups or summary information. When you hover the mouse over any summary field that you can drill down on, then the program displays a drill-down cursor. If you then double-click, the details behind that specific summary value are displayed.

[Note]

You can only drill down on summary values if the section allows drill-downs. If you hide a section, you can drill-down. If you suppress a section, you cannot drill-down.

The available summary types include:

  • Sum

  • Average

  • Maximum

  • Minimum

  • Count

  • Population variance (Statistical)

  • Population standard deviation (Statistical).

[Note]

Not all the summary types are available for all the fields. The available types depend on the type of field.

Parameters

You use the Create New Parameter program to create parameter fields. This program is available from the menu that is displayed when you right-click on Parameter Fields in the Field Explorer pane (the New option).

You can use parameters:

  • In a formula

  • In a record selection formula or

  • In the body of the report itself.

The following table lists the differences between query options and Crystal parameters:

Query Options

Crystal parameters

Specific to SYSPRO business objects designed for reporting purposes.

Can be added to any Crystal Reports report.

Can be added to SRS report forms (using the Form Designer)

Cannot be added to SRS report forms.

Can only be used when you are designing reports based on SYSPRO business objects.

Can be used in any Crystal Reports report, including reports based on SYSPRO business objects.

When the report runs, an SRS form is displayed. This is the designed form for the report or the default report form. The query options are displayed in their relevant tabs on the designed form. There are no query options on the default form.

When the report runs, an Enter Parameter ValuesCrystal Reports window is displayed and you need to select or enter the parameter values. This applies to parameters used in the selection criteria of the report.

Query options are pre-defined in the business object XSD files and SYSPRO Reporting Services validates the query options using the XSD files.

Parameters are not pre-defined or validated by SYSPRO Reporting Services.

You need to name each parameter. This name is displayed within the Parameter Fields group in the Field Explorer pane and is also the name that you can select in the Formula Editor program as a building component of a formula. This name is also used to identify the field when the field appears in the report and when you use the parameter in the Select Expert programs.

[Note]

When a parameter field name appears in the report, a question mark is added to the beginning of the name. This is to identify the field as a parameter field.

You can add various data types of parameters, including the following types:

  • String

  • Boolean

  • Currency

  • Date

  • Date Time

  • Number

  • Time.

After you have created the parameter, the parameter is available:

  • Within the Parameter Fields group in the Field Explorer pane.

  • In the Report Fields pane in the Formula Editor window.

  • In the drop-down list next to the comparison operator in the Select Expert--Record and Select Expert--Group programs.

If you have more than one parameter, you can set the parameter order by using the Set Parameter Order option from the menu that is available when you right-click on an existing parameter or when you right-click on Parameter Fields in the Field Explorer pane.

You use the Edit Parameter program to edit an existing parameter. This program is available:

  • From the Edit Parameter option (Crystal Reports - Edit - Edit Parameter).

  • From the menu that is displayed when you right-click on an existing parameter in the Design pane (the Edit Parameter option).

  • From the menu that is displayed when you right-click on an existing parameter within the Parameter fields group in the Field Explorer pane (the Edit option).

Parameters can also be used in subreports. You cannot delete a parameter if the parameter is being used in the report. You can create a parameter with a static or dynamic list of values.

  • Static parameters

    Static parameters have a static list of values. A static list of values always contains the same values, for example a list of the months of the year. This list does not change often, if ever.

    When you create a static parameter, then you define the value field and optionally a description field for the parameter. You select a field from the data source as the value field that provides the list of values for the parameter. You can also select a field from the data source as the description field. This provides a description for the list of values, for example, if the value field is the warehouse code, then you can use the warehouse description field as the description field for these values.

    [Note]

    You need to ensure that a valid link exists between the field you use as the Value Field and the field you use as the Description Field. If a valid link does not exist, then no data is populated when you select the Append all database values option and the list of values for the parameter is empty.

    If you select the Append all database values option from the drop-down menu next to Actions, then a list of values for the field you have selected at the Value Field option is populated from the data source. You can also insert, delete and move values manually in the list of parameter values. The option is also available to import and export the list of values.

    In the Value Options pane of the Create New Parameter window, you can change the settings for the parameter, including:

    • If you want to display the parameter within the Parameters pane when looking at the report in the Preview pane. If you are displaying the parameter, you can select that the parameter is Editable or Read only.

    • The prompt text.

    • Prompt with description only.

    • Optional prompts.

    • The default value of the parameter.

    • Allowing custom values, multiple values, range values and discrete values.

    • The minimum and maximum length of the parameter.

    • The mask, if any, of the parameter.

    The default options for a parameter are to allow discrete values and to not allow for multiple or range values. If you want to enter a range for the parameter, you need to allow range values. If range values are allowed, the Allow discrete values option is automatically set to False.

    You can select that the parameter is optional. When a parameter is optional, then you do not have to select a value for this parameter if used in record selection. The default value or all values are used if no value is entered at runtime of the report. The default setting is that a parameter is mandatory (Optional Prompt option is False)

    When you run a report with a parameter that allows range values, you need to enter the Start of Range and End of Range values. You also have the option to include the selected start and the selected end values of the range in the report. If you do not want to specify the start value of the range, then you can select the option No lower value. If you do not want to specify the end value of the range, then you can select the option No upper value.

    You can edit or change all the options, including the value and description fields, of a static parameter.

  • Dynamic parameters

    Dynamic parameters have a dynamic list of values. A dynamic list of values is retrieved from the data source every time you run the report and these values are not saved within the report. An example of a dynamic list of values is a list of customer names or products sold.

    Dynamic prompts can include cascading lists of values, where one value in the prompt constrains values in subsequent lists. This adds additional levels of selection for the users. Every time you add a value to the parameter, a new cascading level is created for the parameter. An example of cascading lists is where you prompt for a country code. Depending on the country code selected, the next parameter displays a list of cities that only exist for the selected country code.

    When you create a dynamic parameter, then you define fields from the data source as the values of the parameter. You can also select fields from the data source as the description fields.

    [Note]

    You need to ensure that a valid link exists between the field you use as the Value and the field you use as the Description. If a valid link does not exist, then you can receive an error message when you enter the report prompts for the parameter. The error message is List of Values failure: fail to get values.

    In the Value Options pane of the Create New Parameter window, you can change the settings for the parameter level, including:

    • If you want to display the parameter within the Parameters pane when looking at the report in the Preview pane. If you are displaying the parameter, you can select that the parameter is Editable or Read only.

    • The prompt text.

    • The sort order.

    • Prompt with description only.

    • Optional prompts.

    • Allowing multiple values, range values and discrete values.

    The default options for a parameter are to allow discrete values and to not allow for multiple or range values. If you want to enter a range for the parameter, you need to allow range values. If range values are allowed, the Allow discrete values option is automatically set to False.

    You can select that the parameter is optional. When a parameter is optional, then you do not have to select a value for this parameter if used in record selection. The default value or all values are used if no value is entered at runtime of the report. The default setting is that a parameter is mandatory (Optional Prompt option is False)

    When you run a report with a parameter that allows range values, you need to enter the Start of Range and End of Range values. You also have the option to include the selected start and the selected end values of the range in the report. If you do not want to specify the start value of the range, then you can select the option No lower value. If you do not want to specify the end value of the range, then you can select the option No upper value.

    If you have more than one level for the parameter, you need to enter text that you want to display as a prompt for the group. This text is ignored when you are only using one field in the Value pane.

    [Note]

    When you are designing reports based on SYSPRO business objects, you need to ensure that you understand the XML structure and hierarchy. If you are using incorrect elements in multi-level parameters, then the cascading lists will display the incorrect data.

    You can only edit or change some of the options of a dynamic parameter. You cannot edit or change the following for a dynamic parameter:

    • Type

    • Value columns

    • Description columns.

    [Note]

    If you need to change any of these, then you have to create a new parameter.

Select Expert

You use the Select Expert program to select data by record or by group when you do not want to include all the data in the report. This program is available:

  • From the Select Expert option (Crystal Reports - Report - Select Expert - Record/Group).

  • From the Experts toolbar.

You can select report fields, formulas, summary fields, groups or any field that is available to the report as the selection criteria. A variety of selection criteria operators is available, depending on the type of the field you are using as the selection criteria, including:

  • Is any value

  • Is equal to

  • Is not equal to

  • Is like

  • Is between

  • Starts with and

  • Formula.

You can select records in one of two ways:

  • You can use the Select Expert program.

  • You can use the Formula Editor in the Formula Workshop program to build a formula.

When you use the Select Expert program, it automatically generates a record or group selection formula based on the settings you specify. If you need to create more complex selection criteria, you can use the Formula Workshop program. You can also create a basic selection with the Select Expert program and then edit the formula using the Formula Editor in the Formula Workshop program.

[Note]

When you select a group name or summary field, the program automatically knows that the selection criteria are intended for group selection. In all other cases the program knows that you are setting up record selection. The Select Expert automatically add the HasValue() function to the selection formula if the parameter is an optional parameter. If you change the parameter from optional to mandatory, the HasValue() function is not automatically removed from the formula. The Formula Editor does not automatically add this function to the formula. You can also use saved data selection formulas. Unlike record selection formulas, any change to a saved data selection formula does not cause a refresh to the database. Instead, the report's saved data is used for all subsequent filtering. Saved data selection formulas are used with parameters to create interactive report filters that you can customize.

There are several examples of selection formulas in the Crystal Reports Online Help that you can use as templates.

Formulas

You create and use a formula when the data you want to print on the report is not available in any of the data fields that are available to the report.

Formulas can be used for a variety of tasks, including:

  • To create calculated fields to add to the report.

  • To apply formatting to report objects and text on the report.

  • To define SQL expressions.

  • To apply group and record selections.

  • To extract parts of text strings.

  • To extract parts of a date field.

  • To create custom functions.

  • To convert special fields.

  • To convert strings to numbers.

  • To add running report totals.

  • To perform basic financial calculations.

  • To perform basic statistical calculations.

  • To help you define conditions and messages for report alerts.

[Note]

When you use an XML data source (for example, when the report is based on a business object), then some of the numeric fields may be returned from the XML data source as data type string (text). If you want to use these fields in formulas, calculations or summaries, you need to convert the field from data type string to data type number. You can use a formula in the Formula Workshop program to convert the data type.

Formulas contain two critical components: the components and the syntax. The components are the building blocks of the formula and the syntax are rules that are used to ensure that you create correct formulas.

You can use any of the following components to build formulas:

  • Report fields: Report fields contain all database fields accessible for the report. They also contain any formulas or groups already created for the report.

  • Functions (Built-in functions as well as user-defined/custom functions): Functions are prebuilt procedures that return values.

  • Operators: Operators describe the action that needs to take place between two or more values, for example add, subtract, less than and greater than.

  • Control structures: For example, IF, SELECT and FOR loops.

  • Text.

  • Numbers.

You can use either the Crystal Syntax or the Basic Syntax when building a formula in the Formula Workshop program. The Crystal Syntax is the formula language included in all versions of Crystal Reports. The Basic Syntax is modeled on Visual Basic except that it has specific extensions to handle reporting.

[Note]

Record selection and group selection formulas cannot be written in Basic Syntax.

You can also use SQL expressions fields in the same way as formulas. The only difference is that SQL expression fields are written in Structured Query Language (SQL) and not in Crystal Reports formula language.

You can copy a formula from one report to another report. You can make a duplicate of a formula if you select the Duplicate option from the menu that is displayed when you right-click on the formula field in the Formula Workshop program.

Developers can create User Function Libraries (UFLs) that can be used in the Formula Editor. A User Function Library is a library of functions that is created to address specific needs. It can be programmed in a COM or a Java environment.

You can define the following types of formulas:

  • Report formulas: These are calculations that you want to print on the report.

  • Conditional formatting formulas: These formulas are used to change the layout and the design of the report, as well as the appearance of text, database objects or entire report sections. You normally use the Format Editor program to define the format options, but you can access the Formula Workshop program from the Format Editor if you need to use a formula to define the formatting.

  • Selection formulas: These formulas specify and limit the records, groups and saved data that appear in the report. You normally use the Select Expert program to specify the selections, but you can also enter selection formulas directly in the Formula Workshop to specify the selections.

  • Search formulas: These formulas help you locate data in the report. You normally use the Search Expert to specify the search criteria, but you can access the Formula Workshop program from the Search Expert if you need to use a formula to specify the search criteria.

  • Running Total condition formulas: These formulas let you define the condition upon which the running total will be evaluated or reset. You can access the Formula Workshop program from the Create Running Total Field program if you need to use a formula to define the conditions for the running total

  • Alerting formulas: These formulas help you define conditions and messages for report alerts.

    [Note]

    Search formulas and Running Total condition formulas cannot be created or maintained directly in the Formula Workshop program. You must use the Search Expert and the Create Running Total Field programs to create and maintain these formulas.
Formula Workshop

You use the Formula Workshop program to manage formulas. This program is available:

  • From the Formula Workshop option (Crystal Reports - Report - Formula Workshop).

  • From the Experts toolbar.

  • From the Find in Formulas option that is available from several menus.

There are three areas in the Formula Workshop window:

  • A toolbar area.

  • The Workshop Tree.

  • An area where you can enter the formula itself.

The Workshop Tree contains folders for each type of formula you can create. It also contains folders for custom functions and SQL Expressions. When you open the Formula Workshop program from within another program, for example the Format Editor or Group Sort program, then the appropriate folder in the tree is expanded and the appropriate version of the Formula Editor is displayed.

The Expressions Editor toolbar contains several options, including an option that test the syntax of the formula or custom function and a Browse Data option that displays a list of the values for a selected field.

The Formula Workshop consists of several components, including:

  • The Formula Editor program.

  • The Formula Expert program.

Formula Editor

The Formula Editor is a major component of the Formula Workshop program. You use the Formula Editor to build and edit formulas. The editor has a variety of names depending on the type of formula you are working with:

Editor name

The program is available:

The Formula Editor

  • From the menu that is displayed when you right-click on Formula Fields in the Field Explorer pane (the New option).

  • From the menu that is displayed when you right-click on Formula Fields in the Formula Workshop program (the New option).

The Custom Function Editor

  • From the menu that is displayed when you right-click on Report Custom Functions in the Formula Workshop program (the New - Use Editor option).

The SQL Expression Editor

  • From the menu that is displayed when you right-click on SQL Expression Fields in the Field Explorer pane (the New option).

  • From the menu that is displayed when you right-click on SQL Expression Fields in the Formula Workshop program (the New option).

The Format Formula Editor

  • From the Function icon next to several options in the Format Editor program

  • From the Function icon next to the option New Page After on the Options tab of the Insert Group program.

  • From the Function icon next to several options in the Section Expert program.

  • From the menu that is displayed when you right-click on a section below Formatting Formulas in the Formula Workshop program (the New Formatting Formula option).

The Record Selection Formula Editor

  • From the Formula Editor option in the Select Expert: Record program.

  • From the menu that is displayed when you right-click on Record Selection within Selection Formulas in the Formula Workshop program (the New option).

The Group Selection Formula Editor

  • From the Formula Editor option in the Select Expert: Group program.

  • From the menu that is displayed when you right-click on Group Selection within Selection Formulas in the Formula Workshop program (the New option).

The Saved Data Selection Formula Editor

  • From the Formula Editor option in the Select Expert: Saved Data program.

  • From the menu that is displayed when you right-click on Saved Data Selection within Selection Formulas in the Formula Workshop program (the New option).

The Group Sort Order Formula Editor

  • From the Function icon next to the option Use a Formula as Group Sort Order on the Common tab of the Insert Group program

The TopN/Percentage Value Formula Editor

  • From the Function icon in the Group Sort Expert program.

The Group Name Formula Editor

  • From the Function icon next to the option Use a Formula as Group Name option on the Options tab of the Insert Group program.

The Running Total Condition Formula Editor

  • From the Function icon next to the options Use a formula in the Create Running Total Field program.

The Alert Message Formula Editor

  • From the Function icon next to the Message option in the Create Alert program.
The Alert Condition Formula Editor

  • From the Condition option in the Create Alert program.

The Formula Editor contains four main windows:

  • Report Fields

  • Functions

  • Operators

  • Formula text window.

The Reports Fields, Functions and Operators contain the primary formula components that you use to build the formula in the Formula text window.

Formula Expert

You can use the Formula Expert option to help you create a formula based on an existing custom function. This program is available from the Formula Workshop toolbar.

You can select a custom function from a list of available custom functions and the Summary, Return Type and Function Arguments areas are completed based on the function's definition.

SQL expression fields

You use the Formula Workshop - SQL Expression Editor program to add SQL expression fields. This program is available:

  • From the menu that is displayed when you right-click on SQL Expression Fields in the Field Explorer pane (the New option).

  • From the menu that is displayed when you right-click on SQL Expression Fields in the Formula Workshop program (the New option).

You use SQL expressions fields in the same way as formulas. The only difference is that SQL expression fields are written in Structured Query Language (SQL) and not in Crystal Reports formula language. An SQL expression can be used to query the database for specific sets of data. You can sort, group and select records based on SQL expression fields.

The SQL Expression Editor program requires you to enter the various components according a specific set of rules called syntax. Some of these rules are:

  • Text must be enclosed in quotation marks.

  • Arguments must be enclosed in parentheses (where applicable).

  • Referenced formulas are identified with a leading @ sign.

[Note]

You can use the Internet and other resources to find more information on the syntax rules.

Running totals

Running totals are specialized summaries and continually incrementing total fields. You use the Create Running Total Field program to create running totals. This program is available from the menu that is displayed when you right-click on Running Total Fields in the Field Explorer pane (the New option).

Running totals can be used to:

  • Show values of a total accumulate as it is calculated record by record in a list.

  • Total a value independent of the report's grouping.

  • Total a value conditionally.

  • Total a value from the primary table when the relationship between the primary table and the secondary table are a one-to-many link.

Running total fields are similar to summary fields, but allow more control over how the total is calculated and when it is reset. They are totals that are displayed on a record by record basis.

When you create a running total, you need to supply the following:

  • A name for the total.

  • The field to summarize.

  • The summary operation.

  • A condition upon which to base the evaluation.

  • A condition upon which to reset the evaluation.

The position where you place the running total field on the report affects the value that is displayed on the report. The following table indicates which records are included in the running total value depending on the sections the totals are placed in:

[Note]

This table assumes that the running total is not reset.

Report section

Records included in the running total calculation

Report Header

The first record in the report.

Page Header

All records up to and including the first record on the current page.

Group Header

All records up to and including the first record in the current group.

Details

All records up to and including the current record.

Group Footer

All records up to and including the last record in the current group.

Page Footer

All records up to and including the last record on the current page.

Report Footer

All records in the report - grand total.

Different types of summary operations are available, depending on the type of field you select. Some of these types are:

  • Sum

  • Average

  • Maximum

  • Minimum

  • Count

  • Statistical operations.

You can select from different conditions upon which to base the evaluation of the running total field on:

  • You can evaluate the running total for each record. This indicates that the running total calculation is executed for every record in the report.

  • You can evaluate the running total on the change of a specific field. This indicates that the running total calculation is executed only when the specific field value has changed, for example change of stock code.

  • You can evaluate the running total on the change of a specific group. This indicates that the running total calculation is executed only when the specific group has changed, for example change of the product class group.

  • You can use a formula to define when the running total calculation must be executed.

You can select when the running total must be reset to zero:

  • Never - this indicates that the running total is locked throughout the report and will not be reset to zero.

  • On change of field - this indicates that the running total is reset to zero when the selected field changes.

  • On change of group - this indicates that the running total is reset to zero when the selected group changes.

  • Use a formula - this indicates that you are using a formula to determine when the running total must be reset to zero.

You use the Edit Running Total Field program to change the running total. This program is available

  • From the menu that is displayed when you right-click on an existing running total field in the Design pane (the Edit option).

  • From the menu that is displayed when you right-click on an existing running total field in the Running Totals Fields group in the Field Explorer pane.

  • From the Edit menu (the Edit Running Total option) - Crystal Reports - Edit - Edit Running Totals.

Record Sort Expert

You use the Record Sort Expert program to define how the records for the report must be sorted for printing. This program is available:

  • From the Record Sort Expert option (Crystal Reports - Report - Record Sort Expert).

  • From the Experts toolbar.

You can add and remove a sort field and define the sort direction (ascending or descending) for the data in the report. You can sort on any field that is in the report as well as those fields that are available in the report's data source. The field's data type determines the method in which the data from the field is sorted. You can sort on single fields or multiple fields.

The existing groups sort order is added to the Sort Fields pane of the Record Sort Export window by default and cannot be removed.

Group Sort Expert

You use the Group Sort Expert program to identify top or bottom groups. You can also identify top or bottom percentages. This program is available:

  • From the Group Sort Expert option (Crystal Reports - Report - Group Sort Expert).

  • From the Experts toolbar.

[Note]

A report must contain a group summary value before you can select the Group Sort Expert option. In other words, a report must contain a group summary value in order to be able to perform a group sort. Once you have inserted a group summary, you can select one of the following group sorting options for the group:

  • No Sort

  • All

  • Top N

  • Bottom N

  • Top Percentage

  • Bottom Percentage.

Different options are displayed depending on the sorting option you have selected. You enter the number of top or bottom values where N is the number of values or you enter the percentage of top or bottom values. There is also an option Include ties to accommodate groups whose summarized values are equal. You have two choices regarding all the records from other groups that do not fit the Top N or Bottom N criteria you have set:

  • You can specify a group name for all the fields that do not fit into the top or bottom selections. All these records will be grouped together with this group name. The default is Others.

  • You can eliminate all the records that do not fit into the top or bottom selections. This is the default behavior (the option Include Others, with the name is not selected).

Format Editor

You use the Format Editor program in Crystal Reports to change the appearance of report fields and other report objects. Different tabs are displayed, depending on the type of field selected. This program is available:

  • From the Format Field or Format Text options (Crystal Reports - Format - Format Field/Text).

  • From the menu that is displayed when you right-click on a report object or text field (the Format Field or Format Text options).

  • From the menu that is displayed when you right-click after selecting multiple report objects or text fields (the Format Objects option).

  • From the Experts toolbar.

  • From selecting any of the options in the Fields tab of the Options program (Crystal Reports - File - Options - Fields).

Formatting options available in the Format Editor program include:

  • Format borders, background filling and drop shadows.

  • Add single or double over- and underlines to total fields.

  • Change the color and the font of text.

  • Change the style used to display numbers and currency symbols.

  • Format the display of hyperlinks.

  • Suppress fields.

  • Format date and time fields.

  • Define subreport formatting options.

You can select multiple report objects and/or text objects by holding in the Shift key while selecting. You can then apply the same formatting to all the selected objects.

[Note]

You can also use the Formula Workshop program to create formulas to apply formatting to report objects and text.
Hyperlinks

You use the Hyperlink tab of the Format Editor program to create a hyperlink to a web site, an email address, a file or a report object. This program is available:

  • From the Format Editor program (the Hyperlink tab).

  • From the Hyperlink option (Crystal Reports - Format - Hyperlink).

  • From the Experts toolbar.

[Note]

You need to first select the report object that you want to link to the hyperlink before you can select the Hyperlink or Insert Hyperlink option.

The hyperlink is saved with your report and is available to other users. You can use hyperlinks to display additional information in the report.

You can also use the Function icon to open the Format Formula Editor program to create a hyperlink that is based on data from a field in the data source.

You can use the Create Enterprise Hyperlink program to create a hyperlink to an enterprise document and to change the settings of this hyperlink. These settings include:

  • The target window

  • The output format of the hyperlink (HTML, PDF, Excel, Word)

  • The instance of the document.

Section Expert

You use the Section Expert program to make formatting changes that affect entire sections of the report. The default sections include:

[Note]

These sections are based on a basic report with 2 groups.

Section

More Details

Report Header

The information in this section is printed only at the top of the first page of the report.

Page Header

The information in this section is printed at the top of every page of the report.

Group Header 1

The information is this section is printed at the top of group 1.

Group Header 2

The information in this section is printed at the top of group 2.

Details

The information in this section represents the detail lines of the report.

Group Footer 2

The information in this section is printed at the bottom of group 2.

Group Footer 1

The information in this section is printed at the bottom of group 1.

Page Footer

The information in this section is printed at the bottom of every page of the report.

Report Footer

The information in this section is printed only at the bottom of the last page of the report.

This program is available from:

  • the Section Expert option (Crystal Reports - Report - Section Expert).

  • the Experts toolbar.

You also use this program to insert, delete or merge sections. You can only delete sections that have been added to the originating sections; you cannot delete any of the originating sections. When you insert a section, the originating section is marked as a and the new section is marked as b.

Some of the formatting options that you can select that affect these entire sections include:

Tab page

Option Description

Common

Hiding the section with a drill-down (summary fields)

Common

Suppress the section with no drill-down (summary fields)

Common

Keep the section together over a page break

Common

Suppress blank sections

Common

Make sections read-only

Paging

Reset page numbers

Paging

Page orientation

Color

Background colors

You can also select the Formula Editor icon next to most of these options and build a conditional formatting formula using the Formula Workshop program.

You may decide to hide or suppress the Detail section of a report and only display the groups and group summary totals on the report. If you hide a section, you can drill-down. If you suppress a section, you cannot drill-down.

When you have variable length objects positioned in one section and the Can Grow formatting option is selected for the object, then objects may overlap. This can happen with the following objects:

  • text fields

  • formula fields

  • memo fields

  • BLOB fields

  • subreports

  • cross-tab reports and

  • OLAP grids.

You can prevent object overflowing by creating multiple sections and placing objects below the variable length object in their own section. When the report runs, the first section with the variable length object will finish printing before the second section with the next object prints.

Highlighting Expert

You use the Highlighting Expert program to apply conditional formatting to all types of report fields. This program is available:

  • From the Highlighting Expert option (Crystal Reports - Format - Highlighting Expert).

  • From the menu that is displayed when you right-click on a field in the Details section of the Design pane (the Highlighting Expert option)

  • From the Experts toolbar.

You can either specify a condition based on the selected field's value or by specifying a condition based on the value of a different report field. The program enables you to create a conditional formatting formula. You can use any field that is available to the report in the condition.

You can define multiple formatting conditions to one field and arrange the multiple conditions according priority in the Item list pane of the Highlighting Expert program. A sample of the format is displayed in the Item Editor pane and a sample of the condition is displayed in the Item list pane of the Highlighting Expert program.

When you use the Highlighting Expert program for conditional formatting, some of the formatting you can do include:

  • Highlight all field types in the report.

  • Modify several attributes at once, without writing a formula.

  • Format font styles, background colors, font colors and border styles.

  • Enter values using the locale-specific number format, for example the number format for North American users.

The Highlighting Expert essentially enables you to create the following formula: If the value of field X meets condition A, then apply the specified formatting to the field selected on the report. It enables you to do quick conditional formatting, but for more complex conditional formatting you need to use the Formula Workshop program.

Chart Expert

You use the Chart Expert program to prepare graphs for use in the report. This program is available:

  • From the Chart option (Crystal Reports - Insert - Chart).

  • From the menu that is displayed when you right-click in the Design pane (the Insert Chart option).

  • From the Insert toolbar.

  • From the menu that is displayed when you right-click in an existing chart (the Chart Expert option).

You can chart on the following:

  • Group layouts: Summary and subtotals fields at group level.

  • Advanced layouts: Detail, formula and running total fields.

  • Cross-tab summaries.

  • OLAP data.

Different chart types are available, including:

  • Bar charts

  • Line charts

  • Pie charts

  • Gantt charts and

  • Histograms.

You can create charts with any of the following layouts:

  • You use the Advanced Layout when you have multiple chart values or when you do not have any group or summary fields in the report. You need to specify the conditions (there can be two) and the values (there can be multiple values). This layout include:

    • Values can be grouped in ascending, descending or a specified order. You can also use the Top N grouping or Sort totals.

    • Values can be plotted for each record.

    • Values can be plotted as a grand total for all records.

    • Charts can be based on formulas and running totals.

  • You use the Group Layout when you want to display a summary on a change of a field for fields such as geographic area or branch. In order to create a chart using this layout, you must have at least one group and at least one summary field for that group.

  • You use the Cross-Tab Layout when you want to chart on a cross-tab object. This type of chart uses the fields in the cross-tab for its condition and summary fields.

  • You use the OLAP Layout to chart on an OLAP grid. An OLAP chart uses the fields in the OLAP grid for its condition and summary fields. The report must include an OLAP grid before you can create an OLAP chart.

[Note]

If the report already contains the correct grouping and summary information, then Crystal Reports creates a chart without displaying the Chart Expert program. You can still edit the chart by selecting Chart Expert from the menu that is displayed when you right-click in the chart.

You can define the axes, chart colors and backgrounds.

You use the Color Highlight tab to conditionally apply color based on chart value fields. You apply a color to the selected item by specifying a condition. The Item list pane displays the formula conditions and the Item editor pane lets you create the formula conditions.

When you use conditional formatting, then the following conditions must be met:

  • If your chart type is line, then the chart must have data markers before you can see conditional formatting.

  • An area chart must have two On change of values for conditional formatting to appear.

The placement position of a chart determines which data is displayed and where it is printed. For example, a chart placed in the Report Header section includes the data for the entire report, while a chart that is placed in a Group Header or in a Group Footer section displays only the group-specific data. You can also place charts in subreports.

Drill-downs are also available with the chart. This enables you to analyze the underlying details of the specific section of the chart.

Template Expert

You use the Template Expert program to choose from pre-defined formatting templates for use in the report. This program is available:

  • From the Template Expert option (Crystal Reports - Report - Template Expert).

  • From the Experts toolbar.

Some of the available templates include:

  • Corporate

  • Executive Summary or Title Page

  • Gray Scale and

  • Table Grid.

The default templates are installed to the folder C:\Program Files\Business Objects\Crystal Reports 12.0\Templates\en.

You can modify the format of the report after applying one of these predefined formatting templates.

You can re-apply a template you previously applied to update changes you have made to the report (for example when you have added new fields to the report). You can also undo the current template. A sample preview is available only for those reports that have the Save Preview Picture option selected (Crystal Reports - File Summary - Info Summary - Save Preview Picture).

[Note]

You can only undo or re-apply a previously applied template in the current session of Crystal Reports. Once you have closed Crystal Reports, you cannot undo or re-apply the template. You then have to manually remove the formatting that was applied by the Template Expert. We suggest that you make a backup of the template file before applying a template.

Image objects, drawing and special fields

You can insert a variety of image objects and special fields into the report in Crystal Reports.

You can draw horizontal and vertical lines in the report. The Line option is available:

  • From the Insert menu.

  • From the Insert toolbar.

You can draw a box or a frame on the report. The Box option is available:

  • From the Insert menu.

  • From the Insert toolbar.

You can insert a picture into the report. This can be used for placing a company's logo on the report. The Picture option is available:

  • From the Insert menu.

  • From the Insert toolbar.

The following type of image files can be inserted:

  • Metafiles

  • Bitmap files

  • TIFF files

  • JPEG files

  • PNG files.

You can also insert BLOB (Binary Large Object) fields into the report in the same way as inserting pictures.

You can insert special fields into the report. These fields are listed within the Special Fields group in the Field Explorer pane and include the following:

  • Data Date

  • Data Time

  • Data Time Zone

  • Page N of M

  • Modification Date

  • Group Selection Formula

  • Record Selection Formula

  • Record Number

  • Total Page Count.

You drag these special fields from the Field Explorer pane to the relevant position on the report in the Design pane.

Data Types

Simple data types

A data type is a classification identifying one of various types of data that determines the possible values for that type. The simple data types in Crystal Reports are Number, Currency, String, Boolean, Date, Time, and DateTime.

The following table lists basic descriptions of these data types:

Data type

Details

Number

If you want to perform calculations, you need to ensure that the data type of the fields is number. You enter numbers without comma separators or currency symbols in the Formula Editor.

You can define the formatting of number fields using the Format Editor.

When using a SYSPRO business object as the data source of a report, you need to ensure that the fields you are using in calculations and summaries are numbers. Some business objects return numeric fields as strings in the XML and you then need to convert these string fields to numeric fields using the CDbl function.

Currency

If a field is of type Currency, then the field values are displayed using the currency symbol as defined in the Options program (Crystal Reports - File Options - Fields - Currency).

You can also use the CCur function to convert number values to currency values.

[Note]

You cannot multiply two fields that are of data type Currency. If you try to do this, you receive an error message.

String

Strings are used to save text. The text must be placed between double quotation marks (") or apostrophes (') and cannot be split between lines. If you want to include double quotation marks in a string delimited by double quotation marks, use two consecutive double quotation marks. Similarly, if you want to include an apostrophe in a string delimited by apostrophes, use two consecutive apostrophes.

You can extract individual elements or substrings from a string by specifying the character position or a range of character positions.

You can also extract substrings from a string using the Left, Right and Mid functions.

When using a SYSPRO business object as the data source of a report, you need to ensure that the fields you are using in calculations and summaries are numbers. Some business objects return numeric fields as strings in the XML and you then need to convert these string fields to numeric fields using the CDbl function.

Boolean

The boolean or logical data type is a data type that has two values. The valid boolean values are True or False.

[Note]

Yes can be used instead of True and No instead of False.

Date

The Date type can be used for dates only.

[Note]

The Date and Time types are more efficient than the DateTime type, and so can be used in situations where the added functionality and flexibility of the DateTime type is not needed.

Time

The Time type can be used for time only.

[Note]

The Date and Time types are more efficient than the DateTime type, and so can be used in situations where the added functionality and flexibility of the DateTime type is not needed.

DateTime

The DateTime type can be used for date-times, dates only, or times only and thus is rather versatile.

Converting data types

In general, values of one data type cannot be used where values of another data type are expected without explicitly supplying a type conversion function.

There are a few conversions that are made automatically:

  • Number to Currency

  • Date to DateTime

  • Simple type to Range value of the same underlying simple type.

For all the other type of conversions you need to use a type conversion function. The following table lists some of the basic type conversion functions available in Crystal Reports:

Function Details

CDbl

This function returns a number value created by converting the given number, currency or string type argument to a number value. If the conversion cannot be done, an error is returned. This function is designed to work like the Visual Basic function of the same name.

[Note]

You can use the IsNumeric (str) or NumericText (str) function to check if the string argument can be converted to a number before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

The following examples are applicable to both Basic and Crystal syntax:

  • CDbl ({ARCreditorsReport/CustomerItem/ 
    CustomerHeader.MtdPaymVal})

    Returns the value of a string field in the customer header converted to Number type.

  • CDbl ("$120.20")

    Returns the number value of 120.2.

ToNumber

You can use CDbl or ToNumber as both these functions perform the same function, but we do advise using CDbl to ensure that the conversion is correct.

ToNumber converts a number, currency, text string, or Boolean value to a number.

[Note]

When using the ToNumber function, you should first test the value with the NumericText (str) function. NumericText (str) returns a value of true only if the value in the string can be correctly converted to a number. If you try to convert a value to a number that is not a number, the formula returns an error.

CCur

This function returns a currency value created by converting the given number, currency or string type argument to a currency value. If the conversion cannot be done, an error occurs. This function is designed to work like the Visual Basic function of the same name.

[Note]

  • You can use the IsNumeric (str) or NumericText (str) function to check if the string argument can be converted to a currency before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

  • In Crystal syntax, you can use the To currency operator ($x) instead of CCur when converting from a number value to a currency value. $x converts x from number to currency.

CStr

This function returns a string value and you can use it to convert a number, currency, date, time, or boolean value to a text string to appear as text in your report (form letters, comments, and so on).

ToText

CStr and ToText are equivalent functions.

The ToText function, when used with boolean values, is most useful for combining (concatenating) a boolean value with other text. Otherwise, a Boolean field can be formatted to appear as True or False in the report simply by changing the format on the Boolean tab (Format Editor).

CBool

This function returns a boolean value. It accepts a single argument that can be either a number or currency value or an expression. It returns True if the argument is positive or negative but not 0 and returns False if the argument is 0. This function is designed to work like the Visual Basic function of the same name.

The following is an example of converting an argument into a boolean value:

CBool (CDbl
 ({ARCreditorsReport/CustomerItem/CustomerHeader.MtdPaymVal}))

This function returns True if MtdPaymVal <> 0 and False if MtdPaymVal = 0.

CDate

This function returns a date value created by converting the given number, string, Datetime or YYYYMMDD value.

The DateValue and Date functions are equivalent to CDate. Date can only be used in Crystal syntax since it is a type name in Basic syntax.

[Note]

You can use the IsDate function to check if a string argument can be converted to a date before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

The following is an example of converting a string into a date:

CDate ({InvMovementsQuery/StockCodes/
StockCodeItem/Transactions/
TransactionItem.TransactionDate})

CTime

This function returns a time value created by converting the given number, string, Datetime or HHMMSS value.

The TimeValue and Time functions are equivalent to CTime. Time can only be used in Crystal syntax since it is a type name in Basic syntax.

[Note]

You can use the IsTime function to check if a string argument can be converted to a time value before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

CDateTime

This function returns a datetime value created by converting the given string, number, date, date and time or YYYYMMDD/HHMMSS value.

The DateTimeValue and DateTime functions are equivalent to CDateTime. However, DateTime can only be used in Crystal syntax since it is a type name in Basic syntax.

[Note]

You can use the IsDateTime function to check if a string argument can be converted to a datetime before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

Data source location

You can change the database location of the active database in a report template within Crystal Reports using the Set Datasource Location program (Crystal Reports - Database Setup - Datasource Location).

This program provides a simple way to indicate the new name or location of database files. This is especially useful if someone sends you a report that uses a database that has a different location on your server, or if you have changed the folder or disk location of a database. In addition, the Set Datasource Location program automatically converts your database driver to the data source you have chosen. For example, you can automatically convert an ODBC data source (for a SYSPRO CISAM company) to a OLE DB (ADO) data source (for a SYSPRO SQL company) using the Set Datasource Location program.

You should only change the data source location within Crystal Reports if the SRS report is based on a Crystal driver. If the SRS report is based on a SYSPRO business object or a GL report writer report, we suggest that you do not change the data source location within Crystal Reports, as this may result in unpredictable results. This is because if you change the XML data source within Crystal Reports, then it will not update the related business object and schema file details linked to the report template in the report control file.

[Note]

This program does not physically move the database or databases. It simply directs Crystal Reports to look for the database or databases in another location than the one originally specified when creating the report.

Set Datasource Location

The Set Datasource Location window is divided into two panes, the Current Data Source pane and the Replace with pane.

Current Data Source pane

A list of all the databases and tables currently used in the report is displayed in the Current Data Source pane. The main report is shown as the root node. All subreports are located under one node so that the data sources for them are grouped together. A data source used in two or more subreports is indicated by a different icon at the data source level.

[Note]

If a table is used in two or more subreports but the alias name is different, the real table name is appended to the end.

Detailed information regarding the data source or table is displayed if you expand the Properties node within that data source or table. You can use this information to identify and check the current data sources and tables used in the report. Some entries in the Properties node, for example the database name, have menu options when you right-click on the entry. If you want to set the database location to the location of the active report, expand the report's Properties node and right-click the Data Source field. The following options are available:

  • Edit: Select this option to manually enter a location for the database.

  • Same as report: Select this option to set the location of the database to a local drive.

  • Convert to UNC: Select this option to set the location of the database to a mapped drive.

Replace with pane

The list in this pane displays the same options as the Data tab of the Database Expert window. Refer to the topic Database Expert for more details on the various options.

The Update function

The Update function is available on the Set datasource Location window. You use this option to update the current selected data source or table with the selected data source or table in the Replace with pane.

If the new data source is different from the current data source, then the Map Fields window is display. This window is display when the program detects any of the following changes in the database structure:

  • A field has been deleted.

  • A field has been renamed.

  • The database is completely new.

The program checks for these changes only if the databases have different names or if the name has changed. If the databases have the same name, the Map Fields window does not appear, and you need to verify the database when you have finished setting the location.

Verification of the database

The Verify Database program (Crystal Reports - Database -Verify Database) checks the active databases and reports. If it detects changes, the report must be adapted to prevent errors. The program displays the Map Fields window when it detects either of these types of changes to the database:

  • The name of a field that is used in the report has changed.

  • The database has been upgraded from a workstation/file data source to an SQL data source.

Crystal Reports automatically adapts the report (and does not display the Map Fields window) if it detects any of these changes:

  • Fields have been added to the database.

  • Fields that are not used in the report have been deleted from the database.

  • Field positions have changed in the database.

  • Data types have changed for fields in the database.

Depending on the results of the verification process, different windows are displayed:

  • If the data source cannot be found on the workstation, then the Data Source Selection window is displayed. You need to select the correct data source name to continue.

  • If the program detects no changes in the active database, then a window with the message The database is up to date. is displayed. If this is the case, select OK.

  • If the program detects changes in the database, then a window with the message The database file ["table name"] has changed. Proceeding to fix up the report! is displayed. You need to check all the fields on the report and correct them if required.

  • If the program detects that a field name has been altered in the database, then the Map Fields window is displayed. You need to map the unmapped fields to valid fields in the database.

Mapping database fields

You use the Map fields window to remap existing report fields in the active database if they have been altered. This includes changes that were made to the structure of the database, or when moving a database from a direct access type to a SQL type. The remapping of fields helps you to make sure your reports print with the current version of the active database. The Map fields window is displayed when you select any of the following options from the Database menu (if the report database has changed since you last ran the report):

  • Verify Database

  • Verify on First Refresh

  • Set Datasource Location.

Unmapped current report fields are listed in the upper left pane of the Map Fields window. These are fields for which the program detects a change in the active database. Unmapped fields to map to from the database or tables selected in the Replace With pane are displayed in the upper right pane of the Map Fields window.

If the Match Type option is selected, then only the names of unmapped database fields that is of the same type as the field selected in the upper left pane is displayed. If the Match Type option is not selected, then all the unmapped database fields, regardless of the field type, are displayed.

Mapped fields from the current database and mapped fields from the database or tables selected in the Replace With pane are displayed in the bottom part of the Map Fields window. If you select Unmap, then the two fields are no longer mapped.

Sub-reports

You use the Insert Subreport program to add a report within a report. This program is available:

  • From the Subreport option (Crystal Reports - Insert - Subreport).

  • From the Insert toolbar.

You can use subreports:

  • To combine unrelated reports into a single report.

  • To coordinate data that otherwise cannot be linked.

  • To present different views of the same data in a single report.

  • To perform one-to-many lookups from a field that is not indexed on the lookup field.

  • To group data - for example, you can use subreports for serial numbers, lot numbers and multiple bins.

  • To summary/total data - for example, you can use subreports for branch and salesperson summaries.

When you are designing reports based on SYSPRO business objects:

  • You need to ensure that you understand the XML structure of the schema files. If you are using the incorrect elements in the links between primary report and subreport, then incorrect report results are returned.

  • If you are linking the subreport to the primary report, then we suggest that the subreport is also based on a SYSPRO business object. Unexpected or no results may be returned if the subreport's data source is based on other Crystal drivers, for example Crystal drivers that connect directly to the CISAM or SQL database.

  • When the subreport is based on a SYSPRO business object, then we suggest that you use the Create a subreport with the Report Wizard option on the Subreport tab of the Insert Subreport program. This option creates the subreport as an embedded object within the primary report.

  • You need to add the embedded subreport name to the report control of the primary report, using the Report Control program. You only need to do this if the subreport is based on a SYSPRO business object.

A subreport is almost the same as a primary report, except for the following:

  • A subreport is inserted as an object into a primary report; it cannot stand on its own (although a subreport can be saved as a primary report).

  • A subreport can be placed in any report section and the entire subreport will print in that section.

  • A subreport cannot contain another subreport.

  • A subreport does not have a Page Header or a Page Footer section.

You can define that the subreport is an on-demand subreport. On-demand subreports are useful when you want to create multiple subreports in a primary report. The subreport is displayed as a hyperlink in the primary report. The subreport details are not displayed and the subreport data is not read from the data source until you select the hyperlink. This functionality improves the manageability of the subreports.

You can save the subreport as a primary report. This may be required if you need to run the subreport for example more frequently than the primary report. When you do this, you use the Save Subreport As option in Crystal Reports. The subreport is therefore not saved to the SYSPRO application server by default, but to the local workstation. You then need to copy it to the application server.

[Note]

You can use the Add Other Menu Item option from the Setup menu in the SYSPRO Reporting Services program to add this report to the SRS main menu (if it does not already exist on the menu).

If the subreport was linked to the primary report via a parameter, then a parameter prompt window is displayed when you run the subreport on its own.

There are two types of subreports, unlinked and linked subreports.

Unlinked subreports

Unlinked subreports:

  • Are free-standing reports.

  • Can have the same data source as the primary report or you can use any other data source.

  • Do not attempt to match the records from the primary report.

  • Can be based on a single table or on multiple tables.

  • Are treated as unrelated to the primary report.

You can use unlinked subreports to print subsequent reports. If you place the report in the Report Footer section of the primary report, then the subreport prints immediately after the primary report. Unlinked subreports can also be used to display more information or supply additional details.

Linked sub-reports

Linked sub-reports:

  • Have coordinated data.

  • Have the same data source as the primary report.

  • Attempt to match the records from the primary report on which they are linked.

You create a link from the subreport to the primary report and the program creates the link by using a parameter field. When you link a subreport, then the program creates:

  • A parameter field in the subreport which is used to retrieve values passed to it by the primary report (the parameter(s) is based on the field(s) you select to link to).

  • A record selection formula for the subreport using the parameter(s).

[Note]

The selection formula limits the records in the subreport to those records in which the field you use for the link is equal to the parameter field value. This is called an Implicit Link. For example: You create a primary report from the customer master table, listing account details per customer. You need to insert a subreport from the sales order master table, listing the current orders per customer. To establish a link between the primary report and the subreport, you select the Customer field from the ArCustomer table as the field to link to (A parameter field, ?Pm-ArCustomer.Customer is created) and the Customer field from the SorDetail table as the field to select data in the subreport on. The selection formula created by the program in the subreport is

{SorMaster.Customer} = {?Pm-ArCustomer.Customer}

The only records displayed in the subreport are records from the SorDetail table where the Customer field is equal to the Customer field from the ArCustomer table. The Customer field from the ArCustomer table is passed as a parameter value to the subreport's selection formula.

If you preview a linked subreport from the subreport's Design tab, then the program runs the subreport on its own. It is not waiting for a value to be passed for the link parameter from the primary report and therefore you will receive a parameter prompt. The value entered here is the value that the program then uses to run and display the subreport.

You can also use the linked parameter field in the subreport without using it as part of the selection formula for the subreport. For example, you may want the primary report to pass in a summary value that you can use in calculations in the subreport. This is called an Explicit Link. When you link a field in the primary report to a parameter field in the subreport, then:

  • The program checks the link.

  • Does not create any additional parameter fields.

  • Does not modify the subreport's record selection formula.

Cross-tab reports

Insert Cross-Tab objects

You use the Insert Cross-Tab program to insert a cross-tab object in a report. You can insert the cross-tab object in the Report Header or the Report Footer section of the Design pane. This program is available:

  • From the Cross-Tab option Crystal Reports Insert Cross-Tab.

  • From the menu that is displayed when you right-click in the Design pane (the Insert Cross-Tab) option).

  • From the Insert toolbar.

A cross-tab object is a grid that presents data in compact rows and columns. It consists of the following elements:

  • Rows

  • Columns

  • Summary fields - these fields are found at the intersection of rows and columns and represent a summary (sum, count, etc.) of those records that meet the row and the column criteria.

  • At the end (or beginning) of each row is a total for that row.

  • At the bottom (or top) of each column is a total for that column.

  • At the intersection of the total row and the total column is a grand total.

[Note]

In order to print a cross-tab report, you must enter at least one summarized field in the cross-tab object.

Cross-Tab Expert

You use the Cross-Tab Expert program to define and maintain an inserted cross-tab object in a report. This program is available from the menu that is displayed when you right-click in an existing cross-tab object in the Design pane (Cross-Tab Expert) option.

You add fields as the rows and columns of the grid and also add the fields that you want to summarize on. You can also add formula fields and use the New Formula option of the Cross-Tab Expert program to define a formula to use as the rows, columns or summarized fields. The default type of summary is Sum, but you can change this to other types of summaries as required.

You can select a predefined style to apply to the grid. Some of the predefined styles available are:

  • Basic - Blue

  • Honey Mustard

  • Beach Blue

  • Boarder Orange and

  • Custom designs.

You can optionally customize the grid style on the Customize Style tab of the Cross-Tab Expert program. Some of the options you can customize include:

  • Alias for Formulas

  • Background Color

  • Keep Columns Together

  • Row Totals on Left

  • Column Totals on Top

  • Suppress Empty Rows

  • Suppress Empty Columns.

You can also format the grid lines.