Data Sources and Data Sets in Reporting Services - SSRS
The majority of the time, you'll need to develop many reports as part of a single project. A common database is likely to be used, even though the data in each report is different. To keep things simple and organized, you may build a database connection at the project level and then utilize this same connection to produce datasets in all of your reports. In this post, we'll compare and contrast data sources and datasets.
Data Sources and Datasets
When you are building reports, it’s likely that you will want to design more than one as part of the same project. For example, you might be asked to produce a summary report that allows the user to select an item that automatically opens another report that shows further details on that item.
Although these reports will show different data, it’s highly likely that they will use the same database. To avoid duplicating the connection information and to keep things tidy and manageable, you can create a database connection at the project level and then use this same connection to create datasets in all your reports.
| Learn how to use SSRS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for a Free SSRS Training Demo! |
Data Sources vs Datasets
It is important to understand the difference between data sources and datasets.
Data Sources
A data source contains details about the database server you will be connecting to, the login to use, and the database to use.
Datasets
A dataset contains the specific query that will be used to fetch data for a particular report.
Related page: How To Create A Report Dataset Reporting Services - SSRS
Try it
1. In the Solution Explorer, right-click on Shared Data Sources and select Add New Data Source.
2. Name the data source AdventureWorks.
3. Leave the Type as Microsoft SQL Server and click Edit… to specify a connection string.
4. When the Connection Properties window opens, type the name of the SQL Server that contains the AdventureWorks2012 database. If you are doing everything on a development machine, your local SQL Server can be specified as ‘localhost’.
5. Specify your usual login information for this server and then select the AdventureWorks database from the drop-down list.
6. Click on Test Connection to confirm everything is ok, and then click OK.
7. Review the connection string that was generated for you, and then click OK again.
Note that in Solution Explorer, a new shared data source has been created.
Frequently Asked SSRS Interview Questions & Answers
List of Related Microsoft Certification Courses:
| SSIS | Power BI |
| SSAS | SQL Server |
| SCCM | SQL Server DBA |
| SharePoint | BizTalk Server |
| Team Foundation Server | BizTalk Server Administrator |
Job Support Program
Online Work Support for your on-job roles.
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
- Pay Per Hour
- Pay Per Week
- Monthly
| Name | Dates | |
|---|---|---|
| SSRS Training | Nov 19 to Dec 04 | View Details |
| SSRS Training | Nov 23 to Dec 08 | View Details |
| SSRS Training | Nov 26 to Dec 11 | View Details |
| SSRS Training | Nov 30 to Dec 15 | View Details |
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.
- SSRS Interview Questions
- SSRS Tutorial
- Adding Charts to a SSRS Report (Report Builder)
- Adding Totals and Subtotals to Reporting Services Reports - SSRS
- Different Parts of Creating Drill Down Reports in SSRS
- How to Format a Table Report In SSRS
- Getting Started with Sparklines in SQL Server Reporting Services
- SSRS Grouping data for multi-level reports
- How To Create A Report Dataset Reporting Services - SSRS
- SSRS – How to Create a Report With a Shared Data Source
- SSRS - How to Create Dynamic Reports using Parameters
- How to Deploy SSRS Report in Server
- Reporting Services Number Formatting Expressions In SSRS
- Installing SQL Server Data Tools for SQL Server 2012
- SQL Server Reporting Services SSRS Indicators
- Types of Reports in SSRS
- What is SSRS?
- Matrix Reports in SSRS
- How to Install SSRS on Windows