Creating a Report

This section provides a step by step guide to defining a new report. This involves selecting the data file to retrieve your information from, building a query to select the precise data records and choosing the data fields you want displayed in the report. If the report format required is a basic tabular format like Excel, you can simply click the “Excel Only” option on the Preference page. Without this option being selected, the report layout must then be designed in Crystal Reports by DebtCol.

Reports & Documents > Reporting Add or Maintain Reports

Enter the name of the Crystal Report in the field labeled “Report Name”. Be sure to exclude the file extension (i.e. the Crystal Report samplereport.rpt should be input as samplereport).

Where indicated, select the file to extract your report data from.

Select an appropriate classification where indicated. When running the reports you will need to locate them through its classification.

Describe the Report where indicated. The description is used to help locate the report at run-time.

Provide the heading for the report where indicated. This becomes a merge field within the Crystal Report.

To sort the results of the selected data, choose the sort field and nominate ascending or descending. Secondary sort is achieved by adding a second row and choosing the sort field and sort order. For each additional sort, simply add and define another row. See the example below.

image1.png

Select the fields you need displayed in the Crystal Report where indicated.

image1.png
IMPORTANT: Because these display fields become fields within the Crystal Report for you to drag and drop into the body of the report and format to your liking, you cannot have any blank rows in the display fields table, only valid fields.

In some cases the creation of the report data may be complex enough to require a special program. In such cases, contact DebtCol for a quotation. Any such processes will be entered in the field labeled “Select data using a Program”.

Typically the majority of reports will extract the relevant data using a query.

Use the Query table at the bottom of the page to build your query statement. This statement is used for the database to gather the data for the report and should read like a typical English sentence. Select your options from the drop-down lists and insert your values in the “Criteria Value” column.

The Connector column is how each row of the query connects to the previous. As such, the first row can only be “with” or “without”.

The Criteria Field is the name of the field to be queried.

The Criteria Operator is used in the query to determine if the criteria field is either:-

  • equal to
  • not equal to
  • greater than
  • less than
  • greater than or equal to
  • less than or equal to

the Criteria Value. See the below screenshot for a simple example.

Sometimes you may wish to capture the data entered in the Criteria Value at the time of running the report. For example, capturing a range of dates entered at run-time would allow you to specify the date range where appropriate inside the Crystal report. Simply click the “Capture” box in the row containing the criteria value you wish to capture. See the above screenshot for a simple example. In this example two new fields will be available within the report which carries those dates.

IMPORTANT: Reports can be made available to clients through the online portal. To ensure such reports only extract debts related to their security restrictions, add a row to the query that equates the Client Code to @CLIENTS. See example above.
IMPORTANT: To capture the value of the current system date, enter @DATE as the criteria value.
 Tip: To capture the value of the current system date plus two days, enter @DATE+2 as the criteria value.

Merge fields that can be used in the “Criteria Value” column are:-

@DATE - Captures the current system date

@STARTMONTH - Captures the first day of the current month, OR the date specified in System Settings as the start of the month

@ENDMONTH - Captures the last day of the month (or the current system date if prior), OR the date specified in the System Settings as the end of the month

@CLIENTS - Captures the restricted client(s) in the current users security settings

@CASE - Captures the current case number being access by the current session. This only applies when the report is run from the Case Manager; My Reports page

@USER - Captures the current user code

Preferences Page

 

A report definition can also be created to execute a bulk email if required. Select either “Report” or “Bulk Email” from the field labeled “Type”. This will determine what options you get when running the report.

Select a preferred dispatch method where indicated. This can be used where reports are automatically dispatched from the end of day schedule. Where reports are run manually, the user still has to choose the method of dispatch.

Select a preferred application to export emailed reports to where indicated. This can be used where reports are automatically dispatched from the end of day schedule. Where reports are run manually, the user still has to choose what to export to.

Where the report can be automatically dispatched via email through the mass processing schedule, enter the email address to send it to where indicated.

If you want the report to dispatched to Excel via the client portal, tick “Enable Excel”.

If the report does not require a Crystal Report design, tick “Excel Only” and when the report is dispatched it will display the resulting data in Excel.

Tick “Suppress Recurring Key” if you do not wish to repeat the key to the file within the report.

For bulk emails, in the field labeled “Reply Email Address”, enter the email address that recipient replies should be sent to.

For bulk emails, in the field labeled “Recipient Field”, select which email address to send to.

For bulk emails requiring attachments, enter the attachments where indicated.

Note: If you receive an error message when loading attachments, your Internet Explorer settings may not allow access data across domains. Click here for more.

For bulk emails, be sure to type the email message where indicated.

Click “Save” on the Action Bar

To create the data-source for the Crystal Report, click “Update Header” on the Action Bar.

Now you can create the Crystal Report.

IMPORTANT: You MUST create a Crystal Report to output the report results; unless the report is simply dispatched to Excel.
IMPORTANT: Please note that deleting existing reports could cause problems if it is recorded in the Mass Processing Schedule. Please check the Mass Processing Schedule first to be sure.