New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Creating a linked report

From: SQL Server Reporting Services in Depth

Video: Creating a linked report

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.

Creating a linked report

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.

Show transcript

This video is part of

Image for SQL Server Reporting Services in Depth
SQL Server Reporting Services in Depth

40 video lessons · 9454 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 12m 6s
    1. Welcome
      1m 9s
    2. What you need to know
      1m 36s
    3. Exploring SQL Server Reporting Services components
      4m 17s
    4. Reviewing SQL Server versions
      5m 4s
  2. 49m 41s
    1. The elements of a report
      3m 10s
    2. Creating a report with Report Builder
      12m 11s
    3. Grouping table regions on a report
      6m 1s
    4. Joining data from multiple tables
      4m 33s
    5. Formatting report elements
      4m 34s
    6. Using functions in a report
      11m 0s
    7. Displaying data in a matrix
      8m 12s
  3. 24m 9s
    1. Filtering data and adding parameters to a report
      5m 35s
    2. Customizing report parameters
      5m 4s
    3. Sorting data in a data region
      4m 7s
    4. Applying interactive sorting
      4m 57s
    5. Creating a drillthrough action to connect reports
      4m 26s
  4. 49m 57s
    1. Introduction to charting in Reporting Services
      4m 16s
    2. Creating a column chart
      8m 35s
    3. Adding a generated average to a chart
      4m 5s
    4. Creating a pie chart
      8m 19s
    5. Using sparklines
      6m 38s
    6. Adding a sparkline to a drilldown matrix
      14m 34s
    7. Adding data bars
      3m 30s
  5. 21m 48s
    1. Adding indicators to a report
      7m 52s
    2. Using and configuring gauges
      5m 30s
    3. Using maps in Reporting Services
      8m 26s
  6. 38m 14s
    1. Creating modular reports with report parts
      4m 36s
    2. Adding and updating report parts
      4m 37s
    3. Using subreports and nested regions
      4m 28s
    4. Configuring headers and footers
      3m 9s
    5. Printing and exporting reports
      3m 45s
    6. Using page breaks
      5m 37s
    7. Creating and using shared data sources
      8m 11s
    8. Creating and using shared data sets
      3m 51s
  7. 27m 20s
    1. Organizing reports in Report Manager
      3m 1s
    2. Adding users and configuring report security
      5m 24s
    3. Configuring subscriptions
      5m 13s
    4. Creating a linked report
      4m 8s
    5. Using Report Designer in SQL Server Data Tools
      9m 34s
  8. 1m 2s
    1. Goodbye
      1m 2s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.