Include Related Data in a Report

You may want to create a report that includes primary data from one table and related data from a second table. In one common scenario, a report may include attribute values from an ArcGIS layer and, for each feature, related data from a database.

A report that includes primary and secondary data in a one-to-one or one-to-many relationship is called a Master-Detail report. In order to include related data, the primary and secondary tables or layers must have a field in common, so each primary record relates to the correct secondary records. For example, one table could have a FIPS field that contains the state's FIPS code and the other table could have a STATE_FIPS field that contains the state's FIPS code. When the value in the FIPS field equals the value in the STATE_FIPS field, the data in the two records are for the same state.

Example of a one-to-many Master-Detail report

There are two ways to create a Master-Detail report: using a subreport or using a detail band. The method that you use depends on the number of data sources:

The tutorial below shows you how to create a Master-Detail report using a subreport. The subreport method allows you to create any Master-Detail report, regardless of whether the primary data and related data come from the same data source or from different data sources.

Before you Begin

VertiGIS Studio Reporting can run in two environments—in the cloud or on premises. To do this tutorial, you must have an ArcGIS identity that gives you access to one of these environments.

Main Steps to Create a Master-Detail Report Using a Subreport

The main steps to create a Master-Detail report that uses a subreport are as follows:

  1. Create a new report.
  2. Configure the master data source and add it to the report.
  3. Design the master report.
  4. Add a subreport and open it.
  5. Configure the subreport's data source and add it to the subreport.
  6. Create a report parameter for the subreport.
  7. Add a filter condition to the subreport's query.
  8. Design the subreport.
  9. Preview the subreport.
  10. Return to the master report.
  11. Bind the subreport parameter.
  12. Preview the report.

Tutorial - Create a Master-Detail Report Using a Subreport

In this tutorial, you will design and preview a Master-Detail report that lists one or more US states and, for each state, the counties in that state. The states data comes from a layer in an ArcGIS Online map service. The counties data comes from a layer in a different ArcGIS Online map service. Because the states and counties come from different data sources, you must use a subreport to create the report. The data sources are:

Both the data sources used in this tutorial have a states and counties layer, so it would be possible to get the data from the same data source. For the sake of illustrating a scenario that uses different data sources and therefore requires a subreport, this tutorial gets the data from different data sources.

The layers used in this tutorial both have a field that contains the state's FIPS code. The field is called STATE_FIPS in both layers. The field name happens to be the same in both layers, but this is not required. You will use the STATE_FIPS field to relate the two layers. The steps to relate the two layers are as follows:

  1. Create a report parameter for the subreport.

    The subreport parameter represents the state's FIPS code in the subreport's data source.

  2. Filter the subreport's query based on the subreport parameter.

    You cannot use the subreport parameter directly in the query. Instead, you create a query parameter, use the query parameter in the query filter, and then configure an expression that binds the subreport parameter to the query parameter.

  3. Bind the state's FIPS code in the master report's data source to the subreport control in the master report.

Step 1: Create a New Report

  1. Open VertiGIS Studio Report Designer.

    If you use VertiGIS Studio Reporting in the cloud, you can access Report Designer here.

  2. Click Blank Report.

  3. Press Ctrl+S to save the report. Name the report: Median Age by County

    If you get a server error at any time while designing your report, try re-saving the report. It is possible that your ArcGIS session has timed out.

Step 2: Configure the Master Data Source

  1. Configure the data source's connection information, if necessary.

    If you have already configured the connection information for the map service at http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer, you can skip this step. Continue at the next step.

    1. In Report Designer's sidebar, click Data Sources.

    2. Click Add Data Source and select ArcGIS.

    3. In the Name box, type: Census (sampleserver6)

      When you configure a new data source, choose a meaningful name for the data source. If you change the name later, after you have added the data source to one or more reports, the data source will be removed from the reports. You will have to add back the data source under its new name and reconfigure it.

    4. In the Service URL box, enter: https://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer

      Connection information configured for the master data source

    5. Click Save.

    6. Click away from the Data Sources panel to close the panel.

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

    Location of Add SQL Data Source tool

  3. In the list of data connections, select Census (sampleserver6).

  4. In step 2 of the wizard, expand Tables to show the layers in the map service.

  5. Expand states to show the attributes in the states layer.

  6. Select the checkbox for each attribute that will be included in the report:

    • STATE_NAME
    • STATE_FIPS
  7. Click Finish to close the wizard.

  8. Press Ctrl+S to save the report.

