Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.
- Understanding the elements of a report
- Grouping table regions
- Joining data from multiple tables
- Displaying data in a matrix
- Customizing report parameters
- Filtering and sorting data
- Creating charts
- Adding sparklines and data bars
- Creating at-a-glance reports with indicators
- Using Maps in Reporting Services
- Configuring report security
- Printing and exporting reports
Skill Level Advanced
In Reporting Services it's often useful to create one report that links to another. Earlier, I'd created a report that listed products based on a particular category, and I used this example to show parameters being used. Now, we just developed this as a stand- alone report, and it works great like this, but there's another thing we can do with it. We can create a new report that jumps directly into this one. And because this report accepts a parameter, we can pass a parameter directly into it. This is what's known as a drill-through action.
So to do this I'm going to create a new report. Once again, I'll just do a blank report here, add a data source pointing to AdventureWorksLT, and now I'm going to add a dataset, call this Categories. And from this, I'll drill down into the ProductCategory table and just get ProductCategoryID and Name. There is nothing special about what I'm doing up to this point. I'm creating a normal data source, normal dataset, and I'm actually going to add a fairly normal table to this report.
I'll choose the dataset we just made, and I'll actually have a very simple table that just has a list of the category names in it. I'll choose the Generic style and I'm done. There is nothing remarkable out there, so I will just drag this a little wider and run it. But what I would like to do is make all these clickable so we could jump from this listing across into that report that I created earlier. So back into Design view. I'll give this a title, just so it's a bit more presentable. What we need to do is apply an action to the names that are going to show up for these categories.
Applying an action is a bit easier than interactive sorting. It isn't quite as picky about where you put the action. I could apply it to the actual selected text or I could apply it to the text box itself. I prefer applying it to the text box, so again, the easiest way to do that is click in a blank area and then select a blank area of that cell. This right now showing name rather than selected text. I'm going to open up the Property Pages here, and one of the options I should have here is action. Right now, by default, we're not doing anything.
There is no action for this. We can choose to go to a report, go to a bookmark, go to a URL. I'm going to select Go to report, and it ask us first, well, which report? If I know the name of it directly, I could just type it in, but we also have this Browse button and I can go and look at the reports that have been saved on that report's server. I have previously saved that as Parameter_Example, so I'm going to click Open. Now I could just directly jump into that report, but if the report that I'm going to has been set up to accept parameters then I can pass parameters into it.
This one has, so I'm going to click Add. We get one parameter row. If I click the dropdown, it's going to look and say, well, it apparently takes in a parameter called ProductCategoryID, so I will take that. Now, it's asking, well, what value you're going to pass, and it's just going to give me the options that I named in my dataset. Name is what I'm showing on the page. What I actually want to pass of course is ProductCategoryID. That's it. I'm done. I click OK and I go ahead and run this. Table doesn't look any different, but what I'll find is each of these options are now clickable.
If I click Chains, we'll jump into that second report, passing in the parameter for change. I can use the Back button to jump back up, jump into bottles and cages, and see the products for that category. And what I might even want to do now on this second report, if this was the normal way I was viewing this information, I could even go and hide this parameter section here, because I don't need to show it anymore. The only thing that's missing from a user perspective on this first report is it's not really obvious that these are clickable.
And the way to fix that is simply to jump back into Design view, select that piece of text, and use the Font area here to make it look more like a link. So I'll just change the color to a dark blue and hit the Underline button, and run it again. That's a bit more obvious. Now, here I've just done this as a really simple table that just shows one column of data, but there is no restriction on doing exactly the same thing on a more complex table, or even on a matrix. It's exactly the same idea: selecting the relevant section in your design and applying the action to it to go to another report.
There is no restriction on the number of levels that you can actually drill through, because you're just jumping from one to another.