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

Using Report Designer in SQL Server Data Tools

From: SQL Server Reporting Services in Depth

Video: Using Report Designer in SQL Server Data Tools

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.

Using Report Designer in SQL Server Data Tools

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.

Show transcript

This video is part of

Image for SQL Server Reporting Services in Depth
SQL Server Reporting Services in Depth

40 video lessons · 9827 viewers

Simon Allardice
Author

 
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

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.

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