In the SQL Server Reporting Service (SSRS) report development process; we can use a beneficial feature which is called the “shared dataset”. The purpose of SSRS embedded datasets are to retrieve data from target data sources for only one report but a shared dataset can be used by multiple reports to retrieve data.
And, also using SSRS shared dataset provides some advantages that differ than the embedded datasets, such as a caching mechanism which is the essential property of shared datasets. Another flexibility of SSRS shared datasets is simple management capability. If you change any setting of shared dataset, this change will automatically be applied to all SSRS reports which used this shared dataset. It is necessary to pay attention to one point about shared dataset, though, that is if we want to use shared dataset in SSRS, we have to use a shared data source. Now, we will explore some details about SSRS shared data sources and then go into the details of shared datasets.
A data source can be defined as a connection structure which includes connection details (Database IP, Name etc.) and security credentials about target data resources. The key difference between embedded and shared source is that shared data source can be used by multiple reports and data-driven subscriptions. This type of data source provides us with management simplicity in data connection management. You can easily change database security or connection settings of reports which were applied to shared data source before. But if you use embedded data source, you need to review all reports. We can create shared data source in different ways. It can be created in the SSRS web portal, Report builder or in Visual Studio. In this article, we will use Microsoft SQL Server Data Tools for Visual Studio 2017. At the same time with Microsoft SQL Server Data Tools for Visual Studio 2017 we can develop Integration Services, Analysis Services and SSRS report server projects.
Create a Shared Data Source
Launch Visual Studio 2017, click New, click Project and then select Report Server Project
Right click the Shared Data Sources in the Solution Explorer panel
Click Edit and fill the connection string fields.
Click the Test Connection before to use data source because it helps us to test connection string settings.
After these completed steps; we can deploy the report project to the report server. But before the deployment process we have to change some settings of deployment in the report server projects.
Right click report server project and select properties.
TargetDataSetFolder: This option specifies the target dataset folder in the report server.
TargetDataSourceFolder: This option specifies the target data source folder in the report server.
TargetReportFolder: This option defines the reports folder in the report server.
Overwrite DataSources: This option defines the enable or disable option to overwrite the old data source which was placed in the same data source folder and as the same data source name. If you set this option as false and when you try to deploy to the same data source folder and also the same data source name, the deployment process returns error. The error will look like the below image.
OverwriteDataSets: This option defines the enable or disable option to overwrite old datasets which were placed in the same dataset folder and as the same data source name
TargetServerUrl: This option helps us to set target report server URL in which we will make the dataset, data source and report deployment. We can find this URL in the Web Service URL configuration settings.
TargetServerVersion: This option allows us to deploy report project to previous version of SQL Server Reporting Service. You need to be aware of all features which support your target SSRS version. Otherwise the deployment process will fail because of unsupported features and you will face this type of deployment error messages.
The reason of this error message is that the sunburst chart that is not supported by SSRS 2014 or earlier versions.
Now, we will make first deployment of our SSRS report project. Right click in report project and then select deploy.
If the SSRS deployment is a success you will get an information message which looks like this in the output screen.
After this deployment; the shared data source will be created in the SSRS report server.
Create a Shared Data Source
In the Solution Explorer, right click Shared Datasets and select Add New Dataset.
You can copy/paste the query from which you want to retrieve data from data source. In this demonstration, we will use the following query in Adventureworks2014 sample database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT soh.[SalesPersonID] ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName] ,e.[JobTitle] ,st.[Name] AS [SalesTerritory] ,soh.[SubTotal] ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [Sales].[SalesPerson] sp INNER JOIN [Sales].[SalesOrderHeader] soh ON sp.[BusinessEntityID] = soh.[SalesPersonID] INNER JOIN [Sales].[SalesTerritory] st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [HumanResources].[Employee] e ON soh.[SalesPersonID] = e.[BusinessEntityID] INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = sp.[BusinessEntityID] |
We will re-deploy the SSRS report project and shared dataset placed into path which is defined in the report project property page. Our demo dataset will be placed into Home->Datasets path because we did not change the TargetDataSetFolder property and Visual Studio make deployment to default path. In the report server web portal, we will navigate to Home->Datasets folder and click manage in the SSRS shared data source; property tab will appear.
In the Properties tab, we can edit, delete or change the folder of shared data source.
The Data Preview tab helps us to see a little part of data when we click Load Data.
The Data sources tab allow us to change dataset of shared SSRS data source.
In the Dependent Items tab we can find reports, data-driven subscription or other objects which use this SSRS shared data source for retrieve data.
The Caching tab is a significant settings tab for SSRS shared datasets. In this tab we can enable or disable the caching mechanism for shared data source and at the same time we can determine the cache expiration time. And also we can create a schedule for cache expiration. Now, we will enable the caching and analyze the details of this option.
Check Cache copies of this dataset and use them when available and Cache expires after 30 Minutes options and then click the Apply button.
Now, we will click Load Data in the Data Preview tab two times with 1-minute gap. And then connect to the SSRS ReportServer database and execute the following query.
|
1 2 3 4 |
SELECT ItemPath,TimeStart ,TimeEnd , Source,Status,ByteCount FROM [dbo].[ExecutionLog3] where ItemPath LIKE '%SharedDataSetDemo%' order by TimeEnd desc |
As you can see the above image; the first load data process connects to database and retrieves the recent result from SSRS target data source but the second report execution retrieves data from cache. The caching data is stored in ReportServerTempDB database. And the following query returns the details of cached data.
|
1 2 3 |
use ReportServerTempDB select * from ExecutionCache INNER JOIN ReportServer.dbo.Catalog Cat ON ExecutionCache.ReportID= Cat.ItemID |
In this section we will explore the parameterized shared dataset behaviors with caching. Change the dataset query text like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT soh.[SalesPersonID] ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName] ,e.[JobTitle] ,st.[Name] AS [SalesTerritory] ,soh.[SubTotal] ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [Sales].[SalesPerson] sp INNER JOIN [Sales].[SalesOrderHeader] soh ON sp.[BusinessEntityID] = soh.[SalesPersonID] INNER JOIN [Sales].[SalesTerritory] st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [HumanResources].[Employee] e ON soh.[SalesPersonID] = e.[BusinessEntityID] INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = sp.[BusinessEntityID] where st.[Name] = @SalesTerritoryName |
When click the shared dataset parameters tab, we can see the parameter which name is @SalesTerritoryName. We will re-deploy our report projects.
We will create a dummy report for testing parameter caching. Right click Reports and select New item.
Select Report object and click Add.
Right click in the DataSets folder which is located in the right side of report project. And select the SSRS shared dataset.
Click the Parameters folder and allow blank values for report parameter. Re-deploy the report project.
We will run the Dummyreport in this parameter order.
1-France
2-Northeast
3-Canada
4-France
5-france
When we will run the below query in the SSRS ReportServer database and analyze the parameterized shared data source behaviors.
|
1 2 3 4 |
SELECT top 5 ItemPath,TimeStart ,TimeEnd , Source,Status,ByteCount ,Parameters FROM [dbo].[ExecutionLog3] where ItemPath LIKE '%SharedDataSetDemo%' order by TimeEnd desc |
The above image illustrates that SSRS uses the cache only for the same parameters and also it is case-sensitive to parameters.
I want to add some notes about undocumented details of SSRS caching. In some case maybe we required to clear shared data source cache. To perform this operation, we can use this stored procedure with path parameter.
|
1 |
EXECUTE FlushReportFromCache '/Datasets/SharedDataSetDemo' |
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023