Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70024 Viewers
80 Video lessons · 127422 Viewers
52 Video lessons · 62364 Viewers
59 Video lessons · 48067 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.
Your file was successfully uploaded.