Start learning with our library of video tutorials taught by experts. Get started

SQL Server Reporting Services in Depth

Creating a report with Report Builder


From:

SQL Server Reporting Services in Depth

with Simon Allardice
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

Video: Creating a report with Report Builder

To create a report in Reporting Services we need to use an authoring tool, and I'm going to use Report Builder first. The easiest way to get it is to open up a web browser to the Reporting Services Report Manager website. Now, for me, that's located at dbserver/Reports; it will be a different address for you. On the taskbar of this Report Manager website homepage, there is a button for Report Builder, and when you click this, it's going to download and install the Report Builder application on your machine. You may have to agree to a couple of security questions the first time you do this.

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
SQL Server Reporting Services in Depth
3h 44m Advanced Dec 13, 2012

Viewers: in countries Watching now:

Discover how to create, manage, and deliver interactive reports—not just to print, but to dynamically explore enterprise-level data—with Reporting Services in SQL Server. In this course, author Simon Allardice concentrates on using Report Builder to build and format reports from a variety of data sources, but also shows how to perform basic administration tasks such as granting user access and organizing reports in the Report Manager. Plus, learn how to add interactive sorting and filtering functionality to your reports, and create column and pie charts to better express your data.

Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.

Topics include:
  • 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
Subjects:
Developer Databases
Software:
SQL Server
Author:
Simon Allardice

Creating a report with Report Builder

To create a report in Reporting Services we need to use an authoring tool, and I'm going to use Report Builder first. The easiest way to get it is to open up a web browser to the Reporting Services Report Manager website. Now, for me, that's located at dbserver/Reports; it will be a different address for you. On the taskbar of this Report Manager website homepage, there is a button for Report Builder, and when you click this, it's going to download and install the Report Builder application on your machine. You may have to agree to a couple of security questions the first time you do this.

So what we're opening here is Report Builder 3.0, and that's whether you're using SQL Server 2008 R2 or SQL Server 2012; you just see a different splash page for SQL Server 2008 R2. And as soon as the application Report Builder opens, we get the Getting Started page, with several options for creating reports with table wizards and chart wizards and map wizards. For the first one I'm going to go as simple as possible and just pick the Blank Report option, because I want to focus just on the basic three elements of all reports: the Data Source, the Dataset, and Report Layout--where is the data, what is the data, how should it be presented? So clicking Blank Report we go into Report Builder.

I'm just going to maximize it here. Report Builder looks like a modern Microsoft Office application and shares many of the same user interface ideas. But it's a very focused application, so unlike Word or Excel or PowerPoint, we don't have to worry about a dozen different tabs along the Ribbon to learn. We really just have three: the basic Home tab that's similar to most Office applications, the Insert tab-- now, this one is it specific to Report Builder as it contains all the different elements that we can add to our report, things like charts and Sparklines, tables and matrixes, and we'll see these as we go through the course-- and then there's finally the View tab.

This one is very simple here, as it just lets us turn on or off different sections of the Report Builder application. And there are only a few sections we can affect. The part we're interested in first is this area over here on the left-hand side, the Report Data area. In here is where we say, where is our report data and want is our report data? Without providing something here, there's nothing we can do in this middle section, on this Report Layout. And the first thing we need is the data source; where is our data? So I can either right-click the Data Sources folder and say Add Data Source; I can also get that from the dropdown here in the Report Data section. Either way it does the same thing.

The first thing Report Builder will ask for is a name for this data source, and this is completely up to you. I'm going to point this to the AdventureWorks database; in fact I'll point it to the AdventureWorksLT database, so I'll just say AdventureWorks and that will work for our report. Now, below this, I'm asked if I want to use a shared connection. Basically, that means did someone else already describe how to connect to the same data we're interested in and save that information? Now, because my Reporting Services install is fresh out of the box, I don't have anything previously saved that I could use, so I'm going to click the second option, that I'm going to provide connection details just for this report.

We will explore how to save the details of the data sources we define so that we don't have to define the same ones again and again. So below this, we have the connection type. We've got a dropdown box here. It defaults to SQL Server, but there are many other options, like Oracle. We have TERADATA, Microsoft SharePoint List, even XML files. We're going to just use the default option of SQL Server for most of this course. But I need to say what SQL Server we're interested in, so down below, we have this Connection string section.

Now, if you're a developer who is used to manually writing connection strings, you can just type one in here. Or an easier way is we can click the Build button over here on the right-hand side. Now, we'll talk more about data source connections shortly, just because there are many different possibilities, but in this example I'm going fairly simple. I'm specifying three things: the actual SQL Server name, which for me is going to just be dbserver, the fact that I'm going to attempt to log on to that server using Windows Authentication, basically logging on as myself-- what that means is it won't give me anything I didn't bring to this party; it doesn't give me access to databases I wouldn't get access to normally-- and then down below we have to name the actual database that's on that SQL Server.

If I click the dropdown, we should have a few options here, and I'm going to pick the AdventureWorksLT version. So the server is dbserver. The database I'm interested in is the AdventureWorksLT version, and I'm connecting using Windows Authentication. I'm going to click the Test Connection button. It tells me Test connection succeeded. I click OK, and this is where it's actually entered in that connection string. I'm just going to leave that as is and click OK. That's our data source. So let's see, step 2 is the Dataset.