Step 3: Design the Master Report

  1. Open the Properties panel and expand the Actions section.

  2. Click Insert Report Header Band .

  3. Reduce the height of the TopMargin1 band.

  4. Add the report's title:

    1. Drag a Label onto ReportHeaderBand1.
    2. Change the text to: Median Age by County
    3. Format the title as desired.
    4. Reduce the height of ReportHeaderBand1.

    Report title in the master report's ReportHeaderBand1

  5. Add content to the report body:

    1. In the Field List , expand the data source (sqlDataSource1), and expand the states layer.

    2. Drag and drop the STATE_NAME and STATE_FIPS fields onto the Detail1 band.

      On the design surface, data fields are marked with a data icon .

    3. Add whatever labels you want and format the content of the Detail1 band as desired.

    Formatted content in the master report's Detail1 band (STATE_NAME data field, "State FIPS" label, and STATE_FIPS data field)

  6. Press Ctrl+S to save the report.

Step 4: Add a Subreport

  1. Drag and drop the Subreport control onto the Detail1 band, below the data fields.

  2. Reduced the height of the Detail1 band.

    Subreport control added to the master report's Detail1 band

  3. Double-click the subreport1 control on the design surface to open the subreport.

    The subreport is initially blank. Note that there is no data source in the Field List—the data source that you added to the master report applies to the master report only.

    New, blank subreport

    Every subreport has its own design surface. When a subreport is open in Report Designer, the configuration panels show the configuration for the subreport. In order to see the master report's configuration, you must return to the master report. To return to the master report, press Ctrl+Alt+Home or click the Navigate to parent report tool .

  4. Press Ctrl+S to save the report.

Step 5: Configure the Subreport's Data Source

  1. Configure the data source's connection information, if necessary.

    If you have already configured the connection information for the map service at http://sampleserver5.arcgisonline.com/arcgis/rest/services/Census/MapServer, you can skip this step. Continue at the next step.

    1. In Report Designer's sidebar, click Data Sources.

    2. Click Add Data Source and select ArcGIS.

    3. In the Name box, type: Census (sampleserver5)

    4. In the Service URL box, enter: https://sampleserver5.arcgisonline.com/arcgis/rest/services/Census/MapServer

    5. Click Save.

    6. Click away from the Data Sources panel to close the panel.

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

  3. Select Census (sampleserver5) and click Next.

  4. Expand Tables to show the layers in the map service.

  5. Expand Detailed Counties to show the attributes in the Detailed Counties layer.

  6. Select the checkbox for the following attributes:

    • NAME
    • CNTY_FIPS
    • POP2007
    • MED_AGE
  7. Click Finish to close the wizard.

    The data source appears in the Field List.

    Subreport's data source in the Field List

  8. Press Ctrl+S to save the report.

Step 6: Create a Report Parameter for the Subreport

  1. In the Field List , click Parameters to show the management buttons.

  2. Click to add a parameter.

    A new parameter is created.

  3. Click to edit the parameter.

  4. Configure the parameter's properties:

    • In the Name box, type the following name for the parameter: stateFips

      The name can include letters, numbers, and underscore characters. It cannot include spaces or special characters.

    • In the Description box, type a description of the parameter, for example: FIPS code for the current state

    Subreport parameter configured to take a single FIPS code

  5. Press Ctrl+S to save the report.

    Saving a Master-Detail report that uses a subreport saves the entire report, regardless of whether you are in the master report or in the subreport when you save. You cannot save a subreport independently of the report that contains it.

