MSBI # 33 – SSRS # 4 – Everything and Detailed Explanation of Shared Data Source in SSRS

Hi folks ,

As I know I have given very less focused on SSRS though I know it most lets do some post on same

So counting from my last series of post on SSRS

    In this post we are going to learn everything about SSRS Shared Data Source

Topic are covering in this post are

  • What are Shared Data Source ?
  • Components of shared Data Source !
  • Description of Components of shared Data Source !
  • How to use Shared Data Source ?
  • Tips for using Shared Data Source !
  • Reference links for Study

What are Shared Data Source ?

Basic definition can be Categorized as follows 

  • A shared data source is a set of data source connection properties in SSRS
  • It can referenced by multiple reports, models, and data-driven subscriptions that run on a Reporting Services report server.
  • Shared data sources provide an easy way to manage data source properties that often change over time. If a user account or password changes, or if you move the database to a different server, you can update the connection information in one place.
  • When you run a report from a SharePoint library, connection information can be defined inside the report or in an external file that is linked to the report.
  • After you create or publish a shared data source, you can edit connection properties or delete the file if it is no longer used. Before you delete a shared data source, you should determine whether it is used by reports and report models. You can do this by viewing dependent items that reference the shared data source.
  • Although the list of dependent items tells you whether the shared data source is referenced, it does not tell you whether the item is actively used.
  • If you do not have access to the log files or if the files do not contain the information you want, consider moving the report to an inaccessible folder while you determine its actual status.
    Components of Shared Data source
  1. Name
  2. Description
  3. Connection type
  4. Connection string
  5. Credential type

Description of Components of shared Data Source

  1. Name – It’s a initial identifier as it identifies the item within the report server folder hierarchy.
  2. Description – A description that appears with the item in Report Manager when you view the contents of the folder.
  3. Connection type – This is most important and The data processing extension used with the data source. You can only use data processing extensions that are deployed on the report server
  4. Connection string – It’s a link and connection for the database
  5. Credential type – Specifies how credentials are obtained for the connection and whether they are to be used after the connection is made
  6. I will surely have one post on each of above components as topics as time permits me Smile .

How to use Shared Data Source ?

In the Solution Explorer we can find the option for creation new Shared Data Sources

Solution Explorer in SSRS is as shown

image

Right Click on above and select ‘Add New Data Source’

image

Give Name as  MyFirstSSRSSharedDataSource

image

We have following bunch of option to use as Data Source

image

Don’t need to write or remember connection string just click on edit

image 

Now this screen you are surely familiar I know and form connection string

image 

You can specify various credentials to this shared Data source as security reports is must

image

Click on ok and you will see shared data source is ready to use in your solution explore

image

You can alter this at any point with very easy GUI

So lets review all Steps once

Following are Steps to Create Shared Data Source

Step 1:Open BIDS: Click on Start –> All Programs –> SQL Server 2008 –> SQL Server Business Intelligence Development Studio

Step 2 :From the menu bar, select File –> New –> Project

Step 3 :In the Solution Explorer pane, right click on Shared Data Sources folder –> click on Add New Data Source. Which open Shared Data Source Properties dialog.

Step 4 :Enter Data Source name in Name textbox (we are taking MyFirstSSRSSharedDataSource) and shown as Microsoft SQL Server.

Step 5 :Click on Edit button to set connection string.

Step 6:Enter Server name and Database name from dropdown boxes. Finally click on Test Connection to check the connection.

Step 7:Click OK to complete and save changes. Now you can see MyFirstSSRSSharedDataSourcedata source in Solution Explorer pane

Tips for Shared Data Source

  • To view a list of reports and models that use the data source, open the Dependent Items page for the shared data source.
  • You can access this page when you open the data source in Report Manager or a SharePoint application page.
  • Note that the Dependent Items page does not show data-driven subscriptions.
  • If a shared data source is used by a subscription, the subscription will not appear in the dependent items list.
  • There is no Undo operation for deleting a shared data source.
  • However, if you accidentally delete a shared data source, you can create a new one using the same property values as the one you deleted.
  • You will have to open each report, model, and data-driven subscription to rebind the shared data source to the item that uses it, but as long as the data source properties are the same as before, the reports, models, and subscriptions will continue to function as before.

Reference link are follows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: