How to Display Related Data From Many-to-many Relationships in VertiGIS Studio Reporting

In VertiGIS Studio Reporting, you can display data from a many-to-many relationship by using nested subreports. In this setup, the main report queries the Origin Table, then a parent subreport queries the Relationship Table using the Origin Primary Key from the main report, then a nested subreport queries the Destination Table using the corresponding Destination Foreign Key from the parent subreport.

NOTE: For this to work, it is necessary to include the relationship class as a table in the ArcGIS map or feature service. If the relationship table has not been included, please republish the service so that it is.
 

Step-by-step Guide

Set up the parent subreport

1. Open Reporting Designer and create a new Layer Report. Set the data source to the Origin Table in the relationship.
2. Drag-and-drop a Sub-Report control into the Detail band.
3. Double-click the new Sub-Report control.
4. In the right toolbar, click on the Field List button ( ).
5. Next to Parameters, click the "Add parameter" button ( ). This new parameter will hold the value of the Origin Primary Key from the Origin Table, which corresponds to the Origin Foreign Key in the Relationship Table.
6. Set the Type based on the field type of your Origin Primary Key and set the Name to something that makes sense to you (e.g. OriginPrimaryKey). Optionally, set the Description. Do not change any of the other properties. Click OK.
7. At the top of the Field List panel, click the Add Data Source button.
8. Select the Relationship Table for your many-to-many relationship and click Finish.
9. Expand the newly added data source and click the "Edit query" button ( ) next to the data table.
10. Click Run Query Builder.
11. Expand the Query Properties section.
12. Click the three dots next to the Filter property.
13. Add a condition that binds the Origin Foreign Key in the Relationship Table to a new parameter using the "Equals" operator.
14. Expand the Parameters section and select the newly created parameter. Set the Type property to "Expression" and the Value property to the Sub-Report parameter you created earlier (e.g. [Parameters.OriginPrimaryKey] or ?OriginPrimaryKey).
15. Complete the Query Builder and the Data Source Wizard.
16. Navigate back one level.
17. Single-click the Sub-Report control.
18. In the right toolbar, click on the Properties button ( ).
19. Expand the Data section and then expand Parameter Bindings.
20. Click the "Add" button ( ).
21. Choose the Parameter Name from the dropdown and set the Binding to the field from the Origin Table that is used as the Origin Primary Key.
22. The parent subreport has been configured. Proceed to configuring the nested subreport.

Set up the nested subreport

1. Double-click the recently configured Sub-Report control.
2. Drag-and-drop a new Sub-Report control into the Detail band.
3. Double-click the new Sub-Report control.
4. In the right toolbar, click on the Field List button ( ).
5. Next to Parameters, click the "Add parameter" button ( ). This new parameter will hold the value of the Destination Foreign Key from the Relationship Table, which corresponds to the Destination Primary Key in the Destination Table.
6. Set the Type based on the field type of your Destination Foreign Key and set the Name to something that makes sense to you (e.g. DestinationForeignKey). Optionally, set the Description. Do not change any of the other properties. Click OK.
7. At the top of the Field List panel, click the Add Data Source button.
8. Select the Destination Table and click Finish.
9. Expand the newly added data source and click the "Edit query" button ( ) next to the data table.
10. Click Run Query Builder.
11. Expand the Query Properties section.
12. Click the three dots next to the Filter property.
13. Add a condition that binds the Destination Primary Key in the Destination Table to a new parameter using the "Equals" operator.
14. Expand the Parameters section and select the newly created parameter. Set the Type property to "Expression" and the Value property to the Sub-Report parameter you created in Step 6 of this section (e.g. [Parameters.DestinationForeignKey] or ?DestinationForeignKey).
15. Complete the Query Builder and the Data Source Wizard.
16. Navigate back one level.
17. Single-click the Sub-Report control.
18. In the right toolbar, click on the Properties button ( ).
19. Expand the Data section and then expand Parameter Bindings.
20. Click the "Add" button ( ).
21. Choose the Parameter Name from the dropdown and set the Binding to the field from the Relationship Table that is used as the Destination Foreign Key.
22. Double-click the Sub-Report control and configure this nested subreport with all the information you want from the related features.