How to create a report with the Crystal Reports Wizard

The first step is to connect to the appropriate data source for the type of report you are creating:

Crystal Report

EMu uses ODBC (Open Database Connectivity) to connect with Crystal Reports and it is necessary to identify the ODBC data source for the EMu module for which you are designing the report.

1. Double-click Create New Connection to expand its contents.
2. Double-click ODBC (RDO) to display the ODBC (RDO) box.
3. Select the appropriate database file from the Data Source Name box.

   In this example, we select EMu Catalogue as this is the module from which the EMu report was run in Step 1, and from which the core data for the report is drawn:

   

4. Click Finish to close the ODBC (RDO) box.

   A new folder (EMu Catalogue in this example) is now listed beneath the ODBC(RDO) folder:

   

   The content of EMu Catalogue is drawn from:

   C:\Users\[username]\AppData\Local\KESoftware\Reports\ecatalogue

   which is updated any time a report is run in the Catalogue module in EMu.

5. Select the table(s) that you wish to include in the report.

   Note: As we have seen, one or more .csv files are generated when we run a report in EMu: the main .csv file and a separate .csv file for any field that is a table of values or for any group (see 2. Select the fields to include in the report: the Fields tab for details).
   In this example two .csv files have been generated: ecatalog.csv (the main .csv file) and Group1.csv (storing values from the Creator's Name: (Creator Details) and Role: (Creator Details) fields).

   To include all .csv files for a particular data source, select the required data source under the ODBC (RDO) folder (e.g. EMu Catalogue) and click the double arrow button.

   -OR-

   To move a single .csv file across to the Selected Tables list, select the .csv file (e.g. ecatalog.csv) and click the single arrow button.

   In this example we only move the main .csv file, ecatalog.csv, to the Selected Tables list:

   

   Group1.csv contains values from three fields about the object's creator - their first and last name and role. The Creator's Name and Role fields are both tables of values (they can hold more than one value) and we need to add this data to our report using a subreport. See the IMPORTANT note on The Links screen for the reason why we use a subreport to include values in Group1.csv rather than adding Group1.csv to the Selected Tables list here.

6. Click Next to continue.

   What happens next depends on how many .csv files (tables) were added to the Selected Tables list:

   • The Link screen displays if two or more .csv files were added to the Selected Tables list.

     -OR-

   • The Fields screen displays if only one .csv file was added to the Selected Tables list.

     In this example, the Fields screen will display.

The Links screen

Note: The Link screen only displays if two or more tables (.csv files) were added to the Selected Tables list. In this example only one table was added to the Selected Tables list so the Links screen would not have displayed. The demonstration below is included in order to show how to use the Link screen but the changes made here will have no bearing on the remainder of this example.

The Link screen is used to configure the links that join tables included in the report. Tables are linked by common key fields, in this example, ecatalogue_key:

This is not the place to describe in detail how Crystal Reports manages links between tables, but a quick overview may suffice.

Although Crystal understands that the tables are linked together, it does not always link them in the most useful way. Generally it makes sense to think of the direction of a link as being from the primary table - notionally the table on the left hand side - to a lookup table. In this example, Crystal has not identified ecatalog_csv as the primary table and, as we'll see, it has applied a default Join type of Inner Join, which is not particularly useful.

If we click Link Options, we find that there are four possible Join types in Crystal Reports.

Note: The Link Options button will be greyed out and inaccessible until a link between two tables is selected (click a link to select it - it will turn blue).

The four Join types are:

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

Each Join type specifies different conditions for determining which records will display in a report. As an example, consider this Inner Join scenario: Table A is the primary table, with links to records in Table B (the direction of the link is Left to Right / Table A to Table B). If the Join type is Inner Join, only records in Table A that have a link to a record in Table B will be listed in the report. If a record in Table A does not link to a record in Table B, it will not be displayed in the report.

Note: The default Join type in Crystal Reports is Inner Join : this may not display all the records on which you wish to report.

In this example, for all Table A records to be listed in the report whether or not they are linked to a record in Table B, it is necessary to select the Left Outer Join option.

Important: If records in Table A link to two or more records in Table B (in other words, the field stored in Table B is a table of values, such as Other Names: (Person Details)in the Parties module), it is more appropriate to create a subreport to add records in Table B. Using the method described below, if a record in Table A links to three records in Table B, the Table A record will be listed three times in the report.
For example, Table A records the last name of a Party (Smith). Table B records the Party's Other Names (Smithy, Smit); if we link the tables as described below, two records would be listed in the report containing: 1. Smith Smithy 2. Smith Smit. If you create a subreport for Table B and link that to Table A, our Table A record will be listed once only, but will include both Table B values. See How to add a subreport for details.

To correct the direction of Joins and to specify an appropriate Join type:

1. Click Clear Links.

   A dialogue box displays:

   

2. Click Yes to continue.

   The links are removed from between the various tables.

3. Position the primary table on the left.
4. In the primary table, select the key field (ecatalogue_key in this example) and drag and drop it over the equivalent key field in each lookup table:

   

   Note: Lookup tables may themselves link to other Lookup tables using another key field and it will be necessary to specify these links too.

5. Select the link between two tables (it will turn blue).
6. Click Link Options to display the Link Options box.
7. Select a Join Type radio button. In this example we select Left Outer Join:

   

   Note: If in doubt, use the Left Outer Join option: "The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. 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 table."

8. Click OK.

   If required, repeat for all links between tables.

   

   Note: Note that the links now indicate the direction of the Join (Left to Right).

9. Click Next to continue.

   The Fields screen displays.

Crystal ADO Report

1. Double-click Create New Connection and expand ADO.NET (XML):

   

   The following screen will display:

   

2. In the File Path field, locate and select the xmldata.xml file created when the report was first run.

Tip: Details about the location of the xmldata file can be found here.

3. Click Finish to return to the Database Expert:



Tip: In the screen image above, Group1 contains values from fields that were grouped when fields were added to the EMu report. These fields are tables of values (they can hold more than one value). It is necessary to use a sub-report to add this data to the report.

Field values from the Catalogue are contained in the table called row.

4. Select row and add it to the Selected Tables pane:



5. Click OK.

Note: It is important not to move the xmldata.xml file as this will cause problems when sharing the report with other users.

On the Fields screen we select the fields to be included in the report:

1. Select fields in the Available Fields list and move them to the Fields to Display list:

   To select all fields, simply press the double arrow button

   -OR-

   Select fields individually (press the CTRL key while clicking field names to select more than one field), and press the single arrow button:

   

2. To re-order fields in the Fields to Display list, select a field and click the arrows located above the Fields to Display list.

   The order of fields in this list influences the order in which fields are arranged on the report (left to right across the report), with the field at the top of the list appearing on the far left of the report.

3. Click Next to continue.

   The Grouping screen displays.

On the Grouping screen we specify how fields will be grouped on the report. This is an optional step.

1. If required, select a field by which to group records and press the single arrow button to add it to the Group By list.
2. If necessary, change the order in which the group is sorted by selecting in descending order from the drop list beneath the Group By list (groups are sorted in ascending order by default).

   Note: When using the Standard Report Creation Wizard, it is necessary to add at least one group to the report if you also want to use the Wizard to add totals or charts.

   In this example, records are grouped by the date field: when the report is generated, all records with the same date value will be grouped together and sorted in ascending order (from the oldest to the most recent):

   

3. Add as many fields to group by as necessary.
4. Click Next to continue.

   If you specified a field to Group By, the Summaries screen displays.

   Otherwise the Record Selection screen displays.

The Summaries screen only displays if one or more fields were selected on the Grouping screen. One reason for organising data into groups is to run calculations on groups of records rather than on all the records in the report. On the Summaries screen we choose fields to be totalled (subtotals, counted, etc.). This is an optional step:

In this example, values in Valuation Amount are automatically totalled. We'll also add an average of Valuation Amount:

1. Select the required field in the Available Fields list and click the single arrow button to move it to the Summarised Fields list.
2. Select Average from the drop list beneath the Summarised Fields list:

   

   Use the Up and Down arrows above the Summarized Fields list to order the summaries if necessary.

3. Click Next to continue.

   The Group Sorting screen displays.

The Group Sorting screen only displays if one or more fields were selected on the Grouping screen AND a summary was specified on the Summaries screen. On the Group Sorting screen you can sort groups created on the Grouping screen based on the summaries calculated for each group. It is possible to sort all groups or the top or bottom 5 groups. When sorting by the top or bottom 5 groups, it is also possible to choose the summarised field on which to base the sorting. This is an optional step:

In this example we do not sort fields. Click Help for details about this option.

1. Click Next to continue.

   The Chart screen displays.

In the Standard Report Creation Wizard, the Chart screen only displays if a group was selected on the Grouping screen AND a summary was added to the Summaries screen. On the Chart screen we insert a graph or chart into a report.

It is often easier to understand number values when they are displayed in a graph or chart. Crystal Reports Designer allows you to insert a chart for any summary or sub-totalled field. This is an optional step:

1. To include a chart in the report, select a chart type, e.g. Bar Chart.
2. Give the chart a title, and modify the options if necessary.

   The On change of option specifies the group field to use as a condition for plotting values in a graph or chart. Points will be plotted whenever the value in the group changes. It is possible to choose from groups created on the Grouping screen. In this example there is only one group and it will be selected automatically:

   

3. Click Next to continue.

   The Record Selection screen displays.

On the Record Selection screen we choose fields to select (or filter) records in a report. By selecting records in this manner, it is possible to narrow the scope of a report and improve processing speed. This is an optional step:

In this example we do not filter fields. Click Help for details about this option.

1. Click Next to continue.

   The Template screen displays.

On the Template screen we select a predefined formatting template for the report. This is an optional step:

1. Select a template from the list of Available Templates (a preview displays in the preview pane).

   In this example, No Template was selected.

2. Click Finish to end the Wizard.

   The raw report displays in Preview view of the Crystal Designer:

   

   In the Crystal Reports Designer, we can now modify the report as necessary.

   Note: Most, though not all, changes can be performed in either Preview view or Design view (select the Design or Preview tab on the left hand side of the screen). Design view is most versatile however, so in this example, we generally use the Design tab.

   See How to format the report: overview for details about how to format headings and fields (format the date fields, for instance).

   1. Click OK when the Refresh report data? message displays:

      

      See How to edit a report in the Crystal Designer for details about:

      • How to add groups
      • How to add a total and field summary (sum, average, etc.)
      • How to add a field label
      • How to insert a chart
      • How to add an image, etc.
      • How to add a subreport

      The end result could be similar to:

      

   2. Select File>Save to save the report.

      Note: When selecting File>Save to save the report, check that the Save Data with Report option is not ticked. It is generally unnecessary to save data with the report (and the file size will be reduced). This option sits below the Save option in the File menu.

   3. The final step is to save the report back to EMu so that the report can be run from within EMu.
3. Select File>Save to save the report.

   Note: When selecting File>Save to save the report, check that the Save Data with Report option is not ticked. It is generally unnecessary to save data with the report (and the file size will be reduced). This option sits below the Save option in the File menu.

4. The final step is to save the report back to EMu so that the report can be run from within EMu.