Creating a drill-through report

I'm building my report using the Report Designer in the SQL Server Business Intelligence Development Studio. At the moment I've got a single report which consists of 2 tables. The top table shows some summary data and the bottom table shows more detail.

What I'd like to do is design the report so that when the summary data (in the top table) appears, the user can click on a row in the table and this takes them to a new screen which shows more detail about that item, extracted from the bottom table.

Is this possible? I know it's possible to create a drill-through report if you have the high-level summary data in one report and the low-level detailed data in a separate report, but is it possible to create a drill-through report if all the data is in one single report (albeit in 2 separate tables)?

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

|||

??€?§Q? wrote:

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

The bottom table doesn't contain the same number of rows as the top one. The top table has one row for each member of staff, which consists summary data about how many pieces of work they have completed. What I want is for the user to be able to click on the name of a member of staff, and then they will be taken to a separate screen showing the full details for that particular staff-member (this data should be taken from what is currently the bottom table in the report). The bottom table contains one row for each piece of work that a particular staff-member has completed, so the number of rows would vary depending on whose name has been selected.

I hope this makes sense; it's always hard to explain what you're trying to do on a forum.

|||

Ok, so why not just create a drilldown report and enable navigation on the top report to that drilldown?

Why does the bottom table play into this at all?

|||

You could have a hidden parameter in your report got StaffID. Setup Visibility on the bottom table to hide if staffid is null, and show when it isnt. Do the opposite with the top table, if is hidden if staffid is not null, and visible if it is null.

Setup the navigation on the top table to go to the same report, passing the StaffID that was clicked on.

Since StaffID will not be null, the report will hide the top and only show the bottom.

Now, all that being said, the better way to do that is to have 2 reports, one for the list of staff and one for the details.

HtH

BobP

|||

And to open the report in the new window, you will have to use javascript in the Navigation window.

Select Jump to URL and add this code:

="javascript:void window.open('http://server/reportserver/pages/reportviewer.aspx?%2fFOLDER%2fREPORT&StaffID=" & Fields!StqaffID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

BobP