Add a Data Set

The steps for adding a data set differ depending on the version you have installed. Select the version you are on below to complete the task:

A dataset fetches data from the data source to display in a report. The DataSet panel is where you provide a command type and query string and choose other options for your dataset. Common Values can also be used and are generic report fields that can be added to a report.

  1. Open the Report Designer.
  2. Click the Data tab.
  1. Click Add Data Set.

  1. Enter the Name of the data set. Data set names cannot have spaces.
  1. Select Command Type. The default is Text.
  1. Enter a query to gather the desired fields from the desired database tables. These are the fields that are used to create reports.
  • Understanding Cityworks Data Models helps to link data correctly to generate the desired reports. See Cityworks 23 Data Models on MyCityworks for more information.

    • TIP: To add an entire table to a report, enter the following in the Query field: Select * from table. See Common Query Examples and Add a Dataset in the ActiveReports documentation for more a more in-depth explanation of data sets and crafting queries.

    1. Click Validate.
    2. Click OK.

    BEST PRACTICE: It is best to include only the necessary fields in the query to reduce the load on the application calls to the SQL Server instance. This approach ensures that the application logic iterates through fewer fields, optimizing performance.

    The fields appear in the Data Sets panel on the right side. Now these fields can be dragged and dropped in to a report.

    A data set fetches data from the data source to display in a report. The DataSet panel is where you provide a command type and query string and choose other options for your data set. Common Values can also be used and are generic report fields that can be added to a report.

    1. Open the Report Designer.
    2. Click the Data tab.
    1. Click Add in the Data Sets section.

    1. Double-click the DefaultDataSet.

    It appears under the Data Sets section.

    1. Click Edit Data Set.

    1. Edit the Name of the data set. Data set names cannot have spaces.
    2. Select Command Type. The default is Text.
    3. Enter a query to gather the desired fields from the desired database tables. These are the fields that are used to create reports.
  • Understanding Cityworks Data Models helps to link data correctly to generate the desired reports. See Cityworks 23 Data Models on MyCityworks for more information.

    • TIP: To add an entire table to a report, enter the following in the Query field: Select * from table. See Common Query Examples and Add a Dataset in the ActiveReports documentation for more a more in-depth explanation of data sets and crafting queries.

    If a custom data source was added to pull in attachments, enter something similar in the Query field to connect to the files wanted:

    • Attachments: attachment:<folder>/<filename>.png

    IMPORTANT: To pull in the attachments, the Attachment Root Directory preference in Admin should be edited to include /data/attachments/ in the path. See Configure Domain Preferences in the Admin 3.8 Guide for more information.

    1. Click Validate.

    NOTE: If you are an Oracle client and validate doesn't work, remove the ending semicolon and click Validate again.

    1. Click OK.

    BEST PRACTICE: It is best to include only the necessary fields in the query to reduce the load on the application calls to the SQL Server instance. This approach ensures that the application logic iterates through fewer fields, optimizing performance.

    The fields appear in the Data Sets panel on the right side. Now these fields can be dragged and dropped in to a report.