Dec 23, 2015

RowNumber in SSRS

RowNumber returns a sequential number to every row within the specified scope.

Synatax
RowNumber(Scope)

Parameter
Scope: The name of dataset, data region or group or null/nothing. Based on scope it resets the row count to 1. If scope is defined as nothing it will assign each row a unique number and will be equal to the number of rows.

Similar function in SQL Server:  Row_Number in SQL

Example
Let's create a report and see how RowNumber works. For this, we will continue on a Tabular Report we created in an earlier article.

Step 1: Create a simple Tabular Report
First Create a simple Tabular Report using above link. Reports look like this

Tabular Report

Once report is created we would generate Row Number to the report.

Step 2: Right Click on Department Column Header and add a column to its Left and name the Column Header as S.No

Add Column

S.No Column

Step 3: Right Click on S.No Data Column and click Expression.

Add Expression

Step 4: In the Expression window, set expression value as =RowNumber(Nothing) and click Ok

Set Expression

Step 5: Here we have defined the scope as Nothing, so it will generate a unique RowNumber to the result-set. Let's run the report and see the output.

Report Output

Check the output, we have a column S.No with unique Row Number (S.No) in the output.

Now let's change the scope of RowNumber to a Group.

Step 6: Right click Data Region, Then Add Group>Parent Group and add GroupName as Group

Add Group

GroupName

This will add a new column Group Name to the report.

Group Name Column

Right click and Delete the newly created Group Name Column. While deleting it will prompt two options. Select Delete columns only. Add a new Column S.No by GroupName to the right of S No Column

Add Column
Step 7: Right click the new Data Column S No By GroupName and click Expression. Set the expression to =RowNumber("GroupName")

Set Expression


Step 8: Now we have defined the scope as GroupName, so S.No by GroupName should be reset to 1 for each GroupName. Let's run the report and check the output

Report Output

Check S.No by GroupName column where RowNumber is starting with 1 for each GroupName.

Similar function in T-SQL:  Row_Number in SQL

Dec 15, 2015

Sub Report in SSRS

In this article, I am going to demonstrate step by step example of creating Sub Report in SSRS.

Sub Reports are generally used when we need to show detail data with respect to summary data.

Example
Show Detail Data of Sales with respect to its Sales Header Data
  • Main Report will display Sales Header Data
  • Sub Report will display Sales Detail Data
Prerequisites
Basic steps to create Sub Report
  • Create a Main Report
  • Create a Sub report 
  • Apply Filter Parameter on Sub Report
  • Insert Sub Report into Main Report
  • Connect reports using Parameter(s)

Now lets have step by step example

Step 1: Create a Main Report
  • Create a connection with SQL Server. We would be using shared data source to fetch the data from the database for the report we created in an earlier article. Create a Shared Data Source
  • Right Click Reports>Add New Report, Report Wizard will appear to configure the report. Click Next

Add New Report

  • Configure Data Source. Select the Shared Data Source we created in Step 1 and Click Next

Select the Data Source

  • Design Query either thru Query Builder or write your own query and Click Next.
SELECT  SalesOrderID, OrderDate, SOH.CustomerID, P.FirstName+' '+P.LastName as CustomerName
        , SOH.SubTotal, SOH.TaxAmt, SOH.Freight,  SOH.TotalDue
FROM    Sales.SalesOrderHeader SOH
inner join Sales.Customer C on C.CustomerID = SOH.CustomerID
inner join Person.Person P on P.BusinessEntityID = C.PersonID
WHERE    SOH.OrderDate = @OrderDate

Design the Query

  • Select Tabular as Report Type and click Next.

Select the Report Type

  • You can configure to display the data based on grouping but as of now just click Finish.

Design the Table

  • Here, you can select Table Style. Default Style is Slate. Click Next

Choose the Table Style

  • At this stage, we are done with configuration of Main Report. Change the Report Name to DailySalesReport and Click Finish.

Set Report Name

Main Report

  • Query Parameter OrderDate that we used in the Query in Step 4. will be created automatically. Change the Data Type to Date/Time

Add Query Parameter


Step 2: Create a Sub Report

  • Create another report that would be used as Sub Report using the same step we used to create Main Report using below Query. Name the Report to SalesDeails.
SELECT  SOD.SalesOrderID, SalesOrderDetailID, P.Name
        , P.ProductNumber, SOD.UnitPrice, SOD.OrderQty
FROM    Sales.SalesOrderHeader SOH
inner join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
inner join Production.Product P on SOD.ProductID = P.ProductID

Sub Report


Step 3: Add Filter Parameter to Sub Report

  • Add Parameter SalesOrderId to Sub Report.
  • Set Data type as Integer and Parameter visibility to Hidden as this would be automatically passed from Main Report to Sub Report.

Add Parameter

  • Right Click on Dataset>Datset Properties under Report Data

Dataset Properties

  • In Dataset Properties Windows select Filters Tab and Add Filter SalesOrderDetailId

Add Filter


Step 4: Insert Sub Report into Main Report

  • Add a group to the Data Section

Add Group

  • Merge all the cells


  • Right Click on the Group and select Insert>Subreport

Insert Sub Report

Insert Sub Report

  • Right Click SubReport Section > Sub Report Properties. In the pop window change the Name of Sub Report to SalesDetails and select DailySalesReport as below.

Name the Sub Report

Step 5: Connect reports using Parameter(s)

  • Select Parameters Tab and Add Parameter as below

Add Parameter

We are done with creating the Sub Report. Now lets run the report and see the output.