Connect to a SQL Database
- 4 minutes to read
The Report and Dashboard Server supports the following SQL data providers:
- Advantage Database Server
- Amazon Redshift
- Firebird
- Google BigQuery
- IBM DB2
- Microsoft Access 2007
- Microsoft Access 97
- Microsoft SQL Server
- Microsoft SQL Server Compact
- MySQL
- Oracle Database (legacy provider, x86 only)
- Oracle Database (ODP Managed)
- Oracle Database (ODP)
- Pervasive PSQL
- PostgreSQL
- SQL Anywhere
- SQLile
- Sybase ASE
- Teradata
- VistaDB
- VistaDB5
- XML Dataset
Note that the Report and Dashboard Server does not install any data provider (except Microsoft SQL Server). You should install data providers and register them in GAC.
See How to create a correct connection string for XPO providers for information on how to construct a connection string for each supported provider.
Data Models
Click Data Models on the Report and Dashboard Server’s toolbar to switch to the data models view.
This screen is available for users that have permissions to access and customize data models.
Click Add Data Model to invoke the New Data Model dialog.
- Define the data model’s name and description.
Select the data provider and specify its connection settings.
You can specify connection parameters for Microsoft SQL Server, MySQL, Oracle, PostgreSQL and SyBase ASE. For other providers, enter the connection string in the Connection String field.
Enable the Set Custom Command Timeout option and specify Command Timeout.
The provider terminates the query if it exceeds the specified timeout and the report displays an error message. The timeout settings are available for all providers except XML Dataset.
Note that the Report and Dashboard Server backend uses the Network Service account. If a data model uses Windows authentication to connect to a database, ensure the Network Service account has permission to access the database. See the How To: Use the Network Service Account to Access Resources in ASP.NET MSDN topic for information on how to provide permissions to Network Service.
The created data model appears in the list. Select the model to see its details.
Click Edit to change connection settings.
Tables
Click Tables to open the Tables tab in Edit Data Model Schema.
Check tables to include them in the data model. You can also select which columns a table should contain. Use the search boxes to find specific tables or columns.
Procedures
Click Procedures to open the Edit Data Model Schema dialog’s Procedures tab.
Check procedures you want to add to the data model. Select a procedure to see its fields in the Fields list.
Queries
Click Queries to open the Edit Data Model Schema dialog’s Queries tab.
Click the New Query button to open the Query Builder and create a query.
To add a table or view to a query, drag it from the Available tables and views section and drop onto the design surface.
Check columns that you want to include in the query.
You can join multiple tables within the same query. Drag and drop a subordinate table to include it in a query and create an inner join relation based on a key column.
Click the data relation to display its properties in the Relation Properties section. You can define the join type (Inner or Left Outer) and the logical operator to apply (for instance, Equals to, Is greater than, etc).
Select a column to access its properties. You can sort column records, group data by this column or apply an aggregate function.
Note
You should apply grouping and/or an aggregate function either to all selected columns or to none of them.
Expand Query Properties and specify the query name. Click the Filter property’s ellipsis button to invoke the Filter Editor to filter query results against static values, other fields’ values, parameters, and/or functions.
Click OK to apply the filter, and save the query.
Custom SQL Query
Important
Use custom SQL queries at your own risk, as they may cause security issues.
Custom queries are available when the Custom SQL Query Execution Mode option is set to Execute SELECT Statements Only or Execute All. See General Settings for more information.
Click New Query and select Write Custom SQL Query.
Write the query and give it a name.
Click OK to save the query and make it available in the data source. See Bind a Report to Data and Bind a Dashboard to Data for more details.