Different Parts of Creating Drill Down Reports in SSRS

Creating a drill-down report – Part 1

Drill-drown reports are one way of linking two or more reports so that one report shows summary information and a second report is linked from it to show finer-grained detail. For example, your first report might show sales for each category of products, and the user could click on a category name to see a report of sales for each product in that category.

Try it for this section, the detail report is created with two parameters. These parameters are determined by the query and will be displayed at the top of the screen when you preview the report. Later on, these parameters will be supplied by the summary report, bypassing them over to the detail report automatically when the user makes a selection.

If you would like to become an SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

Try it

1. In Solution Explorer, right-click on Reports and add a new report (Add > New Item > Report. Again, avoid using the Report Wizard for this)

2. Name the report Employee Contribution and click OK

3. Add a Report Data Source called AdventureWorks and configure it to use a shared data source reference, referencing the project’s shared data source.

Related Page: SSRS – How To Create Dynamic Reports Using Parameters

4. Still in the Report Data panel, Add a dataset called EmployeeContribution, embed it in the report, set the data source and set the query text as:

SELECT
p.FirstName + p.LastName AS FullName,
SUM(soh.TotalDue) AS TotalDue
FROM
Sales.SalesOrderHeader soh
INNER JOIN Person.Person p
ON p.BusinessEntityID = soh.SalesPersonID
WHERE
YEAR(OrderDate) = @Year
AND
MONTH(OrderDate) = @Month
GROUP BY
soh.SalesPersonID,
p.FirstName + p.LastName
ORDER BY
TotalDue DESC

5. Click OK.

6. Open the toolbox and drag a table onto the report design surface and then from the dataset, drag the FullName field into the first column’s data cell, and the TotalDue field in the second column’s data cell. Delete the third, empty column.

7. Preview the report and type in the parameters 2006 for Year, and 1 for Month.

How did the report know to ask for the year and month? The query contains @Year and @Month as parameters, so SSRS converted these into report parameters for you automatically.

Creating drill down reports – Part 2

Drill-drown reports are one way of linking two or more reports, so that one report shows summary information and a second report is linked from it to show finer grained detail. For example, your first report might show sales for each category of products, and the user could click on a category name to see a report of sales for each product in that category.

In the Try it for this section, the detail report is created with two parameters. These parameters are determined by the query, and will be displayed at the top of the screen when you preview the report. Later on, these parameters will be supplied by the summary report, by passing them over to the detail report automatically when the user makes a selection.

When specifying parameters and their values, remember that the Name is the parameter expected by the report you are going to run, and Value is the value from the current (parent) report. If the value starts with @ then it is a parameter from the current report as opposed to a value from the report’s dataset.

Related Page: How To Create Drill Down Report In SSRS 2008

Try it

1. In the Summary Sales report, in Design view, right-click on the Total data cell and choose Text Box Properties…

2. Open the Action page and select Go to report.

3. Specify the Employee Contribution report from the drop down list.

4. Specify two parameters by clicking the Add button twice.

5. In the first paramter row, name the parameter Year and then click the first fx button on that row to open the Expression editor.

6. The value required is the Year parameter from the Sales Summary report. This will be passed to the Employee Contribution report. This parameter can be typed in or you can select Parameters from the Category list and then double-click on Year in the Values list. Whichever method you use, ensure that the expression displayed in the upper box reads: =Parameters!Year.Value Click OK

7. In the second row of parameters, you need to specify the Month.

8. Click OK to return to the report.

9. Still with the Total data cell selected, use the toolbar to underline the text. This will tell the user that the cell is clickable.

10. Preview the Summary Sales report, select a year and click on a Total value to see which employees contributed sales to the monthly total.

To get back to the parent report when you’re previewing a child report, you can rightclick on the report and choose Back.

Creating drill down reports – Part 3

With drill-down reports, it is helpful to the user if they can get some confirmation of what they are looking at. For example, if a number of detail reports are opened one after another, it would be very easy to lose track of exactly what is being shown on screen.

It is quite simple to create a title that works dynamically by using a parameter or other value in the report. In the Try it for this section, the title is constructed as an expression made up of some fixed text: “Employee contribution for: “ and a dynamic part derived from the parameter that was passed from the summary report.

One thing to remember when you combine parts like this is that spaces will not be automatically inserted between them, so you will have to manually specify these.

Frequently Asked SSRS Interview Questions & Answers

Expression examples

An expression that combines two words with a space between them:
= “Hello” + “ “ + “world”

A more compact way of achieving the same result:
= “Hello “ + “world”
(note the space after ‘Hello’)

An expression that combines fixed text and a parameter:
= “The selected year is: “ + Parameters!Year.value

Try it

1. Open the Employee Contribution report in Design view

2. Add a page header using the toolbar, or use the menus: Report > Add page header.

3. Drag a text box from the toolbox on to the page header and resize it so that it stretches the full width of the report.

4. Right-click on the text box and choose Text Box Properties from the menu.

5. Open the expression editor for the Value of the text box (click the fx button on the General page) and type in, or construct using the lists below the main expression window, the expression:
= “Employee contribution for: ” +
MonthName(Parameters!Month.Value) + ” ” +
Parameters!Year.Value

6. Once you have the expression (it’s ok to split it over a couple of lines) click OK to return to Text Box Properties, and then OK again to return to the report design screen.

7. Preview the report to check that the Employee Contribution report now has a title that confirms the selected year.

8. Back in Design view, make sure that the Employee Contribution report is open, and expand the Parameters folder in the Report Data panel. Right-click on Year and in Parameter Properties, set the parameter’s visibility to Hidden. As the parameter is being supplied by the parent Summary Sales report, the text box is no longer required in this child report.

9. Repeat the last step to set the Month parameter to hidden as well.

10. Switch back to the Summary Sales report using the tabs above the design surface and preview this report to confirm that the child report still works without the parameter text boxes.

Explore SSRS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator