SSRS – What is Meant by Data Sets?

CodeProjectIn the last article on SSRS, we have discussed about Creating Data Sources. You can read that article here. In this article, we will go over Data Sets in SSRS.

After creating the Data Source definition, the next step in Report development process is to create a Data Set. Before going to that, let’s quickly look at what exactly a Data Set is.

What is Meant by a Data Set?

• In simple terms, Data Set is the query used to get data for the Report.
• It contains a collection of fields based on the data columns.

We can type the query string directly into a generic query designer.

We can execute the query to confirm whether it is valid and will get the needed results.

Data Set Properties

• Data Source Association

  A Data Set has properties besides the query and the fields. In fact, before creating the query, we need to specify the data source which we want to use.

  

• Query Type

  We also need to specify the Query Type property. The default is Text, which means we are supplying a SQL query to select data from the data source.

  

  For some data sources, Table Query Type may be appropriate. It is an option to get all the data from a target database table.

  

  We can specify the Stored Procedure Query Type as well by selecting a stored procedure from the list of procedures available in the data source.

  

• Time Out

  Another important property of a Data Set is the Time Out property. By default, it is set to zero which means the query will wait indefinitely for the data source to return data. If we want to put time limit on the query and the Report has to be failed if data source is taking too long to retrieve data, put that number of seconds as the time out value.

  

Data Set Collections

In addition to the properties, Data Set contains several collections.

• Fields

  The most important collections are the Fields collections. They are actually the columns retrieved by the query. They can also contain Calculated fields which are the fields based on an expression. We can add fields to the collection based on an expression and can use the result in the Report just like any other fields.

• Filters

  Another collection in the Data Set is the Filters collection which doesn’t always exist in every Data Set just like Fields collection. It excludes rows after query execution.

• Parameters

  The third collection in the Data Set is the Parameter collection. Every Data Set may not contain Parameters collection. But if we have parameters in the SQL query, we can find them in Data Set as well.

Shared Data Sets

Data Sets can be created in 2 different ways. One way is to embed the Data Set directly into the Report definition and the other way is to configure it as Shared Data Set. If we need to use the same query repeatedly in different Reports, Shared Data Sets are the best possible way.

Shared Data Set is a new feature in SQL Server  2008 R2. While creating a Shared Data Set, we are actually creating a .rsd file that has an XML structure.

We will look at a detailed example on how to create a Data Set using Business Intelligence Development Studio(BIDS) in the next article.

Reference

• Arun Ramachandran (http://BestTEchnologyBlog.Com)