Create First SSRS Report and Configure Repeat header

Create First SSRS Report and Configure Repeat header

Problem Statement and Scope:

Today we will learn how to build our first SSRS Report and also how to repeat the report header in the every page. It’s simple but sometimes it creates issue when we want to repeat the report header.

Overview: 

When we develop the report we need one functionality that its header must be repeated in every page. Sometime we forgot to do, and we get QA error. 

Use Case:

I will create new report and explain how to configure the header to be repeated mean while we learn how to develop the report from scratch. We will learn step by step development.

Tools:

1. BIDS ( Business Intelligence Development Studio 2010)
2. SQL Server 2014

Step 1: Create new report solution and add new report.

Start the visual studio if you don’t have VS you can use the BDTS to develop the Report project. 

Select File -> New -> Project. Select Business Intelligence template and select “Report Server Project”. Specify the Name of the project here It’s “PracticeSSRSProject” and select the location where you want to create the project directory in Location filed.

Refer below screenshot

1. Once you are done with report project creation. It’s time to add report in the project. Before adding report, add shared data source. Data source is used to provide the connection details of the database to be used to build a report.
2. Go to Solution Explorer-> Right Click on Shared Data Source -> Select Add New Data Source Name your data source.
3. Select Microsoft SQL Server as Type -> Click on Edit Button provide the details. Test the connection and Click on Ok and we are done with adding Data Source. 
   
4. Now Add a new SSRS report in the solution. Right click on “Reports” folder -> Select “Add” -> Select “New Item”.
   Select Report From available template -> Named it like “RepeatHeader”. Click On “Add”.

Step 2: Create Data source reference and Add Data set in Report.

Data source and Data sets are important in the SSRS report. Data source is used to provide the connection details to the data base, used to get the date to build a report.

Data set is used to define your SQL logic. It can be sql query or stored procedure. It will define all the columns you want in the report and also provide you the functionality to pass parameter to sql logic, which you can use to filter the data from the data base.

1. Double Click on Report file in Solution Explored and open in Editor. Go to Report Data Section Right Click on Data Source -> Select “Add Data Source” -> you can see two option to add Data Source one is embedded second is Shared Data Source. You can also add the data source which will be related to your report. But in our case we have created shared data source and we will use that shared data source.
2. Select the data source that we have created and Click on “Ok”.
   
3. In this step we will add the dataset. Go to Report Data Section Right Click on Data Set -> Select “Add Dataset”.
   We can also create the shared data set in the report but normally we use the embedded dataset option and so our dataset will be local to report, in which it is declared. Configure the date set as follows. 
   
   
4. Select “DataSource1” that we have added in the report.Select “Text” in Query type. You can also use the stored procedure. For this demo I have used the static query.Click on “Ok”
5. I have used below query to get the data from the database.
6. SELECT TOP (2000) CONCAT(p.FirstName,' ',p.LastName) AS FullName, SalesOrderID,SubTotal FROM Sales.SalesOrderHeader SOH
   INNER JOIN Sales.Customer C
   ON SOH.CustomerID=c.CustomerID
   INNER JOIN Person.Person P
   ON p.BusinessEntityID=c.CustomerID ORDER BY c.CustomerID
   
7. Once you click on “Ok”. You can see columns in the dataset. Those will be used to build a ssrs report.
   

Step 3: Add table in report and view the report.

1. Right click on Report Body Select “insert” -> Select “Table”. You can see the table on the report body.
   
2. Drag and drop columns from dataset to the table one by one. Select all the text box in the first row and press “F4”. You can see the property pane in the right side of screen. Scroll the pane Go to Background Color Select Silver as background.
   
3. Now report is ready. Click on the “Preview” Button and you can see the report. In report click on Next page button. You cannot see the page header in second page. 
   We will resolve the issue in next step.
   

Step 4: Configure the Header to repeat in each page

1. You have to enable the Advance Mode in group so that you can see the Static fields. Static fields are those which are used for header or some static label.  Click on Small down arrow in the Column Groups Select “Advanced Mode”. Once you select that option you can see the Static fields in the Group.
   
2. Select the First Static field in the Row Group. As soon as you select the static you can see your header in the tablix is selected. That the good thing when you have lots of static fields in the report file, when you will select the static it will be highlighted in the report automatically.After selecting the Static Press “F4” to see the property of static.
   
3. We are in the last step to configure the header to be repeated in every page. You can see the property of the Static just go to RepeatOnNewPage property and set it to True. But make sure that keepWithGroup is set to After.
   
4. Save the report and click on Preview and Here you go. You can see the header is repeated on every page.
   

It’s simple to repeat the header in the new page but sometimes it’s not. So we have learnt how to develop SSRS report including how to repeat page header.

Kudos for us, we learnt how to create SSRS report and Repeat header in every page.

Please share your feedback.

All the Best.

Thanks