Click here to Skip to main content
13,147,956 members (88,849 online)
Click here to Skip to main content
Add your own
alternative version


17 bookmarked
Posted 22 Sep 2009

Setting the Data Source of Reporting Services at Runtime

, 22 Sep 2009
Rate this:
Please Sign up or sign in to vote.
Access multiple databases by a single SSRS report.


Generally, we use a single database server as the data source for reports. But think of a situation where the same needs to be used for production, development, and testing. What if for security reasons, the user will be given the privilege to access the same report but from different database servers? This article will focus on one of the ways of achieving the same.


As already specified, there are many ways of achieving this. One good way of meeting the target has been proposed by Bilal Khawaja. I have come up with a different concept which I would like to share.

Software specification

Visual C# .NET 2008, SQL Server 2008.



As depicted in the above figure, there will be a central database that will have a table and a Stored Procedure.


To create a single report, and based on the people names, access the records from different databases. E.g., all names that start with A will be picked from DB-A; for B, it will be from DB-B, and the rest from DefaultDB.


Step 1: Create three databases namely DefaultDB, DB-A, DB-B.

Step 2: Create a table (say tblInformation) in each of the databases with the columns [Person Name], [Age], [Sex], [Address].

Step 3: Insert some values in all the four tables.

Step 4: Create a Stored Procedure in DefaultDB (say usp_GetResult) like so:

    -- Add the parameters for the stored procedure here
        @FirstLetter varchar(50)    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    -- Insert statements for procedure here
            CASE @FirstLetter
                WHEN 'A' THEN 'SELECT * FROM [DB-A].[dbo].[tblInformation]'
                WHEN 'B' THEN 'SELECT * FROM [DB-B].[dbo].[tblInformation]'

                                ELSE  'SELECT * FROM [DefaultDB].[dbo].[tblInformation]'

Step 5: Open VS 2008. Create a Windows Application.

Step 6: Click on Add->NewItem -> Dataset.


Step 7:


Step 8: Click on Server Explorer.


Step 9: Data Connection [right click] -> Add Connection.


Step 10: Choose the SQL Server connection, database name, and press the Test Connection button. Once successful, click the OK button.


Step 11: Now, expand the Stored Procedures tab and choose the Stored Procedure.


Step 12: Drag and drop the Stored Procedure (e.g., usp_GetResult) into the Dataset.


Step 13: Add New Item -> Report.


Step 14: Drag & Drop a TABLE item from the Reports Item Toolbox and place on the Report form [e.g., DisplayRecords.rdlc]. The field names from the Stored Procedures should be placed in the field that is to be displayed.

Step 15: Now, create a Form, say, Form2.cs. From the Toolbox, go to Reporting tab and choose MicrosoftReportViewer. Drag and drop the same on the form.


Step 16: Click on the |> sign on the top right corner of the ReportViewer and choose the report file (.rdlc).

Step 17: If everything is proper, we will get the following screen:


Step 18: And the code-behind, and add the following code:

public partial class Form2 : Form
    string FirstChar = string.Empty;
    public Form2()

    public Form2(string _FirstChar)
        FirstChar = _FirstChar;

    private void Form2_Load(object sender, EventArgs e)
        // TODO: This line of code loads data into
        // the 'NameDS.usp_GetResult' table. You can move, or remove it, as needed.
        this.usp_GetResultTableAdapter.Fill(this.NameDS.usp_GetResult, FirstChar);


NB:~ Since the Stored Procedure usp_GetResult accepts one argument, the FirstChar variable has been passed.

Step 19: Create another Form, say Form1, whose design view will be like:


Step 20: In the Report Button’s click event, add the following code:

private void btnReport_Click(object sender, EventArgs e)
    Form2 objForm = new Form2(txtName.Text.Substring(0, 1));

Step 21: Run the application. Enter some text in the Name textbox and click on OK. If the name starts with A, it will hit DB-A, if the name starts with B, DB-B will be called, else the default database will be executed.

Where can we implement this

There are a number of situations where this concept can be applied either directly or with a little bit of twisting. Some of them are listed here:

  1. If we want to display records to users based on some categories.
  2. In testing, development, and production environments.
  3. For security reasons, if we want the data to be split across tables.
  4. etc.


This is an easy approach for accessing many database servers via a single report service. Further suggestions and feedback are highly appreciated for the improvement of the article.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

You may also be interested in...


Comments and Discussions

GeneralThank you Pin
Pouriya.GH17-Apr-10 21:45
memberPouriya.GH17-Apr-10 21:45 
GeneralMy vote of 2 Pin
ZCSI6-Apr-10 4:33
memberZCSI6-Apr-10 4:33 
QuestionIs there a way to add datasource of Analysis Services intead of SQL server ? Pin
salamita10-Mar-10 12:04
membersalamita10-Mar-10 12:04 
Question? Pin
felipedr28-Sep-09 15:30
memberfelipedr28-Sep-09 15:30 
In this way you never will have a personalized report. To do that, you don't need to have Reporting Services, just use a GridView with AutoGenerateColumns.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 22 Sep 2009
Article Copyright 2009 by Niladri_Biswas
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid