Tutorial Gateway

Create a New Dataset using SSRS Report Builder Wizard

by

In this article we will show you, How to create a New Dataset using SSRS Report Builder Wizard Or we can say, steps involved in creating Shared dataset in SSRS Report Builder. For this we are going to use the Shared Data Source that we created in our earlier posts.

In order to create a New Dataset we have to open the report builder. To do this, Please open your report manager and click on the Report Builder button as shown in below screenshot

Create a New Dataset using SSRS Report Builder Wizard 1

Once you click on the Report Builder button, Microsoft SQL Server Report Builder will be opened with a starting page as shown in below screenshot. Using Report Builder, we can design Tables, matrix, Spark lines, Chart, Data Bars, Gauges and Map reports ranging from simple reports to complex reports.

Create a New Dataset using SSRS Report Builder Wizard 2

Create a New Dataset using SSRS Report Builder Wizard

In this example, we want to create a new dataset using SSRS Report Builder wizard so Please select the New Dataset tab as we shown below

Create a New Dataset using SSRS Report Builder Wizard 3

If you observe the below screenshot, the Create button at the bottom is Grey out. This is because, report Builder need a data Source before it start creating the dataset. So, let me Browse the existing Data Source by clicking the Browse other data Sources.. hyperlink. Remember, you can’t create a Data source at this point so always create a data source before you create the dataset.

Create a New Dataset using SSRS Report Builder Wizard 4

Once you click on the Browse other data Sources.. hyperlink, a new window will be opened to select the shared data Source from the Report Server.

Create a New Dataset using SSRS Report Builder Wizard 5

After you select the data Source only, Create button at the bottom will be enabled to create a dataset

Create a New Dataset using SSRS Report Builder Wizard 6

Once you click on the Create button, report builder will be opened with the following Window. Under the Database View section, you have an option to select the existing Tables, Views and Store procedures present in the Adventure Works DW 2014.

Create a New Dataset using SSRS Report Builder Wizard 7

In this example we will select two tables from the Adventure Works. To do so, go to Database View pane and expand the tables folder. From the below screenshot you can see that, we are selecting the [Dim Employee] table, and the [Fact Reseller Sales] table.

Create a New Dataset using SSRS Report Builder Wizard 8

Once you check mark those tables, all the fields (or Columns Names) present in both the tables will be added to the Selected Fields pane. As you can see from the below screenshot, under this section we had the Delete field button to delete the unwanted columns from the dataset. In order to delete a field, Please select the filed and then click the Delete Field button.

Create a New Dataset using SSRS Report Builder Wizard 9

0r you can expand the Table in Database View pane, and unchecked the unwanted Columns as we shown below

Create a New Dataset using SSRS Report Builder Wizard 10

Once you finish selecting the required Tables, Fields please click the Run Query button to check the data that the generated query is returning.

Create a New Dataset using SSRS Report Builder Wizard 11

If you are familiar with writing SQL query on your own then click on Edit as Text button

Create a New Dataset using SSRS Report Builder Wizard 12

Once you click on Edit as Text button, following window will be displayed to write your custom SQL query. Once you finish writing the query you can use the Execute button for the result. For now, we will stick with the query that we created in our previous step.

Create a New Dataset using SSRS Report Builder Wizard 13

Under the Selected Fields section we had the Group and Aggregation button. by clicking this button will add Group By condition to all the fields present in the Selected Fields section.

Create a New Dataset using SSRS Report Builder Wizard 14

You can see the same from the below screenshot.

Create a New Dataset using SSRS Report Builder Wizard 15

let me add the Aggregate Functions to the Numeric columns. To do so, Please select the column with numeric data and change the Aggregate from Group By to Sum.

Create a New Dataset using SSRS Report Builder Wizard 16

Use the same technique to apply the Sum function on Order Quantity, Total product Cost, Sales Amount, Tax Amount, and Unit Price

Create a New Dataset using SSRS Report Builder Wizard 17

I think I forgot to explain the Panes under the Query Designer.

  • Database Views: This pane will list out the existing Tables, Views and Store procedures present in the Data Source that you selected.
  • Selected Fields: List of fields that are available in the selected Tables, or Views, or Stored procedure.
  • Relationships: Report builder is intelligent enough to find the relationship between the selected times. But if you want to exclusively provide the relationship then use this pane. To do so, Please select the Edit Fields and select the common filed from both the tables.
  • Applied Filters: Use this pane to apply filters at dataset level. To do so, click on the filter button and add the filter. We already explained the Filters concept in our previous article so please refer Filters in SSRS article.

Create a New Dataset using SSRS Report Builder Wizard 18

Once you finish designing your query (Dataset), click the Save button as shown below.

Create a New Dataset using SSRS Report Builder Wizard 19

After you click on the Save button, a new window called Save As Dataset will be opened. Here you have an option to select the local file system, or the report Server. Here, we are selecting the Report Server

Create a New Dataset using SSRS Report Builder Wizard 20

Let me open my Report Server to show the newly created DataSet

Create a New Dataset using SSRS Report Builder Wizard 21

Thank You for Visiting Our Blog