Configure a Query

To generate a report that includes data, Geocortex Reporting queries the report's data sources for the data. In the simplest case, a report includes data from a single table in a data source. This requires a single query. In more complex cases, a report may include data from multiple tables in the same or different data sources. This requires multiple queries, one for each table.

Configuring a query can occur in several steps. At the very least, you must:

  1. Add a data source to the report: This creates the query. In this step, you specify which fields you want the query to return so that you can include those fields in the report. If you select an entire table, then the query will return all the fields. You can modify the fields later if necessary.

By default, a query returns all the records in the data source. In other words, the query is not filtered. You can restrict which records a query gets as follows:

  1. Add filter conditions to the query: Filter conditions restrict the records returned by the query. For example, you could filter for records in which the value of the POPULATION field is greater than 100 000 and less than 500 000.

Another method to restrict the records returned by a query is to configure the report to take inputs. To do this, you:

  1. Configure a report parameter: Report parameters are input parameters. You can use a report parameter to pass ArcGIS feature IDs into a report at run time.
  2. Add a filter condition for the report inputs: In order for a report parameter to affect which records are returned by the query, you must bind the report parameter to a query parameter in a filter condition.

For more information about report inputs, see Include the Current Features in a Report.

If you know in advance that you want a report to take inputs, you can optimize the steps shown above as follows:

  1. Add the data source.
  2. Configure the report parameter.
  3. Add all the filters conditions at once.

Filter Conditions

Filter conditions are expressions that restrict the records that are returned by a query. They do this by comparing the value of a particular field in the table that is being queried to some value. The value that the field is compared to can be:

To define filter conditions on a query , you use the Filter Editor. There are two ways to configure a filter expression using the Filter Editor:

Open the Filter Editor

Open the Filter Editor from the Query Properties panel in the Query Builder

To open the Filter Editor:

  1. Click Fields to open the Field List, and then expand the data source.

  2. Click the table to show the management buttons.

  3. Click Edit Query to open the SQL Data Source Wizard.

  4. Click Run Query Builder.

  5. In the Query Properties panel, click the icon in the Filter box.

    The Filter Editor opens.

Add a Data Source to a Report

To add a data source to a report:

Adding a data source to a report creates a query that returns the specified fields. You can modify the query later to return different fields if necessary.

  1. Click Add SQL Data Source to open the SQL Data Source Wizard.

    Location of Add SQL Data Source icon

  2. Select the data source and click Next.

  3. Click Tables to show the tables in the data source.

    If the data source is a map service or feature service, the service's layer and tables are listed under Tables.

    Example of a data source with three tables

  4. To show the fields in a table, expand the table.

    If the data source is a map service or feature service, the fields are feature attributes.

  5. Select the checkbox for each field that you want to be available to include in the report.

    These are the fields that will be returned when the data source is queried.

    Table with two fields selected

  6. Click Finish to close the wizard.

  7. To verify that the data source has been added to the report, click Fields to open the Field List, expand the data source, and expand the table.

    Data source in the Field List

Add Filter Conditions to a Query

To add filter conditions to a query:

  1. Click Fields to open the Field List, and then expand the data source.

  2. Click the table to show the management buttons.

  3. Click Edit Query to open the SQL Data Source Wizard.

  4. Click Run Query Builder.

  5. In the Query Properties panel, click the icon in the Filter box to open the Filter Editor.

  6. Add a condition using the desired mode:

  7. Repeat the previous step to add as many conditions as you want.

  8. Click Save.

    The filter expression appears in the Filter box in the Query Properties panel.

  9. Click OK to close the Query Builder.

  10. Click Finish to close the SQL Data Source Wizard.

Modify a Query to Return Different Fields

To modify a query to return different fields:

  1. Click Fields to open the Field List, and then expand the data source.

  2. Click the table to show the management buttons.

  3. Click Edit query.

    The SQL Data Source Wizard opens.

  4. Click Run Query Builder.

    The Query Builder opens. The table is shown at the left of the Query Builder.

  5. Select the checkboxes for the fields that you want the query to return. Clear the checkboxes for the fields that you don't want the query to return.

  6. Click OK to close the Query Builder.

  7. Click Finish to close the SQL Data Source Wizard.

Modify a Report to Use a Different Table in the Same Data Source

This procedure shows you how to change a report to use a different table within the same data source. To do this, you add the new table to the report and then remove the original data source. If the new fields have different names than the original fields, you will have to update the report's layout.

To modify a report to use a different table in the same data source:

  1. Add the new table:

    1. Click Fields to open the Field List, and then expand the data source.

      Data source with one table called easements

    2. Click the data source to show the management buttons.

    3. Click Add query to open the SQL Data Source wizard.

    4. Click Run Query Builder to open the Query Builder.

    5. Drag the table you want to query from Available Tables and Views and drop it in Query Builder.

      Add the new table

    6. Select the checkboxes for the fields that you want returned in the query.

      Fields to return in the query

    7. Click OK to close Query Builder.

    8. Click Finish to close the SQL Data Source Wizard.

      The data source now has two tables.

      Data source with two tables

  2. If you want, configure filter conditions on the query. See Add Filter Conditions to a Query.

  3. Update the fields in the report's layout, if the field names are different.

  4. Configure the report to use the new table:

    1. Click Properties to open the Properties panel.

    2. Select the report (XtraReport) from the Properties drop-down list.

    3. Expand Data and set Data Member to the new table.

      The Data Member property appears in both Data and Actions. You can set the property in either location.

      Data Member configured to use the new table

  5. Remove the original table:

    1. Click Fields to open the Field List, and then expand the data source if necessary.

    2. Click the original table to show the management buttons.

      Management buttons for the original table

    3. Click Remove query to remove the table.

  6. Press Ctrl+S to save the report.

  7. To verify the report, preview it .

Modify a Report to Use a Different Data Source

This procedure shows you how to change a report to use a different data source. To do this, you add the new data source to the report, configure it, and then remove the original data source. If the fields in the new data source have different names than the original fields, you will have to update the report's layout.

To modify a report to use a different data source:

  1. Configure the connection information for the new data source, if necessary. See Configure a Data Source.

  2. Add the new data source to the report. See Add a Data Source to a Report.

  3. If you want, configure filter conditions on the query. See Add Filter Conditions to a Query.

  4. Update the fields in the layout, if the field names are different.

  5. Remove the original data source:

    1. Click Fields to open the Field List, and then expand the data source.

    2. Click the original data source to show the management buttons.

      Management buttons for the original data source

    3. Click Remove Data Source .

  6. Press Ctrl+S to save the report.

  7. To verify the report, preview it .

© 2019 Latitude Geographics Group Ltd. All Rights Reserved.

Documentation Version 5.2