Coming over here onto the Datasets folder in our Report Data section, I'm going to right-click and say Add Dataset. This is what data specifically do we actually want. Well, let's just say I'm wanting to create a simple report detailing the current customer list, so that dataset is our customer. So I'll give this a name. I'll just call it Customers. And again, we have this option, do I want to use a shared predefined dataset? Well, I don't have any, so I'm going to define this dataset just in this report.

And the first thing it's going to ask me for is the data source, which is why we have to define the data source firs, so that we have something to connect to. I'm going to use AdventureWorks. And then come down. We have Query type, because we're really using select statements. We're querying the database. The typical kind is Text, and you could manually enter in some SQL here. You could also select a stored procedure if you had predefined stored procedures to use. I'm going to jump back over to Text, and then rather than type it manually, I'm going to click this button that says Query Designer, and this will give us a visual designer for specifying the data that we want.

Again, I'm just trying to do a simple example here, so I'm going to expand the different options that are available in this AdventureWorksLT database, which is a cut-down version of the full AdventureWorks1. I'm going to expand Customer here, and let's say I'm just interested in the name and basic contact information of our customers. So I'll say FirstName, LastName, CompanyName, EmailAddress, and Phone, and that's all I'm interested in right now. If I wanted to test this, to check it, there is a button up at the top, to say Run Query, and if I click that, we'll get a basic example of the kind of data that we're going to expect from this.

No real surprises here, but it's a good way to check that the queries that we're putting together as we're specifying our data actually work the way we'd expect them to. We'll cover more about this section a little on and also talk about joining different tables together to get more useful information, but for now I'm just going to click OK. And I can actually see that by using the Query Designer what it's actually done here is generate an SQL select statement, a very basic one, but again, it will do the trick right now. There are other options we can provide for this dataset, like changing the names of our fields if I want to.

We can provide filters and parameters, but we'll see all of that later on. I'm just going with a very basic Query. So I click OK. We have our data source; we have our dataset. That's the where and the what of our data. Now we need to say how it's going to be presented. And we're working with the Report Layout in the middle section. I could start dragging the individual fields across, but there is a much better way to do this. I'm going to jump into my Insert tab in Report Builder, which is where we specify our actual report elements, and what I'm going to look for here is just a simple table.

The options here for Table, Matrix, and List give us multiple options for repeating sets of data. The easiest one right now is the Table, and the option I'm actually going to pick here is the Table Wizard. It will just help us create this. So the table expects to be fueled with data, so it's asking for a dataset, and we've already defined that. That's why we did it in that order. So I'll choose that existing dataset and click Next. This next window is asking, what do you actually want to see under that dataset? Do you want to just see all of the fields, or do you want to use some of these to control grouping them together? Let's say, for example, I wanted to group all the customers for a particular company together.

I could use the CompanyName and drag that into the Row groups area. But again, for right now, I'm just going for a very basic report, so the one we're most interested in is the Values box over here on the right-hand side. And I'm going to drag in FirstName, then LastName, CompanyName, EmailAddress, and Phone. We'll work with Grouping a little later on. Click Next. It's detected that we don't have any groups, so these options here are grayed out. All I can do is click Next. And we can pick a different style, a basic color scheme and font setup for our layout.

I'm just going to go with the Slate option and click Finish. And we can see that's been added to the report layout. Yes, I'd want to do a little bit of rearranging to this shortly, but it will work. Now I need to prove it. And what I'm going to do is just give this a title. This is a basic customers report. And I want to prove that this works. Well, up here in the Home tab, the first icon here is Run. We're currently in Design view. I'm going to click Run and actually show this report with the fresh data inside it.

And we have a preview of our report. I can see that I could play around a little bit with some of the widths of these columns to makes this a bit more readable, but it's got the data that I'm interested in. So we have the two different views of the report; we have the view of the up-to-the- minute data, and then I can click that first icon again and go back into the Design view to rearrange this. But what I want to do is actually now save this report definition that I've created. If I look towards the bottom of the Report Builder application, I can see that we're currently connected to the Report Server at dbserver location, which is fine.

So if I come up to the Save button up here on the top, when I click Save it's asking, where do you want to save this? I could save this file to my desktop, to my own My Documents folder, but that's not all that useful. I actually want to save it into Reporting Services so that it's available and accessible by multiple people. And bear in mind what we're saving here is the report definition, not the report output, because we want to be able to run this report tomorrow, next week, next month, and see up-to-the-minute data. The definition of a report is an RDL file.

It's currently Untitled.rdl. I'll just call it My First Report.rdl. Click Save, and we're actually saving up into the Report Server. Well, what does that mean? Well, if I close Report Builder down and go back to our Report Manager website, I'm seeing nothing here because I had a fresh install of Reporting Services, But if I Refresh this page what I'll now get is my first report that I just created available here. I can just click the link to view it and we're viewing this in the browser.

I've got the options up on the top to print it or even to export and save it another format, PDF, XML file, and so on. I'm going to click the Home link just to go back up to the top. Another option I also have is this dropdown arrow that will let me do things like delete this or view report history or manage security or just edit that back in Report Builder again, if I want to make some changes to it. And that again, will open up the Report Builder application and just move us back into the Design mode for this particular report. And we can just repeat this process.

So OK, this report is about as simple as it gets, but it shows that we always need to provide the data source and the dataset before we can do anything with the layout. So we've now got the basics of Report Builder, and we can start to build on this.

There are currently no FAQs about SQL Server Reporting Services in Depth.

Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed SQL Server Reporting Services in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

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