Datasets are essential when it comes to developing reports in SSRS. They represent objects which allow us to display a set of data in our report. It is important to emphasize that datasets don’t contain the data itself, but information on how to fetch a particular dataset from a database.
There are two types of datasets in SSRS:
- Embedded datasets – defined locally in a report where it can only be used
- Shared datasets – defined on project’s level and used by many reports
In this article we are going to create one embedded and one shared dataset and present data related to it in a SSRS report:
- Embedded data set will present data from Students table
- Shared dataset will present data from Departments table
When manipulating with datasets, Report Data tab need to be visible. If it’s visible by default, we are able to see the following in the screen:
Figure 1: Appearance of Report Data tab
If Report Data tab is not visible by default, we display it in the following way:
1. In VS project, open the report you want to work on, and switch to Design view
Figure 2: Design View
2. Click View menu -> Report data (the alternative to this is CTRL+ALT+D)
Figure 3: Making Report Data visible
Embedded datasets are associated only to the reports they are created in.
We get an insight of existing embedded datasets in Report Data tab.
How to create an embedded dataset
Position on Report Data tab, right click on Datasets folder and click Add dataset
Figure 4: Adding new embedded dataset
A window that will open refers to the dataset properties which we suppose to define. There are 4 tabs of dataset properties to be defined, and we will observe each tab as a separate step.
Here we define the following:
- Name of the dataset
- Type of the dataset: in this case we select the option “Use a dataset embedded in my report”
- Data source: we select the data source we are using in the project (in the example, our connection to database is defined as MSSQLSERVER, so we choose that). If we want to create new data source to be used, we do that by clicking on the button New.
- Query type: here we choose the type of the query we will use to retrieve a data from a database.
- Text: We write down the query which will retrieve the data set from the data source (in the example we have used this option)
- Table: We select the table from which the data set will be retrieved
- Stored procedure: We select stored procedure which will be used to retrieve the result data set
- If we prefer building a query with query designer, we have the option to do that by clicking on button “Query Designer…”
- If we want to import a query, we have the option to do that by clicking on button “Import…”
- If we have changed the database fields while developing dataset, we need to refresh the fields in order to pick up the last version of the fields names. This is done by clicking on button “Refresh Fields”.
- Time out: amount of time the retrieval of data from database is postponed (the default value is 0)
Figure 5: Defining Query tab information
In the example presented in Figure 5, we have defined a parameter called DepartmentID in order to explain how to deal with parameters in dataset. Important thing to mention is that we always put sign @ in front of the parameter name when using parameter in a query, since this is the sign that tells us that the value is a parameter. Assigning value to a parameter is done in Parameters tab and will be explained later.
Here we have the following options:
- Add/delete calculated dataset fields which are created based on query fields
- Change the names of the query fields, as well as the names of the dataset fields which were created from query fields (calculated fields):
- Field Name: the name of the field on a report level
- Field Source: the name of the field on a database level
In the example, field names were capitalized for the testing purposes, just to be able to see how we can have different field names at the report level.
- Sorting the dataset fields by using the arrows in order to move them up/down.
Figure 6: Defining Fields tab information
Here we can define dataset options like collation to be used, case sensitivity, access sensitivity and similar. These functionalities are not changed often. Most of the time, default values are used as presented in the Figure 7.
Figure 7: Options tab information
Here we can filter data from data source by defining the dataset filter. This means that query will retrieve the dataset from the database as it is, and then defined dataset filter will be applied to the dataset, so rows that meet the filter’s condition will not be displayed on the report.
- We have the option to add multiple filters, as well as to delete the ones we find unnecessary
- The Expression drop down list gives us the list of query fields which we can use for filtering the data set
- Another drop-down list related to Expression is there to define the type of the expression. It has five values: Text, Boolean, Date/Time, Integer and Float.
- We have the possibility to define the expression and its value as calculated fields. We do that by clicking on the button right next to expression drop-down list/value field
Figure 8: Filters tab information
In the example, we have defined to get in dataset only the rows which have the id 6 or less.
Note: While defining dataset filter, an important thing to do is to set properly the type of the expression. In our case, our expression column is Integer type, therefore a dropdown list related to Expression (4c) should be set as Integer.
So, even though a table Students has a row with ID=7, a dataset which relies on this database table will not contain this row because of the dataset filter being made.
Here we can define dataset parameters.
- We have the possibility to add and delete parameters, as well as to change their order.
- Since we have defined one parameter in a query called DepartmentID, now we have to assign a value to it. We do that by clicking on a button right next to a parameter value drop-down list
- After a new window is opened, we set the value of a parameter (in the example,we want the dataset to hold only students which have departmentid=1).
Figure 9: Parameters tab information
Figure 10: Defining parameter’s value
After we have defined the dataset parameter, we click on OK and dataset will be created.
Figure 11: Created dataset in Datasets
We can see here that the names of the dataset fields are all uppercase. That is because we have changed the names of the dataset fields in Fields tab. If we want to see if we have properly defined dataset filter and parameter, we simply display the dataset fields on a report and analyze the result:
- In the report table we choose to display the dataset fields in tablix report element
Figure 12: Assigning dataset fields to tablix report element
- We go to Preview mode to see how the report looks like
Figure 13: Data display in the report
The report works properly: when it comes to dataset filter, it truly took only first six rows, but since we had one more distinction related to department id being 1, only 3 rows are displayed.
Shared datasets are associated to many similar reports across the project.
We get an insight of existing shared datasets in Shared Datasets folder of the project.
Figure 14: Appearance of Shared Datasets folder
How to create a shared dataset
In our example, currently we have no shared datasets defined. In order to create one, we do the following:
- Check to see if we have shared data source available. If not, first we create shared data source. In order to create new shared data source, we right-click on “Shared Data Sources”, select “Add New Data Source” and define the source we want to use in the report.
Figure 15: Defining shared data source
- Create shared dataset by right-clicking on “Shared Datasets” folder, selecting “Add New Dataset” and defining the shared data source in the same way like we have defined the embedded dataset, since the creation process is the same.
Figure 16: Defining shared dataset
We have created shared dataset called DepartmentsDS as presented in the Picture15.
- If we want to use a shared dataset in a report, we need to create an embedded dataset within a report and then call a shared dataset from that embedded dataset.
Note: Direct usage of shared dataset from “Shared Datasets” folder is not possible.
Therefore, we right-click on Datasets within Report Data, click on Add New Dataset, and create an embedded dataset which will use previously created shared dataset.
Figure 17: Using shared dataset in a report
Data from the dataset will be displayed in tablix if we do the following:
- Set data source of the tablix to relate to Departments dataset
- Assign the fields values from the dataset to the table cells in the report
Figure 18: Assigning dataset to a tablix
Figure 19: Assigning dataset fields to a tablix fields
If we go to Preview of the report, we will have the result:
Figure 20: Data display in the report
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.