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.
how do i pass multi value parameters in the drillthough.
ReplyDeleteNice one. Easy to understand
ReplyDeleteHello
ReplyDeleteThis is a fantastic solution if you want to open the report in the same window; however, my situation requires opening the drill-through report in a seperate window (with 4 parameters) not prompting me to enter parameter. I have spent hours and no luck so far.
Is it possible for you to to do a step-by-step demo just as you have done with the first one above?
Thanks and look forward hearing from you.
Hello Gambian, it is possible to open a drill-through report in a new window using some java script code. Please follow the article - http://www.mssqltips.com/sqlservertip/1283/display-reporting-services-drill-through-reports-in-new-browser/
DeleteHope that helps.
Thanks Faisal. I have already done that; however, two things happened:
Delete1 - by passing one parameter like this [="javascript:void(window.open('Server/Report drill-through report name&rs:Command=Render&ReportDate="+Parameters!ReportDate.Value+"'))"], the drill-through opened, but parameter fields are still active, meaning that it's asking to fill the fields
2- by passing two parameters like this [="javascript:void(window.open('Server/Report drill-through report name&rs:Command=Render&ReportDate="+Parameters!ReportDate.Value+"+Community="+Parameters!Community.Value+"'))"], the drill-through link is not active at all.
I have replaced the (+) signs with (&), but no luck
By the way, I am using Visual Studio 2012.
Thank you