Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Discover how to create, manage, and deliver interactive reports—not just to print, but to dynamically explore enterprise-level data—with Reporting Services in SQL Server. In this course, author Simon Allardice concentrates on using Report Builder to build and format reports from a variety of data sources, but also shows how to perform basic administration tasks such as granting user access and organizing reports in the Report Manager. Plus, learn how to add interactive sorting and filtering functionality to your reports, and create column and pie charts to better express your data.
Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.
Data regions in Reporting Services, these group table, regular tables and matrices, they're easy to create, and it can seem like a natural fit that we would just map data from our tables in the database into, say, table regions in the report. But Reporting Services gives us other ways to display the same data using charts. Charts in Reporting Services are data regions, like tables and matrices, so they are fueled by data sets in much the same way, but they represent that data visually instead.
In Report Builder, in the Insert tab you'll find both a Chart Wizard and an Insert Chart option. The Chart Wizard provides the most common chart types, like bar charts, pie chart, column charts. The Insert Chart option provides those, and a few more, including the less typical scatter shape and polar charts, and there are multiple versions of each chart type. We've got 2D and 3D, stacked and regular, and so on. But your choice here should not be about the visual look of the chart that you like; it's what kind of data do you have to show and what meaning do you want to take from that data? We have line charts, great for showing growth or decline over a period of time, and they are good for comparison as we can chart multiple lines and compare them to each other.
But these are not so good for figuring out, say, what percentage of the whole does one of these lines represents? We have bar and column charts. These are great for direct comparisons, very easy to scan and see which value is bigger than the value next to it. We have the classic pie charts. These are great for representing percentages of a whole. It's easy to look at one slice and get a feel for what that represents in the bigger picture. But these aren't so good for direct comparison between data points. Visually it's quite hard to tell if one of the slices is a slightly more than or slightly less than another.
Now, you can add some overlay percentages or labels to make that more apparent, but if you're mainly interested in understanding how one slice compares to another slice, you would be better off showing that data with a bar or column chart. And also, pie charts are terrible if you want to show more than a few data points. Now, bear in mind you can always add multiple charts to the same report, even showing the same data, but understanding different things about that data. So, how do we get started? Well, charts might look very different, but they're configured in a similar fashion, and you can change the chart type from one to another after you've added the chart onto your report. But there is a couple of terms we need to get familiar with to work with any charts and Reporting Services.
And the best way to illustrate those is with a classic line chart. Now, if you were just to draw this on paper or on a white board, you typically refer to the bottom axis going from left to right as the X axis and then the one going up and down as the Y axis, but Reporting Services will not ask you to provide an X axis and a Y axis; instead, it asks you to provide values and category groups. Values would be the Y axis on a typical line chart. It's the numbers.
How do we show if something is more or less? So your values need to be numeric in almost all chart types. Now, categories, on the other hand, the X axis in a typical line chart, is how you group this data. You could be going month by month or quarter by quarter or region by region. So this doesn't have to be numeric; it could often be text: Southwest region versus Northeast region category A versus category B and so on. Now that might be all you need for a simple chart, but in some charts you might want multiple lines, each representing their own list of values and categories to compare.
Now, essentially when you do that you are overlaying multiple charts on top of each other and having multiple series of values and categories. But what your primarily interested in is always your numerical values and your category groupings. And you'll see these terms in all charts, even if they don't match exactly to the X and the Y. And for example, in a Reporting Services bar chart the values would be left right along the bottom and the categories up and down, but all these charts provide many visual options for how they display.
Let's take a look at a few.
There are currently no FAQs about SQL Server Reporting Services in Depth.
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.