| by Kenneth Chase | No comments

Power BI Paginated Reports in a Day – 06: Designing Report Layouts – Part 3


(upbeat music) All right, continuing with module two, now, with the focus on
designing report layouts and setting dynamic properties. So, each report that you
develop always has a body and optionally, we can
add headers and footers and you’ll find these on
the Insert ribbon tab, the ability to add or
remove a header and footer. The deal is this, the header will repeat on every page and the footer will repeat on every page. I think you do have a property that says exclude the first page. CHRIS: That is true. Your first page might be different. So, let’s focus then,
now, on the report itself. The way that I think of a report design or an RDL file, it’s
actually hierarchical. The report is the top level and then you’ve got the regions
of header, body and layout and then we can add objects into them. You can even add objects
into those objects. CHRIS: That’s true.
So, my thinking is it’s hierarchical and the very
root is the report object. So, what we can do with the report object, is we can configure properties,
like the page units. Inches or centimeters, it
will use regional settings to adapt to what makes
sense to where you are. But inches is always right. (laughs) I’m Australian and I
would tend to disagree. (Chris laughs) Page orientation comes next. Now, this becomes important if you know that the report is intended for printing, to a particular page size. So, this is where we
configure the orientation. Is it portrait, is it landscape? The size, you’ll find that
there are built-in sizes, like, A4.
CHRIS: Or letter. (laughs) What that means in Australia by the way is that’s the typical business page but letter here in the U.S. Or if you choose to customize, then you can configure
precise pixel width. And there’s actually a lot of people who use custom width
and height for things, like printing out name
tags and things like that, so they do very small regions in the page and they have that repeat, so it’s interesting just how many of these features people use in unique ways. Right. Now, next is the
consideration for margins. Top, bottom, left and right, you can configure these as well. Now, this brings us to the topic of, well, how much space do we have to layout our data on the report? And it really is just
a matter of mathematics when you think about it. If the dimensions of your page are fixed by the size of your paper and then you’ve got the height
of your header and footer, if they’re enabled, well, that tells you what
you need to subtract. That’s right. And then you’ve got the
margins to consider as well. You know, have a half an inch
at the top and the bottom and you can come to the
precise width and height of what your body needs to be, if it has to render to a single page. What we find with beginners is, that if you don’t quite
get these properties right and it can be frustrating until you understand the
way Report Builder works, is that they really have to be precise and the moment that your
width is slightly over the available width of the page, then it will result in probably
blank pages being output. Yes, and we’ve all been there. (laughs) I can tell you, when you first get started with the tool, you will have times that
it can be challenging but I think that one of the
great things about this video and this idea of having a
template to get started with is once you configure this once, and if you’re really focusing
on that print scenario, you can continue to use that
template again and again to not have to worry
about that going forward. So beyond to those properties for the report layout itself. In fact, those properties are available on a properties page, so
they’re really easy to open and then set them up front. There are some additional properties that you’ll find only
in the properties pane. Uh, the author and
description and more metadata. The language can control
formatting, in fact, and currency symbols. You can layout multi-column reports. CHRIS: That’s right. PETER: Background color and maybe adding an
image to the background, it’s not a personal
favorite of mine, in fact, but I have seen a nice use of like ghosted images, like a watermark. CHRIS: Yes, watermark’s a big one. Right, and so this is where
you would configure it for the report itself and I think paginated report bear likes his large property,
the last property, and may well have
contributed to your success. CHRIS: That’s true. When competing against Will Thompson. CHRIS: That’s true, I
did show him up that way. But, yes, auto refresh has
been around quite some time in the RDL definition and, in fact, it was
developed for a hackathon. It was a side project that
one of the developers did and actually made it into the product. So, by default, it’s set to zero seconds. It means you won’t have auto refresh but, if you configure beyond zero then it will auto load when the user is viewing
it in the Power BI service. That’s right, they’ll see
the latest data at all times. Now, there’s an additional topic here and I’m going to keep it brief for the purpose of this
introductory video course but there is the concept
of report variables that you can define to store values they can be set to read
only or set to read, write and there are particular
and often advanced design reasons for using these variables. For completeness, I will
introduce them here, but we won’t be talking about them or using them any further. Report objects come next. So having ensured that you’ve
got the headers and footers on and they’re the right dimensions your paper size and orientation and such both are in place. We’re now ready to
consider placing objects onto the canvas itself. So if you recall there
is the insert ribbon tab and there’s actually three groupings. You have the data regions,
the data visualizations and report items. Now, we’ll talk about data
regions and visualizations later because they’re connected to data and we haven’t yet talked about how we’re retrieving data, in detail. CHRIS: That’s true. PETER: So, let’s focus on this group that you’ll find on the insert ribbon tab known as the Report Items. They are the Text Box, the Image, the Line and Rectangle. And they can be added to your page and they can be independent of data that’s retrieved
from your data sources. The Text box is the most
commonly used object in a report design and its purpose is typically
to display some text value. Now that text could be static or it could be based on an expression. CHRIS: That’s true. PETER: That a rendering time
it dynamically retrieves possibly from your data, the value that needs to be displayed. CHRIS: I think expressions
are one of those things, when you get started, you
get hesitant to use them, but as you become more advanced, you find they are incredibly valuable and powerful in terms of what they can do. PETER: Well, if you think
expressions, think Excel. CHRIS: Yes. Does a cell in a worksheet
have a constant value or if it needs to
dynamically produce a value, commence with the equal symbol and then follow by an expression. CHRIS: Yeah, That’s
actually a great example. So it’s almost identical. CHRIS: Yeah, absolutely,
Yeah. Now, the Text box is much more interesting than what you might first think when you see it.
CHRIS: Exactly. Right, like a cell in a
worksheet is a single value but, the Text box is
really, or potentially, a collection of placeholders. Yes, it can do a lot of
stuff, that Text box. And these placeholders
could be static values or driven through expressions. Now, the best example I think
we’ve got is a mail merge. CHRIS: Yes, I think that’s a good one. In a single Text box, you could have, you know, “Dear Customer. “Thank you for purchasing
products from us. “We look forward,” and, anyway. You can inject through placeholders and also control formatting. CHRIS: Yes. And so, you’ve got Rich Text options and the ability to produce
layout like a mail merge. Exactly, and having that
dynamic name to go in there with the static text is
really, really powerful. And there’s one other property, which I love and I use this a fair bit. That if you’re source data actually has HTML tags in
it for styling purposes, you know, colors and emphasis with bold, and that’s the way it’s
stored in your data, you can configure the
placeholder to render that HTML to give you Rich Text Format. Yeah, and that’s something
that a lot of Power BI authors look for today in the desktop tool and they can do in
Power BI Report Builder. The similarities with the cell
in the worksheet continue. So when it comes to formatting properties, what you can do with a cell, like font, color, size, weight, borders, colors, backgrounds, padding. Will it grow if the data
value, you know, is lengthy? It can. It can, so I think by default it will, but you may not want that. So you can turn the can grow off. And there might be reasons
that you want to hide it under certain circumstances. So there’s some visibility
property as well. CHRIS: You know, it’s interesting, a lot of people ask for that ability, to hide or show certain
fields in the Power BI report and you can actually do
that in the paginator report so, again, this is one of
these education things. Right and stay tuned, because when we talk about interactivity one of those capabilities is
achieved through visibility. Yep. Now, the next item is the image. Classically used to
display your company logo. So the image is a standalone image and yet the image could be retrieved from three different places. That’s true. Oh, yeah, you mean you could
have it on a web server, embedded within the reports, so you just load the image in there and from a database. So that’s one of the examples
that people often look for. It was, “Hey, I have this saved
file in my database itself “and so I can just choose that field “and have it show when I
choose each of my records “and you have a different image.” I have a feeling the Adventure Works
company does just that. CHRIS: I have a feeling it does as well. My preference, by the way,
is that you store it once if you can on a web server. Simply because when your logo changes, you don’t need to open lots of reports and change an embedded image. So, if it’s practical to do so, and you’d want to be careful because if the Power BI reports were going to be accessed
outside the organization it would have to be a public
facing URL that would work. But if you can get that working and maybe it’s your
company’s public website and you use that URL, to me
that’s the best way to go. That if that changes in the future then your report simply will
retrieve the latest logo. All right, so image used
could be freestanding like in your header of your report. They could be pictures
associated with rows of data when being retrieved
from your data source. Or they could be the background
of other report items including the report body,
a text box, rectangle, table, matrix or list. Yeah, I would say that I’m with you. I don’t generally use images in the backgrounds of those scenarios. But, again, you absolutely can do that. You have full control. Often in my thinking, less is more when it comes to the
presentation of your data. Because you can do it doesn’t
mean you have to do it. CHRIS: (chuckles) That’s true. From my experience this
is the most least used, if that’s the right way to put it. The line. A line is basically that. It’s a line that allows
for visual interest. The only time I might
consider using it is in a demo or if I need a diagonal line. Because often the easiest
way to control a line is just to use a border on a text box. CHRIS: That’s very true. Yeah, I don’t use lines very often either. PETER: And then, we come to the fourth, which is actually very interesting. The rectangle, of course,
if you want a rectangle or a square, by the way. Then for visual interest, you could add it and you could control with
the borders and colors. However, the rectangle
is actually a container. And so, therefore, we
can place other items inside the rectangle itself. Yeah, the rectangle is
incredibly powerful. Also, the rectangle is very powerful so you have to be very careful when you’re using a rectangle. So it’s one of those things that as you start become more
proficient using Report Builder, you’ll find it gives
you enormous flexibility to do certain things in
terms of grouping and layout but you do have to be careful because if you don’t remember that you have a rectangle behind it you get yourself in trouble very quickly in terms of the layout.
Yeah. If its color is white or transparent you won’t know it’s there.
CHRIS: Yes. So a couple of things to say about it. I mentioned that the report is actually a hierarchical structure. And so the container is
like a folder in there. CHRIS: Yeah, that’s a very good point. All right, so beyond
just a visual rectangle it’s a container and the reason you might
use it, by the way, is that if you’ve got a lot of items and you want to keep them together place them in a rectangle and
when you move the rectangle the items maintain their relative position within the rectangle. So it can work just as a visual design. Your report users won’t see it. But it will help you at design time. One other interesting thing
to note about the rectangle is that the body, the
header, and the footer are special instances of a rectangle. All right. Let’s move on to the second
topic, using expressions. So it’s very common in your report designs that expressions will be used to dynamically assign values most commonly to the value of a text box. That’s the way we display data
back to the report reader. But, in fact, we can apply properties or rather expressions to
many other properties. Styling and formatting. And so what I would say is that almost any property you
can dynamically assign to but not location and position. They need to be locked
in at report design time. And notably, also, the data source itself. The data source and its
connection is not dynamic but a data set query is. CHRIS: Yes. PETER: So, you’ve got an
enormous amount of potential that at design time you can acknowledge that things are variable and, therefore, an expression will solve this when the report is being rendered. CHRIS: Yes, I think
it’s an important thing. So Peter brings up the data
source not being dynamic. You could do dynamic data
sources with SSRS for many years that’s not yet available
in the Power BI service but it is something we’re
hearing people ask about and looking for as we move forward. Thank you for bringing that up, Peter. You’re welcome. (both chuckle) All right, now when you
build out your expression you could use a variety
of .NET namespaces. So programmers are familiar with this. Very rich library of capabilities relevant to programming. Now, we’re not programming
here, in a report design, but there are many functions
that can support us to produce the results that we need. No different to the Excel expressions that refer to Excel functions. CHRIS: Exactly. Now, if you want to get
into more complex logic then the report will support
through a code block. Let’s talk about the code
block in more detail shortly. All right, so we have made mention that expressions are
assigned to properties and there is a particular window that’s going to support you
constructing a valid expression. You can often launch this,
either from the properties pane or directly from a properties page or sometime, even, by right
clicking an item on the canvas. And what this window does, once launched, and here’s an example of an FX button that we click to open the window or in the properties pane you might set a property to an expression. Now what that will do is
open the expression window. Take note at the top left. Remember you can assign expressions to lots of different properties. So it’s telling us, in this example, that its setting the property
for the hidden property. In the box beneath it is an expression. Take care here. The beginners trap is that if it’s an expression it must
always commence with equals. CHRIS: That’s right. PETER: If it doesn’t then
it’s a constant value. So, again, no different from Excel. In the lower part of the
expression window, then, you’ll see in the left there’s a category. It’s like a tree list control. This presents to you all of the resources including built in fields, we’ll talk about them in a moment, parameters, your data set
fields, variables, operators, and common functions in
the expression language that you might use to
construct your expression. Now, what I like about
it for beginners, Chris, is that when you select
from common functions you’ll see that there’s a description and an example provided to you. CHRIS: Yes. So this tool doesn’t expect you to be a proficient developer. But it will provide you
enormous capabilities with expressions and hold your hand to help guide you through
producing what you need. CHRIS: Or your paw.
Or your paw (chuckles). So, let’s take a look
at the built-in fields. There are Globals, there is
the RenderFormat, and User. Let’s just focus on a few
of the commonly used ones. Chris mentioned page numbering. Very common in the footer of your report. And also total pages. The ReportName, I like this one. We can output an expression that says, “What is the ReportName?” Because when you publish your
report to the Power BI servers you can rename it. And there’s nothing more frustrating than opening the cells or the report to see that the title of
the report is different. Very confusing. So, often the text box that
is the title of your report will output the ReportName Global. We made mention of the
User ID, is there as well. So we know who the authenticated user is. So these are all of the resources. A quick note, that some of them behave
differently in Report Builder to how they will behave when published to the Power BI service. CHRIS: Yeah, I think the User ID one is the biggest one and that’s very similar to what you see with Power BI Desktop. Is that if you’re using a User ID field when you’re using it locally you’re using the account name that you’re logged into your machine with but when you publish stuff to the service it’s using a UPN. Because, again, it’s based
on as your active directory and the UPN name so it’s the, mine is [email protected] or whatever. And so you just have to be careful that especially if you’re doing things like for local security scenarios
and things like that exactly what you’re trying to match up. Right, so you’ve gone to all this effort. You’ve tested it on Desktop, you’ve published to the service, so do bear that in mind. So User ID is one that
you must take note of. For me, the execution
time is the other one. So it’s quite common in
the footer of your report that you want to convey when
that report was generated. At development time on your
desktop with Report Builder, it will be local time. When you publish it, and as is the case for all cloud services, it will be your coordinated
universal time, or UTC. CHRIS: Yes. It could be a little bit tricky to come up with an expression if you’ve got users all around the world. To translate that to local time. But if you’re fortunate enough to have them in a single time zone you might find a way to get around that by adding or subtracting
a static number of hours. Or what I’ve done is the easiest thing. When I convey the report
time at the bottom. I put UTC next to it. CHRIS: Yep, that’s a good idea. Right, so that way there’s no confusion. CHRIS: That’s a very good idea. One other thing to know, we’ll point this out in the next video, with a demonstration, is that ReportName returns nothing when you’re previewing it. CHRIS: That’s very true. PETER: But have faith in it. If it’s published to the servers it will in fact return
the name of the report. CHRIS: That’s true. PETER: Okay, when it comes
to building up expressions without getting too detailed, we see an example, in fact, three examples that will return the
value of the sales field. They all achieve exactly the same outcome. But they just do it differently and this is the way that
VB.NET as a language works. What you’ll find is that when you automatically set up expressions using the expression builder often by double clicking the field and it will inject a
reference into your expression it’ll use the first one. Fields!Sales.Value It literally means, from
the fields collection, get the sales item and the value property of the sales item. The note that I’ve included in
the slide’s really important is that any references to
items and names in your report are case-sensitive. So, if sales has a capital S, then you need to use a capital S. If it’s lower case S, you’ll
need to use lower case S. All right, here’s some examples. So, we’ve already mentioned
conveying the report name at the top of the report. This is how we do it. The expression for the text box is, equals Globals collection,
give me the report name. Next page numbering, the
concatenation of literal text to global variables. And there we see the
report execution details. We have a format function. The F, I think, means long format. So, take the execution time, convert it to a string in
long format and, by the way, there are lots of different
format codes you can use to get the exact format that you require. And then you see the UTC appended. I like the last example, in fact we’ll see this
in a later demonstration. Conditional formatting is also achieved. This time, the expression’s
assigned to a color property and according to the
value of the proper field. If it’s less than zero, return red. Otherwise, return black. CHRIS: I often use the
switch statement as well for things like this. So if I have a number of items
I need to use, I use switch. PETER: All right, so if you’re nesting IBs consider switch as a better alternative. Let’s just wrap up on the last topic then. The report itself has a code block that if you want to build much more complex programming logic you can do so by using VB.NET. That means you’ve got the
full capability of looping and whatever .NET can achieve for you. So this is a more advanced design concept but I love the idea that
a report can do this. Yeah, yeah, in fact I know some folks in our field organization
and some of the MVPs have been interesting Azure
Functions as part of this which you can do with the Power BI service so one of the examples that I’m aware of is that people have used Azure Functions to do translations. So they’ve used Azure Functions
embedded in the report to call back to a translator
and a translation service and then actually translated
the report as people view it. Well, how do we do this? So if you open up the
report property pages you will find there’s a dedicated page called the code page. You’ve just got a big box. Sadly, it doesn’t provide you Intellisense all of those wonderful capabilities. So, perhaps you’ll create
that logic in a tool like VS Code or Visual Studio and once you’ve tested it, you’ll copy and paste it in. And the way that you can
then refer to your functions within the code block is
like what you see here. Equals code dot the name of your function and then if it takes parameters, you’ll pass values in. So that brings us to the end of the theory for designing report layouts. Stay tuned, because the next video we’re going to apply much
of what we’ve just discussed in developing a report template that’s going to allow us to quick start the development of our
reports moving forward.

Leave a Reply