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

Using Report Builder

From: SQL Server 2008 Essential Training

Video: Using Report Builder

The point of this web site is to allow us to view and interact with reports. But to do that, we need to make some first. There is a couple of ways to do it, but the one I'm going to pick is this icon here on the bar called Report Builder. You might first think this is a little web site or web application, but it actually isn't. In fact, the first time you click it, you may have to click OK once or twice, because it's going to install an application on your Desktop. This is SQL Server Report Builder, and we're in the 3.0 version of this application with SQL Server 2008 R2.

Using Report Builder

The point of this web site is to allow us to view and interact with reports. But to do that, we need to make some first. There is a couple of ways to do it, but the one I'm going to pick is this icon here on the bar called Report Builder. You might first think this is a little web site or web application, but it actually isn't. In fact, the first time you click it, you may have to click OK once or twice, because it's going to install an application on your Desktop. This is SQL Server Report Builder, and we're in the 3.0 version of this application with SQL Server 2008 R2.

This is an Office style application that you'll use to design your reports, to build your reports, and then they'll be pushed back, saved up to that web site, the Report Manager web site, so that other people can use them and view them. So when it opens up it does have a Getting Started panel that offers to step you through several different wizards. But I'm just going to dismiss that and kind of go back to basics with Report Builder. This application is open. I've got a blank report waiting to be designed here.

Down towards the bottom, I can see a message that says we're currently connected to the report server that I was just in. Yes, that looks correct. But what I have to do is two things. I have to define where it is my data is coming from. My Data Sources. So I've got a Data Sources, a folder over here. I'm going to right-click it and say Add Data Source, describe a connection to some source of data. Now the more advanced you get with Reporting Services, you're going to be likely to setup shared connections.

Connections you want to use again and again. I don't have any, so I'm going to have to select the other option to Use a connection embedded in my report. I'm going to give this data source a name. I want to use it to connect to AdventureWorksLT, so I might as well call it something like dsAdventureWorksLT. I'm just using ds as a prefix to mean data source. It is a SQL Server connection I want to talk to, though I do have a whole bunch of other options, including Azure, Parallel Data Warehouse, ODBC, SharePoint List, Hyperion Essbase and TERADATA, but we're sitting with good old SQL Server.

Then I'm going to click the Build ellipses button here to help me describe my connection, and it's actually a very simple. It's essentially the same information I have to tell it when I'm connecting to SQL Server Management Studio. What server is it? Well, it's the local server, which I'll represent with a dot. I'm going to use Windows Authentication, and as soon as I type that, I'll have a drop-down list. What database you want to talk to? Well, AdventureWorksLT. I'll click the button to test the connection. Everything succeeded. Click OK.

Click OK. So that's step one. My data source is defined, a connection to AdventureWorksLT that this report can use. But I haven't actually said what data I want. Because I'm not just going to dump the entirety of the database on to the page. So this is step two, define the dataset. Now you've got your data source. What do you want to do with it? So I'll right-click and say Add Dataset. Once again, we can give it a name. I'll just leave the default name of DataSet1. And it says, do you want to use a shared one? No, I don't.

I'm going to have to use a dataset embedded in my report, because there is nothing out there to share. Well, what connection, what data source are we using? Well, the only one we have. The AdventureWorksLT one that I just made. After that, here is where I described the query. I can write standard SQL SELECT statements here. I could check this box to select a stored procedure, which would be a great way of working with reports because we can have a nice efficient stored procedures stored in the database.

But unfortunately I don't have one, so I've got the Text option. However, I'd like to think that I'm pretty lazy about this. So rather than type it, I'm going to click this button to say Query Designer. This gives me a great builder of content that I can start to expand the different parts of my database. I've already described my connection, so it knows the options for me. I could then start drilling down into Tables and saying yes, I'd like to have information about the customer. I'd like to know their FirstName, their LastName, and their CompanyName, and their EmailAddress.

I can start to select information from other tables. Perhaps I want to know my customers information and what city and what country they're in. Well, if I do that, as we know, we've got to describe some kind of relationship between the two. Now, while I'm doing this process, I do have a Run Query button up at the top that I can run and click this. It's telling me well, the problem is the following tables aren't related to each other. I don't know what to do. I don't know how to connect them.

Well, it actually does a pretty good job of connecting them. You just have to make sure you're selecting options from tables that are connected to each other. Now, right now, Address and Customer actually aren't connected to each other. They have a join table in between, which is CustomerAddress. So all I'm going to come down-- is come in to CustomerAddress and select one from one table and one from the other. The great thing is while this Auto Detect has the blue box, meaning it's selected, the next time I hit Run Query, it's going to detect those relationships.

