SSRS – List Item based drill down Report

We are very well aware that tables and matrix data regions can display complex data relationships by including nested tables, matrices, charts and gauges but Lists are a little different. They support a free-layout that and can include multiple peer tables or matrices, each using data from a different dataset.

For example; sales admin wants to see the all sales person details in different regions such as France, England and USA. To meet the requirement, List Item reports are the best option to represent the sales person details.

We are going to use the existing database of Microsoft called “AdventureWorks2012” and use the existing view named “Sales.vSalesPerson” to fetch the records from the database.

We need to display the above information in our SSRS report and Report header should be like as “Country wise Sales Person Information”. Whenever user clicks on the country Sales Person Count column then it will drill and show the Sales Person Details of that particular country as shown below:

After click on the country Canada Toggled column then Report will show the following Sales Person information in that country- 

To create List Item based Drill Down report, we will follow the following steps as shown below:

1) In our existing report server project, we will add a new report as “Country wise Sales Person Information” as shown below:

2) Now, we need to create a dataset for the report as shown below:

3) Add Dataset will launch Dataset properties window. We need to set the name as CountrySalesPerson for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:       

SELECT RowId= case when (Row_Number() Over (Partition by CountryRegionName order by CountryRegionName)) % 2 =0 then 2 else 1 end, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, City, StateProvinceName, PostalCode, CountryRegionName, BusinessEntityID FROM Sales.vSalesPerson Order by CountryRegionName, RowId

4) After click on the OK button, a dataset has been added in the report data with parameters also as shown below:

5) Now right click on the report, add report header to the report and done the needful formatting ash shown below-

6) After adding the report header, we need to add the List Item to the report by the help of right click as shown below :

7) Now, we need to add the dataset to the newly added List Item as shown below:

8) Now, right click on the Row Groups of List Item and click on the Group Properties as shown below: 

9) In Group Properties window, replace details to CountryRegion in name and add Group expression as Group on CountryRegionName as shown below:

10) Click on OK button and add text box to the List Item and fill the CountryRegionName as shown below:

11) Preview the report as shown below:

12) Now we need to add the another textbox to hold the number of sales person in that specific country as shown below:

13) Now preview again the report as shown below:

14) Now, we need to matrix to the report to display the sales information into two columns as shown below:

15) Delete the row group from the report as shown below:

16) Now add the column group to the matrix in the column group window as shown below:

17) In the group properties window, do the following setting as :

18) Click OK button and add new List Item to the existing List Item as shown below:

19) Right click on the newly added List Item and add image to hold the logo of the company as shown below:

20) Now, we need to add the sales person details by adding textboxes in the newly added List item as shown below:

21) After adding all the necessary information, List Item should be like as shown below:

22) Now, we need to put this list item to our matrix as shown below:

23) Now preview the report which will be display as shown below:

24) Now, we need to add the drill down facility to the matrix. for this, set the hidden property of the matrix as True and set the toggled item as name of the count sales person textbox as shown below:

25) Now, we need to set background color of the inner List Item. For this, in the properties window of that List item, click on the background color expression as shown below:

26) Set the color condition in the expression window as shown below:

27)  Now click on the preview of the report, which will be display as shown below: 

 

After click on the country Canada Toggled column then Report will show the following Sales Person information in that country- 

 

This completes the tutorial section of the simple List Item based drill report. You can watch these steps in the video also at 

Other Drill Reports in SSRS

1) To learn the Row Drill Down Report, Click here.

2) To learn the Row Drill Through Report, Click here.

3) To learn the Column based Drill Down Report, Click here.

4) To learn the Chart based Drill Down Report, Click here.

How can you create your first sub report. Click here.