can come in very useful, particularly in
[00:00:04.80] column charts and line charts, is
being able to add an average to this.
[00:00:08.80] I am looking right now at the column
chart from the previous example, and it
[00:00:13.07] would be nice to represent an average
for both of these sales numbers, just to
[00:00:17.77] figure out if any of the particular
columns are above average or below average.
[00:00:22.08] Well, we can do this, and we don't even have
to make any changes to the dataset. Here's how.
[00:00:25.83] I am going to jump back into my Design view.
[00:00:29.33] Now, this column chart has been
defined with two sets of values, and it's
[00:00:33.05] important that we're going to
represent an average on both of them:
[00:00:37.51] first an average of the sales YTD
and then an average of the SalesLastYear.
[00:00:41.70] Now, if you might notice here that if
I click these different columns, I'm
[00:00:46.75] seeing different things light up and
be selected, and that's actually very
[00:00:50.56] important with what we are about to do
here, because what I'm going to do is
[00:00:54.40] add what's called a calculated series, and
it's very sensitive to what I have selected.
[00:00:59.82] I am going to close down the Properties
panel here, just to give myself a bit more room.
[00:01:04.64] I can either do this by selecting the
columns themselves, though sometimes,
[00:01:09.03] depending on your chart, that's a little
difficult to grab. But I can also do it
[00:01:12.87] over here, by right-clicking the values.
[00:01:15.45] And the option I am looking
for is Add Calculated Series.
[00:01:18.97] So I can get to this by right-
clicking SalesYTD in the Chart Data section.
[00:01:23.22] I can also get to this by right-
clicking, in this case, the blue bar,
[00:01:27.43] Add Calculated Series.
[00:01:29.37] Reporting Services provides us
with a bunch of different formulas.
[00:01:33.25] Do we want a moving average. Do we want a
detrended price oscillator, or rate of change?
[00:01:38.89] Well, actually going for
something a lot more basic in that.
[00:01:41.76] I'm just interested in the mean.
[00:01:43.42] I don't want a moving average because
that's much more useful over a line chart
[00:01:47.72] that's going across multiple months.
[00:01:49.59] That's not relevant here.
[00:01:50.93] I really just want to know what
the mean average is for all of this.
[00:01:54.18] So I am just going to go ahead and click OK.
[00:01:57.05] Again, in Design view, we are just
looking at dummy data here, but we can see
[00:02:00.92] that it's added in that average, and
even given us part of this legend over here.
[00:02:05.63] So, I am going to go and run.
[00:02:07.63] And it might not look fantastic, but
this would be about right if we are
[00:02:10.38] trying to represent an average of the
blue bars; a couple will be over it and
[00:02:15.34] a few will be below it.
[00:02:16.86] But it's now quite easy to see that
Blythe, for example, is slightly above the
[00:02:21.34] average, where I might not
have been sure a moment ago.
[00:02:23.84] But I'd like to change this a little bit.
[00:02:26.16] So, I am going to go back into
Design view, and I want to go back to that calculated series.
[00:02:31.32] If I click on the chart and then
clicking carefully around, I should be able to
[00:02:35.16] highlight it, seeing that it's
highlighted with the different data points, and I
[00:02:39.42] can right-click that calculated
series itself and go into its properties.
[00:02:43.50] Again, we should expect to see
it's calculating a mean formula.
[00:02:47.01] I can come down to the Legend
and change that if I want to.
[00:02:50.51] Maybe even just change
it to say Average instead.
[00:02:53.17] Now, we have options below for, say, Line
Width.
[00:02:56.63] I am going to actually move that up to 3 points.
[00:02:59.38] I could even change the
Line style to a dashed Line.
[00:03:02.35] Now, it's up to you if you wanted
to pick your own color for this.
[00:03:05.61] At the moment, what's going to happen
is it's going to pick a color from the
[00:03:07.61] palette of the chart, in this case red.
[00:03:10.61] But we'll see a little later
how to change the palettes anyway.
[00:03:12.61] But that becomes a bit more legible,
certainly a bit more useful if we
[00:03:18.01] are comparing those.
[00:03:19.01] Well next, we add the second one.
[00:03:21.10] I wanted to also add an
average for the SalesLastYear.
[00:03:25.25] And being very careful what I have
clicked on in Design view, I can either click
[00:03:29.52] on the columns that represent
SalesLastYear or I can come into the Chart Data
[00:03:34.13] section and right-click over here, add
Calculated Series. Again, it's a Mean,
[00:03:39.06] not a Moving Average. And right now I'll
come down to Border and make this also a 3-
[00:03:43.97] point Line width, and this can be dotted.
[00:03:46.85] Click OK, and we are done!
[00:03:49.20] Run it. We now get a display of both averages.
[00:03:52.04] We get a comparison of sales
year to date versus sales last year.
[00:03:55.89] Very easy to do, very easy to configure.
[00:03:58.46] I could probably do with a bit more
experimenting on the visual look and feel of