options on the Insert tab, and the next
[00:00:04.25] two up in the Data Visualization
section are Gauges and Indicators.
[00:00:08.85] I'm going to talk about both of these
at the same time because they're very
[00:00:11.86] similar to each other.
[00:00:13.30] They're both simpler than charts in that
they typically just display one data point.
[00:00:18.07] But they're also configured to tell us
whether that data point is good or bad or
[00:00:22.33] how far along a scale it's supposed to be.
[00:00:24.66] So, I have a blank report here with
a data source pointing to the regular
[00:00:29.18] AdventureWorks database.
[00:00:30.98] I'm going to go and get us some data.
[00:00:33.09] So creating a new dataset, I'll call
this one TerritoryInfo, and jumping into
[00:00:42.19] Query Designer, I'm going to start
drilling down into the Tables section, and
[00:00:46.55] I've got a SalesTerritory table--
[00:00:48.81] I don't think we've used this one
yet--where I will get the name of the
[00:00:52.90] territory, the Sales Year To
Date, and the SalesLastYear.
[00:00:56.63] I'll just go ahead and run this query to
see if this might be useful data for us.
[00:01:00.98] It looks about right.
[00:01:01.82] We're getting these different regions:
[00:01:03.59] SalesYTD, SalesLastYear.
[00:01:04.90] There is not a ridiculous amount of data,
[00:01:08.14] so it will be good to
have on a table. Click OK.
[00:01:11.74] That's my dataset.
[00:01:12.69] Now, while we can add gauges and
indicators completely independently, like
[00:01:17.78] sparklines, they're very
commonly seen on a table or a matrix.
[00:01:21.19] So I'm going to go ahead and insert a new
table based on that dataset I just created.
[00:01:28.09] And all of these columns, I'm just
going to put in the Values section.
[00:01:31.06] I'm doing no grouping whatsoever,
either up and down or left to right.
[00:01:36.20] Clicking Next, I'll just go right
past the layout, Next again, and this
[00:01:40.68] time I'll pick Slate.
[00:01:45.87] Run this, just to test everything looks okay.
[00:01:48.27] We are getting that information out.
[00:01:51.01] I don't really need the four decimal
places here, and I'd like to make it
[00:01:53.92] apparent this is a currency amount.
[00:01:56.69] So I'll jump back into Design view,
just select in there to grab that text box,
[00:02:01.53] and I can Shift+Select to get the
second one, and change both of the formatting
[00:02:05.40] here to Currency, and then click the
button to decrease the decimal point until
[00:02:10.40] it grays completely out so that we're not
showing any decimal numbers at all. That looks good.
[00:02:16.43] Again, what we're trying to do here
is get the idea. We could have a massive
[00:02:20.12] amount of numbers, and I'd like to
be able to quickly scan them to see if
[00:02:24.26] something is good or bad.
[00:02:25.94] What I'm going to do is add a new column
to this table and put an indicator in it.
[00:02:31.38] So to add a new column, I'll just click
somewhere in the table here to make the
[00:02:35.33] gray handles appear, then grab this
last column, right-click the gray area, and
[00:02:40.63] insert a new column to the right.
[00:02:42.27] And I'm going to insert an
indicator right here in this cell.
[00:02:46.85] We want it to be the same cell that
we're actually seeing the name of the
[00:02:50.51] territory on, or the sum of SalesLastYear.
[00:02:54.10] Like sparklines, it's very
sensitive to where you place indicators.
[00:02:58.23] We don't want to put them in the header row.
[00:03:01.03] So, up to the Insert tab, click
Indicator once, down into the cell I'm
[00:03:05.93] interested in, and click again.
[00:03:08.85] Our Indicator Type window appears.
[00:03:11.51] All of these are configured the same way.
[00:03:13.56] They're just a different
graphical look and feel.
[00:03:15.80] You can change these later
after the fact if you want to.
[00:03:19.17] I'm going to pick something basic,
[00:03:20.55] such as this set of three shapes here--
the traffic light idea--and click OK.
[00:03:26.23] We see that ended in the column.
[00:03:27.78] Well, what happens if we run this?
[00:03:29.92] Absolutely nothing, because that
indicator has no idea what it's meant to display.
[00:03:35.14] Back into Design view, I'm going to select
it.
[00:03:38.17] Selecting indicators and gauges can
sometimes be tricky because they are
[00:03:42.41] wheels within wheels.
[00:03:43.76] There is a couple of things we have
to be careful of when clicking around.
[00:03:47.51] So, if I click again,
I'll eventually select this indicator.
[00:03:50.61] I'll see this little Gauge Data window
appear, because an indicator is a gauge.
[00:03:55.25] That's why we're doing both
of these at the same time.
[00:03:57.99] And it has this dropdown window that's very
similar to working with a chart or a sparkline.
[00:04:03.21] Right now, it's asking for one value.
[00:04:06.19] It's not asking for groups.
[00:04:07.52] It's not asking for series.
[00:04:08.92] It's just saying, hey,
what number am I supposed to represent?
[00:04:12.33] Well, I'm interested in this being a
graphical representation of the sales year to date,
[00:04:17.76] so I'm going to drag that
over here and drop it in.
[00:04:22.25] Go ahead and run it.
[00:04:23.57] Well now we're getting indicators,
although the question might be, what is this
[00:04:27.44] actually meant to be representing?
[00:04:29.16] We've got some green, some red, some yellow.
[00:04:31.74] An indicator is always fueled by one
single value, but it needs to have some
[00:04:36.50] rules about whether that value is good or
bad.
[00:04:38.94] Now, when you drag one on,
it defaults to a percentage rule.
[00:04:43.05] The values of the top 33% are in green,
the middle in yellow, the bottom in red.
[00:04:47.75] Now, it doesn't mean you'll always see
equal amounts of red, green, and yellow,
[00:04:51.77] as the numbers that these are
representing--which is my SalesYTD--aren't
[00:04:56.55] distributed exactly equally, so only
two of them--the most high numbers--might
[00:05:00.95] actually represent 33% of the total.
[00:05:04.66] Having said that, a pure percentage
rule isn't what you want most of the time
[00:05:08.74] anyway, so let's see how to change that.
[00:05:10.52] Drop back into Design view.
[00:05:14.00] I'll highlight this, clicking
the Gauge again till this appears.
[00:05:18.20] We can't do anything really more here,
but I can right-click and I've got an option
[00:05:22.97] called Indicator Properties.
[00:05:25.13] If you don't get the option Indicator
Properties, make sure you're clicking in
[00:05:28.90] the right place, because with that open,
I have an option for Value and States,
[00:05:34.10] and this is where all the
magic happens for an indicator.
[00:05:36.99] It's how you determine which
indicator actually shows up.
[00:05:41.12] First, it's saying okay, I'm displaying
the value of sales year to date for each row.
[00:05:46.34] That's fine, no problem there.
[00:05:48.62] But the next option is, am I
displaying a percentage or am I based on a
[00:05:53.07] specific numeric value?
[00:05:54.77] Well I'm going to switch
to a numeric value here.
[00:05:57.71] And all I want to do is make this
indicator quickly represent whether this
[00:06:02.76] year's sales have already
beaten last year's sales.
[00:06:06.11] So in fact, I don't even
need the yellow indicator here,
[00:06:09.74] so I'm going to highlight an
area of that row and just delete it.
[00:06:13.24] And in this section I describe the rules.
[00:06:16.20] Why should it show up in red,
why should it show up in green?
[00:06:19.47] I'm going to say if sales year to date
have been anything from 0 all the way up
[00:06:24.66] to the same as sales last year, then it's
red.
[00:06:28.63] If it's from sales last year plus one
dollar, or anything above that, then it
[00:06:33.24] can show up in green.
[00:06:34.49] So, 0 is fine as a starting point,
but 33 is not good as an ending point.
[00:06:39.20] I'll just delete that, and I'm
clicking the little expression builder here.
[00:06:42.37] So it's asking for an expression for the
end value, and all I want to do is jump
[00:06:47.34] to my Fields section for this dataset
and say I'm interested in this being more
[00:06:52.12] than sales last year. Click OK.
[00:06:56.16] Next, I have to describe the rules for green.
[00:06:58.94] Well, it's not going to start at 66,
so I click the expression builder. And I
[00:07:02.86] want the button to the right-hand
side, because the one to the left is
[00:07:06.69] controlling a dynamic color.
[00:07:08.07] That's not what I want here. So click this.
[00:07:11.34] What I'm going to do here again is
we're going from sales last year and then I
[00:07:15.53] just enter in plus 1, because
we want to be beat it. Click OK.
[00:07:20.14] I don't want that one to have an end.
[00:07:23.16] As long as it's greater than last year's
sales plus one dollar, it can show up in green.
[00:07:28.13] What you'll also find over here
is the ability to change the icon.
[00:07:32.32] We have a whole list of them,
[00:07:34.04] so if you want to change
it, here's how you do it.
[00:07:36.04] In fact, I might even change that
to a smiley face. Click OK and run.
[00:07:43.23] Now we have the indicator showing up
as long as the sales year to date have
[00:07:48.38] beaten sales last year.