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
One of the options you might have seen in Report Manager when using this dropdown menu is to create a linked report. A linked report saves us from the situation where we might have to define exactly the same report several times over only to show a little bit of different data. So, say I created a great complex report filtered to just the data from the southwest region, and then someone looks at it and says they want exactly the same thing, but for the northeast region. I don't have to create the report again. I just create a linked report and point it to slightly different data.
So, let's go ahead and make one. The starting point is this. I've just created for myself a simple report called WashingtonPeople. It shows the Salespeople data filtered down to Washington. Okay, I have done it as something that's very easy to understand, but let's imagine that this was a lot more complex--historical data, charts, maps, matrices and sparklines--a work of genius report, but just filtered to Washington. And then somebody asks, "Can we get exactly this, but for Oregon instead?" Well, instead of making it a new report and recreating everything, I am going to make a linked report.
Now linked reports will use the same data source and the same data set, the same definition; what's different about them is typically just a parameter. I'm going to go in and edit this original report in Report Builder, because there is a little bit of prep work I want to do if I'm creating a linked report. Right now there's nothing remarkable about this. I am using a shared data source and I have a simple dataset in here. If I look at the dataset, what I'll actually see is that I've got a hardcoded filter in there, where the StateProvinceName is Washington.
And this is about the only thing that I need to change in order to prep this as a linked report because almost always the first step will be have the original report use a parameter instead of hardcoded filter information. So, I'm going to select the checkbox here to use a parameter. Now, because I already have a default value, we should see absolutely no change on this. It'll add a parameter into my report definition, but that should be set up with the default value here of Washington. We are fine. So, if I were to save this and then go back in and run it again, we should see exactly the same thing.
Now right now, because of the default way that parameters appear, we are having the parameter show up at the top. I actually don't want that, so that will be one more quick change. I'll go directly into the parameter and set the parameter visibility to Hidden. Save it again, and run it again. It looks good, looks the same as it was a minute or two ago. Now let's create a linked report from this. So we are basing it on this WashingtonPeople Report.
Now, what I'm doing here is not copying the report; in fact I won't be able to edit this one in Report Builder. I'll click OK to save it. Now, right now it's exactly the same as the first one, because I need to make one change to this. If I go back to the Home page, I should see it showing up here, and it's got a slightly different icon with the chains to demonstrate that this is a linked report. If I go into the settings of it and click Manage, I have got most of the usual options, although things like Data Source are hidden because when it's a linked report, you're not allowed to change that. That's all in the original report.
But what I do have here is Parameters. This I can change, and this is specific to the linked report. So, it's already understanding that the default parameter was Washington. I'm going to change that to Oregon, click Apply, and we are done. Now, if I open my WashingtonPeople version, I'll see the Salespeople in Washington. If I open my OregonPeople report, I'll see the Salesperson in Oregon. If I try and open the linked report in Report Builder, it will start to open Report Builder and then complain about it. Not the friendliest error message in the world, but effectively saying, you're not allowed to edit a linked report; you have to edit the original one.
So, there is no point in me having Report Builder. The great thing is is that any change I made to the original report will immediately affect any linked report I have made based on it, so it's a great way to reuse report definitions.