IntroductionWelcome| 00:00 | (music playing)
| | 00:04 | Hi! I am Simon Allardice and welcome to
SQL Server Reporting Services in Depth.
| | 00:09 | In this course I am going to show you
how to make the most of Reporting Services,
| | 00:13 | the part of Microsoft's flagship SQL
server database platform that deals not
| | 00:16 | with storing data, but with
presenting that data with creating reports.
| | 00:20 | We will begin by seeing just what SQL
Server Reporting Services can do and what
| | 00:24 | you need to get started.
| | 00:25 | We will spend most of our time actually
creating these reports using the authoring
| | 00:30 | tools available. Starting with
creating basic reports, we will then cover a
| | 00:34 | bunch of different ways to transform and
manipulate your data from the raw state
| | 00:38 | in the database into something
much more presentable in your reports.
| | 00:41 | We'll then add interactivity and make
these reports more complex and graphical,
| | 00:46 | adding visualizations of the data
including maps, charts, data bars, and Sparklines.
| | 00:52 | This is what SQL Server
Reporting Services is all about:
| | 00:56 | business intelligence; taking the day do
you already have and making it clear,
| | 01:00 | making it understandable, making it visual,
| | 01:02 | being able to explore it a dozen different
ways to better understand what it can tell you.
| | 01:07 | Let's get started.
| | Collapse this transcript |
| What you need to know| 00:00 | So that we can focus on using Reporting
Services, there are awesome things I'm
| | 00:04 | expecting you to know coming into this course.
| | 00:06 | Now if you are already comfortable
with SQL Server 2008 or SQL Server 2012, that's great.
| | 00:10 | You are ready to go.
| | 00:12 | But it isn't essential and
you be an expert on SQL Server.
| | 00:15 | What is essential is you understand at
least the basics of relational databases.
| | 00:19 | If I talk about tables and rows and columns,
keys and relationships, you know what I mean.
| | 00:24 | Now this database knowledge doesn't have
to be from SQL Server, although that is
| | 00:28 | most convenient. It could be from
Access or Oracle, DB2 or My SQL, as relational
| | 00:33 | databases are all very similar in concept.
| | 00:36 | But if you're completely new to
databases, first take a look at our Foundations
| | 00:40 | of Programming: Databases course
that covers all the basic concepts.
| | 00:44 | It is also helpful to know some SQL.
| | 00:47 | You don't have to be an expert; just
knowing how to write select statements is
| | 00:50 | good. And as you'll see, you won't have
to write much SQL if you don't want to,
| | 00:55 | but SQL Server Reporting Services is
a product built on top of a classic
| | 00:58 | relational database.
| | 01:00 | So the more of that classic relational
database knowledge you already have, the
| | 01:03 | more you will be able to
get out of this product.
| | 01:06 | So if you can write basic SQL
queries, great; and if you are a complete
| | 01:09 | expert, even better.
| | 01:10 | Now what if you have got some of this
knowledge but just need a refresher on this.
| | 01:14 | Well, we have a SQL Essential
Training course and one of the quickest ways
| | 01:18 | you could go through this is to take a
look at the SQL Server 2008 Essential
| | 01:22 | Training Course, particularly the Creating
Databases and Retrieving Data with SQL chapters.
| | 01:28 | And with this in mind, next I am going
to quickly cover some setup questions
| | 01:32 | you might have, some basic terminology,
and what we actually need to get started.
| | Collapse this transcript |
| Exploring SQL Server Reporting Services components| 00:00 | To use Reporting Services successfully,
we need to first make sure we are all on
| | 00:04 | the same page about what it actually is.
| | 00:06 | So Microsoft SQL Server Reporting
Services, which is a bit of a mouthful, so
| | 00:11 | I'll just call it Reporting Services
from now on, although you will also see
| | 00:15 | SSRS used as an abbreviation.
| | 00:17 | This is, as it sounds, part of a
larger product of SQL Server, Microsoft's
| | 00:22 | enterprise-level database software.
And Reporting Services is considered a
| | 00:27 | self-contained component of SQL Server.
| | 00:30 | It is part of this platform, along with
other elements like analysis services and
| | 00:35 | integration services,
| | 00:36 | although we are not interested
in those components in this course.
| | 00:40 | But whoever installs SQL Server can
choose to either have the Reporting Services
| | 00:44 | component installed, along
with the database server, or not.
| | 00:48 | And at its most basic distinction, the
regular SQL Server part takes care of
| | 00:52 | storing your data and Reporting Services
component is an optional component used
| | 00:57 | to generate reports based on that data.
| | 00:59 | Okay, but even if we know we have
access to this Reporting Services part, it is
| | 01:04 | still a vague term, because there is
no single application called Reporting
| | 01:08 | Services the way you have an
app like Microsoft Word or Access.
| | 01:12 | So to understand it, it's best to
understand that there are really three
| | 01:16 | different parts to Reporting
Services after it's been installed.
| | 01:19 | First is the server-side part, the engine of
Reporting Services that runs in the background.
| | 01:25 | It talks to the database and can
manage and deliver your reports.
| | 01:30 | But the question is, where do
reports come from? Who says what's on them?
| | 01:34 | So the second part of the Reporting
Services picture is having an application to
| | 01:39 | help us define and create these reports.
| | 01:41 | And there are a couple of different
applications you can use for report authoring.
| | 01:45 | The main one we are going to use in
this course is called Report Builder,
| | 01:49 | specifically Report Builder version 3.
| | 01:51 | This is a free Microsoft Office-style
standalone desktop application that exists
| | 01:57 | entirely for creating
reports for Reporting Services.
| | 02:00 | It lets you define simple and complex
reports, not just describing the data
| | 02:05 | you want to see, but visualizations of
the data, like charts and maps, all of
| | 02:09 | which we will explore.
| | 02:10 | Alternatively, there is another
application called Report Designer, although
| | 02:15 | there are a couple of other names for
it, because rather than a standalone
| | 02:19 | application, Report Designer
functionality is integrated into Visual Studio
| | 02:24 | or SQL Server Tools.
| | 02:26 | We will talk more about Report Builder
and Report Designer shortly, but do
| | 02:31 | understand right now, there really isn't
much practical difference between these two.
| | 02:35 | I often hear from developers who
assume that the Report Designer that's
| | 02:39 | integrated into Visual Studio must
somehow be more powerful and more fully
| | 02:44 | featured than the one that
looks like an Office application.
| | 02:47 | But that's not the case at all.
| | 02:49 | These are essentially identical in
ability, and it's really more about what
| | 02:53 | environment you prefer to work in,
either a Microsoft Office-style program or a
| | 02:58 | Visual Studio-style program.
| | 03:00 | Sure, if you're a developer used to
working in Visual Studio, you can also do
| | 03:04 | extra developer-focused task like work
with source control. But as far as the
| | 03:09 | reports you create are
concerned, these are the same.
| | 03:13 | So once you have worked with
report authoring what else is there?
| | 03:16 | Well, once these reports are defined,
how you or other people view the report?
| | 03:21 | Typically the reports in Reporting
Services are viewed by going to a website
| | 03:26 | called the Report Manager.
| | 03:27 | This is where you can find different
reports that have been created and view them
| | 03:31 | online. If it's a large report, you can
page through it, zooming in or out, search
| | 03:35 | it, or even change the parameters of the report.
| | 03:38 | Usually, the URL of this website is
whatever your server name is /Reports,
| | 03:44 | although that can be
changed by your system administrators.
| | 03:47 | So our focus in this course is learning
the authoring tools to create and design
| | 03:52 | these reports and being able to share
them successfully in an organization.
| | 03:56 | Unlike some of my other courses, I am
not going to spend too much time here
| | 04:00 | talking about setup configuration,
installation, because most people viewing
| | 04:04 | this course already have SQL Server
Reporting Services in place and need to know
| | 04:08 | how to use. If you are looking for
information on installing SQL Server you can
| | 04:13 | find that in the SQL
Server Essential Training Course.
| | Collapse this transcript |
| Reviewing SQL Server versions| 00:00 | This course works for using Reporting
Services in either the SQL Server 2008 R2
| | 00:05 | or SQL Server 2012 versions.
While there are differences between these versions
| | 00:10 | of the SQL Server platform, the
differences purely in the Reporting Services
| | 00:14 | features are very, very small and most
of the time you would be unable to tell,
| | 00:18 | either viewing or designing a report,
which version is then being used.
| | 00:23 | Okay, they're not completely
identical, and I will of course point out a few
| | 00:28 | situations that are different later in
the course, but know that for the vast
| | 00:31 | majority of examples,
| | 00:32 | it is exactly the same working in
Reporting Services on either the 2008 R2 or
| | 00:37 | 2012 versions of SQL Server.
| | 00:40 | And if you're not sure which
version you have, talk to your database administrator.
| | 00:44 | Now if we're going to make some reports,
we need some data on those reports.
| | 00:48 | So to demonstrate a lot of these
ideas and techniques, I'll be using
| | 00:51 | Microsoft's sample database called
AdventureWorks that I have installed on
| | 00:56 | my database server.
| | 00:57 | AdventureWorks is freely
downloadable from Microsoft.
| | 01:00 | It's at the msftdbprodsamples.codeplex.com
address, and it can be downloaded on
| | 01:07 | either the SQL Server 2008
or SQL Server 2012 platforms.
| | 01:11 | And it is great for what we are doing here.
| | 01:14 | AdventureWorks is a database based
around a fictitious cycling company with a
| | 01:17 | good amount of typical corporate
interrelated data on sales and customer
| | 01:23 | information, human
resource information, and so on.
| | 01:26 | But--and this is important--
| | 01:27 | I am not trying to explain how to
use Reporting Services just with the
| | 01:31 | AdventureWorks database. I need
to use something to illustrate these
| | 01:35 | examples, but I will do my best to
always explain what I'm doing in a way that
| | 01:39 | you can take to your own
database and your own data.
| | 01:42 | If you're someone who learns best by
duplicating exactly what I'm doing, then
| | 01:45 | you may want to use AdventureWorks
and it is a freely available, as you can
| | 01:49 | see. You can talk to your database
administrator to see if you already have, or
| | 01:53 | can get, access to it.
| | 01:54 | Alternatively, you could set up your own
machine as a development box and install
| | 01:59 | everything yourself.
| | 02:00 | That is not necessary for this course
but if you feel comfortable doing that,
| | 02:03 | then by all means go ahead.
| | 02:05 | I am going to quickly go through what
I'm using to record this course, but I do
| | 02:09 | not expect anyone to duplicate my setup exactly.
| | 02:12 | There's simply no need.
| | 02:13 | So I have first, SQL Server 2008
R2, Standard edition installed,
| | 02:19 | including Reporting Services, and
also SQL Server 2012 Standard edition
| | 02:24 | including Reporting Services.
| | 02:26 | And I have both versions of SQL Server
installed independently, just so that I
| | 02:30 | can demonstrate any differences between them.
| | 02:33 | On these database servers, I also have
installed the AdventureWorks databases.
| | 02:38 | Now you'll find there are different
versions of the download files available for
| | 02:42 | SQL Server 2012 and for SQL Server 2008 R2.
| | 02:45 | On the site, you will find multiple
downloads for both 2012 and 2008 R2.
| | 02:51 | There's the regular AdventureWorks
database, which you will find under the
| | 02:55 | OLTP term, and there's optionally, in
either of these locations, you'll find
| | 03:00 | AdventureWorks LT--the LT meaning
a lightweight smaller version of that.
| | 03:06 | And again, optionally, you'll also find
AdventureWorks DW for data warehouse.
| | 03:12 | You will find those links right of the
top level of the page as well, the DW link.
| | 03:17 | And that DW version is for data mining
and analytics, if that's something that
| | 03:21 | you are interested in.
| | 03:23 | I have all three databases--the
regular one without any initials, the LT one,
| | 03:28 | and the DW one--installed on my SQL
Server 2012 server, and also all three
| | 03:34 | installed for my 2008 R2.
| | 03:36 | So if you see those letters as I'm going
through the course, that's what they refer to.
| | 03:41 | But again, you do not need to have these
installed, as long as you're happy with
| | 03:45 | following along with what I'm doing in abstract.
| | 03:47 | Now, I also have Visual Studio 2010
installed on my machine and the SQL Server
| | 03:52 | configuration tools.
| | 03:53 | If you have Visual Studio 2010 or 2012,
you can use those. But if you don't use
| | 03:59 | Visual Studio already, that's okay.
| | 04:01 | It's not essential.
| | 04:02 | So you might be looking at this thinking,
well, what actually is necessary to install?
| | 04:07 | And here is the thing.
| | 04:08 | Most people watching this course
shouldn't need to install anything.
| | 04:12 | Here is what is essential.
| | 04:14 | If you can open up a web browser
already and go to that Report Manager website
| | 04:19 | for Reporting Services, that's the
perfect place where you can get started.
| | 04:22 | The default address again is the
name of your database server/reports,
| | 04:27 | although that can be changed by
your sys admin or database admin.
| | 04:32 | You're looking for a website
that looks something like this,
| | 04:35 | the SQL Server Reporting Services home area.
| | 04:38 | Mine is a fresh install,
so I don't have anything listed here.
| | 04:41 | If yours is being heavily used, you
might find a lot of links and folders here.
| | 04:45 | So if you can get here, we are off to
a good start; and if you can't, talk to
| | 04:50 | your database administrator or sys
admin to find out how you get access to
| | 04:54 | Reporting Services in your organization.
| | 04:56 | There may be multiple servers you can
get access to. In some organizations there
| | 05:00 | might even be one set up just
for testing an experimenting.
| | Collapse this transcript |
|
|
1. Core Report SkillsThe elements of a report| 00:00 | We're going to jump in to creating
reports in just a second, but I have got
| | 00:04 | one key concept to cover first so that when we
do jump in, things will make a lot more sense.
| | 00:09 | See, every report you make in Reporting
Services will require that you provide
| | 00:14 | three elements, three things, three
distinct kinds of information in order, that
| | 00:18 | I'll describe as the
where, the what, and the how.
| | 00:21 | Where is the data for this report,
what is the data for this report, and how
| | 00:26 | should it be presented?
| | 00:28 | And we need to describe each of
these three pieces individually.
| | 00:32 | Without all three, you don't have a report.
| | 00:35 | So first, where is it?
| | 00:37 | Where is the data that you're
interested in, and how do you connect to it?
| | 00:40 | Literally what machine
is it on? What's it called?
| | 00:44 | Because Reporting Services is not
restricted to only creating reports from data
| | 00:49 | on the same physical SQL Server machine.
Sure, that it is very common, but it can
| | 00:54 | talk to other SQL Server machines,
whether they are across the room or across
| | 00:59 | the world. They can talk
to other database systems.
| | 01:01 | They can talk to SharePoint lists.
| | 01:03 | They can talk to XML files.
| | 01:05 | So if we want to create a report and,
say, base it on data in a SQL Server
| | 01:10 | database, we begin by providing the
name of the server, the actual name of
| | 01:15 | that machine, then the name of the
database on that server, because there's
| | 01:19 | typically multiple different databases.
And because just naming a database is
| | 01:24 | not going to get you inside it--all
databases are typically secured, so we
| | 01:29 | will also need to provide some kind
of authentication details so that
| | 01:33 | database will actually let us in.
| | 01:35 | So this is your report's source of data, your
data source, and that is the term that we use.
| | 01:43 | We are defining the data source.
| | 01:45 | So if that's the where, the next
step is what? What is your data?
| | 01:49 | Now, you might think you just
provided this, but you didn't.
| | 01:53 | Our data source is just us pointing
to the database we are interested in,
| | 01:57 | saying where it is and how we connect to it.
| | 01:59 | But we don't want the entire contents of
that database just dumped out on a report.
| | 02:04 | So in the what step we
specify the data that we want.
| | 02:07 | What is that data exactly? What tables,
what rows, what columns, in what order,
| | 02:13 | with what conditions?
| | 02:15 | This is the subset of the data that we are
interested in, and this is called the data set.
| | 02:21 | And finally, once we've defined the where--
the data source--and the what--the data
| | 02:26 | set--we can define the how.
| | 02:28 | How should this be presented?
What does it look like? What is the layout?
| | 02:33 | So from purely presentational choices
like what fonts and what colors we are
| | 02:38 | using, to more structural choices,
because the same data could be shown
| | 02:42 | many different ways.
| | 02:43 | So is it just raw text and numbers, or
will we generate charts and graphs from
| | 02:47 | this data? Will we allow viewers to
interact with the report and re-sort it and
| | 02:52 | move through pages of it?
| | 02:54 | So everything that we are going to do
is going to fall into the where--the data
| | 02:59 | source--the what--the data set--and the
how--our report layout--and it's different
| | 03:04 | kinds of thought process
that we need for each step.
| | 03:07 | So next, let's see an example.
| | Collapse this transcript |
| Creating a report with Report Builder| 00:00 | To create a report in Reporting
Services we need to use an authoring tool, and
| | 00:04 | I'm going to use Report Builder first.
| | 00:06 | The easiest way to get it is to open up
a web browser to the Reporting Services
| | 00:10 | Report Manager website.
| | 00:12 | Now, for me, that's located at dbserver/Reports;
| | 00:13 | it will be a different address for you.
| | 00:18 | On the taskbar of this Report Manager
website homepage, there is a button for
| | 00:23 | Report Builder, and when you click this,
it's going to download and install the
| | 00:26 | Report Builder application on your machine.
| | 00:29 | You may have to agree to a couple of
security questions the first time you do this.
| | 00:33 | So what we're opening here is Report
Builder 3.0, and that's whether you're
| | 00:37 | using SQL Server 2008 R2 or SQL Server
2012; you just see a different splash
| | 00:42 | page for SQL Server 2008 R2.
| | 00:44 | And as soon as the application Report
Builder opens, we get the Getting Started
| | 00:49 | page, with several options for
creating reports with table wizards and chart
| | 00:53 | wizards and map wizards.
| | 00:55 | For the first one I'm going to go as
simple as possible and just pick the
| | 00:59 | Blank Report option, because I want to focus
just on the basic three elements of all reports:
| | 01:05 | the Data Source, the Dataset, and
Report Layout--where is the data, what is the
| | 01:10 | data, how should it be presented?
| | 01:13 | So clicking Blank Report
we go into Report Builder.
| | 01:16 | I'm just going to maximize it here.
| | 01:18 | Report Builder looks like a modern
Microsoft Office application and shares many
| | 01:22 | of the same user interface ideas.
| | 01:24 | But it's a very focused application,
so unlike Word or Excel or PowerPoint, we
| | 01:29 | don't have to worry about a dozen
different tabs along the Ribbon to learn.
| | 01:32 | We really just have three:
| | 01:34 | the basic Home tab that's similar to
most Office applications, the Insert tab--
| | 01:39 | now, this one is it specific to Report
Builder as it contains all the different
| | 01:43 | elements that we can add to our report,
things like charts and Sparklines,
| | 01:48 | tables and matrixes, and we'll see
these as we go through the course--
| | 01:52 | and then there's finally the View tab.
| | 01:54 | This one is very simple here, as it
just lets us turn on or off different
| | 01:58 | sections of the Report Builder application.
And there are only a few sections we can affect.
| | 02:05 | The part we're interested in first is
this area over here on the left-hand side,
| | 02:09 | the Report Data area.
| | 02:11 | In here is where we say, where is our
report data and want is our report data?
| | 02:16 | Without providing something here,
there's nothing we can do in this middle
| | 02:20 | section, on this Report Layout.
| | 02:21 | And the first thing we need is
the data source; where is our data?
| | 02:25 | So I can either right-click the Data
Sources folder and say Add Data Source;
| | 02:30 | I can also get that from the dropdown
here in the Report Data section. Either
| | 02:34 | way it does the same thing.
| | 02:36 | The first thing Report Builder will ask
for is a name for this data source, and
| | 02:39 | this is completely up to you.
| | 02:42 | I'm going to point this to the
AdventureWorks database; in fact I'll point it
| | 02:45 | to the AdventureWorksLT database, so
I'll just say AdventureWorks and that will
| | 02:50 | work for our report.
| | 02:51 | Now, below this, I'm asked if I want to
use a shared connection. Basically, that
| | 02:56 | means did someone else already
describe how to connect to the same data we're
| | 03:00 | interested in and save that information?
| | 03:03 | Now, because my Reporting Services
install is fresh out of the box, I don't have
| | 03:07 | anything previously saved that I could
use, so I'm going to click the second
| | 03:11 | option, that I'm going to provide
connection details just for this report.
| | 03:15 | We will explore how to save the details
of the data sources we define so that
| | 03:20 | we don't have to define the
same ones again and again.
| | 03:23 | So below this, we have the connection type.
| | 03:25 | We've got a dropdown box here.
| | 03:26 | It defaults to SQL Server, but there
are many other options, like Oracle.
| | 03:30 | We have TERADATA, Microsoft
SharePoint List, even XML files.
| | 03:35 | We're going to just use the default
option of SQL Server for most of this course.
| | 03:40 | But I need to say what SQL Server
we're interested in, so down below, we have
| | 03:44 | this Connection string section.
| | 03:46 | Now, if you're a developer who is used
to manually writing connection strings,
| | 03:49 | you can just type one in here. Or an
easier way is we can click the Build button
| | 03:54 | over here on the right-hand side.
| | 03:56 | Now, we'll talk more about data source
connections shortly, just because there
| | 04:00 | are many different possibilities, but
in this example I'm going fairly simple.
| | 04:04 | I'm specifying three things:
| | 04:06 | the actual SQL Server name, which
for me is going to just be dbserver,
| | 04:10 | the fact that I'm going to attempt
to log on to that server using Windows
| | 04:15 | Authentication, basically logging on as myself--
| | 04:18 | what that means is it won't give me
anything I didn't bring to this party;
| | 04:22 | it doesn't give me access to
databases I wouldn't get access to normally--
| | 04:26 | and then down below we have to name the
actual database that's on that SQL Server.
| | 04:30 | If I click the dropdown, we should have a
few options here, and I'm going to pick
| | 04:34 | the AdventureWorksLT version.
| | 04:36 | So the server is dbserver.
| | 04:38 | The database I'm interested in is
the AdventureWorksLT version, and I'm
| | 04:43 | connecting using Windows Authentication.
| | 04:45 | I'm going to click the Test Connection button.
| | 04:47 | It tells me Test connection succeeded.
| | 04:49 | I click OK, and this is where it's
actually entered in that connection string.
| | 04:52 | I'm just going to leave that as is and click OK.
| | 04:56 | That's our data source.
| | 04:57 | So let's see, step 2 is the Dataset.
| | 05:00 | Coming over here onto the Datasets
folder in our Report Data section, I'm going
| | 05:05 | to right-click and say Add Dataset.
| | 05:07 | This is what data
specifically do we actually want.
| | 05:11 | Well, let's just say I'm wanting to
create a simple report detailing the current
| | 05:15 | customer list, so that dataset is our customer.
| | 05:19 | So I'll give this a name.
| | 05:20 | I'll just call it Customers.
| | 05:22 | And again, we have this option, do I
want to use a shared predefined dataset?
| | 05:25 | Well, I don't have any, so I'm going to
define this dataset just in this report.
| | 05:31 | And the first thing it's going to ask me
for is the data source, which is why we
| | 05:35 | have to define the data source firs,
so that we have something to connect to.
| | 05:39 | I'm going to use AdventureWorks.
| | 05:40 | And then come down.
| | 05:41 | We have Query type, because
we're really using select statements.
| | 05:45 | We're querying the database.
The typical kind is Text, and you could manually
| | 05:49 | enter in some SQL here.
| | 05:51 | You could also select a stored procedure if
you had predefined stored procedures to use.
| | 05:56 | I'm going to jump back over to Text,
and then rather than type it manually, I'm
| | 06:01 | going to click this button that says
Query Designer, and this will give us a
| | 06:04 | visual designer for
specifying the data that we want.
| | 06:07 | Again, I'm just trying to do a simple
example here, so I'm going to expand the
| | 06:11 | different options that are available in
this AdventureWorksLT database, which is
| | 06:16 | a cut-down version of the full AdventureWorks1.
| | 06:19 | I'm going to expand Customer here,
and let's say I'm just interested in the name
| | 06:23 | and basic contact information of our customers.
| | 06:26 | So I'll say FirstName, LastName,
CompanyName, EmailAddress, and Phone, and
| | 06:32 | that's all I'm interested in right now.
| | 06:35 | If I wanted to test this, to check it,
there is a button up at the top, to say
| | 06:40 | Run Query, and if I click that, we'll
get a basic example of the kind of data
| | 06:44 | that we're going to expect from this.
| | 06:46 | No real surprises here, but it's a
good way to check that the queries that
| | 06:50 | we're putting together as we're specifying our
data actually work the way we'd expect them to.
| | 06:55 | We'll cover more about this section a
little on and also talk about joining
| | 07:00 | different tables together to get
more useful information, but for now I'm
| | 07:04 | just going to click OK.
| | 07:06 | And I can actually see that by using
the Query Designer what it's actually done
| | 07:09 | here is generate an SQL select statement,
a very basic one, but again, it will do
| | 07:15 | the trick right now.
| | 07:16 | There are other options we can provide
for this dataset, like changing the names
| | 07:20 | of our fields if I want to.
| | 07:21 | We can provide filters and parameters,
but we'll see all of that later on.
| | 07:26 | I'm just going with a very
basic Query. So I click OK.
| | 07:29 | We have our data source;
we have our dataset.
| | 07:32 | That's the where and the what of our data.
| | 07:35 | Now we need to say how
it's going to be presented.
| | 07:37 | And we're working with the
Report Layout in the middle section.
| | 07:41 | I could start dragging the
individual fields across, but there is a much
| | 07:45 | better way to do this.
| | 07:45 | I'm going to jump into my Insert tab
in Report Builder, which is where we
| | 07:50 | specify our actual report elements,
and what I'm going to look for here is
| | 07:54 | just a simple table.
| | 07:57 | The options here for Table, Matrix,
and List give us multiple options for
| | 08:01 | repeating sets of data.
| | 08:03 | The easiest one right now is the Table,
and the option I'm actually going to
| | 08:07 | pick here is the Table Wizard.
| | 08:08 | It will just help us create this.
| | 08:10 | So the table expects to be fueled with
data, so it's asking for a dataset, and
| | 08:15 | we've already defined that.
| | 08:16 | That's why we did it in that order.
| | 08:18 | So I'll choose that
existing dataset and click Next.
| | 08:22 | This next window is asking, what do you
actually want to see under that dataset?
| | 08:26 | Do you want to just see all of the
fields, or do you want to use some of these to
| | 08:29 | control grouping them together?
| | 08:31 | Let's say, for example, I wanted
to group all the customers for a particular company together.
| | 08:36 | I could use the CompanyName and
drag that into the Row groups area.
| | 08:40 | But again, for right now, I'm just
going for a very basic report, so the one
| | 08:45 | we're most interested in is the Values
box over here on the right-hand side.
| | 08:49 | And I'm going to drag in
FirstName, then LastName, CompanyName,
| | 08:53 | EmailAddress, and Phone.
| | 08:55 | We'll work with Grouping a
little later on. Click Next.
| | 09:00 | It's detected that we don't have any
groups, so these options here are grayed out.
| | 09:04 | All I can do is click Next. And we can
pick a different style, a basic color
| | 09:09 | scheme and font setup for our layout.
| | 09:12 | I'm just going to go with the
Slate option and click Finish.
| | 09:15 | And we can see that's been
added to the report layout.
| | 09:18 | Yes, I'd want to do a little bit of
rearranging to this shortly, but it will work.
| | 09:22 | Now I need to prove it.
| | 09:25 | And what I'm going to do
is just give this a title.
| | 09:27 | This is a basic customers report.
And I want to prove that this works.
| | 09:32 | Well, up here in the Home tab,
the first icon here is Run.
| | 09:36 | We're currently in Design view.
| | 09:38 | I'm going to click Run and actually show
this report with the fresh data inside it.
| | 09:42 | And we have a preview of our report.
| | 09:46 | I can see that I could play around a
little bit with some of the widths of
| | 09:49 | these columns to makes this a bit more readable,
but it's got the data that I'm interested in.
| | 09:54 | So we have the two
different views of the report;
| | 09:57 | we have the view of the up-to-the-
minute data, and then I can click that first
| | 10:01 | icon again and go back into the
Design view to rearrange this.
| | 10:04 | But what I want to do is actually now save
this report definition that I've created.
| | 10:09 | If I look towards the bottom of the
Report Builder application, I can see
| | 10:14 | that we're currently connected to the Report
Server at dbserver location, which is fine.
| | 10:19 | So if I come up to the Save button up
here on the top, when I click Save it's
| | 10:24 | asking, where do you want to save this?
| | 10:26 | I could save this file to my desktop,
to my own My Documents folder, but that's
| | 10:31 | not all that useful.
| | 10:32 | I actually want to save it into
Reporting Services so that it's available and
| | 10:36 | accessible by multiple people.
| | 10:38 | And bear in mind what we're saving here
is the report definition, not the report
| | 10:42 | output, because we want to be able to
run this report tomorrow, next week, next
| | 10:46 | month, and see up-to-the-minute data.
| | 10:48 | The definition of a report is an RDL file.
| | 10:51 | It's currently Untitled.rdl.
| | 10:53 | I'll just call it My First Report.rdl.
Click Save, and we're actually saving up
| | 11:00 | into the Report Server.
| | 11:01 | Well, what does that mean?
| | 11:03 | Well, if I close Report Builder down
and go back to our Report Manager website,
| | 11:09 | I'm seeing nothing here because I had
a fresh install of Reporting Services,
| | 11:13 | But if I Refresh this page what I'll
now get is my first report that I just
| | 11:17 | created available here.
| | 11:19 | I can just click the link to view it
and we're viewing this in the browser.
| | 11:23 | I've got the options up on the top to print it
or even to export and save it another format,
| | 11:29 | PDF, XML file, and so on.
| | 11:31 | I'm going to click the Home
link just to go back up to the top.
| | 11:34 | Another option I also have is this
dropdown arrow that will let me do things
| | 11:38 | like delete this or view report history
or manage security or just edit that
| | 11:44 | back in Report Builder again, if
I want to make some changes to it.
| | 11:47 | And that again, will open up the
Report Builder application and just move us
| | 11:51 | back into the Design mode for this particular
report. And we can just repeat this process.
| | 11:56 | So OK, this report is about as
simple as it gets, but it shows that we
| | 12:01 | always need to provide the data
source and the dataset before we can do
| | 12:04 | anything with the layout.
| | 12:06 | So we've now got the basics of Report
Builder, and we can start to build on this.
| | Collapse this transcript |
| Grouping table regions on a report| 00:00 | So while it's very easy to create
reports that just write out every row in a
| | 00:04 | table, we often want to start grouping
things a bit more meaningfully, and that's
| | 00:08 | very easy to do in Reporting Services.
And most of this grouping we'll actually do
| | 00:13 | in the report itself rather than
writing complex SQL to deal with it.
| | 00:17 | So I am going to open up Report
Builder again and create a blank report.
| | 00:22 | The first thing I am going
need here is the data source,
| | 00:25 | so I will right-click and add a data source.
| | 00:27 | I am going to connect again
AdvertureWorkslight, so I will just give it a short
| | 00:31 | name, AWLT, and this will
be embedded in this report.
| | 00:36 | It is talking to SQL Server.
| | 00:37 | I will click the Build
button to start defining this.
| | 00:41 | And the previous example I'd
use the Server name dbserver.
| | 00:44 | This is actually the address of my SQL
Server 2008 R2 server, which means that
| | 00:51 | using Windows authentication to connect
to it, if I click the dropdown here, it
| | 00:56 | will show me the databases that are
in that SQL Server, which are the 2008
| | 01:01 | versions of my AdventureWorks
database, along with a few others.
| | 01:04 | But I could just as easily give it a
different server address for a different version.
| | 01:08 | In fact, for me, my address of
my SQL 2012 server is this one.
| | 01:13 | So connecting again using my
authentication here, if now I click the dropdown
| | 01:18 | box, I will get the AdventureWorks 2012
versions that are installed on that SQL Server.
| | 01:23 | Now, of course this is much more to deal
with your organizational setup than it
| | 01:27 | is with Reporting Services, but it's
worth pointing out that it's very easy in
| | 01:31 | this Connection window to connect to
multiple different sources of data, as long
| | 01:36 | as you're allowed to
talk to that source of data.
| | 01:38 | So I will select the AdventureWorksLT version
and just test that connection. It looks good.
| | 01:44 | Click OK, click OK. We have our data source.
| | 01:47 | Step two the dataset. So right-
clicking here will add a dataset.
| | 01:51 | This time I am going to
go for a list of products.
| | 01:54 | We are going to start grouping these in
some meaningful fashion, so I will call
| | 01:58 | this dataset products and it
will be embedded in this report.
| | 02:02 | It's going to ask what data source we are using.
| | 02:05 | Well of course it's the one we just
defined to AdventureWorksLT. And then I
| | 02:08 | will come down and click the Query Designer
button to actually specify the data that I want.
| | 02:14 | So we are talking to that database.
| | 02:15 | It's showing us all the tables.
| | 02:17 | I am going against the ProductTable.
| | 02:20 | I could just select the checkbox to
bring in every single column from that
| | 02:24 | table, but it's good practice to only
select the columns that you need. Now for
| | 02:28 | me this time around it's going to be
the product name, and I also want to know
| | 02:32 | what category this product is in.
| | 02:35 | Well, if I come down a little bit
more in the product table, there is
| | 02:38 | a ProductCatagoryID.
| | 02:40 | This will be a numeric ID, which isn't
all that useful, but it's what I am going
| | 02:45 | to start with right now.
| | 02:46 | If I go ahead and click Run Query,
we are going to see the name of our products
| | 02:49 | coming back with this ProductCategoryID
number as well, and the idea of course is
| | 02:55 | there's multiple products in a particular category.
| | 02:58 | We don't necessarily know what that
category is, but we will get to that.
| | 03:03 | I just want to do some grouping here.
| | 03:05 | So I am going to ahead and click
OK and say that that was my dataset.
| | 03:09 | The next thing I need is something on this
report that will actually write it out.
| | 03:13 | So what are we trying to do here?
| | 03:15 | Well, let's give it a title first.
| | 03:17 | I am going to click in here and say
that this will be Products Grouped by
| | 03:21 | Category. And because we know there will be
multiple rows, we need some kind of
| | 03:24 | repeating region here.
| | 03:25 | I am going to jump up to my Insert tab,
and the section we are interested in is
| | 03:30 | the Data Regions. The one I want is the Table.
| | 03:32 | I am clicking the dropdown arrow here;
| | 03:34 | I am going to select Table Widget.
| | 03:36 | The first thing that's going to ask for is
what dataset is meant to fuel this table;
| | 03:40 | it of course is the
products dataset I just defined.
| | 03:43 | Selecting that, I click Next. And this is
where it's asking what fields to show.
| | 03:48 | And the previous version, what I've done
is drag everything into the Value section.
| | 03:53 | This time around what I am going to do
is drag Name, as in the product name, into
| | 03:58 | the Value section and Product
Category ID into the Row groups section,
| | 04:02 | telling this table I want to start
grouping these rows together by the
| | 04:07 | Product category ID.
| | 04:08 | So if I have two rows with that
product category of 18, they will be grouped
| | 04:13 | together in that table. And we will see the
impact that has visually in just a second.
| | 04:17 | So this is a very simple example, but I
like to start simple and build from this.
| | 04:22 | So I will go ahead and click Next.
| | 04:25 | We now get an option for grouping. There are
different visual options you can have
| | 04:29 | where the things are shown beside each
other. We have another option here for
| | 04:32 | Blocked, with a subtotal above,
Stepped in the subtotal above.
| | 04:35 | It really doesn't matter in our example
because we are so simple right now, so
| | 04:40 | you can choose any one that
looks visually appealing to you.
| | 04:43 | I am just going to select these
Stepped with subtotal above and click Next.
| | 04:47 | Now we have the option of choosing a style.
| | 04:50 | I am actually going to go ahead
and click the Generic one, which is
| | 04:54 | essentially style-free;
| | 04:55 | it doesn't have background
colors and no particular fonts here.
| | 04:59 | So with Generic selected, I will click
Finish, and it drops in the table into our report.
| | 05:04 | I am going grab the handle on this side
and just drag it a little wider here so
| | 05:08 | we have got more room to play with, and
then from the Home tab of Report Builder,
| | 05:12 | just click Run. And this is
what our table is generating here.
| | 05:17 | Our rows are actually grouped
together into these categories, and they are
| | 05:21 | collapsed by default.
| | 05:23 | So we have the little plus box here
that I can click and start opening up and
| | 05:26 | seeing the products in that particular category.
| | 05:30 | Some of the categories only have one
product in and some of them have two, some
| | 05:34 | of them have many more. But as you can
see, very easy to do, and we are going to
| | 05:39 | see how we start to
format this a little later on.
| | 05:42 | However, our first problem is that
this category number isn't very helpful,
| | 05:46 | isn't very readable.
| | 05:47 | We would really want to see a name
written here rather than a number, but that
| | 05:51 | information--the actual category
name--is stored in a separate table.
| | 05:55 | So next, we are going to see how
to start to join tables together in
| | 05:59 | Reporting Services.
| | Collapse this transcript |
| Joining data from multiple tables| 00:00 | So going from the end of the last
example, we have some grouping going on here,
| | 00:04 | but it's not as useful as it could be.
| | 00:07 | So if I were to look at the underlying
table definition, the issue that we have
| | 00:11 | is when we are looking at the
information for any particular product we are
| | 00:15 | getting a name--say, Water Bottle,
30 OZ--but we are also getting this
| | 00:19 | ProductCategoryID. It's just a number, 32.
| | 00:22 | And what we want to do is take it to a
separate table, ProductCategory, where we
| | 00:27 | can take that number of 32 and actually
get the name of the category, in this
| | 00:31 | case Bottles And Cages.
| | 00:32 | Well, the great thing is, as long as
there's a proper relationship defined in the
| | 00:37 | database--and there is here--it's very
easy for us to focus on what we want,
| | 00:42 | which is the name of the
product and the name of the category.
| | 00:46 | And actually, in this case, we can
let Report Builder pretty much figure
| | 00:50 | everything else out.
| | 00:51 | So back in Report Builder, I'm
going to switch back into Design view.
| | 00:56 | And really what I want to do here is back
things a bit and go back to the dataset.
| | 01:00 | So I actually don't need this table anymore.
| | 01:03 | So I'm going to delete it.
| | 01:03 | Now to make sure I delete it properly,
I first click somewhere inside it and
| | 01:07 | then when the gray bars appear, I'm
going to select the one at the very top-left
| | 01:11 | corner, which will give me the entire
selected table, and then I just delete that.
| | 01:16 | Now what I want to do is redefine the data set.
| | 01:19 | So I'm just going to double-click
the data set name to open that back out.
| | 01:23 | We don't need to change anything about
the data source, but we do need to change
| | 01:26 | something about the query.
| | 01:29 | I could manually change the select
statement, but I'm just going to go back into
| | 01:33 | the Query Designer. It's still
showing me my results from the previous time.
| | 01:37 | I'm just going to hide those by
clicking this button over here.
| | 01:40 | And we just want to focus on the
information that we are interested in, which
| | 01:44 | really, for us, is the name in the Product table,
| | 01:47 | I don't really care about
ProductCategory ID myself, and then from the
| | 01:51 | ProductCategory table, the name of that.
| | 01:54 | Now, Report Builder is actually smart
enough to know that if we have two fields
| | 01:58 | called Name it's not going to work
very well. There be a conflict there, so it
| | 02:03 | will internally rename them as Product
Name and Product Category Name, and it
| | 02:06 | knows that there's a conflict there.
| | 02:08 | So how is this going to work?
| | 02:10 | If you've written SQL, you know you
have to define the join statement
| | 02:14 | yourself, how these two tables are interrelated.
| | 02:17 | But in fact, a lot of that is
being done successfully for us.
| | 02:21 | I can test this by clicking Run Query.
And what I'm getting here is the Product
| | 02:25 | Name and the actual ProductCategory
Name. And as I come down a little bit
| | 02:29 | further, I'm getting different
categories with different products.
| | 02:32 | Now, if you're someone is used to
writing SQL, you might be thinking, hang on the
| | 02:36 | second; isn't there a bit
of a conflict going on here?
| | 02:39 | Well, let's find out.
| | 02:40 | Because just by selecting those fields,
I'm and become up to the top here and
| | 02:44 | click the Edit as Text button, which
will show me the actual SQL statement.
| | 02:48 | And what I'll see here is we are
selecting the name from the product table as
| | 02:53 | Product Name, and selecting the
name from the category table as
| | 02:56 | ProductCategory Name, and it's
generated the inner join statement that joins
| | 03:01 | these two tables together.
| | 03:03 | You see, the query designer here will do
a great job of detecting foreign keys in
| | 03:07 | your database as long as your actual
database has been well defined. And also, if
| | 03:13 | I untoggle this Edit as Text button,
what we want to make sure is that this Auto
| | 03:17 | Detect button in the Relationship
section is highlighted, meaning it's selected.
| | 03:23 | It's trying to detect the foreign keys,
and it will do really good job of that.
| | 03:27 | If the relationships you trying to
describe in your query are not actually
| | 03:31 | defined in the database, or there's
something non-standard, you will have to
| | 03:35 | write the joins yourself.
| | 03:36 | So I'm going to click OK and let's
say that is our dataset. And now I want
| | 03:42 | another table, so over to the Insert
tab and down to Table Wizard, we'll choose
| | 03:46 | that data set, click Next.
| | 03:49 | Product Name is what our Value is, and
ProductCategory Name is what we want to group by.
| | 03:55 | Click Next. And it doesn't really
matter which option we chose here.
| | 03:59 | I'm just going to leave the default,
click Next again, and I'll select Generic
| | 04:04 | so we don't have any colors or fonts
to worry about, and Finish, and just drag
| | 04:09 | this a little wider.
| | 04:10 | Then up on the Home tab, click the
Run button and let's check it out.
| | 04:14 | Now we actually have the name of the
category, so under Chains we see the one
| | 04:19 | Chain entry. Under Caps we
see the AWC Logo Cap entry.
| | 04:23 | So it's already become a lot more
readable, a lot more meaningful, and there's a
| | 04:27 | few missing pieces to it.
| | 04:29 | So next, let's see how to
manipulate the visual layout of this table.
| | Collapse this transcript |
| Formatting report elements| 00:00 | So far I've just let Report
Builder provide fonts and colors.
| | 00:04 | We can of course change what we are
seen here, but there are a couple tricks to doing
| | 00:07 | this successfully. So I am going to
switch back to Design view.
| | 00:10 | I'm in this previous example, the
products grouped by category layout, and this
| | 00:15 | was a table where I just used the
generic color scheme, which is as close to
| | 00:19 | neutral as you can get.
| | 00:20 | First let's take a look at the larger
report. Now, looking at it, I can see there
| | 00:24 | seems to be a difference between some
areas of regular static text, like the
| | 00:29 | title of the report here,
| | 00:31 | and then there are some areas that are
using the square brackets, like for product
| | 00:34 | category name and product name.
These are dynamic, meaning it will change when
| | 00:39 | the report runs and inject the
product name in the category name multiple
| | 00:43 | times. And a main clue for the dynamic
sections of the square brackets around them.
| | 00:48 | However, we can format any of this.
| | 00:50 | So lets I wanted to select the
title and changed the formatting here.
| | 00:54 | I can just grab and select the
text. An easier way is to click on the
| | 00:59 | surrounding part of the actual text box.
Sometimes it's a little difficult to grab.
| | 01:04 | The clue is if you have the grab handles
here we have got the entire thing selected.
| | 01:08 | You can of course use the Standard
home tab on the Ribbon just like in most
| | 01:12 | Office applications, but one thing
that's much more useful to have when you're
| | 01:16 | working with formatting is to make
sure that your Properties window is open on
| | 01:20 | the right-hand side. If it's not, you
can get it from the View tab of Report
| | 01:25 | Builder, just toggling it on and off.
And with that selected, we have a lot more
| | 01:30 | options than you'd just on the Home
tap. You can either view them in the
| | 01:34 | Properties window itself--things like
Padding and Spacing and so on--or you can
| | 01:39 | click this button here to open up the
Property pages for that selected element.
| | 01:44 | And you have a larger window here to
step through different formatting options
| | 01:47 | like Alignment and Border options and Fonts.
| | 01:51 | Towards the bottom, there are some
more complex options like Visibility and
| | 01:55 | Action, which we will get into later.
| | 01:57 | I'm just going to make basic change here
to the font and take that down to 14 points.
| | 02:02 | And that should change the title.
And although it's looked as if the table has
| | 02:07 | actually been matched right up with
the title, that's not really the case; the
| | 02:11 | table is considered an
independent visual element on this report,
| | 02:15 | and we can move it around
and format it as we see fit.
| | 02:18 | But when formatting a table, or any of
the other data regions that we will see,
| | 02:23 | you need to pay attention where you click.
| | 02:25 | So notice that if I click into one
section of the table I also get these
| | 02:30 | surrounding gray bars, and these
are really helpful when formatting or
| | 02:34 | manipulating a table. If I click in the
top I can select the entire column. If I
| | 02:39 | click on the left-hand side, I can
select the entire row, and I can apply
| | 02:43 | formatting to that entire row if want to.
| | 02:46 | If I select in the top-left corner, but
it will disappear, but I will have the
| | 02:50 | entire table selected.
| | 02:52 | And with those grab handles, I can
actually see that I could move that down and
| | 02:56 | into the middle of a report a little
bit; it's not actually tied to the title.
| | 03:00 | Now right now this table does
have a very basic row at the top
| | 03:04 | which is just text. It's the heading
for our category name product name, and I
| | 03:09 | can just type in here and change it,
because that's a little more than I need.
| | 03:13 | I can have this just say Category and Name.
| | 03:16 | But I can also click on the left-hand
side here to grab that entire row and if
| | 03:22 | I want to do something easy, just use
the Home tab of the Ribbon and set that
| | 03:25 | to bold and 12 points.
| | 03:27 | Other options I could have is to say
click in this area which is currently
| | 03:30 | showing Product Name, and with that
selected, either use the Property pages or
| | 03:35 | just the Property window itself and say
change the background color. We'll just do a
| | 03:39 | light gray here just to prove the
point. Go over here and run it, and we are
| | 03:43 | starting immediately to see any of the
changes that I've made. It looks like I could
| | 03:47 | really move this title around a bit,
| | 03:49 | move it something towards at the center,
and you actually see as well when you're
| | 03:53 | moving things around you will see the
lines appear to help you line them up with
| | 03:57 | other elements on the page.
| | 03:58 | Go ahead and run this. It looks a bit more
presentable. And notice that when I open this up
| | 04:03 | we are actually seeing the
background color of the product name elements.
| | 04:06 | Now right now you probably noticed that
is that kind of missing row for each of
| | 04:11 | these entries, and the idea here is
that you might be putting in some kind of
| | 04:15 | aggregate function, a count or total of something.
| | 04:18 | And we are going to see how to do that
a little later on and of course see a
| | 04:22 | bunch more formatting options,
| | 04:24 | because as we go through the course,
will see things like working with page
| | 04:27 | breaks and working with page width
issues, but we really need a few more visual
| | 04:31 | elements to add to our toolkit first.
| | Collapse this transcript |
| Using functions in a report| 00:00 | Databases are great for storing our raw data,
but usually raw data isn't all we want to see.
| | 00:06 | We want to see calculated values on our reports.
| | 00:09 | We want to see the total sales for
last week, or the average daily bug
| | 00:12 | submissions for this quarter versus
the same quarter last year, or perhaps the
| | 00:16 | number of products we have right
now with a status of Out of Stock.
| | 00:20 | But this kind of information, these
kinds of numbers, would not and should not be
| | 00:24 | stored in the database directly
because we can figure them out on the fly.
| | 00:28 | We can calculate these numbers,
and we will use functions to perform
| | 00:32 | these calculations.
| | 00:33 | Common functions including totaling or
summing numbers together, counting them,
| | 00:37 | averaging them, minimum
or maximum, first and last.
| | 00:41 | So let's say I want to create a
report in Reporting Services with the total
| | 00:45 | sales for last week.
| | 00:47 | Now, I could get to this total and add
these numbers together in two different places.
| | 00:51 | First, when I write the dataset or
query or SQL I use a function that asks the
| | 00:57 | database to total up all those
individual amounts and just return with one value
| | 01:02 | to the report, and then all the report
does is spread that single value out.
| | 01:06 | But another option is I don't ask the
database engine to do that calculation.
| | 01:11 | I ask it to return all the values to
the report, and I ask the report to do it,
| | 01:16 | where the query itself is simpler.
| | 01:18 | We get all the numbers back and
in the report we total them up.
| | 01:21 | So which way is better?
| | 01:23 | Now you might think that it's a coin toss;
| | 01:24 | it doesn't matter. Or if you are a
developer, you're probably tempted to say it's
| | 01:29 | best to do it all in the SQL, all in the query.
| | 01:32 | Let the database engine do the heavy
lifting. But actually, in Reporting Services
| | 01:37 | it's often preferable to do this kind of
calculation in the report itself, because
| | 01:41 | we might want that report to not only
show the total but to also be able to
| | 01:46 | drill down into the individual data.
| | 01:48 | We can't do that if all we are
fetching from the database is one number.
| | 01:51 | But I am getting ahead of myself.
| | 01:53 | So let's see an example.
| | 01:54 | So I am in my Reporting Services
Report Manager. I am going to click Report
| | 01:59 | Builder and create a new report.
| | 02:01 | Like most of them that I'm doing
early on, I will just use the Blank Report
| | 02:05 | option here. And the first thing I
need of course is a data source.
| | 02:08 | I am going to point this
to AdventureWorks light.
| | 02:11 | It will be, again, a connection
embedded inside this one report.
| | 02:15 | Selecting SQL Server, I'll use the
Build button and point it to my SQL Server,
| | 02:21 | which I am just going to use AdventureWorksLT.
| | 02:26 | And now create a dataset.
| | 02:27 | What is the data that I am interested in here?
| | 02:30 | Well for this example I am just
interested in sales information, sales totals.
| | 02:34 | So I am going to call this dataset SalesInfo.
| | 02:36 | It would be embedded in the report.
| | 02:39 | We will use the data source I just created
and then I will jump into Query Designer.
| | 02:44 | Now in the AdventureWorksLT database,
that information I'm looking for would be
| | 02:48 | in the Sales Order Header table, and for
the purposes of this example, all I am
| | 02:53 | really interested in is the TotalDue.
| | 02:56 | So selecting that one column, I could
run the query here and we can see some of
| | 03:00 | the results coming out here.
Sure, normally I'd have more data than this.
| | 03:04 | But right now I am staying super
focused on using a few different functions, and
| | 03:08 | I can perform several just from these results.
| | 03:10 | I could get a total by
adding up all these values.
| | 03:13 | I could count all the rows returned to get the
total number of orders, and I can average them.
| | 03:18 | So I am just going to be done with this option.
So I will click OK and my dataset is done.
| | 03:23 | So now I'm going to do
something with the reports.
| | 03:26 | I will just give it a
title here, of Using Functions.
| | 03:30 | And instead of adding a table, I am actually
just going to add three pieces of information.
| | 03:34 | First, what I am going to do is
have a label just to show what they are.
| | 03:37 | So on my Insert section of the Ribbon,
I am going to grab a normal text box here
| | 03:43 | and with that selected, I should have
little crosshairs that I can come down and
| | 03:47 | just draw an area by clicking
and dragging on this layout.
| | 03:50 | I am going to show the total sales,
and I am also going to show the number of
| | 03:55 | orders and the average order amount.
| | 03:59 | Again, this right now is just a label.
| | 04:01 | I am also going to improve the font a
little bit here, take it up to, say, 20.
| | 04:06 | Align this on the right-hand side.
| | 04:08 | I am going to drag it a little bit
wider and as I do that, it will move out the
| | 04:12 | actual size of the page, so
we can see it properly here.
| | 04:15 | In fact that font is a little big.
I will just drop it down to 18 and that will work.
| | 04:19 | Now, I need some actual values that
would be calculated when this report is run.
| | 04:24 | So where do I get them from?
| | 04:25 | If I look over here in the Report Data
section in Report Builder, I can see that
| | 04:29 | my data set is showing up as SalesInfo
with just one field here, of TotalDue.
| | 04:35 | So what actually happens if I drag
that over, because you can drag from
| | 04:41 | the Report Data panel?
| | 04:42 | Now, do be careful if you are dragging
over existing elements in the report
| | 04:46 | layout, like text boxes here; it can
often upset things, so it's often a
| | 04:50 | better idea to kind of move it around so you
don't move over the top of another element.
| | 04:55 | So I am just going to let go.
| | 04:56 | And it's dropping in a textbox here
with this little Expr for expression.
| | 05:01 | So what? What does that actually going to do?
| | 05:05 | After all, if I just dragged on one
column name, is it going to repeat them all?
| | 05:09 | We could have a thousand
different orders so what will this show?
| | 05:11 | Well let's find out.
I am going to go up here and click Run.
| | 05:15 | Now actually here, it's showing here
the Total Sales 956303.5949, and that
| | 05:20 | actually is the total amount of orders.
| | 05:23 | It's adding all those
total due amounts together.
| | 05:26 | The question is, why? Why did it do that
rather than just write out the first one
| | 05:30 | or right out the last one or just
write out some random single value?
| | 05:34 | Well, let's find out.
| | 05:35 | Going back into Design view
I am going to highlight that.
| | 05:38 | And there is a couple of ways I could
get to this, but with that selected, I am
| | 05:42 | going to come over here to my
Property Pages and open that up,
| | 05:45 | because here is where we get the
easy way of looking at the value.
| | 05:48 | It's saying it's getting it from an expression.
| | 05:50 | And if I click the little Expression
Builder button here, I can see that we have
| | 05:54 | got this value being written in this
expression, this calculation, which says
| | 05:59 | =Sum(Fields!TotalDue.Value, "SalesInfo").
| | 06:04 | Okay, the format might look a little
weird here, but what we are basically saying
| | 06:08 | is add together all the values in that
Total due column for the dataset called
| | 06:12 | SalesInfo, and it's this word, Sum,
that is summing them all together.
| | 06:17 | This is Report Builder's best guess of
what I probably want, seeing as I'm not
| | 06:21 | using a table here to repeat multiple columns.
| | 06:24 | So this actually works in this case.
| | 06:27 | Well, what if I wanted something else?
| | 06:27 | What if I wanted the average
instead, or the count of them?
| | 06:31 | Well, I can always use that as a starting point.
| | 06:33 | So I am going to grab the TotalDue again
from my Report Data section and drag it
| | 06:38 | over here and put it
beside, say, Number of Orders.
| | 06:41 | Now, right now this will
do exactly the same thing.
| | 06:44 | If I clicked into the Run mode, we'd
see the same number. But I can use this as
| | 06:48 | a usual starting point.
| | 06:49 | I'll grab this one, open up the
Property pages, and open up the Expression
| | 06:53 | builder for the value. And instead of Sum,
I'm going to just change that to Count.
| | 07:00 | Now, how do I know what this is going to do?
What's the difference between Sum and Count?
| | 07:03 | Well if you have been working with SQL,
you are probably familiar with Sum and
| | 07:07 | Count, but if you are not, Sum is the
total. It will add all the individual
| | 07:11 | numbers together and count will just
add the number of rows together. How many
| | 07:16 | Total Due values do we have?
| | 07:17 | It's not interested in what's in each one;
it's just going to count them all together.
| | 07:21 | Now, if I look down below here in the
Expression Builder, I can see that there is
| | 07:25 | a whole bunch of different options.
| | 07:26 | We have things like datasets that
are available here. We have Variables.
| | 07:31 | Well, there is nothing in here,
Fields, Built-in Fields like PageNumber,
| | 07:35 | TotalPages, ReportName, and so on.
| | 07:38 | And there is a list of common functions.
The most typical ones we are using with
| | 07:43 | data are in the Aggregate or Grouping functions.
| | 07:46 | This is where we have the option for
Averaging, Counting, First, Last, Maximum,
| | 07:50 | Minimum, Summing, and so on, and even
some examples here of how you might use
| | 07:55 | them in the expression itself.
| | 07:57 | You could type these in or you can
actually double-click any of them and it will
| | 08:01 | jump them up into this Expression Builder area.
| | 08:04 | We are going to see more about expressions
later on, but for right now this will do.
| | 08:08 | I am saying =Count instead of =Sum.
| | 08:11 | I click OK, I click OK, and we run this again.
| | 08:15 | Now it's saying Total Sales,
956303, Number of Orders is 32.
| | 08:20 | Well, let's add one more to
add the Average Order Amount.
| | 08:23 | And instead of dragging and
dropping, I will show a different way of
| | 08:26 | putting this one the page.
| | 08:27 | I'm going to jump over to my Insert section,
insert a text box, and with that cross-
| | 08:33 | hairs, I am just going drag in
a place that I want this to be.
| | 08:36 | This is the Average Order Amount.
| | 08:38 | And if I was confident enough in my
expression building, I could actually just
| | 08:42 | type in that expression directly in
here. And if I click off, we are actually
| | 08:47 | going to see that change the expression.
Let's see if that worked.
| | 08:50 | And that seems to be about
right, regarding about 29894.
| | 08:55 | So I can't type that directly in there,
but again, I could just select that and
| | 08:59 | use my Property pages to get to that
expression if I wanted to construct it
| | 09:03 | myself inside this window.
| | 09:05 | And while you're learning how to work
with expressions, it's often not a bad
| | 09:08 | idea to drag something on here to use
as a starting point and then change it
| | 09:13 | rather than invent it all from scratch.
| | 09:15 | This is still looking a little bit
ugly, so let's do a bit of alignment
| | 09:19 | and reformatting here.
| | 09:20 | I am going to grab these three text box areas
and just move the font up a bit to, let's say 16.
| | 09:26 | I may have drag them a little bit wider
just so that number will fully appear.
| | 09:31 | Also in the Home tab, I should be able
to align the left-hand side of them and I
| | 09:35 | will select to left align them as well.
| | 09:39 | The main thing that we are missing
here in terms of formatting is that the
| | 09:41 | numerical ones here look pretty ugly
here. We have got total sells 956303.5949.
| | 09:47 | This is meant to be some kind of currency.
| | 09:50 | Well back into Design view, what I am
going to do here is--grabbing this to get
| | 09:53 | myself a bit more room--
| | 09:54 | is select that first one which
should be a dollar amount and from the Home
| | 09:59 | section of the ribbon,
| | 10:00 | here I have got this Formatting section.
How is this being presented? And right
| | 10:04 | now it says Default. What I
am going to select is Currency.
| | 10:07 | I am also going to select that for
the third one, which is the Average Order
| | 10:11 | Amount. From Default to
Currency. Run that again.
| | 10:15 | And now we have something
that's a bit more presentable.
| | 10:18 | Okay, I could tidy this up a bit more
with alignment, but this should do the
| | 10:23 | trick for right now.
| | 10:24 | If I wanted to do more with the
formatting, I could do a little bit more with any
| | 10:28 | of these selected. If you start to
mouse over the different buttons here, you
| | 10:32 | can see things like placeholder styles.
What that would change it from is this
| | 10:36 | Expr to, for example, Sample Values, 12,345.00.
| | 10:42 | And then I can start manipulating
things like increasing the decimal point or
| | 10:45 | decreasing the decimal point, and I am
getting a little preview of how that
| | 10:48 | would actually look here.
But this should do the trick for right now.
| | 10:52 | As we go forward, we will start
combining these functions with more complex
| | 10:56 | report elements like tables and matrices.
| | Collapse this transcript |
| Displaying data in a matrix| 00:00 | When we have repeating data in our
report, when we know that we have multiple
| | 00:04 | rows of data to show, we're going to end
up using one of three different options
| | 00:09 | here in Report Builder--
| | 00:11 | the Table, the Matrix, and the List--
and these are known as data regions.
| | 00:15 | So what's the difference?
| | 00:16 | Well, the list is the simplest. This is
just a free-form region that will repeat
| | 00:21 | whatever you drag and drop into
it, and we'll see that one later.
| | 00:24 | Tables and matrixes, or matrices, are
more common, more interesting, and more
| | 00:29 | configurable. But whether you select a
table, a matrix, or a list, you're really
| | 00:33 | using the same thing.
| | 00:34 | It's an element called a tablix.
| | 00:36 | So working with these is all very similar.
| | 00:38 | I've already shown a couple of table
examples, and what I'm looking at right now
| | 00:42 | is the Products By
Category report I created earlier.
| | 00:45 | It doesn't really matter.
| | 00:46 | There's nothing special about this one.
| | 00:48 | But if I grab this table by clicking
anywhere inside it and then clicking that
| | 00:52 | top-left section, I can actually see
that this comes under the name of Tablix,
| | 00:56 | and if I open up the Property Pages,
I see it's Tablix Properties, not Table,
| | 01:01 | because it's the same element
for tables, matrixes, and lists.
| | 01:05 | So what does it mean to be a table?
| | 01:06 | Well, when I'm in Design view and I
defined this report, I decided how many
| | 01:11 | columns were shown in it, in this case 2--
| | 01:14 | Category and Name--
although it could have been more.
| | 01:16 | But the columns are fixed.
| | 01:18 | They're static, but table rows are dynamic.
| | 01:21 | What I mean by that is when we run this,
the columns will always be two, but the
| | 01:26 | rows will differ depending on
how many pieces of data we have.
| | 01:29 | It might be a dozen;
it might be a thousand.
| | 01:32 | So the table will expand vertically
down the page based on how many rows have
| | 01:36 | been returned from the database,
but it's always two columns.
| | 01:40 | A matrix, on the other hand, is dynamic both ways.
| | 01:44 | It can expand both vertically and
horizontally, allowing us to cross-reference
| | 01:49 | and drill into the data.
| | 01:51 | If you're a Microsoft Access or Excel
user, it's similar to a cross-tab or a
| | 01:55 | PivotTable; but if those terms mean
nothing to you, no problem. Let's see an
| | 02:00 | example of a matrix.
| | 02:01 | So I'm going to leave this report
alone and just click Report Builder to make
| | 02:05 | a new one. Again, Blank Report for right now,
because we don't need to use any of the wizards.
| | 02:10 | I'll just give this a title, Matrix
Example, and of course repeating this
| | 02:16 | general process of adding a data source.
| | 02:19 | This will be embedded in the
report, and I'm just going to point it to AdventureWorksLT
| | 02:23 | and create the dataset to
define the data that I want.
| | 02:32 | Well, again, what I'm going to
go for is Products information.
| | 02:36 | This will be embedded inside the report.
| | 02:38 | I'll use the data source we just created.
| | 02:41 | And in the Query Designer I'm going to
drill down into the Products table and
| | 02:45 | I'm just going to go for two
pieces of information here:
| | 02:47 | the Name and the Color.
| | 02:50 | Close that down, and then from the
ProductCategory Table I'm going to go for Name.
| | 02:54 | Based on what we talked about earlier,
this should join the two together.
| | 02:58 | If I run the query, it certainly
looks about what I would expect to see.
| | 03:02 | We've got a product with a
color in a particular category.
| | 03:05 | As I start to scroll down, we should
see different categories showing up.
| | 03:09 | If I wanted to verify the SQL itself,
I can click the Edit as Text button and
| | 03:13 | see where it's doing the inner
join between those two tables.
| | 03:16 | Now, I'm purposely making this
dataset as basic as possible, because it's
| | 03:22 | really common to see matrix
examples to go completely over the top with
| | 03:26 | massive complex data, and we really don't
need that just to understand the idea of a matrix.
| | 03:31 | So just having our Product Name,
Product Color, and Product Category, that will
| | 03:34 | give us enough to work with, because I
could show this same data in a table or
| | 03:40 | in a matrix, and in fact it's
quite useful just to compare the two.
| | 03:43 | So, very quickly, I'm going to jump to
the Insert tab and just go through and
| | 03:47 | create a table, then
delete it, and then do a matrix.
| | 03:50 | Table, I would base from the dataset.
| | 03:53 | I could drag my Product Name into
the values and Color into the values.
| | 03:57 | Drag ProductCategory into the Row groups.
| | 03:59 | We've already seen this.
| | 04:00 | This is how I would group
things together in a table.
| | 04:03 | I'll just accept the default options
here, Finish, drag that a little wider so
| | 04:08 | we can see it, and test it, because I'm
going to delete this in just a second.
| | 04:12 | So this is the Dataset being
presented, split up into categories.
| | 04:16 | I can expand each of these.
| | 04:18 | If there's a color for it, it will show up;
if there is not, it won't.
| | 04:22 | So here's a basic Table idea. But
we've already seen this, so what's the
| | 04:26 | difference between this and a matrix?
| | 04:28 | Back over into Design view.
| | 04:30 | I'm going to grab this table first by
clicking anywhere in it, click the top
| | 04:34 | corner, and then hit Delete, and now
I'm going to go up to the Insert tab and
| | 04:38 | actually select the Matrix Wizard
option instead of the Table Wizard one.
| | 04:42 | Actually, here's a secret.
| | 04:43 | There is absolutely no
difference between the two;
| | 04:46 | both the Matrix Wizard and Table Wizard
give you the same window, which is the
| | 04:50 | New Table or Matrix.
| | 04:52 | So again, I'll do exactly the same thing.
| | 04:54 | Choose the Dataset, just as I
did a moment ago. Click Next.
| | 04:57 | This looks exactly the same because it is.
| | 05:00 | The only difference between a table and a
matrix is do you have something in Column groups?
| | 05:06 | Do you have something that will allow
this to expand not just up and down,
| | 05:10 | but left and right?
| | 05:11 | Well, what I'm going to do here is drag
on the ProductCategory_Name into the Row
| | 05:16 | group, so grouping them together by
Category, which is what we did before.
| | 05:19 | I'm going to drag the
Product Name into the Values.
| | 05:22 | I want to see the Product Name there.
| | 05:24 | And I'm going to drag Color, instead
of into Values, which is what I did a
| | 05:27 | moment ago, but into Column groups.
| | 05:30 | This is now a matrix.
| | 05:32 | If there's something in Column
groups, it can expand left and right.
| | 05:36 | However, it won't work yet, and if I try
and click the Next button, I'm going to get an error message.
| | 05:42 | If one or more column groups is defined--
meaning if you're making a Matrix--all
| | 05:45 | the fields in the Values section must
have an aggregate function specified.
| | 05:50 | So what does it mean by this?
| | 05:52 | Well, if we're making a matrix, the
assumption is we're trying to not show every
| | 05:56 | single miniscule piece of data.
| | 05:58 | We actually want to
aggregate or group them in some way.
| | 06:02 | Something needs to be totaled or
counted or averaged, because we're trying to
| | 06:07 | cross-reference this information.
| | 06:09 | So I might be wanting to see sales
totals broken down by region and by
| | 06:13 | salesperson. Or in this case I'm trying
to allow myself to view these products
| | 06:18 | broken down either by category or by color.
| | 06:21 | So I'm providing an aggregate function for this.
| | 06:23 | And let's say I'm going to just count them.
| | 06:26 | So from this dropdown arrow, I can
select one of the common aggregate functions.
| | 06:30 | And in fact, what I'm going to select is
Count, to just count the number of products.
| | 06:36 | What this will allow us to do is
drill into this data in two ways.
| | 06:40 | How many products are in each category,
down the left, counting the numbers of
| | 06:44 | it, and then how many products in each
color, along the top, and again giving
| | 06:48 | me the numbers for it.
| | 06:50 | And I'll get totals of all of these,
plus a full cross-reference between them,
| | 06:53 | and that's a matrix. So, click Next.
| | 06:56 | I'm going to accept the options here,
Show subtotals and grand totals. Click Next.
| | 07:00 | And I'll just select a different style.
| | 07:02 | Let's go with Slate and Finish. That's it.
| | 07:06 | Doesn't look very different, but
remember, this is going to expand up and down
| | 07:10 | and left and right based on the data.
| | 07:13 | So when I run this we'll see a
very different look and feel here.
| | 07:16 | So I can go down the left-hand
side to see the grouping by category.
| | 07:21 | In the category forks we have 3 options,
and the total is representing--and no
| | 07:26 | color has been actually
added to them--a total of 3 here.
| | 07:29 | If I come down a little bit, say into
the Mountain Bikes section, we've got 16
| | 07:34 | showing up, in this column 16 here,
with a total of 32. Again, following
| | 07:39 | them up, we can actually drill down and
see the amount of products in each of the
| | 07:43 | column categories as well, and the full cross
-reference between them. This is a matrix.
| | 07:49 | We can take the same data, display
it as a table, display it as a matrix.
| | 07:53 | You can take it deeper.
| | 07:54 | You can have multiple levels of
grouping, multiple ways to drill down inside
| | 07:59 | this information, multiple row groups
and multiple column groups, and it will
| | 08:04 | support all of that in Reporting Services.
| | 08:06 | But before we get to that,
we're going to see how to add some more
| | 08:10 | interactivity into our reports.
| | Collapse this transcript |
|
|
2. Adding InteractivityFiltering data and adding parameters to a report| 00:00 | So we've started grouping together
data in a couple of different ways--
| | 00:04 | matrixes and tables--but we
haven't filtered anything yet.
| | 00:06 | We haven't restricted the amount of
rows we're bringing back from the database
| | 00:10 | from any of these tables, so let's do that now.
| | 00:13 | Creating a new report. You should be
getting familiar with this general process.
| | 00:17 | We first need the data source.
| | 00:18 | And I do this multiple times because
it is a good idea to start to get very
| | 00:25 | familiar with the different things you're
asked for every time you create a data source.
| | 00:30 | We will get into sharing them a
little more further down the line.
| | 00:34 | So I'll connect to my SQL 2012
Server and go to AdventureWorksLT.
| | 00:38 | It doesn't really matter which one I'm going to;
| | 00:40 | I just need some data to play with.
| | 00:42 | Back into Datasets, let's add a new one.
| | 00:46 | I could be descriptive with this.
| | 00:48 | I want to show products, but not just
all products, products by category. The name
| | 00:53 | of course does nothing special, but it
will allow us to read it later on and
| | 00:57 | realize what this is meant to represent.
| | 00:59 | So selecting the Data Source
I'll jump into Query Designer.
| | 01:05 | Drill down into the Product Table.
| | 01:07 | I'll go for ProductID and
Product Name and Color and ListPrice.
| | 01:11 | We could run this query just to see what
we'd get back, and of course it's going
| | 01:15 | to be everything at this moment, but
I want to restrict them by category.
| | 01:19 | So what I'm going to do is in
this Filter section apply a filter.
| | 01:22 | If I come over to this little Filter
icon here, Add Filter, I'll click it, it
| | 01:27 | asks, what do I want to filter on?
What column am I restricting my information
| | 01:32 | based on? And it's dropped in with
ProductID, but the one I'm actually interested
| | 01:36 | in will be a CategoryID,
so I'll select ProductCategoryID.
| | 01:41 | We have this Operator option,
| | 01:43 | where it is, where it is not, is any of,
none of, more than, less than, and so on.
| | 01:48 | I'm going to say ProductCategoryID
equals, or is, double-click in that area, 35,
| | 01:55 | and then let's test that.
| | 01:56 | I run the query this time and I only get
three results back, because I'm getting
| | 02:00 | the products that are in
that particular category.
| | 02:02 | And sometimes this is all you need is
this very literal fixed restriction, but
| | 02:08 | there will be a lot of cases
where you want to change it.
| | 02:11 | So first, just show me the products in
the Mountain Bikes category, then show me
| | 02:15 | the products in the Hat
category or the Bags category.
| | 02:18 | So when we want to do that, we need to
use parameters, so that this value can be
| | 02:22 | changed when we run the report, and doing
that in the query, in the dataset, very easy.
| | 02:28 | I can just come along here and check that
Parameter checkbox, click OK, and I'm done.
| | 02:34 | Now, if you're curious and you like
reading your SQL, I can see that what I've
| | 02:37 | got here is a regular WHERE
clause in my select statement.
| | 02:41 | We're selecting those columns
from the Product table as long as the
| | 02:45 | ProductCategory ID is equal to, and
here we're using this @ProductCategory ID.
| | 02:51 | That is the name of a parameter that
we can then change when this report runs.
| | 02:57 | Okay, so where's that parameter?
| | 02:59 | Well, let's go see. I'm going to come out of this dataset,
just click OK. And over now in my Report
| | 03:05 | Data section in Report Builder, if I
expand the Parameters folder, I should have
| | 03:09 | a parameter showing up here, ProductCategory ID.
| | 03:12 | Okay, what does this actually mean?
| | 03:14 | Well, first, I better have something
on my report to allow us to see this.
| | 03:18 | First, just give it a title, Parameter
Example, and I'm going to insert a basic table.
| | 03:22 | Use the Table Wizard.
| | 03:23 | We're going to fuel it from the
ProductsByCategory dataset. Click Next.
| | 03:28 | And I'm going to go really simple here.
| | 03:30 | I'm just going to select
ProductID, Name, Color, ListPrice.
| | 03:35 | I'm not even going to bother grouping
them in rows or grouping them in columns.
| | 03:39 | I could do, I just don't need to for
what I'm trying to illustrate here, which
| | 03:42 | is just restricting the rows we
bring back by the category. Click Next.
| | 03:47 | I have no Layout options because I'm
not doing any grouping, and then I'll just
| | 03:51 | pick, say, Corporate, Finish, and we're done here.
| | 03:55 | Drag that a little wider, and I'll
run this to preview this report.
| | 03:58 | So because I filtered on 35,
that is considered the actual default
| | 04:03 | ProductCategoryID. I can see up here
that just by having a parameter, I have
| | 04:07 | this place I can now type into in the report.
| | 04:10 | It's got 35 selected.
| | 04:12 | I could type in 21 and press Enter
and now I get all the products in the
| | 04:16 | category of 21, or 22.
| | 04:19 | If put in a category that
doesn't exist, I'll get nothing.
| | 04:24 | So just the fact that we have that
parameter defined, Reporting Services is
| | 04:28 | giving us this area at the top we can type into.
| | 04:32 | Now, of course I'm just in the preview
of Report Builder right now, but to prove
| | 04:36 | that this works on the website itself,
we'll switch back into Design view and
| | 04:39 | I'll hit the Save button, and
I'll save this to the Report Server.
| | 04:43 | I'll just call this Parameter_Example.
| | 04:46 | You don't need to use
underscores in your naming.
| | 04:48 | You can use spaces.
| | 04:49 | I just prefer underscores.
| | 04:51 | If I switch back to the Report Manager
website and just refresh that, we can see
| | 04:55 | the Parameter_Example one here.
| | 04:57 | If I click into that to view it on the
website, we have the same idea up here,
| | 05:02 | the area that we can type into.
It's coming up with the default of 35 because
| | 05:06 | that was defined in the dataset, but I
can change that to anything else, like
| | 05:10 | 21, and in this case either quick
Return or I could click View Report
| | 05:13 | explicitly to refresh that.
| | 05:15 | And this works fine.
| | 05:17 | Okay, it's not the most friendly page.
| | 05:19 | Right now we need to know the numbers for these
categories to be able to go to a particular one.
| | 05:24 | I'd have to know what 21
represents, or 22, or 25.
| | 05:28 | So next, we're going to see how to
change the appearance of this Parameter area
| | 05:32 | and make it more helpful.
| | Collapse this transcript |
| Customizing report parameters| 00:00 | When you start to add parameters to
your report you can actually have multiple
| | 00:04 | parameters and they will all show
up in this parameter area at the top.
| | 00:08 | But most of the time you want something
more than just an open text box to type
| | 00:12 | into, and we can change the appearance
of this based on the parameter definition
| | 00:16 | in our Report Builder.
| | 00:17 | Now, if you added the parameter through
the Query Designer, it will automatically
| | 00:22 | show up in that section, but you
can also create your own parameters.
| | 00:26 | I can see here that if I right-click on
the folder we can actually choose to add
| | 00:30 | a new parameter, give it a name, set its values.
| | 00:32 | But I don't need to do that right now.
| | 00:34 | Either way, when I have a parameter, I
can actually double-click it in the Report
| | 00:38 | Data section and I'll open up the
Property pages for this parameter.
| | 00:42 | For example, it's telling
me this is ProductCategoryID.
| | 00:45 | It's a data type of an integer.
| | 00:47 | I have options here to change it to a Date/Time.
| | 00:50 | If I changed it to that, we'd see it
show up as a date picker in that top
| | 00:53 | section of the report.
| | 00:55 | If I selected Boolean, it would show
up as a checkbox in that top section.
| | 00:59 | But it actually is an integer.
| | 01:01 | It is a numeric value.
| | 01:02 | I just don't want to look at that.
| | 01:05 | And I have other options
for what actually shows up.
| | 01:08 | In the Available Values section here,
we've currently set it to None, which means
| | 01:12 | we don't provide any values; the
actual user must type them directly in.
| | 01:17 | I can choose to manually specify them
and create different values for both the
| | 01:21 | label--meaning what the user will
actually--see and the value--what will actually
| | 01:25 | be represented behind the scenes.
| | 01:27 | What's even better is this option,
that I could get values from a query.
| | 01:32 | I could create another query and get all
the category names and all the category
| | 01:37 | IDs from a different part of the database.
| | 01:39 | In fact, that's what I'm going to do.
| | 01:41 | I don't have that data set right now,
so what I am first going to do is cancel
| | 01:44 | out of this and add a
new dataset to this report.
| | 01:48 | So I'll right-click my Dataset folder/
Add Dataset, and I want to create a new
| | 01:52 | one called CategoryNamesAndNumbers.
It will be embedded in the report.
| | 01:57 | I can just use the same data
source I already have defined.
| | 01:59 | We are talking to the same
database. That's just fine.
| | 02:02 | Then I jump into the Query Designer.
And all I need to do here is drop down into
| | 02:07 | my ProductCategory table, which is right
here, and all I am interested in is the
| | 02:12 | number of the ProductCategoryID and the name.
| | 02:15 | That's pretty much it.
| | 02:16 | To test this I can run the query,
and yup, that looks about right.
| | 02:20 | Click OK, click OK.
| | 02:22 | That dataset is now defined,
and I can use it in my parameter.
| | 02:26 | So back into the parameter
definition, double-click on this guy.
| | 02:29 | I am going to jump over into the
Available Values section and tell it to get its
| | 02:35 | values from a query.
| | 02:36 | It asks which dataset.
| | 02:38 | Well, the one we just created, the
CategoryNamesAndNumbers. And then I simply
| | 02:42 | have two choices. What are the
actual values you're interested in?
| | 02:45 | What am I going to feed to the
product table so I can filter that data?
| | 02:49 | Well, that actually needs to be the
number, the ProductCategoryID number.
| | 02:54 | But being more friendly to the end user, what
the user sees can be the name of this category.
| | 03:00 | I'm not going to go ahead
and change the default values.
| | 03:02 | If I clicked here, it says specify values as 35.
| | 03:06 | That's because I defined in
the dataset. That's fine.
| | 03:08 | I will just leave it alone.
| | 03:10 | In the Advanced section are some
options for, do I always refresh automatically
| | 03:14 | when this value is changed?
| | 03:16 | I'm going to leave all of
that as is and just click OK.
| | 03:19 | It doesn't look any different here.
| | 03:21 | Well, that's not surprising.
| | 03:23 | I can just go ahead and click Run to
preview this, and what I have up at the top
| | 03:27 | has completely changed.
We no longer have a text field to type into.
| | 03:30 | We have a dropdown list with all the
friendly names of the categories here.
| | 03:34 | So I can select Jerseys, click View Report to
refresh that data, and see the Jerseys here.
| | 03:40 | I can come down and click Mountain
Bikes and either click the button to View
| | 03:45 | Report or press Enter to
refresh that data again.
| | 03:47 | It looks like what I could do with
now is starting to apply a little bit of
| | 03:51 | formatting here: Product ID doesn't
need to be as wide, Name could be a bit
| | 03:55 | wider, and List Price
should be formatted as Currency.
| | 03:58 | Again, doing that kind of
thing is pretty easy stuff.
| | 04:02 | When you're resizing columns,
it's best to grab the gray bar at the top.
| | 04:05 | If I want to change this Sum(List Price),
then what I do is actually click on
| | 04:10 | the text itself until it's highlighted.
| | 04:12 | I can see now here the Number
formatting section is showing up.
| | 04:15 | Change that to Currency and run it,
and that's a bit more presentable.
| | 04:20 | If you want to provide parameters in
your report but not always show them to
| | 04:24 | the end user, that is possible to do as well.
| | 04:27 | Jumping back into Design view and
editing that parameter, I will see that on the
| | 04:32 | first page there are some options for
whether this parameter is always visible
| | 04:35 | or whether it's hidden or internal.
| | 04:37 | One of the reasons you might want to
do this is that you as a report designer
| | 04:41 | may occasionally want to change the
default value of this or the parameter of
| | 04:45 | this every week or every month, but you
don't want to give that option to every
| | 04:49 | single user, every single
person who views that report.
| | 04:52 | And there are even ways to make these
parameters conditionally appear depending
| | 04:56 | on who's looking at it or
on other data on the page.
| | 05:00 | But this is how we begin
to parameterize our reports.
| | Collapse this transcript |
| Sorting data in a data region| 00:00 | We've seen how to filter our
datasets and even apply parameters to them.
| | 00:03 | What we haven't talked about yet is how
to sort our data, so let's see that now.
| | 00:07 | I am in Report Builder, where I've
just created a new blank report, and all I
| | 00:12 | have done is define a simple data source
pointing to the regular AdventureWorks database.
| | 00:17 | I'll give this report a simple title,
and I'm now going to create the dataset.
| | 00:22 | This will be embedded in my report, and
what I'm going to make it do is point to
| | 00:27 | the person table that's in the
AdventureWorks database, just bringing back a
| | 00:31 | list of people. It really
doesn't matter what data we have.
| | 00:34 | I just need something to
illustrate these sorting concepts.
| | 00:37 | So I'm going to jump into the Query
Designer, drill down into that Person table
| | 00:43 | and select a few pieces of data.
We'll go for FirstName, LastName, and I'll also
| | 00:48 | select rowguid, which is the generated
unique identity for this row, just so we
| | 00:53 | have some other piece of data to take a look at.
| | 00:55 | Now, we've seen that in the Query
Designer window there are ways we can apply
| | 01:00 | filters to the data and even parameterize it.
| | 01:03 | There are ways we can auto detect
relationships, and even up here which shows
| | 01:07 | the fields I have selected if I wanted
to apply an aggregate function, I could
| | 01:11 | actually do that right here. But there
doesn't seem to be a way to sort that data.
| | 01:16 | So I can run this query to test it,
and I'm really at the mercy off how the
| | 01:21 | database is internally structured.
| | 01:24 | Here I don't have this information
coming back in any kind of a meaningful
| | 01:28 | sort order. That's more to do with the
indexes that are defined on this table
| | 01:33 | than anything else.
| | 01:34 | So what if I wanted to have this sorted by
first name ascending or last name descending?
| | 01:39 | Well, if you're familiar with SQL, you
know that's pretty simple to do, so you
| | 01:44 | might be tempted to add it yourself.
| | 01:46 | One of the ways I could do is just
click the Edit as Text button up here and I
| | 01:50 | could be tempted to just add in an SQL
ORDER By statement and say order by last
| | 01:55 | name descending or first name ascending.
| | 01:57 | Now this would work, but it's not
the way we would normally do things
| | 02:02 | in Reporting Services.
| | 02:03 | This is another instance where we
should bring back the data that we want.
| | 02:07 | Just don't worry about sorting it in
the query in the dataset. Instead, we'll
| | 02:11 | sort it in the report, in our data
region, our table or matrix. So I'll click
| | 02:16 | OK. That's my dataset defined.
| | 02:18 | Now I'll jump into the Insert tab
and just insert a simple table here.
| | 02:23 | Choose that dataset.
| | 02:25 | I'm going to drag on FirstName and LastName.
| | 02:28 | I could drag on rowguid.
It really doesn't matter if I do or not.
| | 02:32 | I'm not bothering with grouping them
together in any category. I'm just having
| | 02:35 | a list of people. And I'll except all the
other defaults and just add that to the report.
| | 02:41 | Go ahead and run this and we've got our
data, but right now I'm at the mercy of
| | 02:45 | how this database is internally structured.
| | 02:47 | I might get lucky, but I
want some other behavior.
| | 02:50 | So I'm going to decide to sort these by
last name ascending, go back into Design
| | 02:55 | view, and what I want to do is select the
table, so clicking somewhere inside the
| | 03:00 | table to get the gray bars along the top.
I'll then select the top corner to make
| | 03:05 | sure that I have Tablix selected in
my Properties window, and then open the
| | 03:09 | Properties Pages. And over
here we have the Sorting option.
| | 03:13 | So it's saying I don't care about how
this actually comes back from the database;
| | 03:17 | I'm going to sort it in our table here.
| | 03:19 | If I click the Add button, I then
get the option to Sort by, and I'll say
| | 03:23 | LastName, A to Z, so ascending.
That's fine. Click OK. That's it.
| | 03:28 | We now have a LastName
ascending applied to the table.
| | 03:32 | If I wanted to be explicit about
multiple levels of sorting, I'd go back into
| | 03:36 | the table, into the Property Pages,
and add multiple levels of sorting here,
| | 03:41 | making sure I sort by last name and then by
first name and then by middle name and so on.
| | 03:46 | But it's just as easy to change the
order of that, to make it descending.
| | 03:49 | I'll try that again. No surprise here.
| | 03:53 | Now we're descending last name order.
And this might work for a lot of what you
| | 03:57 | present, but what's also very
useful is to be able to apply interactive
| | 04:00 | sorting, to let the end user sort the
data whatever way they want to. So let's
| | 04:05 | see how to do that.
| | Collapse this transcript |
| Applying interactive sorting| 00:00 | I have a typical table here, which
right now has an internal sort applied to it
| | 00:05 | to sort by last name descending.
| | 00:06 | And I want to be able to click in
these header areas and have this toggle
| | 00:10 | ascending or descending by first name,
or ascending and descending by last name.
| | 00:14 | I could apply that ability to the rowguid
as well, but that's probably not very useful.
| | 00:19 | You don't have to apply interactive
sorting on all the columns. It's completely
| | 00:24 | up to you. But here's how we'd add
interactive sorting to a typical table.
| | 00:28 | I'll switch back into
Design view. Now, here's the thing.
| | 00:31 | Interactive sorting is very easy to add, as
long as you know the right place to add it.
| | 00:37 | The first couple of times you do it
it can seem a little tricky to find the
| | 00:41 | right place so that the
correct option will appear.
| | 00:44 | So, I'm wanting to click on the words
First Name or Last Name to apply that sort
| | 00:49 | order, but the way to do it is to
make sure we're adding that interactive
| | 00:53 | sorting feature to the text box here,
not to the text inside the text box.
| | 00:58 | Now, what do I mean by that?
| | 01:00 | Well, as you have probably seen,
when you start clicking around different
| | 01:03 | elements on a report, that the selected
option of this Properties window will
| | 01:07 | change based on what you've clicked on.
| | 01:10 | So if I click on a blank area of the
report, I see the word Body appear, and I
| | 01:13 | can change the properties
of the body of the report.
| | 01:16 | If I click on that top corner of the
table, I'll see the Tablix properties
| | 01:20 | appear. But if I'm interested in this
area, the blue area this says First Name,
| | 01:25 | the header of this column,
| | 01:26 | well, there is a difference between
having the text selected, in which case I'll
| | 01:31 | see selected text over here in the
Properties window, and having just the text
| | 01:35 | box selected, which I can get if I
click a blank area. There I'll see Textbox2.
| | 01:40 | So there is a difference between
the text box and the text inside it.
| | 01:45 | If I just have text selected and click
the Properties pane, I can affect the
| | 01:50 | properties of that text. I have four
sections here, and that's fine. But if I
| | 01:54 | have a text box selected and click its
Properties Pages, I have more properties
| | 02:00 | and more interesting properties.
| | 02:02 | So first, how do I make sure that I
have the actual text box selected?
| | 02:06 | Well, there is a couple of different ways.
| | 02:08 | If you have the actual text selected,
the first clue is click in a blank area of
| | 02:12 | the report somewhere, and then to select
the text box, click in it but try not to
| | 02:16 | be over the actual text of the time.
| | 02:19 | So if here I just click on the background
then I can see I have TextBox2 selected.
| | 02:23 | And this works both for something inside
a table and for something like a title.
| | 02:27 | We've got the sorting data title here.
If I click inside the text, I get selected
| | 02:32 | text; click off on a blank area and then
click back in the general region of it,
| | 02:36 | I'll see the actual ReportTitle text box.
| | 02:38 | Now, if you have a very compact report
where a lot of the columns have been
| | 02:42 | closed up to each other it might
be very difficult not to click text.
| | 02:46 | So here is another way you can do it.
| | 02:48 | So if I have this text selected,
what that means is I have text selected
| | 02:52 | that's inside a text box, that's inside the
table, that's inside the body of the report.
| | 02:57 | Well, I can use the Escape key to drill up
from the lowest level to the highest level.
| | 03:03 | So if I look at the fact I
have got selected text here,
| | 03:05 | if I hit the Escape key
once, I jump up to the text box.
| | 03:09 | If I hit the Escape key again, I jump
up to the table, the Tablix element. If I
| | 03:14 | hit the Escape key again,
I'll jump up to the body level.
| | 03:17 | We want to make sure we're on the text box.
| | 03:20 | Finally, got it. We're good. Let's go.
| | 03:22 | With the text box selected, I open up
the Property Pages and there is an option
| | 03:27 | here called Interactive Sorting. All we
do is check the checkbox. Yes, I want to
| | 03:31 | enable it on this textbox.
| | 03:33 | Well, what do we want to sort?
| | 03:34 | Well, Detail rows is the option here.
I haven't applied any grouping to this
| | 03:38 | table so that's not relevant.
| | 03:40 | But if you did have a grouped table, you
could apply it to your groups here.
| | 03:44 | Now, what do I want to sort by?
| | 03:46 | Well, this is clicking on
FirstName, so I'll sort by FirstName.
| | 03:50 | The option underneath is to apply this
sorting to all groups and data regions.
| | 03:54 | It simply means if you have multiple
tables all showing similar data, do you want
| | 03:58 | any of the sorting to
apply to all of those table?
| | 04:01 | Well, we only have one so
this isn't relevant here.
| | 04:04 | And I'll just click OK.
| | 04:05 | It doesn't seem to have made a
difference, but it will when we run this.
| | 04:08 | Well, I'm also going to do this
on the Last Name column as well.
| | 04:12 | So again, clicking a blank area of
this, double-checking that I can see the
| | 04:15 | highlight around the text box, and
that I see TextBox selected here in
| | 04:19 | Properties, open up pages, jump to
Interactive Sorting, enable interactive
| | 04:24 | sorting, and Sort by LastName. Click OK, Done.
| | 04:29 | Now we go ahead and run this.
| | 04:30 | And what it's done is add the little
up-down arrows to both of these areas.
| | 04:35 | It's actually still applying my
default sort order that I had on the table,
| | 04:40 | which was last name descending, but now we
have interactive sorting that'll override that.
| | 04:44 | So I can click on First Name, sort
ascending, click it again, sort descending,
| | 04:49 | click on Last Name, sort ascending,
click it again, sort descending. And that's
| | 04:54 | how you add interactive sorting to your tables.
| | Collapse this transcript |
| Creating a drillthrough action to connect reports| 00:00 | In Reporting Services it's often useful
to create one report that links to another.
| | 00:05 | Earlier, I'd created a report that listed
products based on a particular category,
| | 00:10 | and I used this example to
show parameters being used.
| | 00:14 | Now, we just developed this as a stand-
alone report, and it works great like this,
| | 00:18 | but there's another thing we can do with it.
| | 00:20 | We can create a new report that jumps
directly into this one. And because this
| | 00:24 | report accepts a parameter, we can
pass a parameter directly into it.
| | 00:28 | This is what's known as a drill-through action.
| | 00:31 | So to do this I'm going to create a new report.
| | 00:34 | Once again, I'll just do a blank
report here, add a data source pointing to
| | 00:38 | AdventureWorksLT, and now I'm going to
add a dataset, call this Categories.
| | 00:44 | And from this, I'll drill down into
the ProductCategory table and just get
| | 00:52 | ProductCategoryID and Name.
| | 00:54 | There is nothing special about
what I'm doing up to this point.
| | 00:57 | I'm creating a normal data source,
normal dataset, and I'm actually going to add
| | 01:01 | a fairly normal table to this report.
| | 01:03 | I'll choose the dataset we just made,
and I'll actually have a very simple table
| | 01:07 | that just has a list of
the category names in it.
| | 01:10 | I'll choose the Generic style and I'm done.
| | 01:13 | There is nothing remarkable out there, so I
will just drag this a little wider and run it.
| | 01:18 | But what I would like to do is make all
these clickable so we could jump from
| | 01:22 | this listing across into that
report that I created earlier.
| | 01:26 | So back into Design view.
| | 01:27 | I'll give this a title, just
so it's a bit more presentable.
| | 01:31 | What we need to do is apply an action
to the names that are going to show up
| | 01:36 | for these categories.
| | 01:37 | Applying an action is a bit
easier than interactive sorting.
| | 01:40 | It isn't quite as picky
about where you put the action.
| | 01:43 | I could apply it to the actual selected
text or I could apply it to the text box itself.
| | 01:48 | I prefer applying it to the text box,
so again, the easiest way to do that is
| | 01:52 | click in a blank area and then
select a blank area of that cell.
| | 01:57 | This right now showing name
rather than selected text.
| | 02:00 | I'm going to open up the Property
Pages here, and one of the options I should
| | 02:04 | have here is action.
| | 02:06 | Right now, by default, we're not doing anything.
| | 02:08 | There is no action for this. We can
choose to go to a report, go to a
| | 02:12 | bookmark, go to a URL. I'm going to select Go to
report, and it ask us first, well, which report?
| | 02:17 | If I know the name of it directly, I
could just type it in, but we also have
| | 02:21 | this Browse button and I can go and
look at the reports that have been saved on
| | 02:25 | that report's server.
| | 02:26 | I have previously saved that as
Parameter_Example, so I'm going to click Open.
| | 02:31 | Now I could just directly jump into
that report, but if the report that I'm
| | 02:36 | going to has been set up to accept
parameters then I can pass parameters into it.
| | 02:41 | This one has, so I'm going to click Add.
| | 02:43 | We get one parameter row.
| | 02:44 | If I click the dropdown, it's going to
look and say, well, it apparently takes in
| | 02:48 | a parameter called
ProductCategoryID, so I will take that.
| | 02:52 | Now, it's asking, well, what value you're
going to pass, and it's just going to give
| | 02:56 | me the options that I named in my dataset.
| | 02:58 | Name is what I'm showing on the page.
What I actually want to pass of course is
| | 03:02 | ProductCategoryID. That's it. I'm done.
| | 03:06 | I click OK and I go ahead and run this.
| | 03:09 | Table doesn't look any different, but what I'll
find is each of these options are now clickable.
| | 03:14 | If I click Chains, we'll jump into
that second report, passing in the
| | 03:18 | parameter for change.
| | 03:19 | I can use the Back button to jump back
up, jump into bottles and cages, and see
| | 03:25 | the products for that category.
| | 03:27 | And what I might even want to do now
on this second report, if this was the
| | 03:30 | normal way I was viewing this
information, I could even go and hide this
| | 03:34 | parameter section here, because
I don't need to show it anymore.
| | 03:38 | The only thing that's missing from a user
perspective on this first report is it's
| | 03:42 | not really obvious that these are clickable.
| | 03:45 | And the way to fix that is simply to
jump back into Design view, select that
| | 03:49 | piece of text, and use the Font area
here to make it look more like a link.
| | 03:54 | So I'll just change the color to a dark blue
and hit the Underline button, and run it again.
| | 03:59 | That's a bit more obvious.
| | 04:00 | Now, here I've just done this as a really
simple table that just shows one column
| | 04:04 | of data, but there is no restriction on
doing exactly the same thing on a more
| | 04:08 | complex table, or even on a matrix.
| | 04:11 | It's exactly the same idea: selecting
the relevant section in your design and
| | 04:16 | applying the action to it
to go to another report.
| | 04:19 | There is no restriction on the number
of levels that you can actually drill
| | 04:22 | through, because you're just
jumping from one to another.
| | Collapse this transcript |
|
|
3. Adding Data VisualizationsIntroduction to charting in Reporting Services| 00:00 | Data regions in Reporting Services, these
group table, regular tables and matrices,
| | 00:05 | they're easy to create, and it can
seem like a natural fit that we would just
| | 00:09 | map data from our tables in the
database into, say, table regions in the report.
| | 00:14 | But Reporting Services gives us other
ways to display the same data using charts.
| | 00:20 | Charts in Reporting Services are data
regions, like tables and matrices, so
| | 00:26 | they are fueled by data sets in much
the same way, but they represent that
| | 00:30 | data visually instead.
| | 00:32 | In Report Builder, in the Insert tab
you'll find both a Chart Wizard and an
| | 00:37 | Insert Chart option.
| | 00:38 | The Chart Wizard provides the most
common chart types, like bar charts, pie
| | 00:42 | chart, column charts.
| | 00:43 | The Insert Chart option provides
those, and a few more, including the less
| | 00:48 | typical scatter shape and polar charts, and
there are multiple versions of each chart type.
| | 00:54 | We've got 2D and 3D,
stacked and regular, and so on.
| | 00:58 | But your choice here should not be about
the visual look of the chart that you like;
| | 01:03 | it's what kind of data do you have to
show and what meaning do you want to
| | 01:07 | take from that data?
| | 01:09 | We have line charts, great for
showing growth or decline over a period of
| | 01:13 | time, and they are good for
comparison as we can chart multiple lines and
| | 01:17 | compare them to each other.
| | 01:18 | But these are not so good for figuring
out, say, what percentage of the whole does
| | 01:23 | one of these lines represents?
| | 01:25 | We have bar and column charts. These are
great for direct comparisons, very easy
| | 01:30 | to scan and see which value is
bigger than the value next to it.
| | 01:34 | We have the classic pie charts. These are
great for representing percentages of a whole.
| | 01:39 | It's easy to look at one slice and
get a feel for what that represents in
| | 01:43 | the bigger picture.
| | 01:44 | But these aren't so good for direct
comparison between data points. Visually
| | 01:48 | it's quite hard to tell if one of
the slices is a slightly more than or
| | 01:52 | slightly less than another.
| | 01:53 | Now, you can add some overlay
percentages or labels to make that more
| | 01:58 | apparent, but if you're mainly
interested in understanding how one slice
| | 02:02 | compares to another slice, you would
be better off showing that data with a
| | 02:04 | bar or column chart.
| | 02:06 | And also, pie charts are terrible if you
want to show more than a few data points.
| | 02:10 | Now, bear in mind you can always add
multiple charts to the same report, even
| | 02:15 | showing the same data, but
understanding different things about that data.
| | 02:20 | So, how do we get started?
| | 02:21 | Well, charts might look very different,
but they're configured in a similar
| | 02:25 | fashion, and you can change the chart
type from one to another after you've
| | 02:29 | added the chart onto your report.
But there is a couple of terms we need to get
| | 02:34 | familiar with to work with any
charts and Reporting Services.
| | 02:36 | And the best way to illustrate
those is with a classic line chart.
| | 02:40 | Now, if you were just to draw this on
paper or on a white board, you typically
| | 02:45 | refer to the bottom axis going from
left to right as the X axis and then the
| | 02:50 | one going up and down as the Y axis,
but Reporting Services will not ask you to
| | 02:55 | provide an X axis and a Y axis; instead, it
asks you to provide values and category groups.
| | 03:03 | Values would be the Y axis on a
typical line chart. It's the numbers.
| | 03:08 | How do we show if something is more or less?
| | 03:11 | So your values need to be
numeric in almost all chart types.
| | 03:16 | Now, categories, on the other hand, the
X axis in a typical line chart, is how
| | 03:20 | you group this data.
| | 03:21 | You could be going month by month or
quarter by quarter or region by region.
| | 03:25 | So this doesn't have to be numeric; it
could often be text: Southwest region
| | 03:30 | versus Northeast region
category A versus category B and so on.
| | 03:34 | Now that might be all you need for a
simple chart, but in some charts you
| | 03:38 | might want multiple lines, each
representing their own list of values and
| | 03:43 | categories to compare.
| | 03:44 | Now, essentially when you do that you
are overlaying multiple charts on top of
| | 03:48 | each other and having multiple
series of values and categories.
| | 03:51 | But what your primarily interested in
is always your numerical values and your category groupings.
| | 03:58 | And you'll see these terms in all
charts, even if they don't match exactly to
| | 04:03 | the X and the Y. And for example, in a
Reporting Services bar chart the values
| | 04:08 | would be left right along the bottom
and the categories up and down, but all
| | 04:11 | these charts provide many
visual options for how they display.
| | 04:14 | Let's take a look at a few.
| | Collapse this transcript |
| Creating a column chart| 00:00 | I am going to begin with a column chart.
| | 00:02 | This might not always be the one that
you want, but it's a great one for getting
| | 00:05 | to grips with how to create
charts in Reporting Services.
| | 00:09 | And just to save some time, I'm
starting here with a blank report where I've
| | 00:12 | defined a data source pointing to
the regular AdventureWorks database--
| | 00:15 | not the LT or DW one, just the regular one.
| | 00:18 | So, the next thing I need
to do is create a dataset.
| | 00:22 | I am going to create one called SalesInfo.
| | 00:25 | It will be embedded in a report, and I
will select the AdventureWorks data source.
| | 00:29 | Jumping into Query Designer, what
I'm going to do is start drilling down
| | 00:33 | into this database.
I am going to go in to the Sales section.
| | 00:35 | But instead of going into Tables, I am
going to go into Views, because there are
| | 00:40 | some views in the AdventureWorks
database that have already been predefined to
| | 00:44 | join several tables together to
provide some useful information.
| | 00:48 | What I'm interested here is
a view called vSalesPerson.
| | 00:52 | This has already been configured that
it can give me the information about
| | 00:55 | each particular salesperson and the total
number of sales they did this year versus last year.
| | 01:01 | So, I am going to select the last name and
then down here, SalesYTD and SalesLastYear.
| | 01:07 | Go ahead and run the query, just
to test that. And it looks good.
| | 01:11 | Now, as ever, I'm not focusing on this
particular structure of this view in the
| | 01:15 | AdventureWorks database.
| | 01:17 | That's of no interest to me.
| | 01:18 | I'm trying to point out that if you
have data that is something like this,
| | 01:23 | here's what you might do with it.
Because we could just show this in a regular
| | 01:27 | table data region on our report,
| | 01:30 | but it would be hard to
easily see what's going on.
| | 01:32 | It's just a bunch of numbers.
| | 01:34 | You can't scan it and see which
one is bigger than the next one.
| | 01:36 | You have to read it a little closer.
So a chart will be useful here.
| | 01:40 | I am going to click OK and say that's my
dataset, and then over to the Insert tab.
| | 01:45 | I am going to find the Chart Wizard.
| | 01:47 | In the next example, I'll use the
Insert Chart option, but we will go with
| | 01:50 | the Wizard for now.
The Wizard doesn't really do an awful lot.
| | 01:54 | First thing it's going to ask
is, what dataset do you have?
| | 01:57 | And of course, it's the
SalesInfo one I just made. Click Next.
| | 02:00 | I'm going to do a Column chart. Click Next.
| | 02:03 | And then we have this page that
looks very similar to creating a table or a
| | 02:08 | matrix data region.
| | 02:09 | And in fact, it is almost identical.
| | 02:12 | The pieces that I'm most interested in
are what goes in the Values section and
| | 02:16 | what goes in the Categories section.
| | 02:18 | Values typically being the actual
number that we are interested in, how we can
| | 02:23 | compare something being greater
than or less than something else.
| | 02:26 | So what I am going to do is drag
across SalesYTD into the Values section.
| | 02:31 | But we are also interested in
grouping, in categorizing the stuff.
| | 02:35 | Well, I am going to do it simply by last name.
| | 02:38 | This will give me a chart that
will allow me to compare the values in
| | 02:41 | a graphical fashion.
| | 02:42 | We will do something with SalesLastYear
a little bit later on in this example,
| | 02:46 | but for now, that will do. I will click Next.
| | 02:49 | In the Style section,
I will just select Generic.
| | 02:52 | I don't need all the extra styling.
| | 02:54 | This will do, for our purposes, and click Finish.
| | 02:57 | I will just drag that a little wider,
and just give it a simple title.
| | 03:02 | Now, when you're looking at a chart
in Design view, this is dummy data.
| | 03:05 | It's not real data.
| | 03:07 | To see the real data,
we need to go ahead and run it.
| | 03:09 | So I will do that now. There's our chart.
| | 03:11 | It doesn't look great.
| | 03:13 | A few too many data points,
a few too many people.
| | 03:17 | I could change this by going back into
Design view and actually just making
| | 03:20 | it bigger. I could grab the chart and
then grab the drag handle, drag it down,
| | 03:24 | drag it to the right.
| | 03:25 | But regardless, I've probably got a
few too many data points if what I am
| | 03:29 | wanting to do is actually compare
salespeople in some meaningful fashion.
| | 03:34 | Well, let's say that what I really
wanted to see was the top 25% of my
| | 03:38 | salespeople, to compare them.
| | 03:40 | Now, I could do this a couple of different ways.
| | 03:42 | I could go back to my dataset and
change that so it only returns a few rows
| | 03:47 | with the top sales numbers. Or I
could apply a filter on the chart itself.
| | 03:51 | I am actually going to do it on the chart.
| | 03:54 | So, we need to get into the properties
of this chart after it's already been
| | 03:58 | positioned in our report.
| | 03:59 | This is another area where you have to
be a little bit careful on what you've
| | 04:03 | clicked on, because it's very easy to,
say, click the Chart Title area where you
| | 04:07 | have selected the title, not the chart.
| | 04:09 | You will find that there
are multiple pieces in here.
| | 04:11 | We've got the Legend over
here on the right-hand side.
| | 04:14 | We've got Axis Title areas and
information down on the left and on the bottom.
| | 04:19 | So you need to be
careful what you have selected.
| | 04:20 | The best way to grab the chart is to
click in a blank area just around here.
| | 04:25 | And what we're looking for is
that Chart is being selected over in
| | 04:29 | the Properties window.
| | 04:30 | Again, another option you could do
is if you have something selected like
| | 04:33 | the Legend, I can then also hit the Escape key
until I see the word Chart there representing
| | 04:38 | yes, I actually am on the
chart, not on a piece of text.
| | 04:42 | Now, there's a couple of ways I
can get to the properties of this.
| | 04:45 | I could right-click an area here,
but it's sometimes easy to miss the right spot.
| | 04:50 | So, with the chart selected, I can also
open up the Property pages from this section.
| | 04:54 | Here we have some basic information,
like the name of this chart, the visibility
| | 04:58 | of it, and we have the Filters section.
| | 05:00 | I am going to add a filter in here to
only include rows where the following
| | 05:04 | conditions are true.
| | 05:06 | What I'm interested in is
where the Sales YTD are equal to--
| | 05:10 | well, I don't want an equal to;
what I actually want is the top 25%.
| | 05:16 | And I'll let it take care of
selecting the right amount of rows here.
| | 05:20 | Click OK. It doesn't change
anything visual about the chart,
| | 05:23 | but when we run it again, we have
a much more straightforward list.
| | 05:27 | We've now got five people that we are
comparing and we can actually see all
| | 05:30 | their individual names here.
| | 05:31 | But really, what I was more interested
in is not just sales year to date; I can
| | 05:36 | now see quickly how one person compares
to another, but I also want to see how
| | 05:40 | they compare to the sales last year.
| | 05:43 | Well, I'll go back into the Design view.
| | 05:45 | This is going to be another
grouping, another category.
| | 05:48 | We could have done it in the
wizard, but we are out of the wizard.
| | 05:51 | We can't go back into the wizard.
| | 05:52 | So, here's how we would make a change to a
chart after it's been positioned on the report.
| | 05:58 | Now, you might first think, well, it's
by selecting the chart and going back
| | 06:02 | into those Property pages.
| | 06:03 | But no, that's not the way we do it.
| | 06:05 | You might think that it's in
the Property window itself.
| | 06:08 | And no, it's not really there
either, or not in any easily accessible
| | 06:12 | fashion. Or you might also think, well, if I
right-click an area of the chart, is it here?
| | 06:17 | Actually, no, it's not in any of those places.
| | 06:19 | But if you right-click the chart so
you see the Chart dropdown here, what will
| | 06:25 | also happen is over here on the
right-hand side, you will have this other
| | 06:29 | pop-up called Chart Data.
| | 06:31 | You've got to be careful with this one
because it's very common to have this
| | 06:34 | hidden behind the Properties window or
sometimes even moved off the right hand
| | 06:38 | entirely just because you can't
see that area of the Design layout.
| | 06:43 | But that's where you should find it, on
the right-hand side of the chart itself.
| | 06:47 | This is what is currently showing, the
things that we had dragged into the right
| | 06:51 | areas in the wizard: our values, which
are chart Sales Year To Date, and our
| | 06:55 | first category grouping, which was LastName.
| | 06:57 | Well, all I'm going to do now is grab
the SalesLastYear part of the dataset over
| | 07:03 | here in my report data, drag it over
into the Values section, and say that it's
| | 07:08 | another bunch of values that I'm interested in.
| | 07:11 | And Report Builder will take care of
grouping everything in the right place.
| | 07:15 | It will take care of giving
it a different set of colors.
| | 07:19 | So, I will go ahead and click Run.
| | 07:20 | And now we have the two sets of columns.
| | 07:23 | We have the Legend being automatically
generated over on the right-hand side, so
| | 07:26 | it's very easy to scan, very easy to look at.
| | 07:29 | And once we're happy with just being
able to select the right part of the chart to
| | 07:33 | figure out where the actual
correct properties are, when we should be
| | 07:36 | right-clicking and selecting the
property pages, and when we should be moving
| | 07:40 | over to the right-hand side of this layout
so I can actually look at the chart data,
| | 07:45 | there's a lot of things you can do with it.
| | 07:47 | Now, one of the options which you won't
see in chart data but you will see just
| | 07:51 | by right-clicking the chart itself--and
again make sure you're right-clicking a
| | 07:55 | blank area of the chart--
is the Change Chart Type.
| | 07:58 | And I wanted to show this with the idea
that even after we've configured it, I
| | 08:02 | could change it from one to another.
| | 08:04 | So, for example, we're currently a
basic column chart, but I am going to come
| | 08:09 | down and select one of the bar chart options,
perhaps even the 3D clustered bar. Click OK.
| | 08:16 | Now, we are actually done here,
because it kept all the configuration that
| | 08:19 | was already in there.
| | 08:20 | We are showing the same information,
using the same dataset, and representing it
| | 08:24 | with the same color.
| | 08:25 | Bar and column charts, very similar to
each other, bar charts just horizontal.
| | 08:30 | Both of these are very easy to configure,
and great to show direct comparisons.
| | Collapse this transcript |
| Adding a generated average to a chart| 00:00 | One ability of Reporting Services that
can come in very useful, particularly in
| | 00:04 | column charts and line charts, is
being able to add an average to this.
| | 00:08 | I am looking right now at the column
chart from the previous example, and it
| | 00:13 | would be nice to represent an average
for both of these sales numbers, just to
| | 00:17 | figure out if any of the particular
columns are above average or below average.
| | 00:22 | Well, we can do this, and we don't even have to
make any changes to the dataset. Here's how.
| | 00:25 | I am going to jump back into my Design view.
| | 00:29 | Now, this column chart has been
defined with two sets of values, and it's
| | 00:33 | important that we're going to
represent an average on both of them:
| | 00:37 | first an average of the sales YTD
and then an average of the SalesLastYear.
| | 00:41 | Now, if you might notice here that if
I click these different columns, I'm
| | 00:46 | seeing different things light up and
be selected, and that's actually very
| | 00:50 | important with what we are about to do
here, because what I'm going to do is
| | 00:54 | add what's called a calculated series, and
it's very sensitive to what I have selected.
| | 00:59 | I am going to close down the Properties
panel here, just to give myself a bit more room.
| | 01:04 | I can either do this by selecting the
columns themselves, though sometimes,
| | 01:09 | depending on your chart, that's a little
difficult to grab. But I can also do it
| | 01:12 | over here, by right-clicking the values.
| | 01:15 | And the option I am looking
for is Add Calculated Series.
| | 01:18 | So I can get to this by right-
clicking SalesYTD in the Chart Data section.
| | 01:23 | I can also get to this by right-
clicking, in this case, the blue bar,
| | 01:27 | Add Calculated Series.
| | 01:29 | Reporting Services provides us
with a bunch of different formulas.
| | 01:33 | Do we want a moving average. Do we want a
detrended price oscillator, or rate of change?
| | 01:38 | Well, actually going for
something a lot more basic in that.
| | 01:41 | I'm just interested in the mean.
| | 01:43 | I don't want a moving average because
that's much more useful over a line chart
| | 01:47 | that's going across multiple months.
| | 01:49 | That's not relevant here.
| | 01:50 | I really just want to know what
the mean average is for all of this.
| | 01:54 | So I am just going to go ahead and click OK.
| | 01:57 | Again, in Design view, we are just
looking at dummy data here, but we can see
| | 02:00 | that it's added in that average, and
even given us part of this legend over here.
| | 02:05 | So, I am going to go and run.
| | 02:07 | And it might not look fantastic, but
this would be about right if we are
| | 02:10 | trying to represent an average of the
blue bars; a couple will be over it and
| | 02:15 | a few will be below it.
| | 02:16 | But it's now quite easy to see that
Blythe, for example, is slightly above the
| | 02:21 | average, where I might not
have been sure a moment ago.
| | 02:23 | But I'd like to change this a little bit.
| | 02:26 | So, I am going to go back into
Design view, and I want to go back to that calculated series.
| | 02:31 | If I click on the chart and then
clicking carefully around, I should be able to
| | 02:35 | highlight it, seeing that it's
highlighted with the different data points, and I
| | 02:39 | can right-click that calculated
series itself and go into its properties.
| | 02:43 | Again, we should expect to see
it's calculating a mean formula.
| | 02:47 | I can come down to the Legend
and change that if I want to.
| | 02:50 | Maybe even just change
it to say Average instead.
| | 02:53 | Now, we have options below for, say, Line Width.
| | 02:56 | I am going to actually move that up to 3 points.
| | 02:59 | I could even change the
Line style to a dashed Line.
| | 03:02 | Now, it's up to you if you wanted
to pick your own color for this.
| | 03:05 | At the moment, what's going to happen
is it's going to pick a color from the
| | 03:07 | palette of the chart, in this case red.
| | 03:10 | But we'll see a little later
how to change the palettes anyway.
| | 03:12 | But that becomes a bit more legible,
certainly a bit more useful if we
| | 03:18 | are comparing those.
| | 03:19 | Well next, we add the second one.
| | 03:21 | I wanted to also add an
average for the SalesLastYear.
| | 03:25 | And being very careful what I have
clicked on in Design view, I can either click
| | 03:29 | on the columns that represent
SalesLastYear or I can come into the Chart Data
| | 03:34 | section and right-click over here, add
Calculated Series. Again, it's a Mean,
| | 03:39 | not a Moving Average. And right now I'll
come down to Border and make this also a 3-
| | 03:43 | point Line width, and this can be dotted.
| | 03:46 | Click OK, and we are done!
| | 03:49 | Run it. We now get a display of both averages.
| | 03:52 | We get a comparison of sales
year to date versus sales last year.
| | 03:55 | Very easy to do, very easy to configure.
| | 03:58 | I could probably do with a bit more
experimenting on the visual look and feel of
| | 04:02 | those lines, but this should do the trick.
| | Collapse this transcript |
| Creating a pie chart| 00:00 | Everybody knows the pie chart, but not
everyone thinks about the right time to
| | 00:03 | use it and the right data to use it with.
| | 00:06 | Pie charts are most useful when you have
just a handful of data points to compare.
| | 00:10 | The recommendation is seven or less.
| | 00:13 | But that doesn't mean that you would
always expect these data points to be
| | 00:17 | waiting in the database for you.
| | 00:18 | No, you often need to construct them by
joining tables and applying functions.
| | 00:22 | And I am going to do that right now.
| | 00:24 | Again, for time's sake, I have a blank
report here where I have added a data
| | 00:29 | source pointing to the regular
AdventureWorks database. That's it.
| | 00:31 | I am going to create a dataset here.
| | 00:33 | And this is going to be
information about customer regions.
| | 00:38 | Jumping into the Query Designer for
this, I am going to drill down again into
| | 00:42 | the Sales area and into the Views area.
| | 00:45 | There is a useful view in the
AdventureWorks sample database called
| | 00:48 | vIndividualCustomer.
| | 00:50 | I am just going to select a few
columns in this, BusinessEntity, FirstName,
| | 00:55 | EmailAddress, and CountryRegionName.
| | 00:57 | I'll actually end up with
fewer than these in just a moment;
| | 01:01 | I just want to show what
happens when we run this query.
| | 01:04 | So you got BusinessEntityID.
That seems to be some kind of key.
| | 01:07 | We've got the FirstName, we've got the
EmailAddress, and then we've got this
| | 01:11 | CountryRegionName area: United
States, France, Australia, Canada.
| | 01:15 | There might be a few others.
| | 01:17 | As I keep scrolling, I get more and more data.
| | 01:20 | There is a fair amount of
data that I could find in this
| | 01:22 | AdventureWorks database.
| | 01:23 | But let's say my business problem here
is I really want to find out how many of
| | 01:28 | our customers come from the US as
opposed to Australia. What is the most
| | 01:33 | popular place for us?
| | 01:35 | Well, I can do that very easily.
| | 01:36 | I am going to do this in the query itself.
| | 01:39 | Here's what I'll do first: unselect
everything except BusinessEntityID and
| | 01:45 | Region. So again, if I just run the
query, that's all I am really getting here: a
| | 01:49 | column and the region.
| | 01:50 | It doesn't really matter
which column I ended up with here.
| | 01:54 | I just needed something, because with
these two selected, what I am going to do
| | 01:59 | is go into my grouping area
here with my selected fields,
| | 02:03 | highlight CountryRegionName, come over
to the aggregate section, and say I want
| | 02:07 | to group by the CountryRegionName.
| | 02:09 | It will also change BusinessEntity to
group by, but I don't want to group that;
| | 02:14 | I want to count it.
| | 02:15 | I am not summing; I am not totaling or
averaging. I am simply saying for each
| | 02:20 | specific distinct country and
region, how many columns do you have?
| | 02:25 | So I could be counting the
BusinessEntityID column or I could be counting the
| | 02:28 | LastName column or the EmailAddress column.
| | 02:30 | It really doesn't matter, as long as I
am grouping by the country and region.
| | 02:35 | Now if I go ahead and run the
query, this is what I get back:
| | 02:39 | the CountryRegionName and an account of
columns that have been returned for that.
| | 02:44 | This is perfect kind of data for a pie chart.
| | 02:47 | We don't have a lot of data points.
| | 02:48 | In this case, it's really six
different categories with six different values.
| | 02:53 | So I am going to go ahead and use this.
I am done with the dataset. Click OK.
| | 02:58 | Give our report a title and jump over
to Insert. Instead of using the wizard, I
| | 03:02 | am just going to say Insert Chart.
As soon as you do that, the crosshairs of your
| | 03:07 | cursor change, so I am just
going to come down and drag this.
| | 03:10 | I can drag it a little lower than the
page itself, and it should expand to fit.
| | 03:15 | Then we get the window that asks us what
type is this? Again, you can change from
| | 03:19 | one to the other after you've
configured them, but I am just going to go ahead
| | 03:22 | with the basic pie chart that's
in the Shape section, and click OK.
| | 03:27 | Now, right now there is absolutely no
connection between this chart and the
| | 03:31 | dataset that I just defined. I need to
make those all myself because I didn't
| | 03:34 | use the wizard, and here's how.
| | 03:36 | We right-click on the chart, just to
bring up the Chart Data section. Again,
| | 03:41 | sometimes it's a little difficult
to grab hold of, but if you click the
| | 03:44 | different elements you
should be able to find it.
| | 03:46 | Even though this is a pie chart so it
doesn't have an obvious X and Y axis,
| | 03:50 | we're still going with the basic
principle that all reports in Reporting
| | 03:54 | Services are concerned about numeric
values and then category groupings, values
| | 04:00 | being always numeric.
| | 04:02 | Well if I look at the dataset we
just have to think about what was the
| | 04:06 | thing that had the number, what
actually had the count, and it was the count
| | 04:09 | of BusinessEntityID.
| | 04:11 | That's what contained 1500 or 3200.
So I am going to drag that across and drop
| | 04:17 | it into the Values section.
| | 04:19 | The only other thing I
need is the Category groups.
| | 04:22 | What are we grouping these
together by? Well, we are grouping by the
| | 04:25 | CountryRegion, so I am going to
drag that into Category groups.
| | 04:28 | Again, it's still using a dummy data
right now, so I am going to go ahead and run
| | 04:32 | it, and that's looking pretty good.
| | 04:34 | Okay, we haven't given it a chart title
here, but I could go ahead and do that,
| | 04:40 | and perhaps drag this a little
lower to give us a bit more room.
| | 04:43 | So this is great data for a pie chart.
| | 04:46 | However, we've got a problem here.
| | 04:48 | It's a little difficult to tell
the comparative size of these slices.
| | 04:51 | That might not be hugely important,
but it might be nice to figure out if, for
| | 04:56 | example, the France section was
bigger or smaller than the Canada section.
| | 05:00 | So it'd be nice to have a bit more data.
| | 05:02 | Well, that's easy to do as well.
| | 05:04 | I am going to go back into Design view
and then I'm going to right-click the
| | 05:08 | chart, right-click in the
middle of the pie chart here.
| | 05:10 | One of the options is Show Data Labels.
Click that, leave that as is, and just go
| | 05:16 | back and run it again.
| | 05:17 | Now, what we are getting here is the
actual count, the actual values that have
| | 05:21 | been returned by the query.
| | 05:23 | That's all well and good, but that
might not be exactly what I was after.
| | 05:27 | What I really wanted was percentages.
| | 05:28 | Well, it's very easy to do as well.
| | 05:31 | Back again into Design view. I need
to select these labels again so I am
| | 05:34 | just going to click in there until
they are all highlighted, and they should
| | 05:37 | select all at once.
| | 05:39 | And then right-click. There should be
an option called Series Label Properties.
| | 05:43 | I'll select that one.
| | 05:45 | The first option in general is, what's
the label data? Where does this come from?
| | 05:49 | By default, it's actually
representing the actual number.
| | 05:53 | What I want it to represent instead is
the percent, and there is an option in
| | 05:57 | this dropdown box here.
| | 05:58 | Now as soon as I select that it's going
to just point out, okay, this property
| | 06:02 | won't have any effect unless there is an
option called UseValueAsLabel is set to
| | 06:06 | False. It sounds cryptic; it really isn't.
| | 06:09 | All it meant was under the hood there
is actually an option saying, hey, I am
| | 06:12 | actually using the value as the
label. Is it okay to set that to false?
| | 06:17 | Well, yes, it is, because I
want to use percent as the label.
| | 06:20 | So I'll click OK. The Font was a little small,
so while I am here I am just going to
| | 06:24 | drill into font and change that to 14
points. I might even change that to a white
| | 06:29 | color, uncheck Bold. Click OK, and run it.
| | 06:33 | and now we have percentages.
| | 06:35 | There it looks like I could do
a bit of experimentation here.
| | 06:37 | We're getting a bit of overlap, so I
might make the font a little smaller, but
| | 06:42 | you get the idea, and it's very easy to
configure and very easy to change what
| | 06:46 | we are seeing on this chart.
| | 06:47 | Well while we're changing a few
properties, let's change a couple more.
| | 06:51 | Back into Design view, I've been using
the basic generic style, which really is
| | 06:55 | this basic set of colors here.
| | 06:57 | We do have some options for what we want.
| | 07:00 | If I click the View section and
open up Properties, you'll find that by
| | 07:04 | selecting the chart--again being
careful I have the chart selected--
| | 07:08 | there should be an option here called Palette.
| | 07:10 | We've defaulted to the BrightPastel,
but there is also the Chocolate Palette.
| | 07:14 | There is also the EarthTones Palette.
| | 07:16 | Some of these I don't think are all
that useful because they just don't have
| | 07:20 | enough distinction between them or
they are a little too pale, but you can
| | 07:24 | experiment with some that you
might find a bit more useful.
| | 07:27 | Not only that, but you'll find that every
element of the chart has its own properties,
| | 07:32 | its own set of customizable features.
| | 07:34 | You just need to be very clear what you
have selected, what you've clicked on.
| | 07:38 | We have the title here, which right
now is looking a little small so I could
| | 07:42 | select that and use either the
Properties panel or the actual Home section to
| | 07:48 | give it a slightly bigger font.
| | 07:50 | I could select the Legend over here.
Legend itself is quite interesting.
| | 07:53 | If I right-click on this guy, it has
its own set of Legend properties including
| | 07:58 | things like changing the position of it.
| | 08:01 | We have this little graphical
representation about where we'd like to position the
| | 08:05 | legend in relation to the rest of the chart.
| | 08:08 | So just with a very little bit of
experimenting, you can see how we can really
| | 08:12 | drastically change the appearance of
all our charts and that works not just for
| | 08:16 | pie charts, but all the others as well.
| | Collapse this transcript |
| Using sparklines| 00:00 | Once you are familiar with creating charts in
Reporting Services it's easy to add sparklines.
| | 00:05 | Sparklines are mini charts, most
commonly shown as small line charts, but they
| | 00:10 | can be any small chart.
| | 00:11 | They are intended to show very
quick but easy-to-scan visual data.
| | 00:16 | In Reporting Services you can
add multiple kinds of sparklines.
| | 00:19 | These can be added on their own.
| | 00:21 | They are sometimes added along with
text. Sometimes you'll use several
| | 00:25 | together for a quick comparison, but
they are also very commonly used embedded
| | 00:29 | inside a table or a matrix.
| | 00:31 | These sparklines are shown without
titles along the axis or without numbers
| | 00:36 | along the axis, because we are not
trying to get an accurate understanding of a
| | 00:40 | particular data point.
| | 00:41 | What they let us do is quickly see, is
this flat and steady, is it volatile, is
| | 00:47 | it trending up or is it trending down?
| | 00:49 | So here's how we make them.
| | 00:50 | I am in Report Builder and just to
save some time, I've created a blank report
| | 00:55 | with a data source pointing to the
regular AdventureWorks database. All I've
| | 01:00 | done apart from that is just change the title.
| | 01:02 | What I need is some data to work with,
so I am going to add a new dataset.
| | 01:06 | I'll call this SalesInfo, because
that's what we're going to go for, and jump
| | 01:10 | into the Query Designer.
| | 01:12 | Drilling down into the Sales section,
I am going to go into the SalesOrderHeader
| | 01:17 | table, which has got the data
that I am most interested in here.
| | 01:20 | And I'll begin by selecting
OrderDate and TotalDue. Run this query.
| | 01:26 | That seems about the kind
of data that I would expect.
| | 01:29 | Now, I know that I am going to have to
change this, because I've got a little bit
| | 01:31 | too much data coming back, but let's
just see the idea of creating a sparkline
| | 01:35 | straight away. Click OK, click OK.
| | 01:38 | I am going to go to the Insert tab of
the Ribbon, find the Sparkline option,
| | 01:42 | click that once. Then my crosshairs will
change on my mouse so I'll just drag it
| | 01:47 | where I want to position it and let go.
| | 01:49 | I get the window very similar to
creating a chart asking what kind of sparkline
| | 01:53 | or what type of sparkline do I want.
| | 01:55 | I am going to select the Line option
because I want to show growth or decline
| | 01:59 | over time, and click OK.
| | 02:01 | Just as with a chart, this is
showing dummy data right now. There is no
| | 02:05 | connection between this sparkline on my
report and the dataset that I've defined here.
| | 02:10 | What I need to do is bring up the
data options, very similar to a chart;
| | 02:13 | in fact, it's really identical to a chart.
| | 02:16 | If I click this again,
I can get my Chart Data option.
| | 02:20 | And again, just to show that if you've
deselect it, you'll need to click it
| | 02:23 | twice, click once and then again to
bring up the Chart Data window, or if it's
| | 02:27 | deselected, you can also right-click,
which typically brings it up directly.
| | 02:31 | This is the same Chart Data window
that we'd see with a normal chart, because
| | 02:36 | really a sparkline is a chart.
So we need something for our values.
| | 02:39 | We need something for our groups.
| | 02:41 | Typically, with a sparkline
you don't have multiple series.
| | 02:44 | That's just way too much data to
show in a small amount of space.
| | 02:48 | So the values is our numeric option.
| | 02:50 | What is going up, what's going down?
So I am going to select TotalDue, drop it
| | 02:55 | into Values, and then the OrderDate is
my groupings and drop that in there.
| | 02:59 | I know I am going to have to
change this, but let's just see why.
| | 03:02 | First I run this and officially it works.
We are getting a sparkline, but it's
| | 03:07 | not what I was looking for.
| | 03:09 | This looks more like a mini column
chart and I was expecting a line chart.
| | 03:13 | It actually is a line chart.
| | 03:14 | It's just too small to
see that it's very volatile.
| | 03:17 | It's going up and down a lot. Because I
am just bringing back too many points of
| | 03:21 | data. This is actually several years
worth of daily sales, often dozens in a day.
| | 03:26 | And that would be impossible to show on
a regular chart, nevermind a sparkline.
| | 03:30 | I need to get a better summary of this
data, perhaps totaling sales grouped
| | 03:34 | together by month or quarter.
| | 03:36 | That would be a much
better scale of data to show.
| | 03:39 | So back into Design, I am
going to drop into the dataset.
| | 03:42 | If I jump into Query Designer I can see
that when I run this query I am getting
| | 03:47 | multiple orders for a particular date.
| | 03:49 | Now, I could start off by at least
grouping the dates together, so selecting
| | 03:54 | my TotalDue and saying I wanted to sum
that and grouping by the other field,
| | 03:59 | OrderDate. That will now give me one amount
per day, but that's still too many data points.
| | 04:06 | I really need to start
grouping these together by month.
| | 04:09 | Now, to do that, the best way to
do it is to edit this as text.
| | 04:12 | We really can't get that level of
detail in the Query Designer visual section.
| | 04:17 | In the SELECT part of the statement I
am no longer interested in the OrderDate.
| | 04:21 | I really just want to know the year and
the month, and I am going to get those
| | 04:25 | separately by using the Year
and Month functions in my SQL.
| | 04:29 | I will just use a constructed column name so
they come back as order year and order month.
| | 04:34 | And below, in the GROUP BY, I want to
group them together by year and month.
| | 04:39 | I can't just group together by month,
because if I select a month, it'd start
| | 04:43 | combining all the sales for January 2005
and January 2006 and January 2007, so I
| | 04:49 | need the year as well as the month.
| | 04:51 | Same thing. I am going to add an ORDER BY.
| | 04:53 | I don't technically need to do that,
but this will allow me to run this query
| | 04:58 | and get an idea of whether this
seems to be what we're looking for.
| | 05:00 | So go ahead and now we are getting the
OrderYear, OrderMonth, and the summed-
| | 05:05 | together TotalDue of all those orders.
| | 05:08 | And this will be a much smaller amount of
data, much better to represent in a sparkline.
| | 05:13 | So I am going to click OK and click OK.
| | 05:16 | I could edit the existing sparkline, but
just to give us some more practice, I am
| | 05:20 | going to delete it and edit again.
| | 05:22 | Up to the Insert tab. Click once on
Sparkline, draw the sparkline that you want,
| | 05:27 | let go. I am going to select the
first Line chart option. Click OK.
| | 05:32 | Clicking the sparkline until I get my
chart data section up. I am going to
| | 05:36 | open up my dataset.
| | 05:37 | The Values is still
going to be the Sum_TotalDue.
| | 05:40 | Do be careful when you are dragging from
the report data region into chart data.
| | 05:45 | It's easy to mix up if you drag
over the top of another element.
| | 05:49 | It can confuse Report Builder.
| | 05:51 | So I let go in Values, and then in my
category groups what I am going to do is
| | 05:56 | first drag over OrderYear and then
drag over OrderMonth. So we'll be grouping
| | 06:01 | in two sections, year, and within
year, each month, and that should do it.
| | 06:06 | We go ahead and run this.
| | 06:08 | That looks a lot more realistic.
Now, what I am seeing at the end here is that
| | 06:11 | the last months of sale is actually
aren't a full month, so it's dropping off
| | 06:16 | very suddenly at the end. If that was an issue I
might want to filter the data so we only had complete
| | 06:22 | months, but this looks about right.
| | 06:23 | Now, this is a standalone sparkline.
| | 06:25 | You probably want a text box beside
this with some kind of label so people can
| | 06:29 | actually tell what it represents.
| | 06:31 | But it's also very, very common to take
these and embed them inside a table or matrix.
| | 06:36 | Let's see that next.
| | Collapse this transcript |
| Adding a sparkline to a drilldown matrix| 00:00 | I'm beginning here in Report Builder
once again with a blank report and a data
| | 00:04 | source pointing to the
regular AdventureWorks database.
| | 00:07 | I am going to add a matrix to this
report and add a sparkline into that matrix
| | 00:12 | so that we will have multiple
sparklines automatically generated for whatever
| | 00:16 | rows are returned from the database.
| | 00:17 | Now, most of our work in this
example will be in preparing the dataset.
| | 00:21 | I am going to take the same idea that I
used in the previous example, of getting
| | 00:25 | total sale broken down by year and
month, but this time I'm going to start
| | 00:30 | joining this together with the salesperson
and region data so I can have a matrix
| | 00:34 | that drills down to
multiple levels of information.
| | 00:37 | So I'll create a new dataset.
| | 00:39 | I will call this SalesInfo.
| | 00:40 | Jumping into the Query Designer, I will
first go and get a very similar set of
| | 00:45 | data to what we've got in the previous
example, into the SalesOrderHeader and
| | 00:50 | select OrderDate and TotalDue.
| | 00:52 | But what I also want here is
information about what salesperson was responsible
| | 00:57 | for this order and what region
does that salesperson handle.
| | 01:00 | Well, that information
isn't in this particular table.
| | 01:04 | There is no name here.
| | 01:05 | I do have a SalesPersonID.
| | 01:07 | So let's start with that.
| | 01:09 | So I will select that as a third column.
We will run that query just to take a look.
| | 01:13 | Well, I am getting back 279, 282, 276,
some kind of ID for a salesperson.
| | 01:17 | Well, let's take a look and see if there's a
simple salesperson table that I can join that to.
| | 01:23 | There is a salesperson table, but it
seems to be joining somewhere else.
| | 01:28 | We've got BusinessEntityID and
TerritoryID, but there's no names involved here.
| | 01:33 | There is no actual name of a
territory nor of the salesperson.
| | 01:36 | That's because in AdventureWorks
there is actually quite a lot of different
| | 01:39 | tables that need to be
joined together to get that data.
| | 01:42 | However, there is a view in
AdventureWorks called vSalesPerson that's already
| | 01:47 | joining some of that information together.
| | 01:49 | VSalesPerson contains first name, last name.
| | 01:53 | It also contains the territory name
and the region name that this salesperson
| | 01:56 | is responsible for.
| | 01:58 | So this is the table that I actually
wants to join with that SalesPersonID.
| | 02:03 | But here's the problem:
there is no obvious join column here.
| | 02:06 | I've got BusinessEntityID,
but not a SalesPersonID.
| | 02:11 | Well, I might try and experiment.
| | 02:12 | First, if I just select, say, the last
name of the salesperson and try and run
| | 02:17 | this query, is it going to work?
| | 02:19 | Well, now I get this error message.
| | 02:21 | Well, these tables aren't related.
| | 02:23 | We have the Auto Detect option on,
but it can't figure out how to tie these
| | 02:27 | two things together.
| | 02:28 | I am going to have to do that myself.
| | 02:30 | So I am going to turn Auto Detect off.
| | 02:32 | What that will do is enable this option
here called Add Relationship, and I can
| | 02:38 | manually say how those tables are
connected to each other. And it actually is
| | 02:42 | from SalesPersonID in the
SalesOrderHeader to BusinessEntityID in vSalesPerson.
| | 02:47 | Now again here, I'm not really trying to
talk about the AdventureWorks example here.
| | 02:52 | Take this as an illustration of how you
would connect two tables if they weren't
| | 02:57 | explicitly defined in the
database as having those connections.
| | 03:00 | So I am going to click that button.
| | 03:02 | When I do that it seemed to do something.
| | 03:05 | I have this Edit Fields option came up,
but I can't see anything. It's not obvious.
| | 03:09 | That's because the Relationship section
is often hidden in your Query Designer.
| | 03:13 | If you want to see it, click the
little dropdown arrow and you can actually
| | 03:16 | have this window section appear with
the Left Table, Join Type, Right Table.
| | 03:22 | And this is what I need to do
is describe this relationship.
| | 03:25 | Because I'd clicked that once,
I already have an entry in the
| | 03:28 | Relationship section.
| | 03:29 | So I can double-click in here.
| | 03:31 | First I select the Left Table.
| | 03:33 | It already understands that in my
current query I am describing two tables or
| | 03:37 | two views or one of each as we have
here: SalesOrderHeader and vSalesPerson.
| | 03:41 | SalesOrderHeader is the table that I want here.
I want the right table to vSalesPerson.
| | 03:47 | It is an inner join. And then I double-
click here to describe what the actual
| | 03:51 | fields are that join these two together.
| | 03:53 | It doesn't actually matter which one
was on the right or the left, because we
| | 03:56 | are doing an inner join here.
| | 03:58 | But the left table was SalesOrderHeader
and that had SalesPersonID, and the right
| | 04:03 | one was BusinessEntityID. Click OK.
| | 04:07 | Now, if I go ahead and run this query,
what we are getting is the OrderDate and
| | 04:11 | order amount and the
LastName of the related salesperson.
| | 04:15 | Because of the indexing structure of the
database, we are getting them back in a
| | 04:18 | rough kind of order of salesperson here,
but it looks like they're being joined
| | 04:22 | the way that we would want them.
| | 04:24 | But I need a little bit more than this.
| | 04:26 | So back into the vSalesPerson table,
I want LastName, and I'm also going to go
| | 04:31 | for the territory name. Run that again.
| | 04:34 | I will find that some territories don't
actually have a name, or some salespeople
| | 04:38 | aren't associated with
one, but most of them are.
| | 04:40 | In fact, I no longer need
SalesPersonID anymore, because I actually have the
| | 04:45 | last name, which is what
I'm really interested in.
| | 04:47 | So I will select SalesPersonID and
just get rid of that. Ok, what next?
| | 04:51 | Well, I want to start grouping
together the TotalDue amount,
| | 04:54 | so adding that together for each
salesperson, but grouped by month and year again.
| | 05:00 | I can start off by selecting to
sum the TotalDue amount and it will
| | 05:05 | automatically start to group the others.
| | 05:07 | I am just going to order these in here, just
to make my thinking about them a little easier.
| | 05:11 | We are trying to sum the TotalDue amount.
| | 05:13 | We are grouping by OrderDate, and then I
will go TerritoryName and finally, LastName.
| | 05:17 | I don't strictly need to do that here,
but it makes it easier now that I have to
| | 05:21 | jump into the Edit as Text section to
make my final grouping choices to this.
| | 05:26 | So very similar to the last time,
what I'm going to do is select the
| | 05:30 | OrderYear and OrderMonth values,
construct those as columns by extracting
| | 05:34 | them out of OrderDate.
| | 05:35 | I am going to allow the inner join to
happen the way it was described in Query
| | 05:40 | Designer and then finally, in GROUP BY,
I want to start grouping them first by
| | 05:43 | year, then by month, then by
territory, and then by LastName.
| | 05:49 | Taking care just to scan that all the
commas are in the right place so I don't
| | 05:52 | either have too many or too few,
| | 05:54 | this looks correct.
| | 05:55 | I am going to go ahead and test this.
| | 05:57 | And this looks about right.
| | 05:58 | We are getting different amounts being
totaled up for any particular year and
| | 06:03 | month for a territory for
a salesperson's last name.
| | 06:07 | Again, your focus here should be on what
can you do with this kind of data, what
| | 06:12 | can you do with this kind result?
| | 06:13 | We are going to take it into a matrix.
| | 06:15 | So click OK and click OK.
| | 06:17 | Over to the Insert tab, I am going to come to
the Matrix section and add a Matrix Wizard.
| | 06:22 | I will choose that dataset
I just defined. Click Next.
| | 06:25 | Now, I could actually define this
matrix a couple of different ways.
| | 06:28 | I could have the months and years
running across the top, or I could have them
| | 06:32 | down the left-hand side and
the regions across the top.
| | 06:35 | What I'm going to do is drag on
TerritoryName into Row groups and then also LastName.
| | 06:41 | So we are going to be starting to group
these things together first by their territories--
| | 06:45 | so the Australia, United Kingdom,
United States--and then within each territory
| | 06:50 | we will be grouping by LastName.
| | 06:51 | Well, that would just give us a table.
| | 06:54 | To turn this into a matrix
we also need column groups,
| | 06:56 | and that is going to be grouped
first by year and then within that by
| | 07:01 | months inside that year.
| | 07:02 | Well, finally, the most important thing,
we need an actual value, which is the Sum_TotalDue.
| | 07:07 | This should be enough to
get our matrix configured.
| | 07:11 | I am going to click Next and select, yes,
the Blocked, subtotal above will do.
| | 07:15 | Stepped might work as well.
| | 07:17 | In fact, I'm going to select Stepped.
| | 07:18 | It's a little smaller on the page.
| | 07:21 | The functionality of these is exactly the same.
| | 07:23 | It's just a slightly
different visual appearance.
| | 07:25 | Then next into the style, and I'll
go with Corporate. Click Finish.
| | 07:30 | It's going to drag this into
the page and go ahead and run.
| | 07:35 | Now we have a matrix with multiple
levels of drilldown ability into it.
| | 07:40 | Reviewing our regions down the left-
hand side, that actually includes the blank
| | 07:44 | region where the word no regions
specified for that particular salesperson.
| | 07:48 | The year's along the top, and we can
drill down into a particular year and view
| | 07:52 | every month or we can drill down
into a region and view the individual
| | 07:57 | salespeople for that region.
| | 07:58 | Now, there is a bit of complexity going on here,
that the totals are a little bit too much here,
| | 08:03 | so I am going to jump back into Design view.
| | 08:05 | And I want to format all these
sums, all the various totals that
| | 08:10 | it's calculating for us.
| | 08:11 | I want them all as currency.
| | 08:12 | I could do them all
individually, but here is an easier way.
| | 08:16 | I click one of them to make sure
that I'm selecting the text box.
| | 08:19 | I don't want to select the individual text.
| | 08:21 | So click once and get the text box in
the lower right-hand corner and then
| | 08:25 | Shift+Click in the upper left-hand corner.
| | 08:27 | Again, all I am selecting here are
wherever we are summing totals together.
| | 08:31 | So it should be, for my
case, just these nine rows.
| | 08:34 | Come up to the number section on
the Ribbon. Change that to Currency.
| | 08:39 | We also don't need
anything after the decimal point.
| | 08:41 | I don't really mind what cents
were sold in a particular month.
| | 08:44 | So I am going to hit the
button to decrease decimal.
| | 08:47 | I will hit that twice.
| | 08:48 | In fact, I want to get rid of
everything after the decimal point.
| | 08:51 | So I am clicking it until it turns
gray and there is no more options there.
| | 08:55 | Let's run it again.
That looks a little better.
| | 08:57 | We are at least successfully formatting
these with the Currency format. So now what?
| | 09:02 | Well, a lot of this was meant to
be about adding a sparkline to this.
| | 09:06 | So back into Design view. What I want to
do is add a sparkline just right in here,
| | 09:11 | before the total amount.
| | 09:13 | Here is the best way to do it.
| | 09:15 | I am going to click anywhere in the
matrix to select it and then select
| | 09:19 | the column under Total.
| | 09:20 | Now, bear in mind you have multiple ones here.
| | 09:22 | I'm looking at the total for all
the regions, so the last column.
| | 09:26 | I am going to right-click the top gray
bar, the gray handle that I have here, and
| | 09:30 | choose to insert column to the left.
| | 09:32 | And I am going to be
inserting my sparklines here.
| | 09:35 | Now, the question is where, because
there is a big difference between putting a
| | 09:39 | sparkline in on this cell as opposed
to this one or this one or this one.
| | 09:44 | What I want is a sparkline that's
associated with each individual salesperson.
| | 09:50 | And that means I need to be conscious of
what row the salesperson name turns up
| | 09:54 | on, which is this one, where we
have last name being spat out.
| | 09:58 | If I had added the sparkline on
this cell, it would be a sparkline for
| | 10:02 | the entire territory.
| | 10:04 | Reporting Services and Report Builder
is very sensitive to where you put your
| | 10:08 | sparklines in a matrix, because it's
going to try and target the right data
| | 10:12 | for that sparkline.
| | 10:13 | So I want it in here.
| | 10:15 | And here's how we do it. Up to the
Insert section, select Sparkline, and then
| | 10:19 | just click in that area.
| | 10:21 | So clicking in that cell
gives me the sparkline type.
| | 10:23 | I'm going to do actually
a column sparkline here and click OK.
| | 10:27 | We are seeing a little
bit of dummy data in here.
| | 10:30 | Now, it's not going to understand what data
it should be showing, but that's easy to add.
| | 10:35 | I select the sparkline itself, so
clicking in it or right-clicking in it until I
| | 10:40 | get the chart data window.
| | 10:41 | And I'm going to drag across the Sum_TotalDue.
| | 10:44 | Again, be very helpful where you're
dragging, because if you drag across another
| | 10:47 | element, like the matrix,
it's easy to mess things up.
| | 10:50 | So drag that into Values, and then in Groups I
want to split this up by year and month again.
| | 10:56 | So over into Groups, first is
OrderYear and then is OrderMonth.
| | 11:00 | I don't need to worry about Territory
and LastName, because I'm already putting
| | 11:05 | in the correct row that's already
been split up by Territory and LastName.
| | 11:09 | So Reporting Services should be smart
enough to understand the correct data and
| | 11:13 | correct numbers to show. So let's try this.
| | 11:16 | Go ahead and run.
| | 11:17 | I don't see anything right now,
because that cell, that particular sparkline,
| | 11:21 | should only appear when I can
see the individual last names.
| | 11:24 | So I'll open up Canada, and yup,
I'm getting the sparklines appear.
| | 11:28 | These are column sparklines.
| | 11:29 | I open up France, I see that one.
| | 11:31 | I open up the Northwest,
I see several sparklines.
| | 11:34 | Now, one of the problems that I'm
having here is the sparklines aren't
| | 11:38 | necessarily matching up against each other.
| | 11:40 | Say, for example, if I look at the
settings for France, there were no sales at all
| | 11:44 | in 2005, but I'm still seeing some
entries in here, some columns showing up.
| | 11:50 | And that's because the sparklines by
default are going to fill themselves to the
| | 11:55 | width of the available data.
| | 11:57 | What I might want to have happen is
that they be a bit more representative, a
| | 12:02 | bit more compared to each other.
| | 12:04 | Meaning that instead of each sparkline
filling as wide as it can, I'd like it to
| | 12:08 | pay attention to what the other
sparklines are during in this matrix.
| | 12:13 | Here's how I'd do that.
| | 12:14 | Back into Design view. I am going
select that sparkline again and
| | 12:18 | then right-click it.
| | 12:19 | What I'm hoping for are
Sparkline properties here.
| | 12:22 | And we have two options here, for
the Vertical Axis Properties on the
| | 12:25 | Horizontal Axis Properties.
| | 12:27 | Horizontal is what I am first interested in.
| | 12:29 | I will select that. And it's first
asking is this category or scalar, meaning is
| | 12:34 | it going by numbers or dates?
| | 12:36 | You might think we need to go by
numbers and dates, but we don't really need to
| | 12:40 | do that because we're already putting
things together absolutely fine by our
| | 12:43 | explicit years and months.
| | 12:46 | What I need instead is this, option
to align the axes, not just inside the
| | 12:50 | territory, but in the larger
matrix, or Tablix region, and click OK.
| | 12:56 | I don't see any visual change here, but
if I run this again and start expanding
| | 13:00 | some of these, what I will actually
see is a different kind of layout now
| | 13:04 | representing different amounts of data
for the people who have been doing this
| | 13:08 | all along and the people who have
only been doing it for a shorter time.
| | 13:12 | Similarly, what we have actually got
happening is that the horizontal axis here
| | 13:16 | might be mismatching.
| | 13:18 | What I mean by that is we may have
people that are doing immense amounts of
| | 13:21 | sales, like Mitchell in the Southwest,
but Mitchell's columns are just as high
| | 13:26 | as, for example, this entry up here in
Australia whose numerical values are
| | 13:31 | actually much less.
| | 13:32 | So now we are actually
matching across the horizontal.
| | 13:35 | We also need to compare
these across the vertical.
| | 13:38 | It's done in a very similar fashion.
| | 13:40 | You don't have to do this.
| | 13:41 | You might find your sparkline
useful enough, but if you want them to be
| | 13:44 | scannable and comparable with
the other, then we jump back in,
| | 13:47 | I go to the Vertical Axis Properties, and
also align these axes within the matrix.
| | 13:53 | Click OK and run this.
| | 13:55 | Now what we should see is it taking
something was actually lower amount of
| | 13:59 | sales, like the Australian region, and
comparing that to the Southwest region
| | 14:03 | with higher sales, we should be able to
relate them to each other and find them
| | 14:07 | easier to just visually scan and
realize who is actually doing more numbers
| | 14:11 | than another person.
| | 14:12 | From this point, we are likely to
want to experiment with the width of the
| | 14:16 | columns that the sparkline is in, and
also with the formatting. You'll find that with
| | 14:20 | the sparklines selected,
| | 14:22 | you do have things like the color
palette that you can experiment with. Very
| | 14:26 | similar to working with a normal chart.
| | 14:28 | But once you've fueled up with the
correct data, well, you can experiment
| | 14:32 | with these all you want.
| | Collapse this transcript |
| Adding data bars| 00:00 | While sparklines are visually simple,
sometimes you need something that's even simpler.
| | 00:05 | So next, we are going to talk
about how to add data bars.
| | 00:08 | These are another mini chart option very
similar to sparklines, and they are best
| | 00:13 | for representing a single data point.
| | 00:15 | The best way to explain
them is just to show them.
| | 00:17 | I am going continue to use this
matrix that I just created, and what I am
| | 00:21 | going to do is add a data bar element that
visually represents the total sales per region.
| | 00:29 | What mean by that is having some way
of quickly scanning this data, even when
| | 00:33 | it's collapsed, and just easily see
which of these numbers compare to which of
| | 00:38 | these other numbers.
| | 00:39 | Okay, it's not that hard to see right
now, but if I had a few dozen more it
| | 00:42 | might be hard to scan them. So, into my Report
Designer where I have this report,
| | 00:47 | what I'm going to do is add a new column,
but this time to the right-hand side
| | 00:50 | of the existing total sections.
| | 00:52 | So I'll grab the column here and up at
the top, in the gray handle, I will insert
| | 00:57 | column to the right.
| | 00:58 | Adding a data bar is very
similar to adding a sparkline.
| | 01:01 | Again, you want to be very conscious
when you are adding it to a table or matrix
| | 01:04 | where exactly you add it, on
what cell that you are clicking in.
| | 01:09 | If I add it to this cell that's at the
same level to the existing sparkline, I'll
| | 01:13 | be adding a data bar for
each individual salesperson.
| | 01:17 | I actually want to add it at the
territory level, which is this row,
| | 01:21 | so I want add it in this cell here.
| | 01:24 | What I do is come up to the Insert tab,
single-click Data Bar, and then take my
| | 01:28 | mouse down, and click on the cell
I'm interested in, which is this one.
| | 01:32 | Data bar types are very simple.
| | 01:34 | We are asked, do we want a data bar
left to right or column up and down.
| | 01:38 | I am going to select the basic Data Bar option.
| | 01:40 | A data bar is based on the same
concepts as a sparkline and a chart,
| | 01:44 | so if we select it and select it again,
we should have our chart data appear.
| | 01:49 | Very commonly might be off to the
right-hand side. So there we go.
| | 01:53 | So you have it deselected, you
click it once and then click it again.
| | 01:57 | You should be able to get
that Chart Data section.
| | 01:59 | All I am going to do is open up my
dataset, and I want to drag in the
| | 02:04 | Sum_TotalDue amount that's being
added up and drag that into the Values
| | 02:08 | section for that data bar.
| | 02:10 | Again, because there is a matrix
in between the Sum_TotalDue on the
| | 02:15 | left-hand side and the chart data
on the right, I do you want to be
| | 02:19 | I am dragging it over a blank area of the
report and into the Values section, and let go.
| | 02:19 | careful where I'm dragging this.
| | 02:24 | Now, normally with charts, we are also
very focused on grouping things together by
| | 02:28 | category, but I don't actually need to
do that here, because this amount that
| | 02:33 | it's totaling will already be grouped
into that territory regional total based
| | 02:38 | on the fact that I'm adding it
just to this row in the matrix.
| | 02:41 | What it's simply going to do is be a
visual representation here of the amount
| | 02:46 | that's actually going be showing up here.
| | 02:48 | So go ahead and run this.
| | 02:50 | Now we have this single data point
data bar that very quickly gives us a
| | 02:55 | representation of what these total amounts
are showing up for each particular region.
| | 03:00 | It takes no time at all to instantly
jump to this one as the largest one--
| | 03:03 | indeed it is 18 million, compared to the
Australia and Germany regions that are much lower.
| | 03:09 | Like charts and like sparklines
data bars themselves can be configured.
| | 03:13 | You select them, you find you've got the
different color palettes that you can select from.
| | 03:17 | We can click the individual data
point and even start playing around with that.
| | 03:21 | They can be configured to actually be
broken down into categories, but it's much
| | 03:25 | more common to use them just to
display a single data point like this.
| | Collapse this transcript |
|
|
4. Indicators, Gauges, and MapsAdding indicators to a report| 00:00 | We've been working our way through the
options on the Insert tab, and the next
| | 00:04 | two up in the Data Visualization
section are Gauges and Indicators.
| | 00:08 | I'm going to talk about both of these
at the same time because they're very
| | 00:11 | similar to each other.
| | 00:13 | They're both simpler than charts in that
they typically just display one data point.
| | 00:18 | But they're also configured to tell us
whether that data point is good or bad or
| | 00:22 | how far along a scale it's supposed to be.
| | 00:24 | So, I have a blank report here with
a data source pointing to the regular
| | 00:29 | AdventureWorks database.
| | 00:30 | I'm going to go and get us some data.
| | 00:33 | So creating a new dataset, I'll call
this one TerritoryInfo, and jumping into
| | 00:42 | Query Designer, I'm going to start
drilling down into the Tables section, and
| | 00:46 | I've got a SalesTerritory table--
| | 00:48 | I don't think we've used this one
yet--where I will get the name of the
| | 00:52 | territory, the Sales Year To
Date, and the SalesLastYear.
| | 00:56 | I'll just go ahead and run this query to
see if this might be useful data for us.
| | 01:00 | It looks about right.
| | 01:01 | We're getting these different regions:
| | 01:03 | SalesYTD, SalesLastYear.
| | 01:04 | There is not a ridiculous amount of data,
| | 01:08 | so it will be good to
have on a table. Click OK.
| | 01:11 | That's my dataset.
| | 01:12 | Now, while we can add gauges and
indicators completely independently, like
| | 01:17 | sparklines, they're very
commonly seen on a table or a matrix.
| | 01:21 | So I'm going to go ahead and insert a new
table based on that dataset I just created.
| | 01:28 | And all of these columns, I'm just
going to put in the Values section.
| | 01:31 | I'm doing no grouping whatsoever,
either up and down or left to right.
| | 01:36 | Clicking Next, I'll just go right
past the layout, Next again, and this
| | 01:40 | time I'll pick Slate.
| | 01:45 | Run this, just to test everything looks okay.
| | 01:48 | We are getting that information out.
| | 01:51 | I don't really need the four decimal
places here, and I'd like to make it
| | 01:53 | apparent this is a currency amount.
| | 01:56 | So I'll jump back into Design view,
just select in there to grab that text box,
| | 02:01 | and I can Shift+Select to get the
second one, and change both of the formatting
| | 02:05 | here to Currency, and then click the
button to decrease the decimal point until
| | 02:10 | it grays completely out so that we're not
showing any decimal numbers at all. That looks good.
| | 02:16 | Again, what we're trying to do here
is get the idea. We could have a massive
| | 02:20 | amount of numbers, and I'd like to
be able to quickly scan them to see if
| | 02:24 | something is good or bad.
| | 02:25 | What I'm going to do is add a new column
to this table and put an indicator in it.
| | 02:31 | So to add a new column, I'll just click
somewhere in the table here to make the
| | 02:35 | gray handles appear, then grab this
last column, right-click the gray area, and
| | 02:40 | insert a new column to the right.
| | 02:42 | And I'm going to insert an
indicator right here in this cell.
| | 02:46 | We want it to be the same cell that
we're actually seeing the name of the
| | 02:50 | territory on, or the sum of SalesLastYear.
| | 02:54 | Like sparklines, it's very
sensitive to where you place indicators.
| | 02:58 | We don't want to put them in the header row.
| | 03:01 | So, up to the Insert tab, click
Indicator once, down into the cell I'm
| | 03:05 | interested in, and click again.
| | 03:08 | Our Indicator Type window appears.
| | 03:11 | All of these are configured the same way.
| | 03:13 | They're just a different
graphical look and feel.
| | 03:15 | You can change these later
after the fact if you want to.
| | 03:19 | I'm going to pick something basic,
| | 03:20 | such as this set of three shapes here--
the traffic light idea--and click OK.
| | 03:26 | We see that ended in the column.
| | 03:27 | Well, what happens if we run this?
| | 03:29 | Absolutely nothing, because that
indicator has no idea what it's meant to display.
| | 03:35 | Back into Design view, I'm going to select it.
| | 03:38 | Selecting indicators and gauges can
sometimes be tricky because they are
| | 03:42 | wheels within wheels.
| | 03:43 | There is a couple of things we have
to be careful of when clicking around.
| | 03:47 | So, if I click again,
I'll eventually select this indicator.
| | 03:50 | I'll see this little Gauge Data window
appear, because an indicator is a gauge.
| | 03:55 | That's why we're doing both
of these at the same time.
| | 03:57 | And it has this dropdown window that's very
similar to working with a chart or a sparkline.
| | 04:03 | Right now, it's asking for one value.
| | 04:06 | It's not asking for groups.
| | 04:07 | It's not asking for series.
| | 04:08 | It's just saying, hey,
what number am I supposed to represent?
| | 04:12 | Well, I'm interested in this being a
graphical representation of the sales year to date,
| | 04:17 | so I'm going to drag that
over here and drop it in.
| | 04:22 | Go ahead and run it.
| | 04:23 | Well now we're getting indicators,
although the question might be, what is this
| | 04:27 | actually meant to be representing?
| | 04:29 | We've got some green, some red, some yellow.
| | 04:31 | An indicator is always fueled by one
single value, but it needs to have some
| | 04:36 | rules about whether that value is good or bad.
| | 04:38 | Now, when you drag one on,
it defaults to a percentage rule.
| | 04:43 | The values of the top 33% are in green,
the middle in yellow, the bottom in red.
| | 04:47 | Now, it doesn't mean you'll always see
equal amounts of red, green, and yellow,
| | 04:51 | as the numbers that these are
representing--which is my SalesYTD--aren't
| | 04:56 | distributed exactly equally, so only
two of them--the most high numbers--might
| | 05:00 | actually represent 33% of the total.
| | 05:04 | Having said that, a pure percentage
rule isn't what you want most of the time
| | 05:08 | anyway, so let's see how to change that.
| | 05:10 | Drop back into Design view.
| | 05:14 | I'll highlight this, clicking
the Gauge again till this appears.
| | 05:18 | We can't do anything really more here,
but I can right-click and I've got an option
| | 05:22 | called Indicator Properties.
| | 05:25 | If you don't get the option Indicator
Properties, make sure you're clicking in
| | 05:28 | the right place, because with that open,
I have an option for Value and States,
| | 05:34 | and this is where all the
magic happens for an indicator.
| | 05:36 | It's how you determine which
indicator actually shows up.
| | 05:41 | First, it's saying okay, I'm displaying
the value of sales year to date for each row.
| | 05:46 | That's fine, no problem there.
| | 05:48 | But the next option is, am I
displaying a percentage or am I based on a
| | 05:53 | specific numeric value?
| | 05:54 | Well I'm going to switch
to a numeric value here.
| | 05:57 | And all I want to do is make this
indicator quickly represent whether this
| | 06:02 | year's sales have already
beaten last year's sales.
| | 06:06 | So in fact, I don't even
need the yellow indicator here,
| | 06:09 | so I'm going to highlight an
area of that row and just delete it.
| | 06:13 | And in this section I describe the rules.
| | 06:16 | Why should it show up in red,
why should it show up in green?
| | 06:19 | I'm going to say if sales year to date
have been anything from 0 all the way up
| | 06:24 | to the same as sales last year, then it's red.
| | 06:28 | If it's from sales last year plus one
dollar, or anything above that, then it
| | 06:33 | can show up in green.
| | 06:34 | So, 0 is fine as a starting point,
but 33 is not good as an ending point.
| | 06:39 | I'll just delete that, and I'm
clicking the little expression builder here.
| | 06:42 | So it's asking for an expression for the
end value, and all I want to do is jump
| | 06:47 | to my Fields section for this dataset
and say I'm interested in this being more
| | 06:52 | than sales last year. Click OK.
| | 06:56 | Next, I have to describe the rules for green.
| | 06:58 | Well, it's not going to start at 66,
so I click the expression builder. And I
| | 07:02 | want the button to the right-hand
side, because the one to the left is
| | 07:06 | controlling a dynamic color.
| | 07:08 | That's not what I want here. So click this.
| | 07:11 | What I'm going to do here again is
we're going from sales last year and then I
| | 07:15 | just enter in plus 1, because
we want to be beat it. Click OK.
| | 07:20 | I don't want that one to have an end.
| | 07:23 | As long as it's greater than last year's
sales plus one dollar, it can show up in green.
| | 07:28 | What you'll also find over here
is the ability to change the icon.
| | 07:32 | We have a whole list of them,
| | 07:34 | so if you want to change
it, here's how you do it.
| | 07:36 | In fact, I might even change that
to a smiley face. Click OK and run.
| | 07:43 | Now we have the indicator showing up
as long as the sales year to date have
| | 07:48 | beaten sales last year.
| | 07:50 | Very simple to implement!
| | Collapse this transcript |
| Using and configuring gauges| 00:00 | Think about a gauge in real life, a fuel gauge
on your car, a battery gauge on a cell phone.
| | 00:06 | A gauge does represent a value,
but you don't actually care what that value
| | 00:09 | explicitly is; you care about its proportion.
| | 00:12 | Am I full, am I half full, am I nearly empty?
| | 00:15 | So I'm going to add a gauge to this table here.
| | 00:18 | First, I'll add a new
column to put the gauge in.
| | 00:21 | So selecting anywhere in the table,
grab the grab handle for this particular
| | 00:25 | column, right-click it,
and insert one to the right.
| | 00:28 | I'll drag it a little wider
because I want a bit more space.
| | 00:32 | I'm going to add a gauge into this
particular cell because I want it per row for
| | 00:37 | the information that I'm
getting about the territories.
| | 00:39 | It's added just like sparklines
and data bars and indicators.
| | 00:43 | I go over to the Insert section, select
Gauge, click it once, and then come over
| | 00:48 | here and just single-click in the cell.
| | 00:50 | And we have a variety of gauges
appear just like we'd have a variety of
| | 00:54 | sparklines or a variety of charts.
| | 00:56 | Several fuel style gauges, linear gauges,
thermometer-style gauges, and so on.
| | 01:02 | Because I'm inserting this gauge into
a table, I'm going to go left to right
| | 01:07 | rather than up and down.
| | 01:08 | So, I'm going to select the very
last option, which is called Bullet Graph
| | 01:12 | here, and click OK.
| | 01:13 | Now, it's a little squished here, and
you will find that gauges in Reporting
| | 01:17 | Services really do expect a bit more room
than, say, just a simple indicator or a value.
| | 01:22 | So, I'm going to drag the column a little
bit wider, just so we can see more of it here.
| | 01:26 | And what I'm going to make this gauge
represent is, say, a $10 million bonus
| | 01:31 | contest, where the sales year to date
is going to push this pointer up and down
| | 01:38 | somewhere in the scale.
| | 01:39 | Now, just like working with indicators,
the gauge is only going to display one
| | 01:44 | value, and that's going to be
represented by what's called the pointer here.
| | 01:48 | But the same way that an indicator
displays one value but it has to be told
| | 01:52 | whether that's good or bad, the pointer
displays one value but needs to be
| | 01:56 | told where on the scale that goes.
| | 01:58 | Well, the first thing we
do is configure the pointer.
| | 02:00 | When you're working with gauges, you
have to be very careful what you have
| | 02:04 | clicked, because as I click around, I
can get the Linear Gauge option here
| | 02:07 | I see on the Properties window.
| | 02:09 | If I click somewhere else in
it, I can get a linear range.
| | 02:12 | I can get a linear scale.
I can get a LinearRange1.
| | 02:15 | I can get the pointer again.
| | 02:16 | So, it can be difficult to
actually find what you're looking for.
| | 02:19 | Right now I want the pointer.
| | 02:21 | So I'm clicking in here until I see
this LinearPointer show up, and I can see
| | 02:26 | the highlight around it.
| | 02:27 | What I'm looking for is to be able to
right-click it and see Pointer Properties.
| | 02:32 | This is where I can set
the value of the pointer.
| | 02:34 | What should this be fueled by?
| | 02:36 | And I'm just going to connect it
to Sales Year To Date and click OK.
| | 02:41 | If I close my Properties panel here,
you'll see that I also have Gauge Data
| | 02:45 | popping up on the right-hand side the
same as with an indicator or a chart.
| | 02:49 | You can also drag and drop there.
| | 02:51 | Well, is this enough? Let's find out.
| | 02:53 | I'll go ahead and click Run.
| | 02:55 | I'm getting the gauge show up, but the
problem is it's pushing out to 100% for
| | 02:59 | every single one of them, because the
number I'm giving it is just a little bit too
| | 03:04 | big for the scale, which is
oriented to 0 through 100%.
| | 03:08 | So, back into Design view,
because I need to change something else.
| | 03:11 | The pointer is fine.
| | 03:12 | I need to change the scale.
| | 03:14 | So I click around till I find the Scale area.
| | 03:18 | It's sometimes helpful to have your Properties
window open so you can see this linear scale.
| | 03:22 | Another clue is by right-clicking.
| | 03:25 | What I had a moment ago was Pointer Properties,
what I want to see now is Scale Properties.
| | 03:30 | Select that, and we have several options here.
| | 03:33 | The main thing we're interested in to
make it show up correctly is, what are our
| | 03:37 | minimum and maximum ranges, and
they are defaulted to 0 through 100.
| | 03:41 | Well, that's not what we want, because we're
giving it a value that could be 10 million.
| | 03:46 | And in fact, 10 million is the max.
| | 03:48 | That's what we're going
for is that 10 million bonus.
| | 03:50 | So, I'm actually going to just type
directly in the number 10 million and click OK.
| | 03:55 | We'll run this again, and actually,
we have the pointer correct now.
| | 04:01 | The problem is, the labels
look absolutely terrible.
| | 04:04 | So, back to the drawing board,
into Design view. Click Scale again.
| | 04:08 | We're clicking around a
little bit till we find it.
| | 04:11 | I want the Scale properties.
| | 04:12 | Our Minimum and Maximum are fine.
| | 04:15 | I do have the options in the Labels
section to actually just turn them off.
| | 04:19 | If I select the labels of their
properties, I can say Hide scale labels, and
| | 04:22 | they just disappear, and that might be fine.
| | 04:25 | Without the labels, I could close
that row up a little bit so it isn't as
| | 04:28 | big as it was before.
| | 04:30 | But another option that I have is just
changing the format of what the labels show.
| | 04:34 | So, I'm going to come down to the Number
section and say that this label should
| | 04:38 | show up as a number.
| | 04:39 | And one of the options I have here is to
check the Show values in and say Millions.
| | 04:46 | We take it to Millions, but even as I'm
clicking around, I can see it's doing 4.00, 6.00.
| | 04:52 | I really don't need the decimal
places, so I'll get rid of those.
| | 04:56 | And now the tick marks represent how
far we are up on a $10 million scale.
| | 05:01 | There are other options here, such as
getting rid of the minor tick marks or
| | 05:06 | the major tick marks.
| | 05:07 | With this being a little cleaner, I can
shrink that column just a bit more, and
| | 05:11 | we'll go ahead and run it.
| | 05:12 | Now, we can tell at a glance who is
up at the top of the scale and who is
| | 05:16 | nowhere near that top.
| | 05:18 | So, experiment with the other options.
| | 05:21 | Be careful when you're working with
gauges, that you have the right thing
| | 05:24 | selected at the right time.
| | 05:26 | This is how you provide
indicators and gauges on your reports.
| | Collapse this transcript |
| Using maps in Reporting Services| 00:00 | Time to cover the last option in the Data
Visualization section, adding maps to our reports.
| | 00:06 | As you might imagine, if you want to
use maps, it does put some expectations on
| | 00:10 | the data that you have.
| | 00:11 | It needs to be geographic in some fashion.
| | 00:13 | While you can go very exact, all the
way to working with GPS latitude and
| | 00:18 | longitude coordinates, you
don't need that to begin with.
| | 00:20 | You can start off as long as you
have some cities or states or counties.
| | 00:24 | So, I want to add a map to this blank
report that I just created a moment ago.
| | 00:28 | I will choose the Map
option and select Map Wizard.
| | 00:31 | The first thing I'm asked for
is a source of spatial data.
| | 00:35 | Now, all maps in Reporting
Services display two kinds of data:
| | 00:39 | spatial data and analytical data,
which is really what's the map and
| | 00:44 | what's important about it?
| | 00:45 | Spatial data describes the points, the
regions or lines or areas of the map.
| | 00:50 | Spatial data is what places exist.
| | 00:52 | Now, right out of the box, I have a
few examples in the Map Gallery section
| | 00:57 | here that I can use.
| | 00:59 | It is somewhat USA centric, but I have
a folder with all the states by county
| | 01:03 | that I can start to drill through and take a
look, get a basic idea of what this map is.
| | 01:07 | And the spatial data is what's describing
the boundaries of all these regions on the map.
| | 01:13 | Underneath that I have three
versions of a larger USA map as well.
| | 01:18 | Up above, there are options to change this
from the Map gallery to an ESRI shape file.
| | 01:23 | If you have access to those, some of
them are commercial, some are freely
| | 01:26 | available that you could link to, for
other countries and world maps and so on.
| | 01:31 | And if you have a lot of GIS data
internally, you could even write your own SQL
| | 01:35 | Server spatial query that actually
returns coordinates, points, and lines and
| | 01:40 | regions that you can work with.
| | 01:42 | I'm going to go with the basic map
gallery here and use USA by State Inset.
| | 01:47 | Click Next. I get a few options about
just positioning the map information if I
| | 01:52 | want to. Zooming in or out.
| | 01:54 | There's an option at the bottom here
to even add a Bing Maps layer, which will
| | 01:59 | underlay the map with either
road or aerial or a hybrid version.
| | 02:03 | It looks interesting, but I really don't
need that right now, so I am going to
| | 02:07 | uncheck that box and just use a
normal map, and click Next here.
| | 02:11 | This next section of the map
visualization is somewhat deceptive.
| | 02:14 | At first, it might look like picking a color
palette for a chart, but it's very different.
| | 02:19 | If I use the Basic Map option, all I am
really going to add is the spatial data.
| | 02:24 | It's just the map, but nothing about the map.
| | 02:27 | If, on the other hand, I choose the Color
Analytical Map or the Bubble Map, it's
| | 02:31 | going to ask me for a source of analytical data.
| | 02:34 | What's important about this map?
| | 02:36 | What am I trying to show
about the individual states on it?
| | 02:40 | Now, I actually don't have a dataset yet.
The dataset would be my analytical data.
| | 02:43 | So I am just going to choose Basic Map
so we can step all the way through this.
| | 02:48 | Just like working with charts or tables,
we have the Color Theme options here.
| | 02:52 | There is a checkbox to change this into
a single color map, which sometimes works
| | 02:56 | well and sometimes doesn't.
| | 02:58 | I am just going to go ahead and click Finish.
| | 03:00 | That's now added to my report.
| | 03:02 | And while I can go ahead and run
this, it's not going to do anything.
| | 03:06 | We might have the map, but we have no
descriptions of anything important about the map.
| | 03:10 | We have no analytical data.
| | 03:12 | Now, there's a couple of ways this can be added.
| | 03:15 | If I've already added the map here,
what you can start doing is adding more
| | 03:19 | layers over the top of it.
| | 03:20 | By clicking the map and clicking
again, you'll get the pop-up window very
| | 03:24 | similar to working with charts or
sparklines, where it shows that right now I
| | 03:28 | have one layer on this map, and I can
use these options at the top to create a
| | 03:32 | new layer, which will again ask me for
spatial data and/or analytical data.
| | 03:37 | If, on the other hand, I wanted to
specifically add analytical data to this map,
| | 03:43 | I can do that as well.
| | 03:44 | Right-clicking the layer will allow me to
bring up an option for Layer Data, and in there
| | 03:50 | I can provide a dataset of analytical data.
| | 03:53 | Could do it that way. I'm not going to.
| | 03:55 | I am actually going to use
the wizard all the way through.
| | 03:57 | I am just going to cancel out of that,
but we need some analytical data first,
| | 04:02 | which means create a dataset.
| | 04:03 | I will add a normal dataset;
there is nothing magical about this one at all.
| | 04:07 | I am going to connect to AdventureWorks.
I had a data source defined earlier.
| | 04:11 | And in my Query Designer, I'm going to
drill down into Sales, into Views, into
| | 04:16 | the vIndividualCustomer, and all
I'm going to look for here is the
| | 04:20 | StateProvinceName, and one other
column. I will go with BusinessEntityID.
| | 04:24 | Now, if I run that query, what I'm
seeing here is things like New South Wales,
| | 04:29 | British Columbia, Seine Saint Denis,
Hamburg. Okay, I'm going to put a filter
| | 04:34 | on this to only bring back the US ones, because
I am going to map this to the United States map.
| | 04:40 | So I will add a filter to this query.
| | 04:42 | I am going to filter on CountryRegionName.
| | 04:44 | I know from looking at this
data that that is United States.
| | 04:48 | And that needs to be an is and not a like.
| | 04:51 | Go ahead and run this query.
| | 04:53 | And now what I'm getting back are
various state province names, California,
| | 04:56 | Washington quite heavily involved
there, with just a Business Entity ID that I
| | 05:00 | don't really care about.
| | 05:01 | As far as I'm concerned, I'm done.
| | 05:04 | This is my dataset.
| | 05:05 | It has all the data that
I will need for this map.
| | 05:07 | Rather than add this as an analytical
source of data, I am actually just going
| | 05:11 | to run through the wizard again, because
it's always good to look at it once or twice.
| | 05:15 | So I am going to select the map, delete it,
and we're going to add a new map here.
| | 05:19 | Back to the Insert Tab, add the Map Wizard.
| | 05:22 | Once again, I'm going to use the Map
Gallery. My spatial data will just be the
| | 05:26 | built-in USA by State Inset. I will click Next.
| | 05:29 | I don't need to make any changes here.
| | 05:31 | I will click Next again.
| | 05:33 | This time around, I could choose
Color Analytical or Bubble Map.
| | 05:36 | I am going to go with Bubble Map.
| | 05:38 | But by selecting that, the very next
thing it's going to ask me for here is to
| | 05:42 | choose the analytical dataset.
| | 05:43 | Okay, I want to represent some
information on this map. Where is the
| | 05:47 | information? So I am going to
choose this dataset that I just created.
| | 05:51 | It has a list of city names.
| | 05:53 | I am going to ask the map to count
them up and to represent them properly.
| | 05:57 | Selecting that, click Next.
| | 05:59 | Now comes the important part.
| | 06:00 | Here is how we tie together our spatial
and our analytical data, to match up the
| | 06:06 | information in our normal dataset so
it shows up in the right place in our
| | 06:10 | spatial data, in our map.
| | 06:12 | It might look confusing. It really isn't.
This middle section contains data
| | 06:16 | that's already in the map.
| | 06:17 | It's already in our spatial data.
| | 06:19 | We have the STATEFP, which is the
FIPS code, the Federal Information
| | 06:23 | Processing State code.
| | 06:25 | We have the USPS
abbreviation, and we have State Name.
| | 06:29 | All those options are
available, built into the map itself.
| | 06:32 | What I need to do is figure out what's in
my analytical data that I can map this to.
| | 06:37 | I can see that really
StateProvinceName needs to connect to STATENAME.
| | 06:42 | So that's what I am going
to select is the Match field.
| | 06:45 | Check the checkbox here
| | 06:46 | that STATENAME in the Spatial
dataset connects to StateProvinceName in
| | 06:50 | my Analytical dataset.
| | 06:52 | Now, click Next. It's going to ask me
for the color theme and visualization.
| | 06:57 | I'm going to select the Generic option.
| | 07:00 | Because I picked a bubble map, it's asking me,
do I want to use bubble sizes to visualize data?
| | 07:04 | And yes, I actually do.
| | 07:06 | I want to have a bubble that represents
customer count in every particular state.
| | 07:10 | So, with that checked, it's
asking, what data am I representing?
| | 07:14 | And I need to click the dropdown
because what I want it to do is do a count of
| | 07:18 | the Business Entity IDs, just count
up all the companies that came back for
| | 07:22 | that particular state.
| | 07:24 | I don't care what any
particular Business Identity ID is;
| | 07:27 | I just want a count of all
of them. So I will do that.
| | 07:30 | And below this, there is
an option to display labels.
| | 07:33 | I don't have to have that, but I find it useful.
| | 07:35 | But I would want the labels to have
the same information, which is count of
| | 07:38 | business entity ID, and I'll click Finish.
| | 07:41 | Giving myself a little more room on
this report, we'll go ahead and run it,
| | 07:46 | and there is our map.
| | 07:47 | And it's immediately obvious where the
AdventureWorks customers are primarily
| | 07:52 | distributed, all along the west coast here.
| | 07:54 | Over 4,000 customers in California, 1,000
in Oregon, 2,000 in Washington, with just
| | 08:00 | a smattering of others
all over the rest of the US.
| | 08:03 | So this can immediately give us a view
of the data that's much more intuitive
| | 08:07 | than looking at long lists in a table,
which is the only way we would have been
| | 08:12 | able to get this before.
| | 08:13 | Getting more advanced, you can create
your own spatial datasets to overlay lines
| | 08:18 | and regions on top of this, but that
is beyond the scope of this course.
| | 08:22 | So hopefully, this will get you
moving on the most common tasks with maps.
| | Collapse this transcript |
|
|
5. Creating Large ReportsCreating modular reports with report parts| 00:00 | So far, in all the reports I have created I
have just been using one element per report,
| | 00:05 | so one table, or one matrix, one chart
or one map. It is of course much more
| | 00:10 | likely that we will want to create reports
that combine multiple elements at the same time,
| | 00:15 | and you can just do that, just add
multiple elements to the same report and
| | 00:18 | configure them the way you want them.
| | 00:20 | But its often very useful,
particularly when learning Reporting Services, that
| | 00:24 | you don't want to try and create this
massive report all at once; instead, it's
| | 00:27 | more useful to configure the pieces
individually and combine them later.
| | 00:30 | And one way to do this is using
report parts. It's a simply idea: take an
| | 00:35 | element of report like a matrix or
a table and just save it by itself,
| | 00:39 | without the rest of the report, without
titles or footers, just the table, just
| | 00:44 | the chart, just the matrix,
and save it to the server.
| | 00:47 | And if that report part is a data
region it can be saved together with its data
| | 00:51 | source and dataset embedded inside it.
| | 00:54 | Okay, so how do we make one?
| | 00:55 | Well, to start with, we do nothing
different. We still create a regular report.
| | 01:00 | We add and configure elements
as we have been doing all along.
| | 01:03 | But then if we look at a part of that
report and think, you know, I could use
| | 01:07 | this table or this matrix or this
chart again, and I don't want to have to
| | 01:11 | re-add and reconfigure it,
| | 01:12 | I make that a report part.
| | 01:14 | So, I'm in a simple Report here.
| | 01:16 | It's actually the first report I created
with the very straightforward Customers
| | 01:21 | Table here, and I want to
create that as a report part.
| | 01:24 | The only other thing that I
have is a text box with an address.
| | 01:27 | I might actually like to publish that is a
report part so it's easy to reuse, so let's see how.
| | 01:32 | Now you may have seen, when the jumping
around Report Builder, that there is a
| | 01:36 | Report Parts button on the Insert tab,
but that's not what we want here.
| | 01:40 | That's how to use existing report parts,
but none exist yet; instead the button
| | 01:45 | we want is the very top-left
one, the circular Office button.
| | 01:49 | And that is an option to Publish Report Parts.
| | 01:53 | You don't actually have to have
anything selected on your report, as it will
| | 01:57 | tell you which parts of this
currently opened report you can publish.
| | 02:01 | I click that button I get two
options, to either publish everything in
| | 02:04 | this report as a report part with the default
setting or to review them before publishing.
| | 02:09 | I'll always check the second option.
| | 02:12 | This scans the currently opened report
and says, okay there is only one option
| | 02:16 | here that I can publish as a
report part; it's that Tablix or Tablix.
| | 02:20 | Now this first point add to me
that it might be useful to give this a
| | 02:24 | slightly better name.
| | 02:25 | If I mouse over a little Informational
button here, down at the bottom it'll
| | 02:29 | tell me that this report part will
reference and embed a data source.
| | 02:32 | And that's fine. If I wanted to extract
the data source and dataset out, I could
| | 02:37 | actually do that by coming
to this section for datasets.
| | 02:40 | I'm not going to; I am just
going to leave it as the Default.
| | 02:43 | However, I am going to click the little
triangle to expand it, because I can add
| | 02:47 | a description of this report part, and that
will make it easier to find the right one later.
| | 02:52 | However, before I publish this, what I
want to do is change the name of this from
| | 02:56 | Tablix or Tablix1 to something better.
I am going to just close this window,
| | 03:00 | highlight the table, and grab the left
corner, and then in the Properties window,
| | 03:06 | find the Name section and change
it from Tablix1 to Customer Table.
| | 03:11 | But I had also said that I wanted to
take this Address here and also publish
| | 03:16 | that as a report part,
so why didn't that appear?
| | 03:18 | Well, it's because you can't just
publish regular text boxes; something has to
| | 03:23 | be either a data region--a matrix, a
table, a chart, or a map--or a rectangle.
| | 03:28 | Rectangles can be published.
| | 03:29 | So, if you want to publish, say, some text that's
grouped together, put it in a rectangle first.
| | 03:33 | So, I am going to grab the Rectangle
option from the Insert tab, just draw it on
| | 03:38 | my report, then grab my text
field and drag it into the rectangle.
| | 03:43 | And then if I move the rectangle
around, it should contain the text field
| | 03:46 | inside it. That looks about right.
I'll just resize it a little bit, take it back
| | 03:50 | to where it was originally.
| | 03:51 | But now when I come back to my Publish
Report Part options, click in the second option,
| | 03:56 | I should have both options
here. We have got Customer Table--
| | 03:59 | I need to put in my description there--
and this Rectangle 1 here, which I can
| | 04:05 | type in Address Text,
change the name of it that way.
| | 04:08 | Now below this, for both of these
options, it's saying that it's going to publish
| | 04:12 | them to the Report Server, which is
where we've been publishing all our reports,
| | 04:15 | but it's going to put them in
a folder called Report Parts.
| | 04:18 | If that folder doesn't exist,
it will be created in just a second.
| | 04:22 | So, I am going to go ahead and click
Publish, and I get the message that 2
| | 04:26 | published successfully. Click Close.
| | 04:27 | Now that's it. We have published a
couple all the Report Parts, which we can now
| | 04:32 | use in other reports.
| | 04:34 | So, let's see how to add them.
| | Collapse this transcript |
| Adding and updating report parts| 00:00 | After publishing your first report
parts, if you didn't have a Report Parts
| | 00:04 | folder visible in your report
manager, you should have one now.
| | 00:08 | To use these I'm just going to go
ahead and create and create a normal report
| | 00:11 | using Report Builder, as we usually would.
| | 00:13 | As ever, I'll do a blank report, but this
time I'm not going to add a data source.
| | 00:17 | I'm not going to add a
dataset. I don't need to.
| | 00:20 | I just want to add some
report parts to this new report.
| | 00:24 | So I'll go over to my Insert tab and
now click this Report Parts button.
| | 00:29 | It opens up what's called
the Report Part Gallery.
| | 00:32 | Now, this could theoretically have
hundreds or thousands of report parts in it.
| | 00:36 | They're searchable, and it'll go through
the name and the description, so I could
| | 00:39 | type in "customer" for example and I
would bring back this customer table with a
| | 00:43 | little preview of it.
Click it once, I get the description.
| | 00:45 | It's a simple list of customers.
| | 00:47 | Though at this point, if I just left
that search field blank and just clicked the
| | 00:51 | Search button, I would get everything
that existed, which is both my customer
| | 00:54 | table and my address text.
| | 00:56 | And all I do is drag it on.
| | 00:58 | So I've drag on the customer table here,
and I need to just reposition it a bit,
| | 01:03 | and then I'll drag on the address text as well.
| | 01:05 | Let's go and prove this
works, click Run. It works just fine.
| | 01:09 | You see, when you drag on that report
part, what is also done is automatically
| | 01:14 | add the embedded data source and
the embedded dataset that that table
| | 01:18 | originally contained.
| | 01:19 | Everything it needs to work is
automatically added to this new report, but I can
| | 01:24 | still go through and change
this table to my heart's content.
| | 01:28 | If I wanted to select that top row and
change the background color, it's not
| | 01:32 | a problem; if I wanted to even delete the
column from the end, not a problem either.
| | 01:37 | I can make any changes that I want to.
It has copied the information from the
| | 01:42 | published report part into my new report.
| | 01:44 | Now, whatever change I make here is not
overwriting the report part on the server
| | 01:49 | unless I wanted it to, in which case
I could republish this part, either
| | 01:53 | overwriting the existing one
or publishing as a new one.
| | 01:56 | And this is how you
publish and reuse report parts.
| | 02:00 | Now, if I have published a report
part and then used in multiple other
| | 02:04 | reports, what happens if a change to
this report part is made over here in this
| | 02:09 | Report Part gallery?
| | 02:10 | Well, let's take a look.
| | 02:12 | I am going to open up the original report
that I'd used to publish that report part.
| | 02:17 | Now, I don't need to actually have the
original one. I could overwrite or update
| | 02:21 | that report part from anywhere.
This is just a good example so we can see
| | 02:25 | what's going to happen.
| | 02:26 | This report is of course
working just fine right now.
| | 02:29 | What I'm going to do is make a small
change to order by last name ascending,
| | 02:34 | which it currently isn't doing.
| | 02:35 | And I'm also going to change the text
report part here from 22 Acacia Avenue to 44.
| | 02:41 | I'll make those changes normally.
| | 02:43 | First selecting the table, I am
going into the Tablix Properties.
| | 02:47 | I'll just add a sort LastName, A to Z. Test it.
| | 02:52 | It seems to work just fine.
| | 02:53 | I'll make one change to the text
over here and that works too, so let's
| | 02:58 | republish these. Up to the Office
button, Publish Report Parts, just review
| | 03:04 | them before publishing.
| | 03:06 | If I open up these, we get a bit of
information that it understands these have
| | 03:09 | already been published.
| | 03:10 | So we do get a checkbox asking if I
want to publish as a new copy of the report
| | 03:15 | part, perhaps to a different location.
I don't want to do that; I just want to
| | 03:17 | overwrite what's there.
| | 03:19 | So I'll click Publish and
we're done. Click Close.
| | 03:22 | No change will be visible here because
I've already made those changes, but I'm
| | 03:26 | now going to switch into the other report.
| | 03:28 | Now, if I'd save this to the server and
then reopened it, it what actually prompt me
| | 03:32 | and tell me that there were changes
to these report parts, and did I want to
| | 03:36 | copy down the changes?
| | 03:38 | It won't overwrite them if I don't want to.
| | 03:40 | So if I want to keep my customize
report part, I just keep it. Because I already
| | 03:45 | have this report open, I'm not getting
prompted, but I can go up to the Office
| | 03:49 | button here and select the option to
Check For Updates. Clicking that I get this
| | 03:53 | prompt up at the top that 2 publish
report parts in this report have been
| | 03:56 | updated on the server.
| | 03:58 | I can click View Updates and it's
figured out there are changes to both,
| | 04:01 | including I can even see the 44 Acacia Avenue.
| | 04:05 | It will not overwrite my changes
unless I tell it to do so, so I can select
| | 04:09 | either both of these options or I
can select just one or the other.
| | 04:13 | I am going to select both and click
Update. Close and you can see that those
| | 04:18 | changes have been copied
back into my new report.
| | 04:21 | We have got 44 Acacia Avenue.
| | 04:22 | And now if I run this report, I've even
got the sorting being applied by last name.
| | 04:27 | So, well these changes won't be enforced
upon any report that uses report parts,
| | 04:33 | it's very useful to have that notification.
| | Collapse this transcript |
| Using subreports and nested regions| 00:00 | Earlier in the course, I showed how we
could create reports that used parameters;
| | 00:04 | either typing in a value or having
perhaps a dropdown selection would allow you
| | 00:09 | to refresh the data based on that selection.
| | 00:11 | Another way of doing this is using
what's called a drillthrough report, so we
| | 00:15 | could use an action to drill
from one report to another.
| | 00:19 | But another way of connecting reports
is to use a feature called subreport,
| | 00:23 | literally one report nested inside another.
| | 00:26 | Now, the typical example is that we
have a report like this one. I called
| | 00:30 | it parameter example.
| | 00:31 | It's a report that accepts a parameter and can
change what it's showing based on that parameter.
| | 00:37 | But if I think this will be useful, I want
to see it contained inside a larger report.
| | 00:41 | So I'm going to jump over to
Report Builder and do that.
| | 00:44 | I've created a blank report here I've
called Categories and Products, and to save
| | 00:49 | some time I'll got a connection to
AdventureWorksLT and a very simple dataset
| | 00:54 | that is currently showing a list of categories.
| | 00:57 | I'm going to show the categories on
this report and then have that subreport
| | 01:01 | that shows the product for each category.
| | 01:04 | I could add this to the report as a
table. What I'm going to do is add it as a
| | 01:08 | list because we haven't used the list yet.
| | 01:11 | There's nothing magical about
using a list with a subreport.
| | 01:14 | It's just an opportune moment to do this.
| | 01:16 | Now, when you add a list, you just then
drag a free-form region onto the page.
| | 01:22 | A list is simply a repeating
region with whatever you put in its
| | 01:26 | container repeating.
| | 01:27 | Now, what does that mean?
| | 01:28 | Well, if I drag over, for example,
name form my dataset, I can position it
| | 01:33 | anywhere I want in that list
container. I'm going to zoom this up to 16-point font,
| | 01:38 | just to make it a bit bolder.
| | 01:40 | If I go ahead and run this, what I get
is this repeating region Handlebars,
| | 01:45 | Bottom, Brackets, Breaks, and so on.
| | 01:47 | But I have got a big blank area of this
repeating region I can use for a subreport.
| | 01:52 | I'll just drag that text box a little wider
so it doesn't have a line break in any more.
| | 01:56 | Up to the Insert tab, find the option
for Subreport, click it once, and then draw
| | 02:01 | out a space for the subreport.
| | 02:03 | Now, it'll fill the space available,
and it will go top to bottom as it needs
| | 02:08 | to, but it's always a good idea to kind of lay
it out with the amount of space that you need.
| | 02:13 | Right now it has no idea what this
should be pointing to and the subreport
| | 02:17 | really is just point to another report.
| | 02:20 | So if I right-click on that, I should
have the option for Subreport Properties,
| | 02:23 | and this is where we tie the two
together. Selecting that one, the main option
| | 02:28 | here is which report do we want,
what do I want to use as a subreport?
| | 02:32 | So I'm going to click the Browse button
and go out to my Report Server and find
| | 02:36 | the one I'm interested in, which
was Parameter_Example, and click Open.
| | 02:41 | The only other important thing I need to
do is select the Parameter section here
| | 02:45 | to join these two together.
| | 02:46 | I'm going to add a new parameter.
| | 02:48 | It will look in the report I'm pointing
to and say, well, that accepts something
| | 02:52 | called ProductCategoryID.
| | 02:54 | That's what I want.
| | 02:55 | And then in the Value section it will
look at the data I currently have on this
| | 02:59 | report and say, what do you want to pass?
| | 03:01 | Well it's not name; it is the number,
ProductCategoryID. Click OK and we're done.
| | 03:06 | I go ahead and run this.
| | 03:08 | Now you'll occasionally get this and
testing it in preview, this data cache for
| | 03:12 | the report added session is not valid.
| | 03:14 | If you're works in preview, that's fine.
| | 03:17 | If not, one thing you could do is just save it.
| | 03:20 | I'll call this SubreportExample.
| | 03:23 | I'm saving it to my Report Server.
| | 03:25 | And then I'll go and actually
look at it in the Report Server as
| | 03:28 | SubreportExample that I just created.
| | 03:30 | So click that once and now we're
actually get it viewing properly.
| | 03:34 | So we have this repeating category, in
this case handlebars, and then all the
| | 03:37 | product for handlebars, bottom brackets,
all the product for bottom brackets, and
| | 03:41 | this is a subreport.
| | 03:43 | And jump to the next page
to see several more of them.
| | 03:46 | Now, as you can see, what it's doing is
adding the title of the subreport on
| | 03:51 | every repeated section, which would be
very different from, say, creating this
| | 03:55 | table as a report part.
| | 03:57 | I probably wouldn't want to see
parameter example being written out all the time.
| | 04:01 | I would have to go into that
subreports and edit that out.
| | 04:04 | But this is the process we would use.
| | 04:06 | Now, I will admit my personal bias here.
| | 04:09 | I don't feel that subreports are
worth the trouble most of the time.
| | 04:12 | They can easily blow out a report layout.
| | 04:14 | And there's very little you can do
with subreports that can't be done with a
| | 04:18 | normal report with multiple
data regions or using report parts.
| | 04:22 | But they are part of Reporting Services,
and occasionally you may run into a time
| | 04:25 | that's convenient to use one.
| | Collapse this transcript |
| Configuring headers and footers| 00:00 | Whenever you make a new report in
Report Builder it might first look like you
| | 00:04 | have a header and a footer section,
but you've actually just been given a
| | 00:08 | footer, because in a normal report
| | 00:10 | this is a title, not a header, so it
will only show at the top of the first page.
| | 00:15 | But we have a footer that is
activated, and that will show on every page.
| | 00:18 | Now, you can toggle headers and
footers on and off from the Insert tab.
| | 00:23 | Add a header, remove a footer, and vice versa.
| | 00:26 | Both the Footer and Header
sections have their own properties,
| | 00:29 | so if I right-click in the footer area,
I have footer properties here where
| | 00:33 | I have such options like do I print on
the first page, do I print on the last page?
| | 00:38 | I'm just going to leave those as is.
| | 00:40 | Now, by default, the footer you given
contains a text box with this expression
| | 00:43 | that will just show the date and
time that this report is generated.
| | 00:47 | But most of the things you would add to a
footer or header are simple, often just text.
| | 00:51 | So first, you would insert a text box.
| | 00:54 | This could just be regular typed text
and can be formatted as such, but you can
| | 00:59 | also add an expression.
| | 01:01 | But even for that, the first thing
you would do is insert a new text box.
| | 01:05 | So, I'm going to draw one here.
| | 01:07 | If I was comfortable with directly writing
expressions, I could just type it in directly.
| | 01:11 | But what I'm going to do is right-click the
text box and select the Expression option.
| | 01:16 | I'm going to construct an expression here
that says what page this is of what page total.
| | 01:21 | So the first thing I'm going to write
is just the word Page in double quotes
| | 01:25 | because that's going to be a string,
just regular text, and put a space after it
| | 01:30 | and then the closing double quote.
| | 01:31 | I'm going to join that together, to
concatenate that, with a built-in variable
| | 01:36 | that's accessible to us.
| | 01:38 | If I look at built-in fields here,
I have all these options here:
| | 01:41 | Execution Time we're already using, Language,
| | 01:44 | OverallPageNumber, OverallTotalPages.
| | 01:47 | I'm going to use OverallPageNumber.
| | 01:48 | In fact, that's designed to be used in
a header or footer and usually recommended
| | 01:52 | that if you do it, you use it in a footer.
| | 01:55 | I'll double-click that, and we have
it entered up here into my expression
| | 01:58 | Globals!OverallPageNumber. Another
ampersand. We'll join this with an 'of' with a
| | 02:05 | space on either side, and I'll tie
that to OverallTotalPages, and click OK.
| | 02:11 | So now I have that
expression typed in the bottom here.
| | 02:15 | I'd like to make sure that everything along
the bottom is actually using the same font.
| | 02:19 | I'm just going to highlight all of them.
| | 02:21 | I'll set them to Arial 8 points, and in
the Align section, I'll align the Tops.
| | 02:26 | I could go ahead and run this,
| | 02:28 | but all we're going to get
is page 1 of 1 right now.
| | 02:30 | I actually really need a
bit more data to work with.
| | 02:33 | Probably the easiest way is to just drop in one
of the report parts I created a little earlier.
| | 02:38 | This should give us at
least some data to play with.
| | 02:41 | And if I expand one of the
columns, it will push it out a bit.
| | 02:43 | So let's go ahead and run it. Scrolling down,
| | 02:46 | I can see that, for example,
we've got page 2 of 57.
| | 02:50 | So, we're getting the full
count of it on each page.
| | 02:53 | Now, the number of pages this report
will create will be different whether
| | 02:57 | you're viewing it on the website or
exporting it as a PDF or a Word document,
| | 03:01 | but Reporting Services will
figure the correct one out.
| | 03:04 | It's all to do with how that report
is rendered, which we're going to talk about next.
| | Collapse this transcript |
| Printing and exporting reports| 00:00 | Reporting Services can generate or
render your report in multiple ways.
| | 00:05 | When you view a typical report using
the Report Manager website it's rendered
| | 00:09 | out as HTML, as a web page, and this is
what has the most interactivity, allowing
| | 00:14 | drilldown actions and drillthrough
reports, interactive parameters, and so on.
| | 00:19 | And if you have a lot of data, Reporting
Services doesn't often even figure out
| | 00:22 | how many pages you have, because you'll
actually see a question mark up at the
| | 00:26 | top page, one of question mark.
| | 00:28 | It's rendering these pages one by one
as it doesn't need to figure them out
| | 00:33 | until you ask for them.
| | 00:34 | But you also have the option of
exporting these reports in multiple formats
| | 00:38 | using the button on the toolbar here.
| | 00:40 | And there's more going on here than it
might first seem, because when you do
| | 00:44 | this it's not just using the web
browser; Reporting Services will actually
| | 00:47 | totally regenerate this report.
It will render it again in a completely
| | 00:52 | different format using a different set of rules.
| | 00:54 | So if I were to export this file as PDF,
Reporting Services will figure out the
| | 01:00 | entire report so it can generate a
file with explicit page numbers, but there
| | 01:04 | would be no interactivity, no
dropdowns, no drillthroughs.
| | 01:07 | Now one thing worth bearing in mind
when generating a PDF is what happens, say, if
| | 01:12 | you have an interactive
report with collapsible regions.
| | 01:16 | Now, some people will think that the PDF
will automatically span them all for the
| | 01:19 | printable version, but no,
that's not what it does.
| | 01:21 | It will take the current state of the
report, which for me I'd only expand it to
| | 01:25 | areas, and that's what will be
generated as the PDF, whatever I'm looking at.
| | 01:30 | So whatever you have selected in the
browser is what you'll get in the document.
| | 01:34 | But sometimes you're more interested in
the data than how this actually looks.
| | 01:39 | So if, on the other hand, you choose to
export as CSV, comma separated values, or as
| | 01:44 | XML, you'll get the entire set
of data but no layout information.
| | 01:49 | And there are also specific rendering
engines for turning this into Excel or Word.
| | 01:55 | If you're someone who lives in Excel,
this is often a very convenient way to make
| | 01:58 | this data your own and
perform your own actions on it.
| | 02:01 | It's very common when you export as
Excel, you'll get a prompt, a warning or an
| | 02:05 | error about this, and until
you actually enable editing,
| | 02:09 | you won't get that full interactivity.
| | 02:11 | This does are very good job of
presenting this Excel spreadsheet the way I just
| | 02:15 | saw it on the web page, but you do
have the ability to expand the data.
| | 02:19 | Now, the Word and the Excel rendering
engines have been improved in the 2012
| | 02:23 | version of SQL Server.
You can still do it in 2008 R2,
| | 02:27 | but it looks a little nicer in 2012.
| | 02:29 | And you can also export or render out in
just a pure image format as a TIFF file
| | 02:35 | or as a web archive and HTML,
but I'm not going to show those here.
| | 02:39 | Now there are a couple of big
distinctions that people don't tend to think about
| | 02:43 | when they first start using Reporting Services.
| | 02:45 | First is the idea that reports that
might work really well when viewed on the
| | 02:49 | web don't necessarily work
well as a PDF and vice versa.
| | 02:52 | And the most problematic
component is, not surprisingly, the matrix.
| | 02:57 | Because if someone's looking to
experiment with a multicolumn expandable
| | 03:02 | collapsible matrix that works great on
the web, but also wants to try and fit
| | 03:06 | this into an 8x11 sheet,
| | 03:09 | well, they're going to have a bad time.
| | 03:11 | You try and print the current view
of this and you will find the dreaded
| | 03:14 | horizontal pages. It can be used to map
these out, but it's really not the most
| | 03:20 | straightforward thing to let us
know what we're looking at here.
| | 03:23 | You see it's not at all unusual to
create two versions of the same report, one
| | 03:28 | targeted at the web and one targeted
for printing, with a more explicit but
| | 03:34 | more restricted layout.
| | 03:35 | Now, the most important thing when
working with printed pages is having more
| | 03:39 | control over page breaks and page orientation.
| | 03:42 | So let's get into that next.
| | Collapse this transcript |
| Using page breaks| 00:00 | It's always worth bearing in mind that the
Report Builder layout is not strictly WYSIWYG;
| | 00:04 | it's not what you see is
what you get--it can't be.
| | 00:07 | You don't know what you're
going to get until this runs.
| | 00:09 | Now I've created a simple report with
one table on it that's showing products
| | 00:14 | grouped together into categories.
Nothing magical here, but if I take a look at
| | 00:18 | it, it looks like product name is a
little too small. We could do with dragging
| | 00:22 | that a little bit wider.
| | 00:23 | So I'm going to go back into Design
view and start to move this out, give
| | 00:25 | myself a bit more room.
| | 00:27 | See, because the assumption is that the
most common way to view these reports
| | 00:32 | is on the web, it will happily expand left or
right and up to down as much as you need to.
| | 00:37 | So if I drag this out and keep
dragging it out this way and then select this
| | 00:41 | column, give myself a bit more room,
| | 00:43 | if we go ahead and run it,
it looks pretty good here.
| | 00:46 | But if I view the print version of this,
jumping over to Print Layout--I don't
| | 00:51 | actually have to go and
export to PDF, although I could--
| | 00:54 | Print Layout thinks about it, but it's
now transferring this onto multiple pages.
| | 00:59 | I'm only getting three columns here.
| | 01:02 | I jump ahead and there is Size and List Price.
| | 01:04 | Yeah, this is not
exactly what I was looking for.
| | 01:08 | And the thing is, as you add elements
to the page Report Builder, will not stop
| | 01:13 | you from just getting wider and wider.
| | 01:15 | So, if you're creating a report that
you're targeting for printing, it's your
| | 01:18 | responsibility to restrict the
layout so that it always works.
| | 01:22 | And your first challenge is always
make sure it's not too wide.
| | 01:25 | Now, by default, new reports are created
in portrait orientation, and if you do
| | 01:30 | need a bit more width, one thing you
could do is change the orientation of the
| | 01:34 | page from portrait to landscape. And now
to do that, you need to find a blue area,
| | 01:39 | not the actual report layout itself, but
just off the side. Right-click it and go
| | 01:43 | to Report Properties.
| | 01:45 | Here is the option to go to Landscape and
I click OK, go ahead and run this again.
| | 01:50 | Now I still have Print Layout selected
so I can see this, and I can see it's made
| | 01:54 | a difference, but I'm still losing a
column off to the right-hand side. But with
| | 01:59 | a little bit of moving things around.
I should be able to fix that. I'm going to
| | 02:02 | bring this back in a little bit
and test this again. Much better.
| | 02:07 | Now I've switched to landscape
orientation and I'm actually getting all of
| | 02:11 | this on the same page.
| | 02:13 | So be prepared to do a little bit of
testing before you're actually know for
| | 02:17 | sure how this is going to work.
| | 02:19 | On that Report Properties section is
information not only about the orientation
| | 02:23 | but also about the margins, which by
default are 1 inch on either side.
| | 02:27 | Now the next most common need when
we're targeting a report and printing
| | 02:32 | is to have more control over page breaks.
| | 02:35 | Say for example that what I wanted to do
is start breaking these apart by category.
| | 02:39 | I want to have each category showing up on
its own page instead of just all in a row.
| | 02:44 | Well, one thing that strikes people as
odd occasionally is you will find there is
| | 02:48 | no page break option in the Insert tab,
because instead you add a page break to
| | 02:54 | another component. Most commonly,
you're going to add it to a data region.
| | 02:57 | In my case, I want to actually have a
page break between each group in this table.
| | 03:03 | I want each new category
to start on its own page.
| | 03:05 | Well first, I might try seeing if
that's in the properties of the table itself,
| | 03:10 | in the actual Tablix component.
| | 03:12 | As always, it can be a little
difficult to select. One of the most reliable
| | 03:16 | ways with the Properties window open, I'll
select Tablix and jump to the Properties pages.
| | 03:21 | There are page break options here,
but they affect the larger table by itself.
| | 03:26 | So add a page break before,
add a page break after.
| | 03:29 | That doesn't mean each individual category;
| | 03:31 | it means the entire table--not what I want here.
| | 03:34 | Though there are some useful options for,
say, repeating the header rows on each page.
| | 03:40 | So maybe I think perhaps it's on the
actual row itself, the row that represents
| | 03:44 | the actual category.
| | 03:46 | What happens if I select the row and grab that?
| | 03:48 | Well, there is no options there, and
the only option I have is the general
| | 03:52 | Tablix Properties again.
| | 03:53 | So it's not obvious where I would
select to do a page break for each category,
| | 03:58 | but I can. It's actually in the Grouping section.
| | 04:01 | So from the View part of the Ribbon,
I need to turn on Grouping, which is how it
| | 04:05 | describes how this table is grouped together.
| | 04:08 | In my case, I dragged on ProductCategory_Name.
| | 04:11 | Here is where I apply the page break settings.
| | 04:14 | If I right-click this, I'll find the
section called Group Properties, and in that
| | 04:19 | I'll get a Page Break option.
| | 04:21 | So if select between each instance of a
group, we have also at the start and also
| | 04:25 | at the end, but that's fine.
| | 04:26 | I'll click OK, and let's preview this.
| | 04:30 | We have the first categories showing up,
Landscape Orientation with all of its
| | 04:34 | data, and then starting to go
through each individual one.
| | 04:38 | Now, the other way to add a page break is not
directly, but we can add one to a rectangle.
| | 04:47 | So if when laying out a page we wanted,
say, to have a large cover title, but to
| | 04:52 | have a page break between this and
then the table I could insert a rectangle.
| | 04:58 | It doesn't have to have anything in it.
| | 05:00 | Then right-click the rectangle, click
it's Properties, and we have a Page break
| | 05:03 | option here, so Add a page break after.
| | 05:06 | Click OK. Go ahead and run it.
| | 05:08 | Now the first page is just cover page,
and the next page is where our table begins.
| | 05:13 | Now one of the downsides of working
with page breaks is you really need to be
| | 05:16 | focused on these for
printed versions of your reports.
| | 05:19 | If I switch back to the web layout,
it becomes kind of annoying that for web use,
| | 05:24 | which is what I'm looking at here, I
first have to worry about pages before I can
| | 05:27 | go and see the next amount of content.
| | 05:30 | So for web use multiple page
breaks can actually get quite annoying.
| | 05:33 | But when you're focused on
printing, this is the way to add them.
| | Collapse this transcript |
| Creating and using shared data sources| 00:00 | In all the reports I've created so
far I've been creating and embedding a
| | 00:04 | separate data source inside each report
describing individually how each report
| | 00:09 | will find and connect to its source of data.
| | 00:11 | But as it's common to create a lot of
reports that will connect to the same
| | 00:15 | source--say the same database--we also
have the idea of creating a shared data
| | 00:19 | source, defining it once, and saving it
and reusing it across multiple reports.
| | 00:24 | You might wonder why I don't
talk about these earlier.
| | 00:27 | Well, it's because while shared data
sources are great, they do add a little
| | 00:30 | complexity that we just
don't need right up front.
| | 00:33 | And the more familiar you are with
creating a few of these embedded data
| | 00:37 | sources, the easier it is to make shared ones.
| | 00:40 | So I'm looking at a typical embedded data
source here, like I've been doing all along.
| | 00:45 | We're connecting to SQL Server, and I
used the helper window in the Build button
| | 00:50 | to create the connection string here.
| | 00:53 | And here's the thing: any data source we
define, whether it's shared or embedded,
| | 00:57 | it's used multiple times in
the lifetime of our report.
| | 01:01 | Because when we are defining the
report we are using this information to
| | 01:05 | connect to the database, to ask it what its
tables are, so we can build queries and preview data.
| | 01:10 | Well, it's going to ask us who we
are, and if we can't provide the right
| | 01:14 | credentials, it will refuse us access.
| | 01:17 | So in the data source
there is a Credentials section.
| | 01:20 | Now all along I've just been letting it
do the default option, which is use the
| | 01:24 | current Windows user, Windows authentication,
| | 01:27 | meaning who I am signed on to the
network. Using Report Builder, that's who we're
| | 01:32 | going to use to connect to the database.
| | 01:33 | Now that assumes that the database knows
who I am as a signed-on user on the network.
| | 01:38 | But some databases need a different set
of credentials to how I'm logged on to
| | 01:43 | the Windows network.
| | 01:44 | We can provide these too.
| | 01:45 | You can create a data source that
includes a particular user name and password.
| | 01:51 | You can prompt for credentials every
time. You would enter in some prompt text
| | 01:55 | like provide a username and password.
| | 01:58 | If you're connecting to a really
low-security database it might not even
| | 02:01 | need any credentials.
| | 02:03 | And the reason why we have to provide
these is not only we're using this when
| | 02:07 | we're building this report, but also
when somebody runs this report, it's going
| | 02:11 | to use this data source
that connect to the database.
| | 02:14 | So selecting the option to use the
current Windows user doesn't always mean use
| | 02:19 | me as the author of the report;
| | 02:21 | it means whoever is either trying to
design the report or trying to run the report.
| | 02:25 | And if you find, for example, that you
can define a report successfully, but
| | 02:28 | other people can't view it, it's often because
you have access to the database, but they do not.
| | 02:33 | Now, when you're having that problem,
sometimes it's tempting to manually write in
| | 02:38 | your own username and password here,
embedding that information into the report.
| | 02:43 | But be careful with that one, because
it's much better to figure out whether the
| | 02:46 | person who's viewing the report should
have access to that database and if they
| | 02:50 | shouldn't, then they shouldn't be
using your credentials to bypass that.
| | 02:53 | Now, this is much more about your
internal organizational structure and your
| | 02:58 | database setup then it is about
Reporting Services, but here is where we can
| | 03:02 | provide pretty much any option you might want.
| | 03:05 | Now creating a shared data source will also
bring all of these choices right up front.
| | 03:10 | I'm going to jump over into Report
Manager, because you can't create a shared
| | 03:15 | data source in Report Builder.
| | 03:16 | You have to create it from Report
Manager itself, and there are no helper
| | 03:20 | windows to help us do it. Here is how to do it.
| | 03:22 | I'm looking at the homepage of my
Reporting Services Report Manager.
| | 03:26 | Now when I'm creating a shared data
source I do like to have them contained in
| | 03:29 | their own folder the same way we have
report parts, so first I'm going to create
| | 03:33 | a new folder here. Let's call it Shared
Data Sources and go into that folder, and
| | 03:38 | there is an option right here on the
toolbar called New Data Source, and it gives
| | 03:42 | us a web page to create this.
| | 03:43 | I'm going to call this AdventureWorksLT.
And notice that while some of this looks
| | 03:48 | similar to what we see in Report
Builder, we don't have a Build button.
| | 03:51 | We don't have a helper window. We have to
manually provide the connection string.
| | 03:55 | Well if I just happen to have Report
Builder open with an embedded data source,
| | 04:01 | what I can do is actually just grab that
guy out of here and paste it right back
| | 04:05 | over into that website.
| | 04:07 | If you're familiar with creating
connection strings, you're fine just typing them in.
| | 04:11 | Although word of warning: if you aren't
using a database that needs the server
| | 04:15 | name backslash instance name,
| | 04:18 | oftentimes Report Builder will
construct that with two backslashes, though the
| | 04:22 | shared Data Source window tends to get
a little annoyed about that, so I'm going
| | 04:26 | to subtract one of those.
| | 04:28 | This simply means I'm connecting to
dbserver\sql2012, which is the name of my SQL
| | 04:34 | Server and the name of the machine
that it's on, and initial catalog describes
| | 04:39 | the actual database on that SQL
Server that I'm connecting to.
| | 04:42 | Then we have the same Credentials option.
| | 04:45 | Do I provide a new set of credentials
every time by the user running the report,
| | 04:49 | meaning we're prompting for
information, the username and password.
| | 04:53 | Am I going to explicitly hardcode
credentials stored in the report server for
| | 04:57 | this shared data source so that
everyone who uses it uses the same credentials?
| | 05:02 | If it's very low-security, I could
say credentials are not required.
| | 05:06 | Well I'm going to go with the
Windows integrated security again.
| | 05:10 | We have the Test Connection
button. Let's give it a go.
| | 05:12 | When I click that, it often
changes the page up a bit.
| | 05:15 | I've got connection created successfully.
| | 05:16 | I'm going to click OK.
| | 05:17 | I now have a shared data source defined
in my Report Manager on the Report Server.
| | 05:23 | Now let's see how to use it.
| | 05:24 | I'm going to jump back over to Report
Builder, cancel out of this, and just
| | 05:28 | create a new report.
| | 05:29 | And for the first time I'm
actually going to use the report wizard.
| | 05:32 | I will select the Table or Matrix Wizard.
| | 05:35 | Click that option. The first thing
it's going to ask me for is a dataset.
| | 05:38 | I'll tell it I'm going to create a
dataset, but the first thing it's going to
| | 05:42 | ask me for is a data source.
| | 05:44 | Now if I already used a shared data
source before, I'll actually find my most
| | 05:49 | recent ones showing up here.
| | 05:50 | I haven't yet, so I need to go and
browse for that one I just created.
| | 05:54 | Click the Browse button.
| | 05:55 | We should go out to the root of our
report server. There is my Shared Data
| | 05:59 | Sources folder that I had, and there
is my AdventureWorksLT shared Data
| | 06:03 | Source. Click Open.
| | 06:04 | I can test that connection. We're fine.
| | 06:06 | Click Next. It jumps into the Query
Designer for me to define the dataset.
| | 06:10 | I'll just quickly create something here.
| | 06:13 | Just to prove that this one works, we'll go
for Product Name, ProductID, and ListPrice.
| | 06:27 | And we now have the table on the page
and we're using that data source both when
| | 06:31 | we're designing this and
when we're running the report.
| | 06:33 | The important thing here not being so much
this table, this could be showing anything.
| | 06:37 | It's the idea of the shared data source.
| | 06:39 | It has a slightly different icon if I
expand the Report Data section, in that it
| | 06:43 | has this little alias icon out there.
| | 06:46 | Now from this point on, once you've
used the share data source once, it
| | 06:49 | actually gets even quicker.
| | 06:51 | If I were to create another new report--I'm
just going to abandon changes to the last one.
| | 06:56 | We'd choose Table or Matrix Wizard.
| | 06:58 | I'll create a dataset, and I already have
that last data source connection that I
| | 07:02 | defined, so very quick to
start stepping through this.
| | 07:05 | I don't need to go through this. I'm just
going to cancel out of it, as all I was
| | 07:09 | doing was demonstrating this.
| | 07:10 | Now, as long as they have been
set up correctly, shared data sources are
| | 07:14 | the quickest and easiest way to connect,
and using them is considered a best practice.
| | 07:19 | One reason for this is if your
connection details do change--say the server name
| | 07:23 | changes because you go from a
development server to a production server--then
| | 07:27 | you could change it once in the shared
data source and that's much preferable
| | 07:30 | than having to go into every single
report with an embedded data source.
| | 07:35 | Now one thing worth pointing out is if
you're already using a lot of embedded
| | 07:38 | data sources you don't have to
remove them and re-create everything.
| | 07:42 | I could go to one of my reports that I
have that's using a previously embedded
| | 07:47 | data source, such as my
Grouping and Joining example,
| | 07:51 | open that up in Report Builder where
right now I'm using an embedded data source
| | 07:55 | pointing to AdventureWorksLight.
| | 07:57 | What I could do is just change that
over to use a shared connection and select
| | 08:02 | my AdventureWorksLight version there.
| | 08:04 | And now I'm using that shared data
source, but I don't have to change anything
| | 08:08 | else about this report.
| | Collapse this transcript |
| Creating and using shared data sets| 00:00 | And with the same idea as creating shared
data sources, we can also create shared datasets.
| | 00:05 | Now, this is not quite as common across
the board, as you are more likely to need
| | 00:10 | different datasets across
different reports, but they can be useful.
| | 00:12 | Now as with creating other modular
components like shared data sources or
| | 00:17 | report parts, I like having a folder to put my
shared data sets in to make them easy to find.
| | 00:21 | So just create a new folder here,
share Data Sets, and while we created a new
| | 00:28 | data source from this window, we actually go
back into Report Builder to create a shared dataset.
| | 00:33 | So I'll open up Report Builder, but I'm
not going to create a report because on
| | 00:38 | the Getting Started page is
another option we haven't seen yet.
| | 00:42 | None of the wizards, it's the second
one on the left here, the New Dataset.
| | 00:45 | Now the first think it's going to ask for
is to choose a shared data source connection.
| | 00:51 | Shared datasets must use a shared
data source so creating the shared data
| | 00:56 | source comes first.
| | 00:58 | I have already used this data source in
Report Builder, which is why it's showing up here.
| | 01:02 | If I wanted to connect to another
one that I just created, I can browse
| | 01:06 | other data sources. But this is fine.
| | 01:08 | It's the one I want, so I
am going to click Create.
| | 01:11 | And really, it just opens up a
full-screen version of the Query Designer.
| | 01:15 | We are using the data source, in this
case connecting to AdventureWorksLT.
| | 01:19 | I can drill down onto something simple.
| | 01:21 | I'll go and create ProductName,
ListPrice and Color, connect that to the
| | 01:26 | ProductCategoryTable.
| | 01:28 | Just as like creating a normal
dataset, we have all these features like auto
| | 01:31 | detection of relationships.
| | 01:33 | I can go ahead and run this query.
| | 01:35 | I can edit it as text if I want to
go want to go against the raw SQL.
| | 01:39 | If I accidentally selected the wrong
connection, I could use this Select button
| | 01:44 | to point to a different one, but I am
connecting to the right one right now.
| | 01:47 | The only other button I have that's
new is this one, Set Options, which is the
| | 01:52 | same as right-clicking the dataset and
viewing the properties of it, allowing
| | 01:56 | us to change the names of the fields,
provide parameters, provide filters, and so on.
| | 02:01 | I don't need to do anything there;
| | 02:02 | I just want a simple dataset.
| | 02:04 | The last thing I need to do is come
up here and click the Save button.
| | 02:08 | Shared datasets can be saved anywhere
on the Report Server, but I'm doing to go
| | 02:13 | into the Shared Datasets folder I
created a moment ago and save it there.
| | 02:16 | Just give it a more useful name, and we are done.
| | 02:22 | From this point on, when I create a
new report in Report Builder, it will be
| | 02:26 | available as an option.
| | 02:27 | And bear in mind, we already define that
using a shared data source, so it makes
| | 02:32 | the process even quicker.
| | 02:33 | If I go ahead and say that I'm
creating new report based on the Table Wizard,
| | 02:37 | instead of creating a dataset, I am
just going to point to a shared dataset.
| | 02:41 | I'll browse to that one that one that I
just created, and I am done. The dataset
| | 02:46 | is there, the data source is there; all I
now need to do is drag the fields into
| | 02:50 | the right parts of the table.
| | 02:52 | I'll get product information grouped
together by category name, and we are done.
| | 03:00 | Sure, it might need a little formatting,
but it's off to a really good start, and a
| | 03:04 | really fast way to start creating these reports.
| | 03:06 | As with shared data sources, as soon as
I create a new report, it's going to be
| | 03:10 | even quicker to access that one.
| | 03:13 | If I use the Table Wizard, even the
first screen is going to point to that
| | 03:17 | shared dataset and basically I'm
already there, ready to format the table.
| | 03:21 | Now, one great reason for using shared
datasets is if you're creating multiple
| | 03:26 | versions of the same report, one
perhaps focused on the web making the most of
| | 03:30 | interactive controls and one for printing.
| | 03:32 | But they are also great if there
is just a common query that might be
| | 03:36 | tedious to keep rewriting.
| | 03:38 | Shared datasets are editable.
| | 03:39 | You can just go into the particular
folder that you had, view the details of it,
| | 03:43 | change the name of it, or use the
dropdown menu to open it back up in Report
| | 03:48 | Builder and change any details about them.
| | Collapse this transcript |
|
|
6. Core Administrative TasksOrganizing reports in Report Manager| 00:00 | So I've created a lot of reports and the
Report Manager website is becoming a mess.
| | 00:05 | These need to be organized.
| | 00:06 | The fundamental method of organizing
reports is like organizing any other document.
| | 00:10 | You create folders--when
that's not good enough, subfolders.
| | 00:14 | We've already done this a
couple of times in the course.
| | 00:17 | You just create a new folder directly
from the Bar and Report Manager and call
| | 00:21 | it whatever you want.
| | 00:22 | However, it is worth thinking
about your best top-level structure.
| | 00:26 | There are no official recommendations as
it's so dependent on your organization.
| | 00:31 | You might create folders to strictly
organize your reports by departments,
| | 00:35 | so have a Sales folder, Purchasing,
Manufacturing, Accounts. Or you might go by purpose.
| | 00:41 | You might create one folder for
financial reports, another for inventory,
| | 00:45 | another for personnel.
| | 00:47 | It's whatever makes the most sense.
| | 00:49 | You might even create a Work In
Progress folder for your own experiments.
| | 00:53 | However, I would say that the most
common structure I see is loosely based on
| | 00:57 | departments, and there is a good reason for that.
| | 00:59 | Folders aren't just for organization.
| | 01:01 | They will make your life a
lot easier with security.
| | 01:04 | As we'll see in a moment, once should
have gathered reports in a folder, you can
| | 01:08 | restrict access to that
folder to only certain people.
| | 01:10 | And if your current security
structure in your network is already based on
| | 01:14 | well-defined departments, it will be a
lot easier to map that security to your
| | 01:19 | reports if you're using a similar structure.
| | 01:21 | You can drill down into any individual
folder, like the one I just created here,
| | 01:26 | and create new subfolders inside it.
| | 01:28 | There is no limit to this;
| | 01:29 | it's just whatever you find helpful.
| | 01:32 | As you start to drill down, what you'll
find is the breadcrumb up at the top will
| | 01:35 | allow you to step up one
level and then another level,
| | 01:39 | in my case, all the way to the homepage.
| | 01:41 | Once you've created a couple of these
folders, you can start moving the reports around.
| | 01:45 | There is a Move option you'll find
on the dropdown menu for every report.
| | 01:49 | You'll also find it in Manage.
| | 01:51 | It lets you quickly scan the folder
structure and drop it into the folder that you want.
| | 01:56 | You can also use the dropdown menu to
rename the report if you need to do that.
| | 02:00 | Now, there are few different ways of naming them.
| | 02:04 | You can use of spaces if
you have multiple words.
| | 02:07 | What's also common is you'll see
underscores used instead of spaces.
| | 02:10 | This can be helpful, particularly
if you're using many subreports.
| | 02:15 | Most of the time spaces will work
just fine, but from force of habit, I tend
| | 02:19 | to either use underscores or no spaces at
all and just use camel casing to name them.
| | 02:24 | In the same section, you'll find another
copy of the Move option that will allow
| | 02:28 | you to move that report around. And one
thing that's very useful is to be able to
| | 02:32 | give your reports a description.
| | 02:35 | When you have a lot of reports, giving
them good descriptions is one of the best
| | 02:38 | things you can possibly do for your
users, because it not only helps you scan
| | 02:43 | the reports that you do have--after
you've applied your changes, you will see it
| | 02:47 | in the list of reports--
| | 02:49 | but you can also use those
words in the search box to find it.
| | 02:52 | And when you start having reports in
folders and subfolders and more subfolders,
| | 02:57 | this is often the quickest way to
find the one you're looking for.
| | Collapse this transcript |
| Adding users and configuring report security| 00:00 | Let's talk about security, and I don't
mean the security of the database your
| | 00:04 | report is connecting to.
| | 00:05 | That's all taken care of in
the data source, as we have seen.
| | 00:08 | I am talking instead about
security inside Reporting Services itself.
| | 00:12 | So who gets to view this Report Manager website?
| | 00:16 | Who could click the link to view a
particular report, and who can't? And who could
| | 00:20 | just view a report versus who could edit
it and move it and delete it, because
| | 00:24 | every item here--every folder, every
subfolder, every single report, every
| | 00:29 | shared data source, every report
part--they all have independent security
| | 00:34 | settings, a different list of people
that can be allowed to view or edit them.
| | 00:38 | What you will find is they all have a
Security option in the dropdown box.
| | 00:42 | But it is considered a best practice to
apply security at a folder level rather
| | 00:48 | than individually on each report or
each element, because if you change security
| | 00:54 | on a folder, say the Sales folder here,
whatever you put in that folder will
| | 00:58 | take on the same settings, and the very
top level, the Home page here that we are
| | 01:03 | looking at, is considered a folder in
itself, the top folder, the top container.
| | 01:07 | So right now, everything I am looking
at--all the reports, all the folders
| | 01:11 | and everything inside them--takes on the
settings that reply to this top-level folder.
| | 01:15 | So what are those settings?
| | 01:16 | Well, if I have the right permissions,
I can click this button up here called
| | 01:20 | Folder Settings and I'll see what's inside Home.
| | 01:23 | Currently, this is what I have set to.
| | 01:25 | It's very simple here.
| | 01:26 | It's working with security groups on
this machine, saying that anyone who is in
| | 01:30 | the Administrators group is considered
in the role of a content manager, whereas
| | 01:35 | for me, anyone who is considered an
Authenticated User, which just means anybody
| | 01:40 | who is logged on to this machine with a
valid user ID, is considered a Browser.
| | 01:44 | A Browser is someone who is allowed
to view the Reporting Services website,
| | 01:49 | can drill down inside the navigation
of it, can actually browse reports, but
| | 01:53 | can't move anything, can't delete anything,
can't change security, can't create reports.
| | 01:58 | A Content Manager can do
everything that I just mentioned there.
| | 02:02 | They can create new reports.
| | 02:03 | They can move them, delete them, add
data sources, share data sources, and so on.
| | 02:07 | And right now, because I haven't made
any other changes, these are applying to
| | 02:12 | every folder and every subfolder.
| | 02:14 | So how would we change that?
| | 02:16 | Well, let's say for example in my
Sales folder I don't want everybody to be
| | 02:21 | able to browse these reports.
| | 02:22 | I only want them viewable
by people in the Sales group.
| | 02:25 | Well, once I am inside the Sales folder,
I can go to my Folder Settings and I
| | 02:29 | have the Security option here.
| | 02:31 | I can see that what it's doing right now,
| | 02:33 | it's got exactly the same
settings as I had in the folder above it.
| | 02:36 | So I am going to click the option to
Edit Item Security, and it's going to tell
| | 02:40 | me well, you didn't have
particular independent security here.
| | 02:43 | It was being inherited from a parent item,
| | 02:46 | the folder it was contained in, the Home folder.
| | 02:48 | So do I want to apply different
security settings here? Yes, I do.
| | 02:52 | So I will click OK.
| | 02:54 | And then what I am going to do is
select the Authenticated Users as a
| | 02:57 | Browser and delete it.
| | 02:59 | That means just anybody who is logged
on is not allowed to see this folder.
| | 03:03 | They are not allowed to browse items inside it.
| | 03:05 | I am going to click the option for a
new role assignment, and this is showing me
| | 03:10 | the built-in Reporting Services roles.
| | 03:12 | We have got Browser, Content Manager,
Report Builder, Publisher, and My Reports.
| | 03:18 | What I am going to do is
add a new security group.
| | 03:21 | I can add a group or an individual user.
| | 03:23 | So if I had an individual user that I
wanted to allow to view these reports--and
| | 03:28 | I have got someone I know;
his username is Hannac--
| | 03:31 | I can make them a Browser,
click OK, and they are added here.
| | 03:35 | My Report Server is installed and
running under a local account for the purposes
| | 03:39 | of recording this course, so I
am using local groups and users.
| | 03:42 | In most large organizations you are
running under a domain, so you'd need to use
| | 03:47 | domain groups and users.
| | 03:48 | And while we can add individual
users, that's kind of high maintenance,
| | 03:52 | so what's more typical is you will add groups.
| | 03:55 | I know there is a group called Sales
that contains all the salespeople,
| | 03:59 | so I am going to type that in and
select Browser, and now everyone who is in
| | 04:03 | Sales can get into this folder and browse the
items in it, but they can't change anything.
| | 04:08 | So one more thing I am going to do is
create a New Role Assignment and say that
| | 04:12 | the Sales Managers are allowed to
create and configure new reports.
| | 04:18 | I might wonder, which one should they have.
| | 04:20 | There is an option here called Report
Builder, which sounds like it might be
| | 04:24 | the one for people creating and defining new
reports, but that's not actually a great role.
| | 04:29 | It doesn't give someone the ability
to save a new report to the server.
| | 04:34 | If you wanted someone who could add and
create new reports, you'd want at least publisher,
| | 04:39 | although publisher cannot move things
around and apply security, so the best one,
| | 04:44 | if you're really getting seriously
into it, is the Content Manager Role.
| | 04:48 | I am going to click OK.
| | 04:50 | So now what we have is anyone in
Administrators is a full Content Manager in
| | 04:54 | this Sales folder and any subfolder inside it.
| | 04:57 | Hannac can browse it, as well as
everybody inside the Sales group, and all the
| | 05:01 | Sales Managers are also
considered Content Managers.
| | 05:04 | Now, when you're working with these
roles, they can be changed, deleted, added
| | 05:09 | to, reconfigured, so if you either see
or if you need something else, you can
| | 05:14 | talk to your database administrator.
| | 05:15 | But if you are just creating reports and
folders, data sources and report parts,
| | 05:20 | the role you need at least
is the Content Manager role.
| | Collapse this transcript |
| Configuring subscriptions| 00:00 | Right now, for anyone to see any of
these reports, they have to come to the
| | 00:04 | website and click the link to either
view it on the site or be able to select
| | 00:08 | the Export option to view it
in one of the other formats.
| | 00:11 | But we can also have these reports
generated automatically on a schedule to
| | 00:17 | create a subscription to them, say
that have a sales report sent to our Inbox
| | 00:21 | every Monday morning at 8 a.m.
| | 00:23 | But this can require just a little more work on
our part as a report creator, so let's see why.
| | 00:29 | I am going to take one of these reports that
I have. It really doesn't matter which one.
| | 00:33 | I'm going to pick this very simple
first report I created right at the start.
| | 00:37 | Let's say this is the one that I
want to subscribe to. Okay, sounds good.
| | 00:42 | I can see that it's working.
| | 00:43 | I want this every Monday morning.
| | 00:45 | I am going to go back to the
containing folder, which for me is just the Home
| | 00:49 | page here, and from the dropdown menu,
| | 00:51 | I'm going to select Subscribe.
| | 00:53 | I choose that option and
I am refused. It says no.
| | 00:57 | Well, the question is, why? Here is the deal.
| | 00:59 | Right now when I run this report,
it's connecting to the database and the
| | 01:04 | database is asking from my credentials.
| | 01:06 | It's saying okay, someone is
trying to get this data. Who are you?
| | 01:10 | And because of the way our data source
is defined, I am passing my own Windows
| | 01:14 | credentials through
because I am currently logged on.
| | 01:17 | But if I want to tell the server to generate
the report by itself regardless of if I
| | 01:23 | am even logged into the system, it just
doesn't have any credentials to do that.
| | 01:27 | Reporting Services doesn't
automatically have all the credentials it needs to
| | 01:31 | talk to every database it
might possibly connect to.
| | 01:34 | So to allow subscriptions, this report
needs to have a data source that includes
| | 01:40 | credentials, so that it will work correctly.
| | 01:42 | Now, this report actually has
an embedded data source in it.
| | 01:46 | I could go back into Report Builder to
edit it, but I can also actually edit it here.
| | 01:51 | If I choose the menu and just come
down into Manage, an option I have over on
| | 01:56 | the left-hand side is Data Sources,
which is really just a description of the
| | 02:00 | embedded data source that I currently have.
| | 02:02 | And right now it's giving me the
description, the Connection string, and saying
| | 02:05 | it's using Windows integrated security.
| | 02:08 | That's what's passing through
my credentials whenever I run it.
| | 02:11 | But if I am not going to be around when
it runs, I need to provide something else.
| | 02:16 | So here is most useful option right now,
| | 02:18 | the option to have credentials
stored securely in the report server.
| | 02:22 | Now I could write my own Windows
Account in here, but I don't want to write my
| | 02:26 | actual credentials in here.
| | 02:28 | That would just be a bad practice.
| | 02:30 | So what I have done is I have talked
to the database administrator on this
| | 02:33 | database, which in this case is me, and I
have created an account in the database
| | 02:38 | that is set up as ReadOnly with
access to the tables that it needs.
| | 02:42 | I am going to type in those
credentials so they are stored with this report.
| | 02:47 | I named that account AW2008ReadOnly,
and I will type in my password for it.
| | 02:52 | In my case, it is an account
that's directly stored in the database.
| | 02:55 | It's not a Windows account, so I am not
going to say Use as Windows credentials.
| | 03:00 | I am going to click Test Connection.
| | 03:01 | I get this message Connection
created successfully, looks good.
| | 03:04 | I still need to click Apply;
otherwise, it won't be saved.
| | 03:07 | Now those credentials are stored as part
of the data source and this data source
| | 03:11 | is embedded inside this report.
| | 03:13 | I could also do this same idea with a
shared data source, but for purposes of
| | 03:17 | time, I am just doing it this way.
| | 03:19 | So I am going to go back and make
sure that this report runs normally.
| | 03:22 | We don't want to interfere with that.
| | 03:24 | Yeah, it looks good, looks the
same way it did just a moment ago.
| | 03:27 | But now from the dropdown menu,
when I click Subscribe, I should have that option available.
| | 03:32 | A subscription in Reporting Services
can generate an email, or one of the other
| | 03:37 | options is it can dump it into a
Windows File Share, a shared folder.
| | 03:42 | E-Mail should be the default option for
delivery, and if you don't see the E-Mail
| | 03:46 | option it means Reporting Services
have not been configured to talk to a mail
| | 03:50 | server, so talk to your
system administrator about that.
| | 03:53 | If you want to dump it into a shared
folder, you do need to provide a path to it
| | 03:58 | and any credentials that are
needed to access that folder.
| | 04:01 | I am going to go with E-Mail.
| | 04:03 | It's assuming that it's being delivered
to me, so I am going to leave that, but
| | 04:06 | I also have options to add CCs and BCCs.
| | 04:09 | We have the typical options for
delivery that you also get when you're
| | 04:13 | viewing it on the website.
| | 04:15 | We can generate as a PDF,
as a Word file or Excel.
| | 04:18 | The default one was opening as MHTML,
which is really the compressed archived
| | 04:22 | web page itself so we could open up in the
browser, but I know this report could work as PDF,
| | 04:27 | so I am going to select PDF.
| | 04:29 | And then towards the bottom, the only
other thing that we have to specify, how
| | 04:33 | often do I want it, what
schedule do I want this on?
| | 04:35 | It's defaulting to 8 a.m. every Monday
of every week, but I can click the button
| | 04:40 | to build my own schedule to do it every
hour or once a month or twice a day, and
| | 04:45 | we also have options to
begin and end this schedule.
| | 04:48 | I am just going to leave it on the default,
every Monday at 8 a.m., and click OK, click OK.
| | 04:55 | My subscription is created. We are done.
| | 04:57 | I could do this on multiple reports.
| | 05:00 | Later, if I've forgotten exactly what
and where I've subscribed to different
| | 05:05 | reports, I do have the My
Subscriptions link at the top of the Report Manager
| | 05:09 | website where I can view these or delete them.
| | Collapse this transcript |
| Creating a linked report| 00:00 | One of the options you might have
seen in Report Manager when using this
| | 00:03 | dropdown menu is to create a linked report.
| | 00:06 | A linked report saves us from the
situation where we might have to
| | 00:10 | define exactly the same report
several times over only to show a little
| | 00:14 | bit of different data.
| | 00:15 | So, say I created a great complex
report filtered to just the data from the
| | 00:19 | southwest region, and then someone
looks at it and says they want exactly the
| | 00:23 | same thing, but for the northeast region.
| | 00:25 | I don't have to create the report again.
| | 00:27 | I just create a linked report and
point it to slightly different data.
| | 00:30 | So, let's go ahead and make one.
| | 00:32 | The starting point is this.
| | 00:33 | I've just created for myself a
simple report called WashingtonPeople.
| | 00:38 | It shows the Salespeople data
filtered down to Washington.
| | 00:41 | Okay, I have done it as something
that's very easy to understand, but
| | 00:44 | let's imagine that this was a lot
more complex--historical data, charts,
| | 00:48 | maps, matrices and sparklines--a work of
genius report, but just filtered to Washington.
| | 00:54 | And then somebody asks, "Can we get
exactly this, but for Oregon instead?"
| | 01:00 | Well, instead of making it a new report
and recreating everything, I am going to make a linked report.
| | 01:04 | Now linked reports will use the same data
source and the same data set, the same definition;
| | 01:10 | what's different about them
is typically just a parameter.
| | 01:13 | I'm going to go in and edit this
original report in Report Builder,
| | 01:17 | because there is a little bit of prep work I
want to do if I'm creating a linked report.
| | 01:21 | Right now there's
nothing remarkable about this.
| | 01:23 | I am using a shared data source
and I have a simple dataset in here.
| | 01:27 | If I look at the dataset, what I'll
actually see is that I've got a hardcoded
| | 01:31 | filter in there, where the
StateProvinceName is Washington.
| | 01:35 | And this is about the only thing that I
need to change in order to prep this as
| | 01:39 | a linked report because almost always
the first step will be have the original
| | 01:43 | report use a parameter instead
of hardcoded filter information.
| | 01:47 | So, I'm going to select the
checkbox here to use a parameter.
| | 01:50 | Now, because I already have a default value,
we should see absolutely no change on this.
| | 01:55 | It'll add a parameter into my report
definition, but that should be set up with
| | 02:00 | the default value here of
Washington. We are fine.
| | 02:03 | So, if I were to save this and then go
back in and run it again, we should see
| | 02:07 | exactly the same thing.
| | 02:09 | Now right now, because of the default
way that parameters appear, we are having
| | 02:13 | the parameter show up at the top.
| | 02:14 | I actually don't want that,
so that will be one more quick change.
| | 02:19 | I'll go directly into the parameter
and set the parameter visibility to
| | 02:23 | Hidden. Save it again, and run it again.
It looks good, looks the same as it was
| | 02:31 | a minute or two ago.
| | 02:33 | Now let's create a linked report from this.
| | 02:35 | So we are basing it on
this WashingtonPeople Report.
| | 02:40 | Now, what I'm doing here is not copying
the report; in fact I won't be able to
| | 02:44 | edit this one in Report Builder.
| | 02:46 | I'll click OK to save it.
| | 02:50 | Now, right now it's exactly the same as
the first one, because I need to make
| | 02:53 | one change to this.
| | 02:54 | If I go back to the Home page,
I should see it showing up here, and it's got a
| | 02:59 | slightly different icon with the chains to
demonstrate that this is a linked report.
| | 03:03 | If I go into the settings of it and
click Manage, I have got most of the usual
| | 03:07 | options, although things like Data Source
are hidden because when it's a linked
| | 03:11 | report, you're not allowed to change that.
That's all in the original report.
| | 03:14 | But what I do have here is Parameters.
| | 03:17 | This I can change, and this is
specific to the linked report.
| | 03:21 | So, it's already understanding that
the default parameter was Washington.
| | 03:24 | I'm going to change that to
Oregon, click Apply, and we are done.
| | 03:29 | Now, if I open my WashingtonPeople version,
I'll see the Salespeople in Washington.
| | 03:35 | If I open my OregonPeople report,
I'll see the Salesperson in Oregon.
| | 03:39 | If I try and open the linked report in
Report Builder, it will start to open
| | 03:45 | Report Builder and then complain about
it. Not the friendliest error message
| | 03:49 | in the world, but effectively saying,
you're not allowed to edit a linked report;
| | 03:53 | you have to edit the original one.
| | 03:54 | So, there is no point in
me having Report Builder.
| | 03:57 | The great thing is is that any
change I made to the original report will
| | 04:01 | immediately affect any linked report I
have made based on it, so it's a great
| | 04:05 | way to reuse report definitions.
| | Collapse this transcript |
| Using Report Designer in SQL Server Data Tools| 00:00 | We've been using Report Builder all
along to create these reports, but another
| | 00:04 | option that's available is to use
Report Designer, which is the name for the
| | 00:08 | report layout option that is
integrated into Visual Studio.
| | 00:11 | Now, unlike Report Builder, you can't just
open this from this Report Manager website.
| | 00:16 | It actually requires installation
on your machine called SQL Server Client
| | 00:20 | Configuration Tools.
| | 00:22 | It's a little odd to describe
because you don't actually open a tool
| | 00:25 | called Report Designer.
| | 00:27 | If you have the SQL Server 2008 R2
tools installed as I do, you'd get to it by
| | 00:32 | opening up what's called the Business
Intelligence Development Studio, or BIDS.
| | 00:39 | But in SQL Server 2012, there is no BIDS;
| | 00:42 | this time around it's called
the SQL Server Data Tools Program.
| | 00:46 | If I open that up, it's going to
look like it's opening up Visual
| | 00:49 | Studio, because really,
it is integrated inside that.
| | 00:52 | Now, as I mentioned at the start of the
course, don't get caught up in thinking
| | 00:55 | that you will have all sorts of
new report features; you won't.
| | 00:59 | Report definitions created in Report
Builder and Report Designer are identical.
| | 01:04 | They use the same file format,
and they have the same ability.
| | 01:07 | It's true that a couple of versions ago
Report Builder was more limited, but
| | 01:11 | that's simply not the case anymore.
| | 01:13 | The main reason you might use BIDS or
this SQL Server Data Tools is that you're
| | 01:18 | already an existing Visual Studio user,
and/or you want to integrate what you're
| | 01:22 | doing in Reporting Services, into, say,
an existing source control setup or a
| | 01:26 | current development project.
| | 01:28 | But if you want or need it,
let's compare and contrast.
| | 01:31 | So here are the key differences.
| | 01:33 | I have Visual Studio open, which is what
opened by selecting SQL Server Data Tools.
| | 01:38 | It might either be Visual Studio 2010 or 2012.
| | 01:41 | If you're using SQL Server 2008,
it's going to be Visual Studio 2008.
| | 01:46 | That part of it really doesn't matter.
| | 01:48 | But there is a bit more
formality with the initial setup.
| | 01:51 | Rather than just going ahead and
creating a single report like we would do in
| | 01:55 | Report Builder, everything we do
using BIDS or SQL Server Data Tools is
| | 02:00 | contained inside a project.
| | 02:02 | Now, really all that means is it will
automatically group everything inside a
| | 02:06 | folder, whereas with Report Builder
we'd have to create that folder ourselves.
| | 02:10 | So with this open--and I have the
normal Visual Studio installed as well,
| | 02:14 | which means I get a lot of project
options here--one of them will be the
| | 02:18 | Reporting Services option, and with
SQL Server Data Tools there really are
| | 02:22 | only two options here: either just an
empty Report Server Project or Report
| | 02:26 | Server Project Wizard.
| | 02:28 | I am going to select the wizard here,
and I'm going to name this project.
| | 02:31 | I will just call it SSDT_demo.
| | 02:34 | Now, as with a Visual Studio project,
I'm first creating these files locally,
| | 02:39 | saving them to my own machine, and I
will need to deploy these files to the
| | 02:43 | Reporting Services server when I'm ready.
| | 02:46 | It creates the project and
opens up this Wizard panel.
| | 02:49 | Now, what you'll see is that we have many of
the same options that we have in Report Builder.
| | 02:54 | They just often look a little different.
| | 02:55 | It's even giving us a summary here.
| | 02:57 | First, we're going to need to select a
data source from which to retrieve the
| | 03:00 | data--yup, done that all along--
design a query to execute against it--yes--
| | 03:05 | select the type of reports, select the layout,
select the formatting. All right, click Next.
| | 03:10 | First option is to select the data
source, and while this window might look a
| | 03:14 | little different, we've
been doing this all along.
| | 03:16 | We'll give it a name.
| | 03:17 | I will just call it
Adventure Works Light (AWLT).
| | 03:20 | It is Microsoft SQL Server.
| | 03:21 | I've got buttons that will give me the same
windows we've been using in Report Builder.
| | 03:26 | So we create our data
source in much the same way.
| | 03:28 | I even have an option here to
make this a shared data source.
| | 03:31 | I am just gong to keep going with Embedded.
If we go to the next page,
| | 03:35 | it's going to ask us to design the
query, basically design the dataset.
| | 03:39 | I open up the Query Builder.
| | 03:40 | This Query Designer is a little more code-
oriented than the one that we'd see in Report Builder.
| | 03:46 | By default, it will show the SQL or
show the tables that you're actually using.
| | 03:51 | You can toggle these parts on and off
if you don't have an awful lot of room.
| | 03:55 | By right-clicking the
blank area, I can add a table.
| | 03:58 | I am using Adventure Works LT, so I will
just go against Product and Product Category.
| | 04:04 | It's brought those back from the database,
but by looking at the fact that those
| | 04:08 | foreign keys were defined, it's even
figured out how they're joined together.
| | 04:12 | So while it might look fairly different,
it's really doing the same kind of thing.
| | 04:15 | So I am going to select Name, Color,
and ListPrice from the Product table, and
| | 04:21 | also the name of the Category.
| | 04:22 | I can go ahead and click the
exclamation mark to just preview and run this.
| | 04:26 | And no big surprises here--
it's just what I would expect.
| | 04:29 | Now the only thing it's going to do
is whereas in Report Builder, it would
| | 04:33 | automatically solve any potential
naming conflicts by prefixing them with the
| | 04:38 | name of the table, what it's doing
with the second name column is it's just
| | 04:42 | calling Expr1, so I will change
that to CategoryName, just so we get more
| | 04:47 | useful information
showing up in the bottom here.
| | 04:49 | I am going to click OK.
| | 04:51 | My Query, my Dataset is defined. Click Next.
| | 04:55 | Again, same idea, just a slightly
different window for it. It's asking if I want
| | 04:59 | a table or a matrix. I'm going to go with
a table, with tabular data. Click Next.
| | 05:04 | This looks a little different here.
| | 05:05 | We have options for
grouping certain pages together.
| | 05:08 | So, is there a particular field
that will represent an individual page?
| | 05:12 | Otherwise, the main things that we
need are what we call groups and values in
| | 05:16 | Report Builder, which is groups and details here.
| | 05:18 | So I am going to say I want to group
things together by category name, and I
| | 05:22 | want Name, Color, and ListPrice
showing up in the Details section.
| | 05:26 | We even see a little preview of
where that would position it. Click Next.
| | 05:30 | Very similar to the wizard in
Report Builder, we just have the choices
| | 05:33 | between Stepped and Block.
| | 05:35 | We have the options to include
Subtotals and Enable drilldown.
| | 05:39 | I am actually going to leave those as
the default options here. Click Next again.
| | 05:43 | We have the same set of styles:
| | 05:46 | Slate, Forest, Corporate, and so on.
| | 05:48 | I will just select Slate and click Next.
| | 05:50 | Now, the last thing is, where does this go?
| | 05:53 | We're writing this as a file on
our local machine, but it needs to be
| | 05:56 | published somewhere.
| | 05:58 | Now, because we didn't open it up
from the website, it doesn't know exactly
| | 06:02 | where our Reporting Services server is.
| | 06:05 | It's making a guess that I
might have one installed locally.
| | 06:07 | I actually do, but I want to publish
this to my Reporting Services server, which
| | 06:12 | is at dbserver/ReportServer.
| | 06:14 | ReportServer is the most common name
for the end point, for the web service
| | 06:20 | of Reporting Services.
| | 06:21 | And the version I'm being asked for, is it
SQL Server 2008 R2 or later? Yes, it is.
| | 06:26 | Down here it's going to ask
me for a Deployment folder.
| | 06:29 | It sounds very technical. All it's going
to end up doing is creating a folder that
| | 06:33 | we will be able to see in Report Manager.
| | 06:35 | So SSDT_demo, that will do.
Same name as the project.
| | 06:39 | Click Next, and I will give this report
a name, call it SSDTReport and finish.
| | 06:46 | Many of the same options. Sometimes they
look a little different, sometimes they
| | 06:49 | look substantially different.
| | 06:51 | Now, we've got this report,
and we're looking at the layout itself.
| | 06:54 | The preview of this looks a little
different, because it's only rendering what's
| | 06:59 | being shown on the report, but the
behavior will be the same when we actually
| | 07:02 | run this report on the website.
| | 07:04 | We have the same Report Data section
showing up on the left-hand side with our
| | 07:08 | data sources and our datasets
that I could drill down into.
| | 07:11 | We have the same Properties
pane on the right-hand side.
| | 07:15 | Clicking around different parts such as
the title or the table itself will show
| | 07:19 | us that same information
that I get in Report Builder.
| | 07:22 | Because this is Visual Studio, we also
have Visual Studio-specific options like
| | 07:26 | Solution Explorer and Team Explorer.
| | 07:29 | We have the Row Groups and
Column Groups section at the bottom.
| | 07:32 | The only thing that we don't have
the same is the ribbon up at the top.
| | 07:36 | What we're actually looking
for there is the toolbox instead.
| | 07:39 | This is going to contain all those
options that I could add to this report.
| | 07:43 | But they're all exactly the same:
| | 07:45 | Subreports, Maps, Data Bars, and Sparklines.
| | 07:48 | Even as you see when we click around
on the table itself, we get the same
| | 07:51 | ability for grabbing the columns,
for grabbing the table itself.
| | 07:55 | If I right-click, everything looks
pretty much identical. Opening up the Tablix
| | 08:00 | Properties gives us the exact same
property pages that we get in Report Builder.
| | 08:04 | I am going to save this and then I
click the Preview section on the tab.
| | 08:08 | It looks pretty familiar;
| | 08:09 | the only thing that really does look
different is the fact that it's embedded
| | 08:12 | inside Visual Studio.
| | 08:13 | Now, if I jump over into my Reporting
Services Report Manager here--I am just
| | 08:19 | going to refresh this page--
| | 08:20 | I don't see anything new, even though
I am previewing that option in Visual
| | 08:24 | Studio, because I really need to
publish this. I need to deploy.
| | 08:27 | I need to push it from my local
machine up to the report server.
| | 08:31 | The way I'm going to do this is go
over to my Solution Explorer section and
| | 08:35 | right-click my project
name here and click Deploy.
| | 08:39 | I get an output window here that
talks me through it, connecting to that
| | 08:42 | Reporting Services server,
publishing that report.
| | 08:45 | I get 0 errors, 0 warnings. One has succeeded.
| | 08:48 | Well, let's go find out.
| | 08:50 | I jump over into Report Manager.
| | 08:51 | I am going to refresh this.
| | 08:53 | And what I get is this new folder, SSDT_demo.
| | 08:57 | I can drill down inside it. I see the report.
| | 08:59 | If I click the report,
| | 09:00 | it opens up just the same way all
the other reports I had created were.
| | 09:04 | If I use the dropdown menu,
we have the same options.
| | 09:07 | I can even click Manage to get to these
options here, even get to things like data sources.
| | 09:12 | In fact, if I go back to that folder,
I could take this report, click the
| | 09:16 | menu, and even though I'd created it
using SQL Server Data Tools, I can open
| | 09:21 | it up in Report Builder, because once
that's published using SQL Server Data
| | 09:25 | Tools, it's just a report.
| | 09:26 | It's the same format we've been using all along.
| | 09:28 | So I could open it up in Report
Builder, make changes to it there, and even
| | 09:32 | save it back.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks for joining me through this
course on SQL Server Reporting Services.
| | 00:04 | The goal of this was to get you
familiar with all the significant elements and
| | 00:07 | concepts of creating reports, using data
sources and datasets, adding a table or
| | 00:12 | a matrix, a chart or sparkline, working
with parameters and security, and more
| | 00:17 | besides, and hopefully you feel this was useful.
| | 00:19 | Now, there is obviously more that can
be done, particularly when it comes to
| | 00:23 | starting to combine these ideas
together to create your own larger reports.
| | 00:27 | Well, one thing to keep in mind is
that there is useful help available.
| | 00:31 | You'll find help systems both
from the report manager website--
| | 00:35 | there is a help link at the very
top-right--and in Report Builder itself,
| | 00:40 | if you just click the question mark up here.
| | 00:43 | The Report Builder help is very good
indeed, particularly in areas that we can't
| | 00:48 | or just wouldn't want to cover in a
course like this, things like having a
| | 00:52 | reference of all the available expressions.
| | 00:55 | So make sure to keep this handy,
because it helps a lot. And good luck
| | 00:59 | creating your reports. See you next time.
| | Collapse this transcript |
|
|