Yes, indeed, it's going to bring me back both the information from the Customer table and the information from the Address table where the two are joined together, which is great. We can go on a little further than that. Maybe what I want is customer information from the Customer table, address information from the Address table, and some order information from say the SalesOrderHeader table. Well, from there, the things I'm interested in are the CustomerID and let's say the TotalDue. Do another quick Run Query.

I like to do these step-by-step, where it seems to be actually connecting all of these together. One of the great things is if we're not sure about the SQL that's being generated, up here we have this Edit as Text button. I can jump into that and it actually shows me the SQL that is trying to build, which in this case has the JOIN statements. In fact, that looks pretty good. I don't want to mess with it. I'll just click the Edit as Text button again to take me back into the Designer view. There are ways I can start to actually add aggregate functions over here too.

I could decide to use my COUNTs, my MAX, my MIN, my SUM, all of that. I don't need to use any right now. This is actually just fine for me. Below this, we have applied filters that we could start to type in things like the FirstName, like a particular Value. We see a little later how we can actually parameterize this. I'm just going to leave it as is. Although, just to show one last thing, I'll show how you could jump into say the Edit as Text part and just if I wanted to, use there SQL as a starting point and then just finish it off with mine. ORDER BY TotalDue DESC. Click OK.

This is all I need to define this dataset, although I could step through renaming options for the fields, change things like collation, add my own filters, add my own parameters. I'm not going to do that right now. I'm just going to click OK. Well, then what? We have this information that could just be dragged and dropped on to our report. Now right now, that wouldn't be a very useful report if all I had was a State or a FirstName in the middle of it. There are a couple of helpers if I know that I'm going to have multiple rows of information.

I click up here on my Insert tab. I have what are called data regions. The simplest is a List, and you just drop your fields anywhere in that and it'll repeat them. But we also have the Table here. Go to Table Wizard, which is quite useful. Click that guy. It's going to say, is there a dataset already defined? Well, yes, there is, this one. So I'll select that new dataset and click Next. The next setup is asking to arrange the fields, because of course the data I brought back could be being used for any ID. I could be wanting to total things up or group them together.

The main part that we're interested in here is the values. I can drag these things across, FirstName, LastName, CompanyName, EmailAddress, City, State, and all I need is TotalDue. You'll see that there is a little drop- down here where you still can add on your choice of aggregate functions. I'm just wanting a fairly flat list, so that's okay. If I was expecting multiple customers from the same company, I could drag over CompanyName into Row groups.

To start breaking that apart into groups of rows. I don't have to here. I am just wanting something fairly flat so I'm going to click Next. It gives me a preview of what it's going to show, organizes the table. If I was grouping multiple things with aggregate functions, I'd have an option to show some totals and grand totals, but I'm not. It's just a flat list drawn from multiple tables here. I've got a choice of various styles that we could use. Let's pick Slate and click Finish.

This is our report now. I have button over here that I can click Run to take a little preview of it. It actually seems to be working okay. We're actually drawing the information. It's in descending order of TotalDue. I'm going to click the button up here to say Design, switching back into the Design mode. Maybe I think that my fonts are actually showing up a little bit large right now, and I want to affect the fonts on the top column. I'm going to do a select anywhere in here, and then you'll notice that I have a little great handle over here on the left.

I can select that and then as long as I've got the Properties Inspector open, and if I haven't, I can get that from the View part of the Ribbon here. I can select that first row. Come down to Font and I think I'm going to change that to a FontSize of 8 points. It looks good. I'll also do 8 points on the second row too. I can actually do a lot of formatting. In fact, most of what you end up doing with Report Designer is going to be in the formatting and arranging of this.

I still don't have a title for my report. So I'm going to click to add some title here. Customer Amounts perhaps. Then go up to the Ribbon and hit Save. Now what it's going to ask is, where to? And here is the default. Save up to the website, up to the Report Manager website. Well I am about to give this a name. I'm going to call this Customer Totals and click Save. Well, the question is so what? Now I can come back and design this a little later.

But what I'm going to do is just close down Report Builder. Go back to the Report Manager. I need to refresh this page, because it doesn't know that I've uploaded it yet. But when I do, I actually finally have something on the page to look at. Now there is a bunch of options in the drop-down such as Delete, Subscribe, View Report History, Manage, Security, Edit in Report Builder. But I could just click the hyperlink itself. Customer Totals. Click that and notice that within the web site, it opens it up. It retrieves that information.

It gives us options up here to print it, to even select from this, to allow us to export it in CSVs and PDFs and Word files. We've got the ability to zoom in a little bit, and just start it do some basic interaction with that report. So while we can take our reports a lot deeper than that, we can add parameters to them, we can add more formatting, this is the general process for creating and saving a new report using Report Builder 3.0.

