SSRS – Creating Datasets – Example

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

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.

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.

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.

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

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

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

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.

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.

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.

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

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

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.

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.

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

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

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