Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

SSRS Sub Reports and deployment process-How to do it



Introduction

In this blog, we will learn about two important aspects of SSRS. We will learn how to create a sub-report in SSRS. We will also learn how to deploy an SSRS report file into the production server and manage it using the report manager tool.

Sub-reports allow us to add one or more related reports to the Data region. The advantages of Sub Reports are as follows.

  • Enable us to include numerous related reports in a single Report. 
  • SSRS sub-reports permit us to Nest or Insert One report inside the other. 
  • Permits us to pass parameters (Parameter esteems) from the fundamental report to the SSRS sub-report 
  • If you place the SSRS sub-report inside the table or lattice, at that point Sub-report will be rehashed for each column. Else, it will show once.

Once you have developed an SSRS report and tested in the development server, next is the process of deployment in the live server from where the user can view the report. SSRS reports are generally deployed in a web environment so that users can access the report from anywhere.

Over the next few pages, we will be discussing different formats of SSRS sub-reports and also about the report deployment process.

Sub Reports in SSRS

We will show the sub-report design using both Report Designer and report developer. First, we will use a Report designer. We will start with a simple sub-report example. In this context, it may be said that Sub-reports and main reports are two SSRS reports which are clubbed together. These reports can function separately as independent reports. We will also be using the Adventureworks database for all the SSRS examples. The tables that we will be using are SalesOrderHeader and SalesOrderDetails.

Step 1

Open Visual Studio and select new project and select a Report Server Project.

Click ok and a new project will be created.

Step 2

Right-click on the Reports tab in the solution explorer and click on Add New Report.

The report wizard screens will Popup. Follow the steps of report creation and create the main report .We will be using the following SQL query for the main report.

select SalesOrderID,OrderDate,CustomerID,PurchaseOrderNumber,TaxAmt from [Sales].[SalesOrderHeader] where [email protected]

Step 3

The final report design will look like below

The final output of the report will be as below.

Step 4

Similarly we will create the report that we will use as a sub-report in the same way. For the sub report we will be using the following SQL query.

select SalesOrderID,ProductID,OrderQty,UnitPrice,LineTotal from [Sales].[SalesOrderDetail] where [email protected]

The @salesid is a parameter which would receive value from outside. When we create the sub-report this will be the connection between the main report and the sub report.

The second report design will look like below.

 

And the output looks like

Step 5

As discussed earlier both the main report and the sub report exists as a separate report in the SSRS report project.

Step 6

Next we will create the Sub Report. Open report1.Right click and click on insert and select Sub Report.

Step 6

A blank sub report container is inserted into the report.

Step 7

Right click on the Sub report container and select sub report properties.

Step 8

The sub report properties window pops up. Set the name of the report to be used as sub report. Here we will choose Report 2.

Step 9

Next we will join the parameter of the main and sub report.

Then we click on Ok.

Step 10

The report design window will look like below.

Step 11

Now if we run the report, the output will look like below.

We will next use the report builder tool to replicate the sub report development process.

Step 1

We will use the same two queries mentioned above to create the two report files using report builder. The files names will be given as Report1 and Report2.Report1 in builder will look like below.

 

And running the report the output will be like below

The report need to be deployed in the Report Server. We will discuss about the process in the later part of this blog.

Similarly Report2 in builder will look like below.

The output will look like below.

This also needs to be uploaded in the Report server.

Step 2

Open Report1.Go to insert tab and click on sub report. Drag the sub report box on the report body.

Step 3

Right click on the sub report box and click on sub report properties.

Step 4

The Sub-report Properties window comes up. Browse and selects the report name (Report1) that you want to use as sub-report from the report server.

Click ok.

Step 5

The final output will be like below.

Next we will see how we can add multiple sub report in one report.

Step 1

We will create another report3 in visual studio report designer. We will use the following query for the purpose.

select ProductID,Name from [Production].[Product] where ProductID in (@prodid)

The report in designer will look like below.

The output of the report looks like below.

Step 2

We will now merge the second report with the newly created report. We insert a sub report box in the second report like below.

Step 3

Right click on the sub report box and add report3. 

Also tag the product id parameter and the report column together.

Click ok.

Step 3

Finally, we run the report. The output of the report will look like below.

Here the first main report is connected to subreport1 through the parameter sales id. The second report is connected with Report 3 through the parameter Productid.

So when we pass Salesid as the parameter for the main report, the main report evokes the first sub-report and subsequently the first sub-report evokes the second sub report using the productid.

We can repeat the same process using Report builder. The only thing that we need to keep in mind is that we have to upload the three reports into the report server before invoking the sub-report feature.

Deployment process for SSRS Reports

Next we will learn how to deploy a SSRS report.

Step 1

First we need to configure the reporting server properties. Right click on the SSRS project and click on properties.

Step 2

The report property page comes up.

Here we need to set the following properties.

  • TargetDatasetFolder: Please specify the folder name where you want to deploy the Shared Datasets present in this project. By Default, Datasets is the name, but you can change as per your requirement.
  • TargetDataSourceFolder: Please specify the folder name where you want to deploy the Sources present in this project. By Default, Data Sources is the name, but change as need.
  • TargetReportFolder: Please specify the folder name where you want to deploy the SSRS Reports we created in this project. By default, the Project name will assign to this. You can alter as per your requirement.
  • TargetReportPartFolder: Please provide the folder name where you want to deploy the Report Parts present in this SSRS project (If any). By Default, Report parts are the name.

TargetServerURL: This property is used to provide the Target URL where we want to deploy the SSRS project. For instance, If you’re going to deploy to a report server installed in share point, then provide the site URL.

Once the configuration is done click on Ok.

Step 3

Right click on the report project and click on deploy.

Step 4

The output window comes up.

This allows you to check the details of the deployed report.

Step 5

Next we can open the report manager and check the deployed SSRS project.

Summary

The blog summarizes the process of the creation of sub reports in both report designer and report builder. It also shows the process of deployment of SSRS report. We have touched upon these subjects in brief. To know more about these topics further study or professional training on these subjects is required.

SQL Tutorial Overview


    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

7 days 14 Jul 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

3 days 10 Jul 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

9 days 16 Jul 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

10 days 17 Jul 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

8 days 15 Jul 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

3 days 10 Jul 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

7 days 14 Jul 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 15 Jul 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

16 days 23 Jul 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

7 days 14 Jul 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

10 days 17 Jul 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

6 days 13 Jul 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews