Intro to Report Data in SQL Server Reporting Services (SSRS)
Report data can come from multiple sources of data in your organization. Your first step in designing a report is to create data sources and datasets that represent the underlying report data. Each data source includes data connection information. Each dataset includes a query command that defines the set of fields to use as data from a data source. To visualize data from each dataset, add a data region, such as a table, matrix, chart, or map. When the report is processed, the queries run on the data source, and each data region expands as needed to display the query results for the dataset.
Note
Reporting Services integration with SharePoint is no longer available after SQL Server 2016.
Data in Report Builder
Data sources in the Report Data pane A data source appears in the Report Data pane after you create an embedded data source or add a shared data source.
Connection Dialog Box Use the Connection Dialog Box to build a connection string or to paste a connection string.
Data connection information The connection string is passed to the data extension.
Credentials Credentials are managed separately from the connection string.
Data Extension/Data Provider Connecting to the data can be through multiple data access layers.
External data sources Retrieve data from relational databases, multidimensional databases, SharePoint lists, or Web services.
Defining terms
Data connection. Also known as a data source. A data connection includes a name and connection properties that are dependent on the connection type. By design, a data connection does not include credentials. A data connection does not specify which data to retrieve from the external data source. To do that, you specify a query when you create a dataset.
Data source definition. A file that contains the XML representation of a report data source. When you publish a report, its data sources are saved on the report server or SharePoint site as data source definitions, independent of the report definition. For example, a report server administrator might update the connection string or credentials. On a native report server, the file type is .rds. On a SharePoint site, the file type is .rsds.
Connection string. A connection string is a string version of the connection properties that are needed to connect to a data source. Connection properties differ based on data connection type. For examples, see Create data connection strings - Report Builder & SSRS.
Shared data source. A data source that is available on a report server or SharePoint site to be used by multiple reports.
Embedded data source. Also known as a report-specific data source. A data source that is defined in a report and used only by that report.
Credentials. Credentials are the authentication information that must be provided to allow you access to external data.
Tips for Specifying Report Data
Use the following information to design your report data strategy.
Data sources Data sources can be published and managed independently from reports on a report server or SharePoint site. For each data source, you or the database owner can manage connection information in one place. Data source credentials are stored securely on the report server; you do not include passwords in the connection string. You can redirect a data source from a test server to a production server. You can disable a data source to suspend all reports that use it.
Datasets Datasets can be published and managed independently from reports or the shared data sources that they depend on. You or the database owner can provide optimized queries for report authors to use. When you change the query, all reports that use the shared dataset use the updated query. You can enable dataset caching to improve performance. You can schedule query caching for a specific time or use a shared schedule.
Data used by report parts Report parts can include the data that they depend on. For more information about report parts, see Report Parts in Report Designer (SSRS).
Note
Report parts are deprecated for all releases of SQL Server Reporting Services starting with SQL Server Reporting Services 2019 and all releases of Power BI Report Server starting with Power BI Report Server September 2022.
Filter data Report data can be filtered in the query or in the report. You can use datasets and query variables to create cascading parameters. With cascading parameters, users can narrow choices from thousands of selections to a more manageable number. You can filter data in a table or chart based on parameter values or other values that you specify.
Parameters Dataset query commands that include query variables automatically create matching report parameters. You can also manually create parameters. When you view a report, the report toolbar displays the parameters. Users can select values to control report data or report appearance. To customize report data for specific audiences, you can create sets of report parameters with different default values linked to the same report definition. You can also use the built-in UserID field to customize data for different audiences. For more information, see Report Parameters (Report Builder and Report Designer) and Built-in Collections in Expressions (Report Builder and SSRS).
Data alerts After you publish a report, you can create alerts based on report data. Then you receive email messages when it meets rules that you specify.
Group and aggregate data Report data can be grouped and aggregated in the query or in the report. If you aggregate values in the query, you can continue to combine values in the report within the constraints of what is meaningful. For more information, see Filter, Group, and Sort Data (Report Builder and SSRS) and Aggregate Function (Report Builder and SSRS).
Sort data Report data can be sorted in the query or in the report. In tables, you can also add an interactive sort button to let the user control the sort order.
Expression-based data Because most report properties can be expression-based, and expressions can include references to dataset fields and report parameters, you can write powerful expressions to control report data and appearance. You can provide a user the ability to control the data they see by defining parameters.
Display data from a dataset Data from a dataset is typically displayed on one or more data regions, for example, a table and a chart.
Display data from multiple datasets You can write expressions in a data region based on one dataset that look up values or aggregates in other datasets. You can include subreports in a table based on one dataset to display data from a different data source.
Use the following list to help define sources of data for a report.
Consider whether to use embedded or shared data sources and datasets. Collaborate with owners of sources of data to implement and use authentication and authorization technology that is appropriate for your organization.
Understand the software data layer architecture for your organization and the potential issues arising from data types. Understand how data extensions and data processing extensions can affect query results. Data types differ among the source of data, data providers, and the data types stored in the report definition (.rdl) file.
Understand the Reporting Services client/server architectures and tools. For example, in Report Designer, you author reports on a client machine that uses built-in data source types. When you publish a report, the data source types must be supported on the report server or SharePoint site. For more information, see Data Sources Supported by Reporting Services (SSRS).
Data sources and datasets are authored in a report and published to a report server or SharePoint site from a client authoring tool. Data sources can be created directly on the report server. After they are published, you can configure credentials and other properties on the report server. For more information, see Create data connection strings - Report Builder & SSRS and Reporting Services Tools.
The data sources you can use depend on which Reporting Services data extensions are installed. Support for data sources can differ by client authoring tool, report server version, and report server platform. For more information, see Data Sources Supported by Reporting Services (SSRS).
Data source credentials vary based on data source type and on whether you are viewing reports on your client or report server or SharePoint site. For more information, see Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode), Specify Credential and Connection Information for Report Data Sources, and credential information specific to each tool in Reporting Services Tools.
Related Tasks
Tasks related to creating data connections, adding data from external sources, datasets, and queries.
| Common Tasks | Links |
|---|---|
| Create data connections | Create data connection strings - Report Builder & SSRS |
| Create datasets and queries | Report Embedded Datasets and Shared Datasets (Report Builder and SSRS) |
| Manage data sources after they are published | Manage Report Data Sources |
| Manage shared datasets after they are published | Manage Shared Datasets |
| Create and manage data alerts | Reporting Services Data Alerts |
| Cache a shared dataset | Cache Shared Datasets (SSRS) |
| Schedule a shared dataset to preload the cache | Schedules |
| Add a data extension | Implementing a Data Processing Extension |