Show transcript

This video is part of

Image for SQL Server 2008 Essential Training
SQL Server 2008 Essential Training

74 video lessons · 36360 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 2m 21s
    1. Welcome
      1m 19s
    2. Using the exercise files
      1m 2s
  2. 17m 58s
    1. SQL Server core concepts
      9m 4s
    2. SQL Server editions
      3m 8s
    3. Applications included with SQL Server
      5m 46s
  3. 26m 1s
    1. Preparing for installation
      3m 44s
    2. Creating service accounts
      2m 33s
    3. Installing SQL Server
      11m 42s
    4. Post-installation checks
      3m 9s
    5. Installing sample databases
      4m 53s
  4. 13m 35s
    1. Introduction to SQL Server Management Studio
      8m 7s
    2. Introduction to SQL Server Books Online
      3m 6s
    3. SQL Server system databases
      2m 22s
  5. 1h 26m
    1. Planning your database
      9m 39s
    2. Creating a SQL Server database
      4m 7s
    3. Creating tables
      7m 51s
    4. Data types in SQL Server
      12m 25s
    5. Defining keys
      8m 9s
    6. Creating default values
      4m 39s
    7. Creating check constraints
      2m 25s
    8. Creating unique constraints
      4m 34s
    9. Introduction to relationships and foreign keys
      9m 51s
    10. Creating relationships in SQL Server Management Studio
      8m 14s
    11. Database normalization
      11m 47s
    12. Creating computed columns
      3m 10s
  6. 23m 11s
    1. Using the SQL Server Import and Export Wizard
      3m 58s
    2. Importing Excel files into SQL Server
      6m 11s
    3. Importing CSV files into SQL Server
      5m 27s
    4. Importing Access databases into SQL Server
      7m 35s
  7. 55m 29s
    1. Introduction to Transact-SQL
      3m 43s
    2. Using SELECT statements
      7m 16s
    3. Changing the default database
      2m 21s
    4. Creating conditions in SQL
      8m 10s
    5. Sorting your output
      3m 23s
    6. Using aggregate functions
      7m 12s
    7. Finding unique values
      2m 14s
    8. Joining multiple tables together
      8m 0s
    9. Using subqueries
      9m 33s
    10. Viewing execution plans
      3m 37s
  8. 19m 36s
    1. Writing INSERT statements
      5m 47s
    2. Writing UPDATE statements
      4m 38s
    3. Writing DELETE statements
      2m 54s
    4. Using the OUTPUT clause to return inserted keys and GUIDs
      6m 17s
  9. 32m 52s
    1. Introduction to SQL functions
      6m 26s
    2. Using SQL configuration functions
      2m 14s
    3. Using string functions
      7m 26s
    4. Using date functions
      6m 27s
    5. Creating user-defined functions
      10m 19s
  10. 28m 46s
    1. Introduction to stored procedures
      4m 23s
    2. Creating stored procedures
      11m 23s
    3. Introducing transactions
      4m 23s
    4. Creating transactions
      8m 37s
  11. 16m 39s
    1. Understanding and creating indexes
      6m 32s
    2. Monitoring and rebuilding indexes
      6m 0s
    3. Monitoring database size and integrity
      4m 7s
  12. 11m 41s
    1. Creating backups
      4m 21s
    2. Creating differential backups and using backup compression
      3m 40s
    3. Restoring databases
      3m 40s
  13. 17m 40s
    1. Introduction to SQL Server security and permissions
      5m 54s
    2. Adding a Windows user to the database
      5m 7s
    3. Creating SQL Server logins and switching authentication modes
      6m 39s
  14. 36m 41s
    1. Introduction to SQL Server Reporting Services
      2m 52s
    2. Connecting to the Report Manager
      4m 29s
    3. Using Report Builder
      12m 4s
    4. Formatting values in reports
      4m 17s
    5. Adding indicators to reports
      5m 11s
    6. Adding charts to reports
      3m 54s
    7. Working with report security
      3m 54s
  15. 24m 41s
    1. Introduction to SQL Server Integration Services (SSIS)
      1m 57s
    2. Using Business Intelligence Development Studio (BIDS)
      6m 59s
    3. Creating and executing a simple SSIS package
      7m 35s
    4. Importing packages into SQL Server Management Studio
      3m 21s
    5. Scheduling jobs with SQL Server Agent
      4m 49s
  16. 31s
    1. Goodbye
      31s

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
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

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.


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.

Join now "Already a member? Log in

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 2008 Essential Training.

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
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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

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.