Types of Data Sources

There are three types of data sources. The first type of data source is the embedded data source. This type is kept within the report, and cannot be accessed by other reports . In the SSRS documentation, this is referred to as a report-specific data source.

The second type of data source is the shared data source. The largest difference between the two data sources is location. A shared data source lives on the Report Server as a separate entity from the reports, whereas the definition for a report-specific data source is stored within the report itself.

This allows other reports to use them for their data sources.

The third type of data source is an expression that is used to dynamically choose the data source at runtime. This is called a data source expression. Remember that just about every property can be modified by an expression. Data sources are no different.

Report-Specific Data Source

The report-specific data source should only be used when the data needed to process a report should be restricted to that report. If multiple reports need to access the same data source with the same credentials, you should use a shared data source. This is because maintaining lots of embedded data sources can be cumbersome. After the report has been published to the Report Server, the embedded data source has to be maintained as part of that report. Then, you can change it to reference a shared data source.

Shared Data Source

A shared data source exists on the Report Server as a separate entity. Report developers can define a shared data source in Visual Studio, but it does not overwrite an existing data source by default in the same manner that it overwrites reports.

A shared data source is useful when

• Many reports use data from the same location with the same credentials.

• An administrator needs to have an easy way to change the location of a data source for several reports. This is the case, for example, when moving reports from development to production.

Data Source Expressions

An expression can be used to define the connection at runtime. A classic case is the difference between an active OLTP database and historical data or a data warehouse. Many companies store historical data that is more than six months old in a data warehouse. You would have to determine an appropriate connection from some report-level parameter.

Like all expressions, a data source expression would have to be written in Visual Basic.NET and preceded by an "=" sign.

To define data source expressions, consider the following guidelines:

• Do not use a shared data source. You cannot use a data source expression in a shared data source. You must define a report-specific data source for the report instead.

• Design the reports using a static connection string.

• Use a report parameter to specify the values to be passed to the expression. The parameter can pull from a list of valid values from a query using a separate data source. Later in this chapter, you will see how to set up parameter dependencies that allow you to do this.

• Make sure all the data sources implement the same schema.

• Before publishing the report, replace the static connection string with an expression.

It is easiest to use Windows authentication. This is because the Report Server stores data source credentials separately from the data sources themselves . Another option is to hard-code the credentials, or prompt a user for login credentials.

The following is an example of an expression-based data source for SQL Server:

 ="Data Source=" &Parameters!DBServer.Value & ";Initial Catalog=NorthWind 

The preceding example assumes that there is a parameter called DBServer .