By: Rob Fisch | Comments (1) | Related: > Reporting Services Data Sources
Problem
The Reporting Services datasource is a programming object that holds the configuration information on how to access data for reporting. This includes the server name, database name, the type of data source (i.e. SQL Server, Oracle, text file, etc.) and the credentials of the user accessing the data.
Reporting Services offers many ways to use datasources. They can be localized (embedded) within a report or they can be "shared" (usable by more than one report). When shared, they can be decentralized within a single project (and report folder) or they can be utilized centrally amongst multiple projects and folders.
What is the best method to use?
Solution
In order to decide on the usage of centralized vs. decentralized Datasources in Reporting Services, an analysis of the pros and cons of each type can be helpful for decision making purposes. Based on the information decide which type best fits a particular situation.
Pros | Cons |
Embedded in the report | |
|
|
Shared - same folder as reports | |
|
|
Shared - centralized location for multiple projects/folders | |
|
|
Summary
There is no one-size-fits-all solution that fits every situation. With some additional effort, some of the Pros and Cons can be mitigated by using multiple configurations in the Configuration Manager. Whatever you decide, understanding these issues will help you choose the best datasource type to use for your situation.
Next Steps
- Try making a localized datasource connection using some or all of the configurations set as parameters.
- For an example of parameterized datasource connections, download the solution from the Reporting Services Report - Determining Free Space Per Database to see some examples.
- Check out all of the Reporting Services tips on MSSQLTips.com!
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips