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
We've been using Report Builder all along to create these reports, but another option that's available is to use Report Designer, which is the name for the report layout option that is integrated into Visual Studio. Now, unlike Report Builder, you can't just open this from this Report Manager website. It actually requires installation on your machine called SQL Server Client Configuration Tools. It's a little odd to describe because you don't actually open a tool called Report Designer. If you have the SQL Server 2008 R2 tools installed as I do, you'd get to it by opening up what's called the Business Intelligence Development Studio, or BIDS.
But in SQL Server 2012, there is no BIDS; this time around it's called the SQL Server Data Tools Program. If I open that up, it's going to look like it's opening up Visual Studio, because really, it is integrated inside that. Now, as I mentioned at the start of the course, don't get caught up in thinking that you will have all sorts of new report features; you won't. Report definitions created in Report Builder and Report Designer are identical. They use the same file format, and they have the same ability. It's true that a couple of versions ago Report Builder was more limited, but that's simply not the case anymore.
The main reason you might use BIDS or this SQL Server Data Tools is that you're already an existing Visual Studio user, and/or you want to integrate what you're doing in Reporting Services, into, say, an existing source control setup or a current development project. But if you want or need it, let's compare and contrast. So here are the key differences. I have Visual Studio open, which is what opened by selecting SQL Server Data Tools. It might either be Visual Studio 2010 or 2012. If you're using SQL Server 2008, it's going to be Visual Studio 2008.
That part of it really doesn't matter. But there is a bit more formality with the initial setup. Rather than just going ahead and creating a single report like we would do in Report Builder, everything we do using BIDS or SQL Server Data Tools is contained inside a project. Now, really all that means is it will automatically group everything inside a folder, whereas with Report Builder we'd have to create that folder ourselves. So with this open--and I have the normal Visual Studio installed as well, which means I get a lot of project options here--one of them will be the Reporting Services option, and with SQL Server Data Tools there really are only two options here: either just an empty Report Server Project or Report Server Project Wizard.
I am going to select the wizard here, and I'm going to name this project. I will just call it SSDT_demo. Now, as with a Visual Studio project, I'm first creating these files locally, saving them to my own machine, and I will need to deploy these files to the Reporting Services server when I'm ready. It creates the project and opens up this Wizard panel. Now, what you'll see is that we have many of the same options that we have in Report Builder. They just often look a little different. It's even giving us a summary here. First, we're going to need to select a data source from which to retrieve the data--yup, done that all along-- design a query to execute against it--yes-- select the type of reports, select the layout, select the formatting. All right, click Next.
First option is to select the data source, and while this window might look a little different, we've been doing this all along. We'll give it a name. I will just call it Adventure Works Light (AWLT). It is Microsoft SQL Server. I've got buttons that will give me the same windows we've been using in Report Builder. So we create our data source in much the same way. I even have an option here to make this a shared data source. I am just gong to keep going with Embedded. If we go to the next page, it's going to ask us to design the query, basically design the dataset. I open up the Query Builder.
This Query Designer is a little more code- oriented than the one that we'd see in Report Builder. By default, it will show the SQL or show the tables that you're actually using. You can toggle these parts on and off if you don't have an awful lot of room. By right-clicking the blank area, I can add a table. I am using Adventure Works LT, so I will just go against Product and Product Category. It's brought those back from the database, but by looking at the fact that those foreign keys were defined, it's even figured out how they're joined together.
So while it might look fairly different, it's really doing the same kind of thing. So I am going to select Name, Color, and ListPrice from the Product table, and also the name of the Category. I can go ahead and click the exclamation mark to just preview and run this. And no big surprises here-- it's just what I would expect. Now the only thing it's going to do is whereas in Report Builder, it would automatically solve any potential naming conflicts by prefixing them with the name of the table, what it's doing with the second name column is it's just calling Expr1, so I will change that to CategoryName, just so we get more useful information showing up in the bottom here.
I am going to click OK. My Query, my Dataset is defined. Click Next. Again, same idea, just a slightly different window for it. It's asking if I want a table or a matrix. I'm going to go with a table, with tabular data. Click Next. This looks a little different here. We have options for grouping certain pages together. So, is there a particular field that will represent an individual page? Otherwise, the main things that we need are what we call groups and values in Report Builder, which is groups and details here. So I am going to say I want to group things together by category name, and I want Name, Color, and ListPrice showing up in the Details section.
We even see a little preview of where that would position it. Click Next. Very similar to the wizard in Report Builder, we just have the choices between Stepped and Block. We have the options to include Subtotals and Enable drilldown. I am actually going to leave those as the default options here. Click Next again. We have the same set of styles: Slate, Forest, Corporate, and so on. I will just select Slate and click Next. Now, the last thing is, where does this go? We're writing this as a file on our local machine, but it needs to be published somewhere.
Now, because we didn't open it up from the website, it doesn't know exactly where our Reporting Services server is. It's making a guess that I might have one installed locally. I actually do, but I want to publish this to my Reporting Services server, which is at dbserver/ReportServer. ReportServer is the most common name for the end point, for the web service of Reporting Services. And the version I'm being asked for, is it SQL Server 2008 R2 or later? Yes, it is. Down here it's going to ask me for a Deployment folder.
It sounds very technical. All it's going to end up doing is creating a folder that we will be able to see in Report Manager. So SSDT_demo, that will do. Same name as the project. Click Next, and I will give this report a name, call it SSDTReport and finish. Many of the same options. Sometimes they look a little different, sometimes they look substantially different. Now, we've got this report, and we're looking at the layout itself. The preview of this looks a little different, because it's only rendering what's being shown on the report, but the behavior will be the same when we actually run this report on the website.
We have the same Report Data section showing up on the left-hand side with our data sources and our datasets that I could drill down into. We have the same Properties pane on the right-hand side. Clicking around different parts such as the title or the table itself will show us that same information that I get in Report Builder. Because this is Visual Studio, we also have Visual Studio-specific options like Solution Explorer and Team Explorer. We have the Row Groups and Column Groups section at the bottom. The only thing that we don't have the same is the ribbon up at the top.
What we're actually looking for there is the toolbox instead. This is going to contain all those options that I could add to this report. But they're all exactly the same: Subreports, Maps, Data Bars, and Sparklines. Even as you see when we click around on the table itself, we get the same ability for grabbing the columns, for grabbing the table itself. If I right-click, everything looks pretty much identical. Opening up the Tablix Properties gives us the exact same property pages that we get in Report Builder. I am going to save this and then I click the Preview section on the tab.
It looks pretty familiar; the only thing that really does look different is the fact that it's embedded inside Visual Studio. Now, if I jump over into my Reporting Services Report Manager here--I am just going to refresh this page-- I don't see anything new, even though I am previewing that option in Visual Studio, because I really need to publish this. I need to deploy. I need to push it from my local machine up to the report server. The way I'm going to do this is go over to my Solution Explorer section and right-click my project name here and click Deploy.
I get an output window here that talks me through it, connecting to that Reporting Services server, publishing that report. I get 0 errors, 0 warnings. One has succeeded. Well, let's go find out. I jump over into Report Manager. I am going to refresh this. And what I get is this new folder, SSDT_demo. I can drill down inside it. I see the report. If I click the report, it opens up just the same way all the other reports I had created were. If I use the dropdown menu, we have the same options. I can even click Manage to get to these options here, even get to things like data sources.
In fact, if I go back to that folder, I could take this report, click the menu, and even though I'd created it using SQL Server Data Tools, I can open it up in Report Builder, because once that's published using SQL Server Data Tools, it's just a report. It's the same format we've been using all along. So I could open it up in Report Builder, make changes to it there, and even save it back.