Step 7: Add a Filter Condition to the Subreport's Query

  1. In the Field List , expand the data source (sqlDataSource1).

  2. Click Detailed Counties to show the management buttons.

  3. Click to edit the query.

    The SQL Data Source Wizard opens.

  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 the condition to match the state's FIPS code:

    • Default Mode:

      1. Hover the pointer to the right of the And operator to show the plus icon .

      2. Click the plus icon and select Add condition.

        New condition, ready to be configured

      3. Click the blue box, expand the Detailed Counties layer, and select STATE_FIPS.

      4. Click the arrow in the gray Enter a value box and select Parameter.

        The box turns yellow and says Create new parameter.

      5. Click the yellow box and select Create new parameter to create a new query parameter.

      6. Type the following name for the parameter: fipsFromMaster

      7. Click away from the condition.

        The filter expression looks like this in the default mode:

      8. Select the Advanced Mode checkbox.

        The expression looks like this in Advanced Mode:

        The right-hand term should specify the query parameter that you just defined. This is a limitation of the Filter Editor.

      9. Type the parameter name after the question mark.

        The expression should look like this:

    • Advanced Mode:

      1. Select the Advanced Mode checkbox.

      2. Type or paste the following expression in the box: [Detailed Counties.STATE_FIPS] = ?fipsFromMaster

        You can use the == operator instead of the = operator: [Detailed Counties.STATE_FIPS] == ?fipsFromMaster

  7. Click Save.

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

    Do not attempt to preview the results yet. The filter expression includes a query parameter that does not yet have a value, so the query will fail.

  8. Click OK to close the Query Builder.

  9. Click Next.

  10. Expand the FipsFromMaster query parameter to show the Name, Type, and Value.

  11. Set Type to Expression.

  12. Click the icon in the Value box to open the Expression Editor.

  13. Expand Parameters.

    These are the subreport parameters.

  14. Double-click the stateFips parameter, shown at in the screen capture.

    The Expression Editor will build the expression and display it, shown at . The expression is [Parameters.stateFips].

    Expression that accesses the stateFips subreport parameter

  15. Click Save.

    Association between query parameter fipsFromMaster and report parameter stateFips

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

  17. Press Ctrl+S to save the report.

Step 8: Design the Subreport

  1. In the Field List, expand Detailed Counties.

  2. Drag the fields from the Field List and drop them on the detail1 band.

    To insert multiple fields at one time as a table, select the fields by holding down the Ctrl or Shift key when you select the fields, and then drag the selection to the design surface.

  3. Add labels for the data fields:

    1. Open the Properties panel, expand Actions and click Insert Group Header Band .

      The Group Header Band allows you to include labels that appear once for each time the subreport runs, in other words, once for each group of counties.

    2. Add labels for the data fields to GroupHeaderBand1 and format them.

    3. Reduce the height of GroupHeaderBand1.

  4. Reduce the height of the detail1 band.

    Formatted content in the subreport

  5. Press Ctrl+S to save the report.

Step 9: Preview the Subreport

  1. Click Preview .

    You are prompted to enter a state FIPS code.

  2. Click in the stateFips box and type: 06

    This is the FIPS code for California.

  3. Click Run.

    The subreport opens as a PDF in a separate browser tab.

    Subreport preview

Step 10: Return to the Master Report

  1. In Report Designer, press Ctrl+Alt+Home or click the Navigate to parent report icon .

    Report Designer shows the master report.

    Location of icon to navigate to the master report

Step 11: Bind the Subreport Parameter

  1. Select the subreport1 control on the design surface.

  2. In the Properties panel, expand Data and then expand Parameter Bindings.

    If you don't see a Parameter Bindings section within the Data section, make sure that you are in the master report and you have selected the subreport1 control on the design surface.

  3. Click to add a parameter binding.

  4. Select stateFips from the Parameter Name drop-down list.

  5. Open the Binding drop-down list, expand the sqlDataSource1, expand the states layer, and select STATE_FIPS.

    The parameter binding's configuration means: The STATE_FIPS attribute in the states layer of the data source called sqlDataSource1is bound to the parameter called stateFips.

    Field in the master report's data source bound to a subreport parameter

  6. Save the report.

Step 12: Preview the Report

  1. Click Preview .

  2. Click Run.

    The Master-Detail report opens as a PDF in a separate browser tab. The report includes data from two different ArcGIS map services, one (the master) for US states and the other for related data about the counties in each state.

    Master-Detail report preview