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

• MSBI # 4– SSRS # 1- What is SSRS ? – (SQL Server Reporting Services)
• MSBI # 7– SSRS # 2- Get familiar with BIDS–SSRS ? – (SQL Server Reporting Services)
• MSBI # 16 – SSRS # 3 – Some Question about SSRS for Deeper Understanding !

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

I will surely have one post on each of above components as topics as time permits me .

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

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

Give Name as  MyFirstSSRSSharedDataSource

We have following bunch of option to use as Data Source

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

 

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

 

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

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

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

• Reporting services – Using a shared data source for dynamically generated and deployed reports – Stack Overflow Creating, Modifying, and Deleting Shared Data Sources (SSRS)
• Creating, Modifying, and Deleting Shared Data Sources (SSRS)

 

Hope this helps !!

Thanks for visiting my blog !!

Hope you will like this post on Master Data Services and ready to use

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 22: Sept 01–Sept 03 Dactylonomy of Web Resource