Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server

Setting the Data Source of Reporting Services at Runtime

Rate me:
Please Sign up or sign in to vote.
4.70/5 (7 votes)
22 Sep 2009CPOL3 min read 51.2K   400   17   4
Access multiple databases by a single SSRS report.

Introduction

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.

Background

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.

Concept

1.jpg

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

Aim

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.

Implementation

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:

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

    -- Insert statements for procedure here
     DECLARE @MAINQRY AS VARCHAR(50)
     
     SELECT @MAINQRY=
            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]'
    END
     
     
     EXEC(@MAINQRY)
END

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

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

2.jpg

Step 7:

3.jpg

Step 8: Click on Server Explorer.

4.jpg

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

5.jpg

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

6.jpg

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

7.jpg

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

8.jpg

Step 13: Add New Item -> Report.

9.jpg

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.

Image 10

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.

11.jpg

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

Image 12

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

13.jpg

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

C#
public partial class Form2 : Form
{
    string FirstChar = string.Empty;
    public Form2()
    {
        InitializeComponent();
    }

    public Form2(string _FirstChar)
    {
        InitializeComponent();
        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);

        this.reportViewer1.RefreshReport();
    }
}

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:

14.jpg

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

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

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.

Conclusion

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.

License

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



Comments and Discussions

 
GeneralThank you Pin
Pouriya Ghamary17-Apr-10 21:45
Pouriya Ghamary17-Apr-10 21:45 
GeneralMy vote of 2 Pin
ZCSI6-Apr-10 4:33
ZCSI6-Apr-10 4:33 
Title alludes to setting DB connection string at runtime when really only select across databases, IMHO.
QuestionIs there a way to add datasource of Analysis Services intead of SQL server ? Pin
salamita10-Mar-10 12:04
salamita10-Mar-10 12:04 
Question? Pin
felipedr28-Sep-09 15:30
felipedr28-Sep-09 15:30 

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.