How to create custom Crystal Reports for Retail Management System Store Operations and Retail Management System Headquarters

Article ID: 836441 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

INTRODUCTION

This article describes how to create custom Crystal Reports for Microsoft Retail Management System Store Operations and for Retail Management System Headquarters.

More information

  1. Use Crystal Reports version 8.5. Crystal Reports version 9.0 may work, but the original reports were designed with version 8.5. Version 9.0 may not produce the same results.
  2. Create the connection.
    1. Start Crystal Reports, click to select the As a blank report check box, and then click OK.
    2. In the Data Explorer window that appears, click More Data Sources.
    3. Click OLE DB, click Make a New Connection, and then click Add.
    4. In the Data Link Properties window, click to select the Microsoft OLE DB Provider for SQL Server check box to indicate the data source.
    5. Click Next, and then type the logon information for your computer that is running Microsoft SQL Server.
    6. Click the name of your Retail Management System database.
    7. Click OK to finish the connection setup.
  3. Turn off Smart-Linking.
    1. In the Data Explorer dialog box, click Options.
    2. Click Auto Smart Linking to turn off Smart-Linking.

      Note Add the fields and formatting to the report before you go on to steps 4 through 9. For more information about how to create and to format a Crystal Report, see the Crystal Help menu or visit the following Business Objects Web site:
  4. Create two custom Formula Fields, and then put them in the Report Header.
    1. On the main menu in the Crystal Reports window, click Insert.
    2. Click Field Object, and then click Formula Fields.
    3. Click the third button from the left in the Field Explorer window to create a new Formula.
    4. In the Formula Name window, type SelectionCriteria as the name, click OK, and then type None as the formula.
    5. Click the Save and Close button (third button from the left) in the Formula Editor window to save and exit the Formula Editor window.
    6. Repeat steps a through f for the second custom formula field. Type SortOrder for the name, and type None for the formula.
    7. Put both fields in your custom report by dragging them from the Field Explorer and then dropping them into the Report Header.
  5. On the File menu, click to clear the Save Data with Report check box.
  6. On the main menu of the Crystal Reports window, click Database, and then click Show SQL Query. In the "FROM" clause, delete any text that shows the database name and the owner's name, as in the following example:

    Original FROM clause:
    StoreOps.dbo.Transaction [Transaction] INNER JOIN StoreOps.dbo.TransactionEntry TransactionEntry ON 
    [Transaction]."TransactionNumber" = TransactionEntry."TransactionNumber"
    Modified FROM clause:
    Transaction [Transaction] INNER JOIN TransactionEntry TransactionEntry ON
    [Transaction]."TransactionNumber" = TransactionEntry."TransactionNumber"
    Note Do not remove any duplicate tables. Remove only the database name and the owner's name.
  7. On the main menu of the Crystal Reports window, click Database, and then click Verify Database. Make sure that the following message appears:
    The database is up to date.
  8. If one of the following tables is included in your custom report, set an alias for the table, and then modify the FROM clause in the Show SQL Query window:
    Transaction
    Order
    Check
    1. If one of these tables exists, on the main menu of the Crystal Reports window, click Database, and then click Set Alias.
    2. Select the table, and then click Set Alias.
    3. Insert brackets ([]) around the table name, and then click OK.
    4. Click Done. On the toolbar, click Database, and then click Show SQL Query.
    5. Wherever the table name appears, make sure that there are brackets around the table name, as in the following example:
      FROM [Transaction] [Transaction] INNER JOIN TransactionEntry
      TransactionEntry ON [Transaction]."TransactionNumber" = TransactionEntry.
      "TransactionNumber"
      
      
  9. Create a .def file for the report. Give this file the same name as the name of the report. For more information about how to create this file, open an existing .def file that is found in the CrystalReports folder of your Store Operations or your Headquarter installation folder. This file gives Store Operations or Headquarters the filter and the sorting criteria for your report. The following is an explanation of variables that are used in Retail Management System Report Definition files:

    Filename: The report file name.

    Description: The description that appears in the report window.

    GroupCount: The number of group sections that are in the report, not including the detail section.

    StartGroup: The first group section for sorting purposes.

    TableCount: The number of tables that are used in the report.

    VisibleSectionName: The name of each section that you want to display on the report. For example, this could appear as follows:

    VisibleSectionName = DETAIL
    VisibleSectionName = GH2
    VisibleSectionName = GH1
    VisibleSectionName = GF2

    DETAIL indicates the detail, GH1 indicates the header section for group 1, and GF1 indicates the footer section for group 1.

    Field: A field that you can sort by or use in selection criteria. It is defined as follows: Field = {description}, {field name}, {type}, {level}, {criteria}, {G.T. #}.

    Description: The name that will be displayed on the Sort by and Criteria tabs of the Report Properties window in Store Operations Manager.

    Field Name: The name of the table and column where the data field is found in the database. Only those fields that you wish to sort by or to restrict the report by must be included in the .def file. The format is TableName.ColumnName.

    Type: The type of data in the field: 0 = string; 1 = numeric; 2 = date; 3 = Boolean.

    Level: The sort level where the field can appear. For example, 1 means that you can sort by this field in sort level 1. Use 0 if you do not want to be able to sort by this field.

    Criteria: Indicates whether this field is to be used in selection criteria. Use -1 for yes and 0 for no.

    G.T.: For future use.

Properties

Article ID: 836441 - Last Review: November 1, 2013 - Revision: 3.0
Applies to
  • Microsoft Retail Management System Headquarters 1.3
  • Microsoft Retail Management System Store Operations 1.3
  • Microsoft Retail Management System Store Operations 1.2
Keywords: 
kbnosurvey kbarchive kbmbsmigrate kbhowto KB836441

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com