Click here to Skip to main content
Click here to Skip to main content

SSRS – What is meant by Data Sets?

, 7 Apr 2014
Rate this:
Please Sign up or sign in to vote.
CodeProjectIn the last article on SSRS, we have discussed about Creating Data Sources. You can read that articles 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 t

In the last article on SSRS, we have discussed about Creating Data Sources. You can read that articles 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.

DataSet1

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

DataSet2

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.

DataSet3

  • 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.

DataSet4

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

DataSet5

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.

DataSet6

  • 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.

DataSet7

 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.

DataSet8

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 a 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)


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 7 Apr 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid