Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Centralize and Control Data Access in SSRS 2008 R2


By:   |   Last Updated: 2010-08-16   |   Comments (3)   |   Related Tips: > Reporting Services Security

Problem

Reporting is a common and frequent requirement of any business practice and often reports are developed by business users as well as the IT department of the organization. Data Source and Data Sets are two major entities used in any report development using SQL Server Reporting Services (SSRS) whether the report is developed using Business Intelligence Development Studio (BIDS) or report builder. To keep a track and control of the data access logic implemented on a report level, it becomes very necessary to centralize access to data from the report server for better maintenance, reducing repetitive efforts and efficient use of resources. In this tip we would take a look at how we can address this using SSRS 2008 R2 and BIDS.

Solution

Use of Shared Data Source and Shared DataSets in report development can help to centralize data access using SSRS 2008 R2. Shared Datasets is a new enhancement introduced in SSRS 2008 R2 version, and we will take a look at how this can be implemented and how this can help to improve the reuse of existing logic. I will be using the term report level dataset or data source and shared dataset or datasource frequently in the discussion ahead. By report level data source or dataset, it means a dataset or data source that is embedded in the report and is available only to the report in which it is created. Also please keep in mind that this article assumes some working level of knowledge of SSRS.

To get an idea of the problem in question, follow the below steps to create an exercise which will help us get a view of the problem in question.

1) Create a new SSRS project using BIDS and name it something relevant.

2) Create a shared data source as shown in the below screenshot, by right-clicking the Shared Data Sources folder in the solution explorer pane and selecting Add New Data Source.

 Data Source and Data Sets are two major entities used in any report development using SQL Server Reporting Services (SSRS)

3) Create a shared dataset as shown in the below screenshot, by right-clicking on Shared Datasets folder and selecting Add New Dataset. Keep in mind that shared datasets can be created only from a shared data source and they have the extension of ".rsd".

Create a new SSRS project using BIDS

4) Create three different reports named SharedRep1, SharedRep2 and SharedRep3.

5) In SharedRep1 report create a dataset from the Shared Dataset as shown in the below screenshot. As you will be creating a report level dataset from the shared dataset, you will not be required to create an embedded data source at the report level.

Create three different reports named SharedRep1, SharedRep2 and SharedRep3

6) For SharedRep2 report, create a report level data source from the shared data source and a dataset embedded at report level from the same. And for SharedRep3 report, create a data source and dataset at the report level. After you have done this, the report data pane should look like the below screenshot. Sections marked as 1, 2, and 3 represent the Report Data section of reports SharedRep1, SharedRep2 and SharedRep3 respectively.

 create a report level data source from the shared data source and a dataset embedded at report level

7) Add some data on the report and deploy this solution on the report server. Navigate to the report manager and you should be able to find your reports, a folder named Data Source and Datasets. Navigate to the Shared Data Source which we have named "SharedDS" and you should be able to see options as shown in the below screenshot.

Navigate to the Shared Data Source which we have named "SharedDS"

All the reports using this data source can be controlled just by managing this single data source. One key advantage of this shared data source is that you can disable processing of all the reports using this data source just by disabling it. Also when reports are moved across environments like Development to QA, and QA to Production, it becomes very easy to manage and control data source access of all the reports using this data source.

8) Navigate to the Shared Datasets folder and click on the shared dataset "SharedDS" and you should be able to see the options as shown in the below screenshot, where Dependent Items tab is selected. This helps to make an impact analysis of reports that would be affected when any change is made to this dataset. Finally, if you have business users who need access to complex datasets created by some complex logic, the same can be shared as a shared dataset and it can be used from Report Builder too by business users.

a Shared Data Source helps to control and centralize data source connections of multiple reports using a single connection and thus making maintenance easier

So in summary, a Shared Data Source helps to control and centralize data source connections of multiple reports using a single connection and thus making maintenance easier. And Shared Datasets help to centralize and reuse dataset logic, for IT Developers and Business Users of any organization. Follow the next steps mentioned below to experiment with the results of the above exercise.

Next Steps
  • Disable the shared data source, and check which reports still continue to execute. Try changing the reports data source to the shared data source for those reports which still continue to execute.
  • Try using the shared dataset from Report Builder 3.0 for a new report.


Last Updated: 2010-08-16


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 15, 2013 - 10:36:11 AM - BJ Back To Top

I have a shared data source. Whenever we change the data source connection on the server, it does not refresh it in BIDs. When we manually change it in BIDS, it still tries to use the old data source connection.

Where is the local copy of data source stored and how to refresh it  (I deleted bin\debug\*.rds and rebuilt, still would not work).

The only thing that works is in each and every report, change it to embedded and back to shared and save it.

Thats a lot of time for each report every time data source changes.

thanks

 

BJ

 


Thursday, September 16, 2010 - 2:12:39 PM - Siddharth Mehta Back To Top
.RSD is the extension for shared dataset definition file.


Thursday, September 16, 2010 - 1:54:29 PM - adrey Back To Top
I am impressed with the informative and clearly explained tips on your site.  Is it possible that in step 4 you meant to write .rds is the extension of a shared data source or is there a different extension that I don't know about?


Learn more about SQL Server tools