SSRS Shared Dataset with Parameter

ByHariharan Rajendran

SSRS Shared Dataset with Parameter

As you know, a dataset is the direct input component for SQL Server Reporting Services. These datasets are part of rdl files. Reports can use the datasets as an embedded dataset or shared dataset.

But in a real time, we mostly used embedded dataset for reports. Now, shared dataset is the mandatory option for SQL Server 2016 Reporting Services mobile reports.

There are plenty of articles available to show how to use the Shared Dataset for mobile report publisher. This article explains to you that how to create a shared dataset.

Shared dataset can be created any of the following tools.

  1. Visual Studio Data Tools
  2. Report Builder

Let us see the steps to create a shared dataset in Visual Studio Data Tools.

  1. Create a Reporting Services Project in Visual Studio
  2. Create a Shared Data source Connection under Solution Explorer
  3. Right Click on Shared dataset Folder and choose “Add New Dataset”
  4. Specify the name of the dataset and choose your data source.
  5. Under Query Type, you have 3 options. Text, Table & Stored Procedure. In our case, choose Text
  6. Pass your T-SQL Query. It can be simple or complex queries
  7. Make sure the query is correct and click Ok.
  8. Right click on your dataset and click deploy.
  9. Check the deployed dataset in datasets folder in report manager URL.

Let us discuss another scenario.

Scenario: Need to pass the parameter in shared dataset.

Follow the below steps,

  1. Add the parameter in your T-SQL Query as like below,

Where <columnname> = @<parametername>

In the above syntax, you can pass any condition as like regular T-SQL script. Instead of value pass parameter name with @ symbol.

  1. Once you added the above line, go to parameters section.
  2. You can see your parameter there as like below or you can add one new parameter using Add option.
  3. Select the check boxes based on your requirement but you need to pass one default value
  4. Deploy the dataset.

Share your comments below.

 

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply