Creating Report Datasets for Other Data Sources (Report Builder 2.0)
Report Builder 2.0 can access OLE DB or ODBC data source types. After you create a data source definition in your report, you specify a query in the query language of the data source to create a dataset.
Note
Report Builder 2.0 does not support adding custom data providers on your local computer. In server mode, you can access any data provider that is installed and registered on the report server. For more information, see Report Servers and SharePoint Report Servers (Report Builder 2.0).
Specifying Connection String Properties for a Data Provider
To set connection string properties specific to a data provider, from the General page of the Data Source Properties dialog box, click the Build button to open the Connection Properties dialog box. For an OLE DB data source type, you can select the data provider for the data source and set extended data source properties through the Data Link Properties dialog box. For an ODBC data source type, you can set the connection string. For more information and example connection strings, see Example Connections (Report Builder 2.0).
Query Designer Support for OLE DB and ODBC Data Sources
Report Builder 2.0 provides a query designer to help you specify a query for an OLE DB or ODBC data source. By default, the text-based query designer opens in one of three modes: Text, TableDirect, or StoredProcedure. You can type a query, select all the columns from a database table, or select a stored procedure. For more information about how to use this query designer, see Text-based Query Designer User Interface (Report Builder 2.0).
Some features depend on implementations provided by the data provider. For example, some data providers do not support parameterized queries or do not support named parameters. For more information, see Using Query Parameters with Specific Data Sources (Report Builder 2.0).
Query Designer Support for Other Data Sources
The query designer for each data provider is registered when Report Builder 2.0 is installed. When you create a query for a dataset based on a data source, the query opens in the default view for the query designer for the data provider. For more information, see Query Designers and Data Sources (Report Builder 2.0).
Mapping Data Types from the Data Provider for the Report
When you create a dataset, the data types of the fields are mapped to a subset of common language runtime (CLR) data types from the .NET Framework. Data types that cannot be clearly mapped are returned as strings. For more information about working with field data types, see Working with Fields in a Report Dataset (Report Builder 2.0). When you create a parameter, the data type must be a supported report definition data type. For more information about mapping data types from the data provider to a report parameter, see Working with Data Types in Expressions (Report Builder 2.0).
Special Characters in a Password
If you configure your ODBC or OLE DB data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters such as punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem. If changing the password is impractical, you can work with your database administrator to store the appropriate credentials on the report server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.
Note
It is recommended that you do not add login information such as passwords to the connection string. Report Builder 2.0 provides a separate tab on the Data Source dialog box that you can use to enter credentials. For more information, see Specifying Credentials for a Report Data Source (Report Builder 2.0).