(upbeat music)
PETER: Putting together all of the theory
in module two, Designing Report Layouts.
It's one of my favorite things to do
is to create a report template.
The report template then becomes a starting point
for all the reports that I develop.
So here I've just launched Report Builder.
The Getting Started window is opened,
and this time, sorry Paginated Report there, no wizard.
(crosstalk) So I will click the blank report instead.
The number one thing to do at this point is to save.
So File, Save As.
I'm gonna save this to the local machine.
Whereupon I have a solution folder
for the work that I do.
And the name of this report, then,
is going to be
Report Template.
Save.
All right, now as a template,
it's going to be the starting point
for other reports that I develop.
So, the first thing that we're gonna look at
is that we've got the Insert ribbon
that presents to us all of the different regions,
visualizations, and report items.
And, what I'm gonna do then is ensure
that on the View ribbon, that everything is open.
And say be default, the properties pane
is not, so I'm gonna open it.
I'm also gonna open the parameters pane.
Remember, it's my preference they're all open.
I don't think I even need the parameters pane
but I get that out of the way up front.
Now, to select the report itself,
in fact, it's a lesson, in it's own right,
to know what selection means and how to do it.
So, for example, by clicking in a blank area,
in fact, I've just clicked in here.
And I want you to notice that the properties pane
provides a description of what's currently in focus.
There's the body.
What would happen if I click in the dark area around.
We can then see it's actually the report
that's in focus.
All right?
So, get used to this because at first,
it doesn't seem quite natural
or might be confusing about what's in focus.
And it's really important because if you have
the wrong thing in focus, you start formatting
the wrong things.
Don't worry if that happens, by the way,
because Control + Z is your friend.
CHRIS: Yes.
PETER: So, be prepared to make mistakes.
But, as you become more proficient,
it's like, getting in there,
selecting the right object
and then configuring it.
All right, so when it comes to configuring the report,
you could click the dark area.
That's one way to do it.
And then when it comes to the properties
of the report, they're available to us
in the properties pane.
Or, the most commonly accessed
are available through the properties page.
Now there are multiple ways to do the same thing.
So, even by right clicking that dark area,
an alternative way to get to it
is by using the Context menu.
So, here's the property page for the report itself.
And now is the time that we pay close attention
to the page units, and I'll leave them in inches.
Whether I am designing portrait
or landscape and it will be a portrait template.
Here are the pre-configured paper sizes.
And I think we'll leave it at letter,
in which case the width and height
are automatically filled in for us.
So, the next consideration would be do we need to modify
what the margins are doing.
And, for today, we'll leave them at the defaults
at one inch each.
Okay, so a quick mathematics on this, Chris.
What's the maximum width that my report body can be?
CHRIS: Well, your maximum width
if your report body has two one inch margins
would be six and a half.
PETER: All right.
That's 8.5.
CHRIS: Yep.
PETER: Subtract a left margin of one
and a right margin of one.
Okay, so there's no need for me
to actually change these properties
at this stage.
Well no, let's change them just to make things interesting.
Zero point five.
So, Chris, test.
How much space for width does my report body have now?
CHRIS: I think 7.5, Peter.
PETER: There we go.
8.5 subtract point five, subtract point five.
All right.
Click okay, that has updated the report properties.
Now, what isn't clear, especially for a beginner
is that we've actually got two regions here.
Let me highlight that this is, in fact,
the report body and beneath it is the report footer.
How do I know that?
Well, if I click in that empty area,
remember, I'm confirming that that is the body.
There is no header at the top.
There just isn't.
If I click beneath that dashed line,
then there's the report footer.
Now, my intention is that every report
should have a page header and the way that I can
turn that on is come to the Insert ribbon tab,
here in the Header and Footer group,
I've got the ability to click on Header and add it.
Or, right click in this dark area
and I can add the page header here.
All right, so now I have three regions.
I'm going to click to select the header,
and here I've got the height of one inch.
Let me go ahead and select the property of height
and I'm going to change that to become point five
of an inch.
Actually, I'd reckon one's better.
I would like point five for my footer.
So, I'll come to the footer, select it,
and set it's height to point five.
Now, the text box that we have by default.
What would have happened if we didn't have a header Chris,
would be, if we put our title.
CHRIS: It would only show on the first page.
PETER: It would only show on the first page
'cause it's at the top of the body.
CHRIS: Yes.
PETER: All right, now the intention is
I want that text box to be on every page,
so, having selected it, you see the way
that the border around?
And also this four headed arrow
gives me the ability to reposition it.
CHRIS: That's right.
PETER: And I'm allowed to reposition it
into a different section.
Remember, these are just rectangles.
CHRIS: That's right.
PETER: They're very special rectangles.
CHRIS: That's right.
PETER: So, I'm going to drop it
up into the header.
Now, it guarantees that it will repeat
on every page.
I could drag drop it to the very top left.
But let me show you right now,
that with that text box selected,
here in the Properties pane under position,
I can expand the group, the location,
and I could manually update that left
and top to zero.
CHRIS: Yes.
PETER: Position it right where I need it.
All right, now it might be easier
if I scale this up.
This is a lot of precision work
that I need to do here.
Okay.
Next thing I'm gonna do, in anticipation
that the top right, I want the Adventure Works logo.
So, I could just drag drop this,
or, you know, I'm a sorta precise guy
so I wanna come to the size property,
and here I'm gonna control it
that I would like that width to be exactly 6.5.
6.5?
That looks good.
Notice the way that the width exceeded the width
of the body that was there by default.
Now in fact, what did we describe
was the maximum width of the body, Chris?
CHRIS: Seven and a half inches.
PETER: Seven and a half inches.
So, let's go ahead and click the body
and see that it's width is 6.5,
but I can extend it to 7.5.
Be careful, if it's 7.51.
CHRIS: Yes.
PETER: We're then gonna end up in some trouble.
CHRIS: Yes.
PETER: So, it's precisely, in fact,
the maximum width that we're allowed to have there.
CHRIS: Well, this is where we get into the whole concept
of pixel perfect.
You have full control over all of these items.
PETER: Exactly.
And that's the way I like to do it
in the Properties window.
Because size, location, it's all there.
Now, selecting the text box again,
we mentioned that it's very similar
to a cell in a worksheet.
So, coming to the Home ribbon,
I can do things like modify the font size,
let's reduce this to 20.
Let me make it bold.
And, let me put a border style.
I like the idea that the bottom of the text box
is a line.
I think it looks quite sharp.
CHRIS: It does.
PETER: So, here on the ribbon,
we've got a border group.
And here I'll just turn on
that there will be a bottom border.
So, I don't need to add a line.
CHRIS: No.
PETER: That was my point earlier.
Awesome.
Now, what we're gonna do is use an expression
that will return the name of the report
in that text box.
And so, by right clicking a text box,
you'll see the Context menu allows us
to launch the expression builder.
This is a common thing to do with a text box.
And the expression builder says,
"Well, this is for the value of your text box."
The equal sign is in place
so we just need to come up with an expression.
You'll see the cursor is positioned
right after the equal symbol.
And here below that box, in the category list,
we've got built in fields.
And simply by double clicking Report Name,
it injects the expression to return
whatever the report name is.
Remember, in Report Builder, though,
it will return empty string.
Clicking okay, have a look at the notation here Chris.
So, when you see a text box that has square brackets,
it's telling you that it's a shorthand notation
for an expression.
If it has an ampersand, then it represents
a built in field.
CHRIS: That's right.
PETER: So, at a glance, you already know
what the expression is.
You don't have to open up the expression builder.
All right, I'm gonna add another text box
because, often when you've got parameters
in reports, you need to convey back to the report itself
so that the reader knows what the filter is
that's been applied.
Now, when it comes to adding an item
to the layout, there's two ways to do it.
You can come to the Insert ribbon tab,
and you can click on text box,
and then you can click for the location.
What I've learned is, that I often just click
where I want something.
And you'll see that the context menu
allows me to insert an item like a text box.
So, this text box is going to be for storing whatever
our subtitle is.
Watch the way when I'm resizing this
that we've got these bars that help line up.
In fact, they're really good.
Whoever developed this did a fantastic job,
and that pixel precision can be done
just by drag drop and aligning to existing items.
CHRIS: Yep.
PETER: All right.
CHRIS: Just like PowerPoint, for example.
PETER: Exactly.
So, with this selected text box,
notice that it gets a very generic name.
Often it doesn't really matter what the name is,
but sometimes it does because some expressions
might wanna refer to it.
They probably won't wanna refer to a subtitle,
but for completeness, let me show you
that there is in the General group,
a name here.
And providing that you provide a unique name like Subtitle,
then that will become the unique name
for the item in the report.
CHRIS: Yeah, when you don't have many report elements
it isn't as big a deal.
But often times you'll have report elements
with dozens of text boxes, tables,
and other items.
And if you just have generic names for all of them,
it is difficult to understand what text box 31 is.
PETER: (laughs) Yes, especially when
there's an error in the expression.
Text box 31, you need to go hunting for it.
Because this is a report template,
I would go to extra special care
knowing that it's going to be used
as a starting point for potentially hundreds of reports.
CHRIS: Exactly.
PETER: So, we'll go for that detail now.
Now in the top right corner of our page,
we want the company logo.
So, I right click in that blank area
at the top, and I'm going to insert an image.
Now, that opens up the image properties.
And this is where you see that that image
can be sourced either from an external URL,
it could be embedded directly into the RDL document,
or it's being retrieved from a dataset field.
Well, we're going to embed an import
from an image that I have on my file system.
So, here on my D drive.
And here we are in what we call Lab 2A assets.
Here is the Adventure Works logo.
All right, having selected it,
we can go ahead and provide a more friendly name for it.
So, this property window actually let's us set
the name as well.
And then I'm just gonna go ahead and click okay.
The image is added to the page.
You could either do this through the properties pane,
or today I'm happy to just line that up.
Awesome.
Okay, and so there we have the company logo.
What's next Chris?
If we've done the header, I guess next
we have to focus on the footer (crosstalk).
CHRIS: Exactly.
PETER: And what you'll find
is that the blank report, it already included a footer.
It already had a text box added.
In fact, you can see that the name of the text box
is the execution time.
And in fact, the expression that's been assigned
is the global built in field for execution time.
CHRIS: Exactly.
PETER: Okay.
So, we could probably improve upon this.
Remember how the execution time is really UTC?
CHRIS: Yes.
PETER: So, in my resources
I've got, for lab 2A, I've got some snippets.
And you can see here, let me copy this first,
is a snippet that builds up a string
that brings in a formatted execution time,
but also adds page numbering.
So, right click the text box, open up the expression window,
remove everything, CHRIS: Yes.
including the equal symbol (laughing),
because when I paste this in,
all right, you'll see the equal symbols there.
Equals literal text, concatenate,
format the global built in field
to this format, continue concatenating
some literal text.
This is a favorite of mine, the vbCrlf
is, in fact, a constant.
CHRIS: Yes.
PETER: For carriage return line feed.
And then we continue to build up on the next line
the page, and there's the built in field
for page number, of total pages.
All right, that could all easily be built up
in the expression builder by double clicking
these built in fields.
CHRIS: That's right.
PETER: You inject them into your expression.
But, to same time for today, we've done so.
Now, Chris, note what we see in the text box
is just this expression token.
CHRIS: Yes.
PETER: Um hm.
CHRIS: Yeah, I noticed that.
PETER: You noticed that (laughing).
Okay, so the explanation is this,
that if your expression is a simple,
like a built in field or a field or parameter,
you'll get a shorthand notation.
CHRIS: Yes.
PETER: But the moment that it's anything
much more complex, we just get this token
informing us that there is an expression
in that box.
CHRIS: You know, what's interesting,
and I'll drop a little news here,
is that one of the developers in the team
has actually added a formula bar
into Report Builder and internal private build,
where if you go over the expression,
you'll see the actual expression fully called out
without having to double click into it anymore.
PETER: So, like a tool tip?
CHRIS: Yes, exactly.
But it'll be an expression bar at the top
just like you have, say, in Excel.
PETER: I love that idea
'cause it saves me having to poke around.
You could put a quick question about that expression.
CHRIS: Yes, exactly.
PETER: So, let's apply some formats
to this text box.
First of all, the default was right aligned.
CHRIS: Yes.
PETER: In this report,
I would like to go for a text align
that is left.
We're going to put a border style
across the top.
Now, I could do that on the ribbon,
but let me show you how I might also achieve that
on the properties pane.
So, here I expand border style,
and I put a solid border on the top.
Next, we will put this to the very left.
So, in my position properties, left zero inches.
The top will be point zero one.
And then from the size point of view,
let's make it the full width of our body, 7.5,
and the height will be point four.
Okay, this is something that just comes a little naturally,
actually, with time and practice.
CHRIS: Yes.
And you'll find which areas you end up going.
I end up going to the Properties pane
very often when I can do it through right click actions.
It's just a force of habit.
PETER: Well, I wonder what our report
looks like Chris.
One way to find out is to go ahead and preview it.
Remember, you've got the choice
here on the Home ribbon tab to run from here,
or some people find it easier just to toggle
between the design and preview down here.
CHRIS: Yes.
PETER: All right, how does it look?
CHRIS: Well, so, what's interesting is,
you know, over the years with the evolution
of reporting services and to today,
the initial thought was, "Hey,
"I'm building this for a view inside of a browser."
And that they, you could also then,
print out this content.
But, over time, when people have used
paginated reports for more and more,
is actually to format this in a way
that is a document format so it can be exported,
printed, or saved in that format.
So, this is, again, where this page layout
view here in Report Builder is so valuable.
So, if you were to click on that page layout view
in the ribbon up there.
PETER: So, this one here?
CHRIS: Yes, exactly.
So, if you click on this, you'll see
how now it, this is how you would expect it to look
if you're creating a letter template in this fashion.
So, this is a great way to see it.
And again, if you had parameters,
you could still interact with this at this point.
So, you can keep it in this format
while you're interacting and previewing it.
And it gives you a great way to understand
exactly what your end users will be receiving
if they export this out.
PETER: Beautiful.
So, it's really validation that we've got
within the constraints of our page properties,
or our report properties of margins and size.
And also notice here that the page one of one.
CHRIS: Yep.
PETER: That's important too.
It was page one of two, we'd be a little suspicious
that, okay, there's something not quite right.
CHRIS: There's blank pages or something.
PETER: So, I will point out
just one quick thing here.
That, you know, I'm gonna save this off as a template.
Before you save off, you just kinda verify
that everything is correct.
Just on a visual inspection.
What often happens is, for example,
if I just added a text box here,
what can sometimes happen is that,
if you resize things, the body will always resize
to accommodate it.
So, it's actually quite common
that, you know, you may not have even noticed it
that by configuring and resizing and adding items,
the body just resized to accommodate them.
And there could be a problem with that.
So, for example, if I delete this text box,
it doesn't revert back to the widths I need.
So, my practice is that whenever I'm finalizing
a report design, the last thing I do
is just check those properties.
Click the body, we know 7.5 is the max.
Ah, for some reason, it's stretched beyond that.
Let's bring it back.
And maybe that height should be two.
Check that the header is still one.
Check that the footer is still point five.
CHRIS: Exactly.
PETER: All right, and then your final,
absolute test as Chris has just mentioned,
that print layout provides the final validation.
So, Chris, I think we've arrived at the end
of what is now our portrait report template.
And, we'll use that moving forward
as we develop much more interesting reports.
CHRIS: This is great, Peter.
Thanks so much.