Learn more

Report Manager window

Learning about Report Manager

Using Sage Estimating Data on Reports

Creating a simple estimate report using SAP Crystal Reports®

This topic describes how to use SAP Crystal Reports, specifically, to create a report that prints data for a selected estimate in Sage Estimating. You can adapt these general steps for the report designer you are using.

The following instructions assume that:

  • You have a Report Designer role.
  • You understand how to use the Crystal Reports Designer.
  • You are using Crystal Reports 2013 with Support Pack 2. If you use a different version, the workflow may be different.

Before you start

Before creating a report design for Sage Estimating, you need to:

  1. Note: This step is required only if no other reports exist in Report Manager. If you have existing reports, open the Estimating Management Console, select any report, and then skip to step 2(d).

    1. Start SAP Crystal Reports.
    2. On the Start Page tab, click Blank report.
    3. In the Database Expert window that appears, click [Cancel].
    4. On the main menu, click File > Save.
    5. In the File name text box, type blank.rpt.
    6. Navigate to an easy-to-access folder.
    7. Click [Save].
    8. Exit the program.

    You will select this file in the Report Manager.

  2. Use the Estimating Management Console to create sample data for your report designs:

    1. On the toolbar, click [Report Manager].
    2. Click New Report > Crystal Report.

    3. In the New Report window:

      1. In the Report box, type a name for the report you want to create.
      2. From the File Name box, browse to and select an existing report design (.RPT) file.
      3. If you want to change the order that information appears on the report:
        1. For the Sequence option, select Custom,.
        2. Click the browse button An image of the Browse button beside the option.

        3. In the left-hand pane of the Edit Grouping Hierarchy window that appears, select fields that represent the information you want appear on the report, clicking the right arrow key to include each one in the report sequence.

          Use the up and down arrow keys to specify the order that this information will appear on the report:

      4. Click [OK].
    4. Select one or more estimates from the grid (lower right-hand pane) to use for sample data, and then click [Generate Data].

    5. When prompted to select options for the report working data, click [OK] to accept the default selection.

      Report Manager stores the sample data in a custom reports database, where it is available to use when you are designing reports.

    6. Click [Check In].

  3. When creating a report design in Crystal Reports, you need to identify a single estimate that has the characteristics you want to report on.

    Note: When you run an estimate report, Sage Estimating uses the EstimateId of the estimate displayed at run time. However, when designing the report, you need to supply the EstimateId for a specific estimate that you will use to lay out the report elements.

    To find the EstimateId values that are available in the custom reports database, query the values using SQL Management Studio:

    1. Start Microsoft SQL Server Management Studio.
    2. In the Connect to Server window, connect to the SQL database.
    3. For Server type, select Database Engine.
    4. For Server name, select your Sage Estimating server.
    5. In the Object Explorer:
      1. Expand the Databases node.
      2. Expand the *ReportDesign* database.
      3. Expand the Tables node.
      4. Right-click the Report.Estimate_v1 line on the table.

      5. Click Select top 1000 rows.

        The Results tab displays the estimates available in the custom reports database, including the EstimateIds and the names of the estimates.

    6. Write down the EstimateId for the estimate that you want to work with when you design the report.

Use the Standard Report Creation Wizard to create an estimate report

All of the following steps and parameters are required to report on a Sage Estimating estimate.

    1. Start SAP Crystal Reports.
    2. Select File > New > Standard Report.
    3. In the Standard Report Creation Wizard:
      1. Expand Create New Connection.
      2. Expand OLE DB (ADO).
      3. In the OLE DB Provider window:

        1. Select SQL Server Native Client 10.0.

          Note: If you are using Microsoft SQL Server 2014, select SQL Server Native Client 11.0.

        2. Click [Next]

      4. In the OLE DB (ADO) Connection Information window:

        1. In the Server box, enter the SQL Server instance where your custom reports database resides. (It is the same instance where you are storing your Sage Estimating data.)

          For example, enter (local)\SAGE_ESTIMATING.

        2. Select the Integrated Security check box.

        3. For the Database option, select your custom reports database.

          The custom reports database is given a unique ID using the following naming convention:

          {ESTIMATE DATABASE NAME}_ReportDesign_{USER ID}

          {USER ID} is your network logon name or ID.

        4. Click [Finish].

  2. Tip: For a list of the tables you can link to a report, see Using Sage Estimating Data on Reports

    On the Data tab of the Standard Report Creation Wizard:

    1. Expand the database node you added in step 1.
    2. Expand the Report schema node.

    3. Select the Estimate_v1 table.

      Note: You must select Estimate_v1, or you cannot complete step 4.

    4. Select any additional tables you want to report on (for example, Item_v1).
    5. Click [Next].
    6. On the Link tab, click [Next].

  3. Tip: You can use the SQL Server Management Studio or the Crystal Reports Designer to view the names of fields in each table. For more information, see Using Sage Estimating Data on Reports

    On the Field tab of the Standard Report Creation Wizard:

    1. Select fields (columns from the tables)—for example:

      • Estimate_v1.Name
      • Item_v1.PhaseCode
      • Item_v1.ItemCode
      • Item_v1.Description
      • Item_v1.TakeoffQuantity
      • Item_v1.TakeoffUnitName

    2. Click [Finish]

      You then see the main Crystal Reports window, in preview mode.

  4. Note: For steps on querying the custom reports database to identify valid EstimateId values, see Before you start.

    1. In the Field Explorer pane in Crystal Reports:
      1. Right click Parameter Fields, and then click New.

      2. In the Name field, enter EstimateId.

        Note: This field is case-sensitive.

      3. For the Type option, select Number.
      4. For the Value Field option, select Estimate_v1.EstimateId.
      5. Click [OK] to return to the preview.
    2. With the EstimateId parameter selected, on the Report menu, click Select Expert > Record.
    3. In the Select Expert — Record window:
      1. On the Estimate_v1.EstimateId tab, from the first drop-down list, select is equal to.
      2. In the drop-down list that appears next, select EstimateId.
      3. Click [OK].
    4. In the Enter Values window, type the EstimateId for the estimate that you want to use for the report, and then click [OK].

    5. On the Report menu, click Refresh Report Data, or press F5.

      If you entered a valid EstimateId, Crystal Reports displays preview data.

    1. On the main menu in Crystal Reports:

      1. Click File.

      2. If the Save Data with Report option is selected, clear it.

        Important! This step removes the ID for the estimate you used to create the report. The report can then take on the ID of the current estimate when you run the report in Estimating.

      3. Click Save As.

    2. In the Save As window:

      1. Navigate to an easy-to-find folder.
      2. Enter the report file name.
      3. Click [Save]

After creating the report, add it to Estimating Management Console

When you finish the report design, you must make the report available to Sage Estimating by adding it to the Report Manager in the Estimating Management Console.

To add a new estimate report to the Report Manager, follow the steps in Adding a report to the Report Manager: