Configure a Data Source

Configuring a data source in Report Designer provides VertiGIS Studio Reporting with the information it needs to connect to the data source and query it for data to include in the report. You only need to configure a data source once. You can then use it in any report that you author.

To configure a data source, you first specify the type of data source, for example, ArcGIS, JSON, SQL Server, Oracle, and so on. This is the provider. You then configure the individual connection settings that are required by that data source. VertiGIS Studio Reporting builds a connection string from the settings that you configure. When a user runs a report, VertiGIS Studio Reporting uses the connection string to initiate a connection with the data source. For more information regarding data source parameters, see Data Source Parameters.

You must know in advance what information is required to connect to a particular data source. If you aren't sure, ask the data source's administrator or refer to a resource like The Connection Strings Reference.

Reporting supports three types of data source out of the box—ArcGIS, JSON, and SQL Server. These providers work in both on-premises Reporting and cloud Reporting. You can use a wide variety of other providers with on-premises Reporting by installing the provider's driver on your server.

To add a data source, click Data Sources | Add Data Source. You are presented with a list of providers to select from. The list contains the three out-of-the-box providers, ArcGIS, JSON, and SQL Server. In on-premises Reporting, additional providers may be listed if Report Designer finds an installed driver for the provider. The Other option allows you to configure the connection information for providers that do not appear in the list.

List of providers, showing one provider for which a driver is installed

For instructions on how to configure the connection information for a specific provider, see:

• Configure an ArcGIS Data Source

• Configure a JSON Data Source

• Configure a SQL Server Data Source

• Configure Other Types of Relational Databases

Configure an ArcGIS Data Source

VertiGIS Studio Reporting supports ArcGIS map services and feature services as data sources.

On-premises and Cloud Reporting require ArcGIS Server data sources to be from version 10.3 or above.

To configure the connection information for an ArcGIS service, you must configure the service's URL. If the service is secured, you may have to configure a user name and password as well, depending on what type of security is used:

• Federated Security: If the service is federated with Portal for ArcGIS, you do not need to configure a user name and password. VertiGIS Studio Reporting uses the end user's ArcGIS identity to access federated services.

  

  Example configuration for a federated map service

• ArcGIS Online-Hosted Feature Services: If the service is hosted by ArcGIS Online, then you do not need to configure a user name and password. VertiGIS Studio Reporting uses the end user's ArcGIS identity to access hosted services.

  

• Web-Tier Security: If the service is secured using web-tier security, you must configure the Web Username and Web Password settings. Use the Add Parameter function to add the settings.

  

  Example configuration for a feature service that is secured using web-tier security

• Token-Based Security: If the service is secured using token-based security, you must configure the Token Username and Token Password settings. Use the Add Parameter function to add the settings.

  

  Example configuration for a map service that is secured using token-based security

There are two ways to configure an ArcGIS data source:

• Data Sources panel: This is the general method to configure data sources.

• Layer Report: The Layer Report template creates a report that is bound to an ArcGIS data source and takes a list of feature IDs as input. The template has a Use a new ArcGIS layer or table option that provides a step to configure the service's URL.

  If the connection requires additional settings, for example a user name and password, you cannot use the Use a new ArcGIS layer or table option. You must configure the data source in the Data Sources panel before using the template to create a new report. You can then use the template's Use an existing data source option to create a layer report that is bound to the data source.

  To open the template, click File | New | Layer Report.

To configure an ArcGIS data source using the Data Sources panel:

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

   

2. Click Add Data Source and select ArcGIS.

   You can also configure an ArcGIS data source from scratch using the Other option.

3. In the Name box, type a name for the data source.

   Choose a meaningful name. If you change the name after you have added the data source to one or more reports, the data source will be removed from those reports. You will have to add back the data source under its new name and reconfigure it in each report.

4. In the Service URL box, type or paste the URL for the map service or feature service.

   For example:

   http://maps.mydomain.com/arcgis/rest/services/LocalMap/MapServer

   https://services.myorg.com/arcgis/rest/services/Utilities/FeatureServer

   This is the URL for a service, not a layer. You will specify the layers later, when you add the data source to the report.

   Support for ArcGIS Server FeatureServer endpoints on versions 10.5 and lower is limited. For ease of use, create data sources using the MapServer endpoint.

5. If the service is secured using web-tier or token-based security:

   • Web-Tier Security:

     1. Click Add Parameter and select Web Username to add the setting to the panel.

     2. Type the user name in the Web Username box.

     3. Click Add Parameter and select Web Password to add the setting to the panel.

     4. Type the password in the Web Password box.

   • Token-Based Security:

     1. Click Add Parameter and select Token Username to add the setting to the panel.

     2. Type the user name in the Token Username box.

     3. Click Add Parameter and select Token Password to add the setting to the panel.

     4. Type the password in the Token Password box.

   If you ever need to change the password for an existing data source, you must type the full password. Editing selected characters in the existing password will fail.

