Create a New Report in SSRS Report Builder Wizard

In this article, we will show you how to create a New Report in SSRS Report Builder Wizard. Or we can say steps involved in creating New Table or Matrix report using SSRS Report Builder.

For this, we are going to use the Stored Procedure that we created in our previous Posts. Below screenshot will show you the data that we are going to use in this stored procedure:

TIP: If you want to create a custom Stored Procedure, then I suggest you refer SQL Stored Procedure article that we mentioned in SQL Server Tutorial.

Create a New report in SSRS Report Builder Wizard 0

Now, Let me write SQL query to create a Stored Procedure on Adventure Works DW:

USE [AdventureWorksDW2014]
GO

IF OBJECT_ID ( 'SP_ProductSales', 'P' ) IS NOT NULL   
    DROP PROCEDURE SP_ProductSales;  
GO
 
CREATE PROCEDURE [dbo].[SP_ProductSales]
AS
BEGIN
      SET NOCOUNT ON;
	  SELECT Prod.Color, 
             Prod.EnglishProductName AS ProductName, 
             Fact.SalesAmount,
	         Fact.OrderQuantity, 
             Fact.TotalProductCost, 
             Fact.TaxAmt
       FROM DimProduct AS Prod 
         INNER JOIN FactInternetSales AS Fact 
            ON Prod.ProductKey = Fact.ProductKey 
END
GO

To create a New SSRS Report, 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 report in SSRS Report Builder Wizard 1

Once you click on the Report Builder button, SQL Server Report Builder will be opened with a starting page, as shown in the below screenshot.

Create a New report in SSRS Report Builder Wizard 2

Create a New report in SSRS Report Builder Wizard

In this example, we want to create a new report in SSRS Report Builder wizard so Please select the New Report tab, and then select the Table or Matrix Wizard option as we have shown below

Create a New report in SSRS Report Builder Wizard 3

After you select the Table or Matrix Wizard option, a new window called New Table or Matrix will open, as we shown below. If you had any existing data sets or Shared dataset in your Report Server, then use the first option.

For now, we are selecting the second option. I suggest you refer the Create a New dataset in Report Builder article to understand the steps involved in creating the shared dataset.

Create a New report in SSRS Report Builder Wizard 4

Next, we have to create a Data Source (Connection to Database). If you had any existing Data Sources or Shared Data Source, then you can select them by clicking the Browse button. Here we will create a new Data Source.

Create a New report in SSRS Report Builder Wizard 5

Once you click on the New button, the following window will be opened to write the connection string

Create a New report in SSRS Report Builder Wizard 6

We changed the Data Source name to AdventureWorksDW, and also hardcoded the connection string.

If you find any difficulty in writing the connection string, click on the Build button, and Report Builder will create a Connection String for you.

Create a New report in SSRS Report Builder Wizard 7

Please select the newly created Data Source and click the next button.

Create a New report in SSRS Report Builder Wizard 8

Design a Query window: Use this window to create a dataset for your report. We explained each step involved in this query designer to Create a New dataset in the report Builder article. For now, we are selecting the Stored procedure that we created earlier.

Create a New report in SSRS Report Builder Wizard 9

Run the query to check the data returned by the Store procedure

Create a New report in SSRS Report Builder Wizard 10

Next, we have four sections:

  • Available Fields: List of all the available columns in our dataset.
  • Row groups: If you want to use any Column to use as the Row group then add that column name to this section
  • Values: All the column names that you want to use in the table will place in this section.
  • Column groups: If you want to use any Column to use as Column group then add that column name to this section
Create a New report in SSRS Report Builder Wizard 11

We are adding the Color to Row Group, and remaining Columns to Values. I suggest you to refer Add Row Groups and Column Groups article to understand the Grouping functionality.

Create a New report in SSRS Report Builder Wizard 12

Choose the layout: For now, we are leaving the default settings.

  • Show subtotals and Grand Total: If you want to show the subtotal and the grand total in your report, then select this option.
  • Expand/Collapse groups: Enable or disable the Drill Down functionality.
Create a New report in SSRS Report Builder Wizard 13

Please choose the layout from the existing templates.

Create a New report in SSRS Report Builder Wizard 14

Now you can see the auto-generated report.

Create a New report in SSRS Report Builder Wizard 15

Remove the extra space around the table report, and click the Run button

Create a New report in SSRS Report Builder Wizard 16

Once you click the Run button, the following report will generate. From the below screenshot, you can see that the report is showing the Drill Down Functionality. We already explained this concept in the Drill Down Reports article.

Create a New report in SSRS Report Builder Wizard 17

Try to expand every + button beside the color.

Create a New report in SSRS Report Builder Wizard 18

Let me change the report name to Table report using Report Builder and click on the Save button to save the report in the report server.

Create a New report in SSRS Report Builder Wizard 19

After you click on the Save button, a new window called Save As Report 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 report in SSRS Report Builder Wizard 20

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

Create a New report in SSRS Report Builder Wizard 21

If you want to see the report preview or Run the report, then you don’t have to access the Report Builder or BIDS. Just click on the report will run the report. Let me select the report that we saved previously and click on it

Create a New report in SSRS Report Builder Wizard 22

Now you can see the report inside the Report Server

Create a New report in SSRS Report Builder Wizard 23