Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

Create a New Dataset using SSRS Report Builder Wizard

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 a Shared dataset in SSRS Report Builder.

For this, we are going to use the Shared Data Source that we created in our earlier SSRS posts.

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

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 the SSRS Report Builder wizard. So please select the New Dataset tab

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. It is because report Builder needs a data Source before it starts 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 open 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, the report builder 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.

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 checkmark those tables, all the fields (or Columns Names) present in both the tables will add 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. 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

Or 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, the 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: The report builder is intelligent enough to find the relationship between the selected times. But if you want to provide the relationship exclusively, 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 the dataset level. To do so, click on the filter button and add the filter. We already explained the filter concept in 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.

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 open. Here you have an option to select the local file system or the Report Server. Here, we are choosing 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

Filed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy