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

Tagged as

SSRS – Creating Datasets – Example

, 28 Apr 2014
Rate this:
Please Sign up or sign in to vote.
CodeProject In the last article on SSRS, we have discussed about Datasets, Dataset properties, Dataset collections, Dataset types etc. You can read that articles here. In this article we will go over an example of creating Datasets in SSRS. In the last article, we have found that there are two types

In the last article on SSRS, we have discussed about Datasets, Dataset properties, Dataset collections, Dataset types etc. You can read that articles here. In this article we will go over an example of creating Datasets in SSRS.

In the last article, we have found that there are two types of Datasets – Shared Datasets & Embedded Datasets. Let’s start by importing an existing Dataset into the project by right clicking on the project and selecting Add->Existing Item.

SSRS1

This will navigate us to somewhere in the file system where a .rsd file is stored. Select the file and click Add. That adds the file into the Shared Datasets folder.

 SSRS2

SSRS3

We can also create our own Dataset by right clicking on the Shared Datasets folder and selecting the command Add New Dataset.

When we double click on the .rsd file, we will get the Dataset properties.

SSRS4

We need to specify a Data Source for the Dataset. In case of Shared Datasets, the Data Source must be a Shared Data Source. Then we need to specify a query for the Dataset. We can specify the query directly in the properties window or click on the Query Designer button to get a graphical or generic query designer. We can import the query from another Report or a saved query file as well.

In this case, the query returns 2 columns – CalenderYear & SalesAmount. These have been added as the fields in the field collection for the Dataset.

SSRS5

Now let’s look at how an Embedded Dataset is added. First of all, right click on the Dataset folder and select Add Dataset.

SSRS6

It will show the Shared Datasets which are already in the project.

SSRS7

But if you need to create an Embedded Dataset, change the option there.

SSRS8

Then select a Data Source from the drop down list. This may be either a Shared Data Source or an Embedded Data Source. After making the Data Source selection, we need to provide the query. When we click on Query Designer button, a graphical query designer window appears. We can add tables by clicking on the last button in the toolbar.

SSRS9

Alternately, we can select Edit as Text button to navigate to the generic query designer. In either way, both the designers have the run button which helps us to execute the query and see the results in the bottom portion of the designer.

SSRS10

If we have a query which is already available in an another file, we can import it by navigating to a folder which contains the file and select either a SQL script or a .rdl file. In this example, let’s use Sales.sql script and import it into the query designer.

SSRS11

SSRS12

It will be easier to read if we switch over to Edit as Text.

SSRS13

SSRS14

Click on the run button  and verify whether the query executes properly. We can also see the columns that are returned.

SSRS15

When we click OK, we can see that now the Dataset folder contains the Dataset reference as well as the individual fields. That is, at this point, the Dataset is embedded.

SSRS16

If we decide later to turn the Dataset into a Shared Dataset, just right click on the Dataset and then use the command Convert to Shared Dataset.

SSRS17

Notice the icon that appears after this. The arrow icon indicates that this is a Shared Dataset.

SSRS18

The Dataset will be added to the Shared Datasets folder in the right hand side as well.

SSRS19

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)

Share

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
Web02 | 2.8.140821.2 | Last Updated 28 Apr 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid