This post will walk you through the process of creating and passing parameters to a drillthrough report in SQL Server Reporting Services 2008. I have got two reports:
Employee Summary - This report shows the activity of the all employees by month. The columns in the report include name of the employee, his/her practice area, hours he/she worked during that month and the revenue generated by the employee etc as shown in the figure below.
Employee Details - This report shows the activity of the employee at a more detail level. It includes additional columns such as the name of the client the employee worked for, name of the engagement(project), description of the work etc as shown in the figure below. We will setup this report as our drillthrough report. Also, this report has two parameters -Months and Employees, whose values will be passed to this report from the main report(which in this case isEmployee Summary). So basically we are obtaining the values for these two parameters from the main report.
Users will use the Employee Summary report to look at the activity of all the employees at a glance. To find out more details, users will click on the employee name to go to the drillthrough report to find out more details about each employee's activity during that month. To create a drill through report, right-click on the Employee Name column in the main report (Employee Summary)and select Text Box Properties as shown in the figure below.
The Text Box Properties dialog box appears as shown in the figure below.
Select the Action page. In the Enable as a Hyperlink; options, check the option Go to report. In the Select a report from list: drop-down, select Employee Details report. Click the Add button to add the parameters that are defined in the drillthrough report(Employee Details). Your screen will look similar to the one shown below.
To see the action, run the main report(Employee Summary), click on any employee name to see more details about the activity of that employee for that month. Lets click the first one(Gary Miller, Jan 09). The drillthrough report(Employee Details) will open up, but please note that it will only show the details for the employee "Gary Miller" for the month of January as shown in the figure below.