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