Problem
What exactly are datasets in SSRS? How do they differ from data sources and just plain queries?
Solution
SQL Server Reporting Services (SSRS) uses the object type of a data source as the basic unit of connecting a data source to a report. (See this tip – SQL Server Reporting Services SSRS 2017 Data Sources) A data source is the connection details that allow a report designer and report consumer to define where the data lives and includes items like a table in SQL Server or an API. Continuing this concept, the dataset is the basic object that defines the structure of the data to be loaded and establishes a collection of fields.
SSRS Dataset Configuration
When configuring a dataset, we need to know the actual source of the data, i.e., the database systems, an API, or maybe even an OLAP source. This is important to know because the dataset options available to the report designer are somewhat dependent on the data source. For instance, several database systems allow for the use of a stored procedure, and some (SQL Server included) provide the ability to build the dataset via a GUI interface.
In addition to the data source, the second question is: will the dataset be shared or embedded within the report being designed? The first screenshot below displays a list of shared datasets that can be used across multiple reports in the project. The second screenshot shows an embedded dataset for use only with that report.
Query Tab
The process of creating either a shared or embedded dataset is basically the same. As displayed in the image below, when a new dataset is added to a report, the report designer must name that dataset and then select whether the dataset is embedded or shared. If the dataset is shared, a particular pre-created shared dataset is selected.
If an embedded dataset is needed, the report designer will need to name the dataset and then, most importantly, select the data source for the data. The data source types are as varied as the data and include various Microsoft products (SSAS, SQL Server, Azure, Oracle, MySQL, XML, Teradata, etc. -see SQL Server Reporting Services SSRS 2017 Data Sources). The query type (text, table, or stored procedure) depends on the data source. Note: With several data sources, these options may be grayed out as the table option is in the image below.
Another handy feature is the ability to import a SQL query into a dataset; this allows the query to be designed in an alternative tool, saved as a SQL file, and then imported directly into the dataset setup.
Also, for SQL Server and some other data sources, the query designer option can be used to establish the dataset query in a GUI selection style.
One last item on the Query tab window is the Time out option. This option is often overlooked because of its location – one must scroll to the bottom of the window to see it. It is important to set this value accordingly to prevent a dataset from running too long. A value of 0, though, indicates that there is NO time out, and thus the query can run forever, at least from SSRS’s standpoint. It should be noted that the query time out is only evaluated at 60-second intervals.
Fields Tab
The next menu option available for datasets is the Fields tab. This tab allows a report designer to complete several tasks including:
- Renaming fields that are presented to the report designer. In the image below, the right side is the name used by the query, while the name on the left is the field name shown to the user adding the fields to the report. Thus, you can change the name of the column retrieved from the database. (To be frank, I am unsure of the use case for not just changing names in the base query.)
- Reordering the fields in the field list.
- Removing fields from the field list. (Note: This only removes the field from being available to the report designer but does not remove the field from the base query. Also, if you want to “undelete” the field, the refresh button on the prior screen will add that field back to the field list.)
- Adding new fields using the Add button.
As noted in the following screenshot, the Add option provides two methods of adding fields: 1) a calculated field, or 2) a query field.
In the below example, a new field named Territory_Name_2 is added using the Name field (from the Sales.SalesTerritory) table. You can see how adding or changing names can be very helpful.
Adding a calculated field instead provides a build button (shown below). The build button provides a way to use SSRS’s built-in fields, variables, operators, and functions to “build” any needed fields.
Clicking on the build function button opens the Expression Builder screen where you can generate a whole sundry of different results, from constants to complete visual basic type formulas.
Options Tab
Moving on to the Options tab, this tab provides details that very few report designers will ever change. Collation, case, accent, and kanatype sensitivity control how the data is viewed. The width sensitivity controls the default view for the character width.
Likely the one option that would be changed is the last one, Interpret subtotals as detail rows, which controls if a subtotal row is viewed as an aggregate total or a detail row. This change is only valid if the subtotal does not use the aggregate() function.
Just as rows can be filtered at the query level, filters can also be applied at the dataset level. Filters can be as simple as the one shown below which limits the dataset to show only those products with the color “Blue”.
More complete filters, like the example below, can be established, including building complex expressions for the Expression and the value to be compared against.
Parameters Tab
The final dataset tab is for parameters. The tab allows for the mapping of parameters (variables) in the actual query itself to be mapped to parameters (or other values) created in the report. For example, in the screenshot below, the WHERE clause contains several criteria for the Order Date and the Sales Person ID. These WHERE clause criteria are compared against values that begin with @ which signifies an SSRS parameter should be used as that value. Thus, when the report runs, the values will be selected or entered and when the dataset is run, the input values are used in the dataset query.
On the actual parameters screen, the three parameters are defined and specific options are selected, including:
- Allowing null values for that parameter.
- Allowing multiple values for that parameter (in vs = in the query).
- Whether the parameter is read-only.
- Defining a default value to be used.
Additional Helpful Options
Now that the bulk of the dataset options have been reviewed, there are a few additional helpful hints that could assist a report designer.
First, if you define an embedded dataset and later decide that dataset should be a shared dataset, switching is as simple as right-clicking on the dataset and selecting Convert to Shared Dataset.
Additionally, when deploying a report, an embedded data set is automatically pushed as part of the report; to the contrary, a shared data set must be deployed as its own unit. Further, the shared dataset retains its permissions on the report server web site.
This tip covered the full process of defining an SSRS report, including selecting a data source, defining a query, determining any filters, and finally, defining any needed parameters.
Next Steps
