You can go into Design View and quickly generate an average, or create a calculated series, from information in a dataset that is presented in column charts and inline charts. When adding a generated average to a chart in your SQL Server database, you can also change colors and design elements in the chart to enhance readability, as this online video shows.
One ability of Reporting Services that can come in very useful, particularly in column charts and line charts, is being able to add an average to this. I am looking right now at the column chart from the previous example, and it would be nice to represent an average for both of these sales numbers, just to figure out if any of the particular columns are above average or below average. Well, we can do this, and we don't even have to make any changes to the dataset. Here's how. I am going to jump back into my Design view. Now, this column chart has been defined with two sets of values, and it's important that we're going to represent an average on both of them: first an average of the sales YTD and then an average of the SalesLastYear.
Now, if you might notice here that if I click these different columns, I'm seeing different things light up and be selected, and that's actually very important with what we are about to do here, because what I'm going to do is add what's called a calculated series, and it's very sensitive to what I have selected. I am going to close down the Properties panel here, just to give myself a bit more room. I can either do this by selecting the columns themselves, though sometimes, depending on your chart, that's a little difficult to grab. But I can also do it over here, by right-clicking the values.
And the option I am looking for is Add Calculated Series. So I can get to this by right- clicking SalesYTD in the Chart Data section. I can also get to this by right- clicking, in this case, the blue bar, Add Calculated Series. Reporting Services provides us with a bunch of different formulas. Do we want a moving average. Do we want a detrended price oscillator, or rate of change? Well, actually going for something a lot more basic in that. I'm just interested in the mean. I don't want a moving average because that's much more useful over a line chart that's going across multiple months.
That's not relevant here. I really just want to know what the mean average is for all of this. So I am just going to go ahead and click OK. Again, in Design view, we are just looking at dummy data here, but we can see that it's added in that average, and even given us part of this legend over here. So, I am going to go and run. And it might not look fantastic, but this would be about right if we are trying to represent an average of the blue bars; a couple will be over it and a few will be below it. But it's now quite easy to see that Blythe, for example, is slightly above the average, where I might not have been sure a moment ago.
But I'd like to change this a little bit. So, I am going to go back into Design view, and I want to go back to that calculated series. If I click on the chart and then clicking carefully around, I should be able to highlight it, seeing that it's highlighted with the different data points, and I can right-click that calculated series itself and go into its properties. Again, we should expect to see it's calculating a mean formula. I can come down to the Legend and change that if I want to. Maybe even just change it to say Average instead. Now, we have options below for, say, Line Width.
I am going to actually move that up to 3 points. I could even change the Line style to a dashed Line. Now, it's up to you if you wanted to pick your own color for this. At the moment, what's going to happen is it's going to pick a color from the palette of the chart, in this case red. But we'll see a little later how to change the palettes anyway. But that becomes a bit more legible, certainly a bit more useful if we are comparing those. Well next, we add the second one. I wanted to also add an average for the SalesLastYear. And being very careful what I have clicked on in Design view, I can either click on the columns that represent SalesLastYear or I can come into the Chart Data section and right-click over here, add Calculated Series. Again, it's a Mean, not a Moving Average. And right now I'll come down to Border and make this also a 3- point Line width, and this can be dotted.
Click OK, and we are done! Run it. We now get a display of both averages. We get a comparison of sales year to date versus sales last year. Very easy to do, very easy to configure. I could probably do with a bit more experimenting on the visual look and feel of those lines, but this should do the trick.
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