Loading

Create a new Crystal report and add it to PRISMAdirect

This topic describes how you can create reports using Crystal Reports which is a third party component. You have to upload the created reports in PRISMAdirect.

Procedure

  1. Open Crystal Reports Editor and select "Report wizard" to create a report.
  2. Select the data source for the report.
    1. Create a new connection.
    2. Select "OLE DB (ADO)".
    3. Select "Microsoft OLE DB Provider for SQL Server".
  3. Type the logon credentials for the selected data source.
    1. Select the PrintAccounting database.
    2. User is sa.
    3. Type the password for user sa. You have defined the password for user sa when you installed PRISMAdirect.
    4. Click [Finish].

      The PrintAccounting database is now available to Crystal Reports.

  4. Select the data that you want to report on.

    You can select tables or a stored procedure. A stored procedure is a group of SQL-statements compiled into a single execution plan. Each table contains a limited set of ticket items.

    NOTE

    Use only one stored procedure per report. Do not select an additional table or stored procedure when you already use a stored procedure for a report. More than just one stored procedure in a report can lead to serious performance issues, especially for databases with many records.

    When you create a report, you can select tables or one stored procedure. Each table contains a limited set of ticket items. A stored procedure is a group of SQL-statements compiled into a single execution plan. You can select one of the following stored procedures per report:

    • uspGetAllOrdersItems

      Contains all order ticket items including the custom ticket items at order level.

    • uspGetAllJobsItems

      Contains all job ticket items including the custom ticket items at job level.

    • uspGetOrderJobsItems

      Contains all order and job ticket items including the custom ticket items at order level and job level.

  5. For a stored procedure, you have to provide the input parameters. Tables do not have parameters.
    1. Select False for @DumpDataInTempTable.

    2. Type a unique name for @TempTable, for example "table1".

    3. @selectedColumns:

      Later in this procedure, you can select which fields you want to display in the report. In that step, you can select from a set of available fields.

      • Enable option "Set to Null" if you want to display all available fields.

      • Fill this parameter with the desired fields names if you want to display only a subset of the available fields. Separate each field name with a comma.

    4. You can filter the results of the stored procedure using a valid WHERE clause. The WHERE clause is the condition of a SQL query. The name of the parameter that can contain the WHERE clause depends on the selected stored procedure. In this example, procedure uspGetAllJobsItems is selected and therefore the parameter name is @jobsFilter:

      • Type a valid WHERE clause in this parameter to filter the results of the stored procedure.

        For example, stored procedure uspGetAllJobsItems queries the tables JobItems and JobCustomItems. Table JobItems contains amongst others column JobNumber. So, you can filter the results by using JobNumber > 5 AND JobNumber < 20 in parameter @jobsFilter.

      • Leave this parameter empty if you do not want to filter the results of the stored procedure. Do not select "Set to Null".

    5. Click OK.

  6. Select which fields you want to display in the report.
  7. Optionally, you can select a field to group the data. Separating your data into groups often helps you to focus the information in the report.
  8. You can select a field to add summary information to the report. For example, select the field for colour pages to display a summary of the total number of colour pages in the report.
  9. Select which chart you want to use for the report.
  10. You can add new fields to an existing report.
    1. Expand the table or stored procedure in the "FieldExplorer" pane.
    2. Drag and drop the additional fields in section "Details" of the report.
  11. Add the created report to PRISMAdirect.
    1. Open the [Configuration] workspace of PRISMAdirect.
    2. Click [Options] - [Upload custom reports].
    3. Browse to the concerning RPT file.
    4. Click [Open].

      The report is added to the [Configuration] workspace. Now, you can generate the concerning report in PRISMAdirect.