6. If you need to configure additional settings:

   1. Click Add Parameter , select Other, type the name of the setting, and click OK.

      The setting is added to the panel.

      Use the setting name defined by the provider. This ensures that the provider will recognize the setting. Refer to the provider's documentation for information.

   2. Configure the setting.

   3. Repeat these steps for each additional setting.

7. Click Test to test the connection.

   If the test fails, verify the information you have configured and make corrections as needed.

   Make sure you have specified the URL to the service, not to a specific layer.

8. Click Save.

   The data source is listed on the Data Sources panel.

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

Configure a JSON Data Source

VertiGIS Studio Reporting supports JSON data sources. Using JSON allows you to dynamically generate data in a client application and use this data in a report.

To configure a JSON data source:

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

   

2. Click Add Data Source and select JSON.

3. In the Name box, type a name for the data source.

   Choose a meaningful name. If you change the name after you have added the data source to one or more reports, the data source will be removed from those reports. You will have to add back the data source under its new name and reconfigure it in each report.

4. In the JSON box, type or paste the JSON that is a representative sample of the expected JSON that client applications will supply.

5. Click Test to validate the JSON.

   If the test fails, verify the information you have configured and make corrections as needed.

6. Click Save.

   The data source is listed on the Data Sources panel.

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

Configure a SQL Server Data Source

To configure a SQL Server data source:

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

   

2. Click Add Data Source and select SQL Server.

   You can also configure a SQL Server data source from scratch using the Other option.

3. In the Name box, type a name for the data source.

   Choose a meaningful name. If you change the name after you have added the data source to one or more reports, the data source will be removed from those reports. You will have to add back the data source under its new name and reconfigure it in each report.

4. In the Data Source box, type the location of the server, for example, localhost.

   DataSource will always be used, as it points to the location of the SQL instance, either by common name or IP address.

5. In the Initial Catalog box, type the name of the database.

6. Type the user ID in the User Id box.

7. Type the password in the Password box.

   If you ever need to change the password for an existing data source, you must type the full password. Editing selected characters in the existing password will fail.

8. If you need to configure additional settings that don't appear in the panel:

   1. Click Add Parameter .

   2. If the setting that you want to configure appears in the list, select the setting. This adds the setting to the panel.

      If the desired setting does not appear in the list, select Other, type the name of the setting, and click OK to add the setting to the panel.

      Use the setting name defined by the provider. This ensures that the provider will recognize the setting. Refer to the provider's documentation for information.

   3. Configure the setting.

   4. Repeat these steps for each additional setting.

9. Click Test to test the connection.

   If the test fails, verify the information you have configured and make corrections as needed.

10. Click Save.

    The data source is listed on the Data Sources panel.

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

Configure Other Types of Relational Databases

Besides the out-of-the-box providers (ArcGIS, SQL Server, and JSON), VertiGIS Studio Reporting supports many other providers, such as MySQL, PostgreSQL, Oracle, Oracle ODP.NET, and Oracle ODP.NET Managed.

To use other providers with VertiGIS Studio Reporting, you must install drivers for the providers. Because they require you to install a driver, other providers are only available for on-premises Reporting.

When you configure a data source in Report Designer, Report Designer detects which drivers are installed and lists those providers in the drop-down list. In this case, you can select the provider from the list to configure its connection information. If the provider is not listed, select the Other option.

To configure the connection information for a provider:

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

   

2. Click Add Data Source and select the type of data source. If the desired type is not listed, select Other.

3. In the Name box, type a name for the data source.

   Choose a meaningful name. If you change the name after you have added the data source to one or more reports, the data source will be removed from those reports. You will have to add back the data source under its new name and reconfigure it in each report.

4. Configure the XpoProvider setting.

   The XpoProvider setting specifies the provider, for example, Oracle, Postgres, or MySQL. To find out the value to enter, see Connection Information for Selected Providers.

5. Configure the settings that are needed to connect to the data source.

   Some settings may already appear in the panel, ready to configure. You can also add settings to the panel as follows:

   1. Click Add Parameter .

   2. If the setting that you want to configure appears in the list, select the setting. This adds the setting to the panel.

      If the setting does not appear in the list, select Other, type the name of the setting, and click OK to add the setting to the panel.

      Use the setting name defined by the provider. This ensures that the provider will recognize the setting. Refer to the provider's documentation for information.

   3. Configure the setting.

   4. Repeat these steps for each setting that does not appear in the panel.

6. Click Test to test the connection.

   If the test fails, verify the information you have configured and make corrections as needed.

7. Click Save.

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

Connection Information for Selected Providers

The following table provides examples of connections for different providers. In the examples, the configured settings and their values are represented as setting Name: value.

Connections for Selected Providers

Access

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Jet: Example configuration: XpoProvider: MSAccess Provider: Microsoft.Jet.OLEDB.4.0 Data Source: C:\myaccessdb.mdb ACE: Example configuration: XpoProvider: MSAccess Provider: Microsoft.ACE.OLEDB.12.0 Data Source: C:\MyDatabase.accdb

Advantage

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Advantage Data Source: \\myserver\myvolume\mypat\mydd.add ServerType: local User ID: ASSSYS TrimTrailingSpaces: true

ArcGIS

This provider is available out of the box in both VertiGIS Studio Reporting in the cloud and on premises. To configure an ArcGIS connection, click Add Data Source, select ArcGIS, and configure the settings that the data source requires, for example: Service URL: http://maps.mydomain.com/arcgis/rest/services/GISInfo/MapServer To configure an ArcGIS data source from scratch, click Add Data Source, select Other, and set the XpoProvider setting to Geocortex.Reporting.Engine. Add a Feature Provider setting and set it to GeoRestFeatureSource. Other supported settings include: Service URL (required) Token Username (optional) Token Password (optional) Web Username (optional) Web Password (optional)

Asa

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Asa Uid: MyUsername PWD: MyPassword DBF: c:\mydatabase.db

Ase

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Ase Port: 5000 Data Source: MyAseServer User ID: MyUserName Password: MyPassword Initial Catalog: MyDatabase

DB2

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: DB2 Server: MyAddress:MyPortNumber User ID: MyUserName Password: MyPassword Database: MyDatabase

Firebird

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Firebird DataSource: localhost User: SYSDBA Password: masterkey Database: MyDatabase.fdb ServerType: 0 Charset: NONE

MySQL

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: MySql Server: localhost Database: MySQLDatabase Uid: mysqluser Pwd: mysqlpassword

Oracle

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Oracle Data Source: TORCL User Id: oracleuser Password: oraclepassword

Oracle ODP.NET

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: ODP Data Source: TORCL User Id: oracleodpnetuser Password: oracleodpnetpassword

Oracle ODP.NET Managed

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: ODPManaged Data Source: localhost User Id: oracleodpnetmanageduser Password: oracleodpnetmanagedpassword

Pervasive SQL

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Pervasive Server: MyServerAddress UID: MyUserName PWD: MyPassword ServerDSN: MyDatabase

PostgreSQL

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: Postgres Server: 127.0.0.1 Database: PostgreSQLDatabase User Id: postgresqluser Password: postgresqlpassword

SQLite

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: SQLite Data Source: C:\database.db

SQL Server

This provider is available out of the box in both VertiGIS Studio Reporting in the cloud and on premises. To configure a SQL Server connection, click Add Data Source, select SQL Server, and configure the settings that the data source requires, for example: Data Source: localhost Initial Catalog: SQLServerDatabase User Id: sqlserveruser Password: sqlserverpassword To configure a SQL Server connection from scratch, click Add Data Source, select Other, and set the XpoProvider setting to MSSqlServer. Use the Add Parameter function to configure the other settings that the data source requires.

SQL Server CE

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: MSSqlServerCE Data Source: C:\MyDatabase.sdf Password: MyPassword

VistaDB

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: VistaDB Data Source: C:\mydatabase.vdb4

VistaDB5

To use this provider with an on-premises installation of VertiGIS Studio Reporting, you must install a driver. This provider is not available in VertiGIS Studio Reporting in the cloud. Example configuration: XpoProvider: VistaDB5 Data Source: C:\mydatabase.vdb5

Modify a Data Source's Connection Information

Changing the connection information for a data source changes it for every report that uses the data source.

For instructions on changing a single report to use a different data source, see Modify a Report to Use a Different Data Source.

To modify a data source's connection information:

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

   

2. Click the data source that you want to modify.

   Alternatively, click    beside the data source and select Edit.

3. Modify the data source as desired.

   To remove a setting, delete the setting's value.

4. Click Test to test the connection.

5. Click Save.

Remove Connection Settings

If you need to change the connection information for an existing data source, you may have to remove connection settings that no longer need to be configured. Report Designer does not provide a function to remove existing settings. Instead, delete the value of the setting and save the connection information. The next time you open the data source in the Data Sources panel, the setting will be gone.

To remove a setting from an existing connection:

1. In the Data Sources panel, select the data source.

   The data source's settings show.

2. Delete the value from the setting that you want to remove.

3. Click Save.

4. Click away from the Data Source panel to close the panel.

5. To verify that the setting has been removed, re-open the Data Sources panel and select the data source.

Delete a Data Source

If you delete a data source that is used by one or more reports, the reports will no longer work. They will not be able to connect to the data source.

To delete a data source:

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

   

2. Click    beside the data source that you want to delete.

3. Click Delete.

   You will be prompted to confirm.

4. Click OK.

   The data source is removed from the Data Sources panel.