{QTtext}{timescale:100}{font:Verdana}{size:20}{backColor:0,0,0}
{textColor:65280,65280,65280}{width:960}{justify:center}
{plain}
[00:00:00.51]
One ability of Reporting Services that
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
[00:04:02.36]
those lines, but this should do the trick.
[00:04:05.06]
