Power BI Report Builder & Paginated Reports

Formatting Power BI reports for laptop or tablet delivery is quite forgiving. For example, a long table can be viewed simply by scrolling. No page breaks to worry about. But what about printing investor financial reports or invoices, for example? There are times you need to control formatting exactly for delivery as a PDF or on paper, constraining tables to fit on a page and adding headers and footers. That’s where building paginated reports in Power BI Report Builder comes in. 

In this instructive on demand webinar, you will learn the basics of working with Power BI Report Builder. We demo 

  • The difference between interactive and static report delivery 
  • The benefits of and use cases for paginated reports 
  • How to use Report Builder to 
    • Connect to a data source/dataset to build a report 
    • Design a basic “pixel perfect” report page 
    • Publish a report 
    • Maximize licensing 

Presenter

Pat Powers
BI Trainer and Consultant
Senturus, Inc. 

Pat is one of our most popular presenters, regularly receiving high marks from participants for their subject matter knowledge, clarity of communication and ability to infuse. Pat has over 20 years of experience in data science, business intelligence and data analytics and is fluent across multiple BI platforms. They are a Tableau Certified Associate and well versed in Power BI. An expert in Cognos, their product experience goes back to version 6. Pat has extensive experience in Actuate, Hyperion and Business Objects and certifications in Java, Python, C++, Microsoft SQL. 

Machine transcript

0:11
Hello everyone.

0:13
Hey, welcome everybody.

0:14
Welcome to another Senturus webinar.

0:17
This time around, we’re going to talk about Power BI, Report Builder and Paginated reports.

0:22
We are still going to talk about Report Builder and paginated reports.

0:27
So introduced me for those of you who don’t know me, which you know, pretty surprising.

0:34
There you go.

0:34
Look at that, Steve and thank you Steve for pasting that a second time and look at that.

0:41
You got some kudos up there in the chat window in the Q&A just for you, Steve.

0:45
But we’re going to do an introduction for those of you who don’t know me.

0:49
Then we’re going to talk about why do you need Power BI paginated reports, Why do you need a report builder at all?

0:57
What is the purpose of it?

0:59
And I’m going to do a demo because there’s some things I think are misunderstood about Report Builder and I’m going to do 2 specific demos given enough time to show you that there’s more to Report Builder than you probably think.

1:17
Then we’ll finish up with our usual Senturus overview and additional resources and time allowing go through any of the questions that I have missed along the way.

1:27
Hopefully that sounds good to everybody.

1:28
Hopefully you’re all doing swell on this Thursday.

1:32
So for those of you who do not know me, that is me.

1:34
Pat Powers, BI Consultant, trainer, Architect, Data Scientist.

1:40
Pain in the you know what extraordinaire this is?

1:44
My 27th year of data science, data analytics, data warehousing, whatever term does your you wish to give it this week.

1:52
Been doing this for nearly three decades with just about every product you’ve ever seen.

1:57
And I am certified in all of said products.

2:00
Power BI, Tableau, Cognos.

2:04
Been doing it all.

2:05
Doing them all way too long.

2:06
I’m old, I’m tired.

2:07
And man, that about sums it up.

2:13
Come on.

2:15
And now for our poll, Because what is a centrist webinar without a poll?

2:20
We got to have a poll.

2:22
So let’s ask, how do you deliver static reports currently?

2:27
Now, by static, we’re not talking just about printed versions.

2:36
Wow, words lost me there.

2:37
We’re not talking just about printed versions, talking about things that you might e-mail anything else.

2:42
So how do you do it?

2:44
Let me start that poll.

2:45
All right, here we go.

2:48
Going once, going twice, boom.

2:53
And what a Shaka Rooney.

2:59
The majority of you are using Excel or other file based solution.

3:04
It doesn’t come as any surprise.

3:05
This is what I literally expected this poll to end up looking like.

3:13
So let’s talk about how we can make those deliveries better because while we’re on this topic, we also need to keep in mind things that are going on with Fabric and things that are going on in terms of the overall Power BI, Power architecture, Microsoft stack architecture and how Fabric is this going to be this container for all this including the ability to do paginated reports from within your ServiceNow.

3:50
Today we’re talking about the desktop tool, but it’s important to still keep that in mind as we move into the future.

3:57
And so that’s why I want to show you two very specific things today.

4:01
And 1st off, as I’m sure everyone of you is aware, as I’m sure you’re all aware, we’ve got challenges of report authoring, right.

4:14
I think it’s very, very common for all of us to know you’ve got, you’ve got that person who’s in HR, you’ve got that person who’s retiring in in three months and they are not about to try and learn some new fancy technology.

4:32
And they say, look, dear, can’t you just give it to me the way I’ve always been using it?

4:37
Look, I’ve been here since 1974, Can I just have my same piece of paper?

4:43
And that means you’ve got to replicate Excel spreadsheets, paper invoices, everything that’s been around since 1974, right?

4:56
Yes, Scott in 1974.

4:57
Hey, everybody.

4:58
Scott Felton has joined us.

5:00
Everybody give it up for Scott Felton.

5:03
Scott Felton is as our wonderful sales director.

5:06
And if you want to talk more about this, you can always get with Scott.

5:10
Scott, we’ll talk to you more.

5:13
Me, when I’m done with this, I’m going to go home and sit in the dark because I still have no power.

5:18
So how do we replicate these things like Excel spreadsheets, paper invoices, purchase orders, all those things?

5:27
Because in today’s modern tools, that’s not what they’re built for, right?

5:32
Today’s modern tools.

5:34
No, you have to make everything pretty.

5:36
Look.

5:36
Look at all these pretty visualizations.

5:39
Look at all the pretty colors.

5:42
Yeah.

5:42
Well, how well does that print?

5:45
How well does that e-mail?

5:47
How well does that look on everybody’s monitor?

5:50
These are the questions that I would wager a lot of you.

5:55
A lot of you ask.

5:56
Yes.

5:57
Andrew.

5:58
You came in late, Andrew.

5:59
Sorry.

5:59
Yeah, No.

6:00
Last night at 1:15 AM, a giant lightning bolt hit the power substation next to my house.

6:08
I’ve been without power since 1:15 AM.

6:10
I am sitting.

6:11
So again, those of you who did join late, I am not at my normal workstation.

6:15
I am sitting in a rented office space.

6:18
I’m talking to you on less than adequate hardware and I’m doing the best I can.

6:25
So we’ll get We’ll get through this though, gang.

6:27
I promise you.

6:28
Hey again, look at this.

6:31
How are you going to print this?

6:32
How are you going to take this?

6:33
How are we going to use this?

6:35
The way that, the way that the your purchasing department wants it?

6:39
Okay.

6:39
How’s your purchasing department supposed to put this in that file cabinet that they have been guarding since 1975?

6:46
Right.

6:47
We started giving them papers in 74 and 75.

6:50
They started filing them.

6:52
So oops, paginated reports.

6:57
Paginated reports are meant to be designed to be printed.

7:01
They’re meant to be shared.

7:02
And they’re called that because they are meant to fit well on a page.

7:09
They are meant to fit well on a formatted page.

7:14
They display all the data in a table, even if that table spans multiple pages.

7:19
They are designed exactly for what we’re trying to do here, Okay.

7:26
One of the things that’s really important about them is they’re pixel perfect, meaning that you can, you can replicate that invoice form that you’ve been using for the last 20 years.

7:40
Now, you know, I say 1974, like it was only 20 years ago because, you know, 95 was only 10 years ago.

7:47
Right.

7:48
Friends just went off the air.

7:49
Right.

7:51
Just saw the last episode of Seinfeld.

7:56
Yeah, 1993 wasn’t 30 years ago.

7:58
OK, let me have that one.

8:02
All right, they do become interactive when you use them through the service, but here you get the best of both worlds.

8:09
And again, This is why I want you to be here today, because most people tend to tend to go into blinder mode and they think of these only in terms of printing or PD F’s or whatever.

8:24
But the reality is, is you can also publish these to your service and when you publish these to your service then they can become interactive.

8:34
We can have interactive sorting, we can have drills, We can have different functionality on there.

8:41
They can also be designed through the service as I just said.

8:44
So once again, thinking of the bigger picture, thinking of fabric, thinking of all these other newer things, all of this comes together all right.

8:53
How we doing so far Everybody doing great.

8:57
Let me let me.

8:58
Let me see you raise.

8:59
Raise those hands if you’re doing great.

9:02
Look at that.

9:03
Thank you.

9:04
Wow.

9:04
Look at all those hands going up.

9:06
You’re awesome.

9:08
I like it when everybody actually pays attention to me.

9:11
You don’t get into a job like this unless you have ego issues.

9:14
Just saying So.

9:18
Some things to know about paginated reports.

9:22
One of the nice things is a single paginated report can have multiple different data sources.

9:28
It doesn’t have an underlying data model, so you’re not locked into having to do all the joints and everything else you can You cannot have.

9:38
You cannot have two different data sources in the same object on a paginated report, but you can have multiple data sources on a paginated report.

9:48
You can also embed these data sources and data sets into the report itself.

9:54
The tool to use this is called Report Builder.

9:58
If Report Builder looks a little familiar to you, which I’m sure it will to some of you, you’ll understand why when I say that it is a local desktop tool that is essentially the rebranding of something many of you have probably already used, you can use on Prem data after you upload it to the service.

10:21
You do have to create a gateway, though, So there has to be a gateway if you’re using it on Prem.

10:26
All right, one of the big differences that you’re going to see here, those of you who are used to using Power BI Desktop, you’re used to be able to connect into oodles and oodles of different data sources, specifically Excel or CSV or, you know, Web Monkey or some strange thing like Jethro.

10:51
You can connect almost anything you want to Power BI Desktop.

10:56
This list.

10:58
This is the list that Report Builder can connect to.

11:02
Okay that is important to keep in mind.

11:04
This is the list.

11:08
The only saving grace you’ve got here is the fact that you can use an ODBC connection which allows you to at least connect to a little bit more variety of things.

11:20
But at the end of the day, this is what you got.

11:25
What you see is what you get.

11:27
Okay.

11:29
Keep that in mind.

11:31
Keep that in mind when you’re going to build reports, we build them again in Report Builder.

11:41
We have different things that we can do.

11:42
We can build matrix reports, we can add charts.

11:45
We can also do free form.

11:47
This is where I think most people don’t really utilize the full advantage of Report of Report Builder.

11:56
They maybe are doing things like this.

11:59
This is pretty much text with some colored boxes, but you can you can do things like data bars and sparklines and variances.

12:11
These are possible in the tool, so look in addition to those invoice type reports, you can also add things like this.

12:22
Calculations, groups, drill downs, visual indicators, data bars, so sparklines.

12:28
Basically, they’re more than just a printed piece of paper.

12:35
And I want you to use the tool that way.

12:38
Because yeah, you’re going to have people who want the printed piece of paper, but you can also lure them in and get them on the service and get them using more by saying, hey, if you log in, look at all these other cool things you can do.

12:55
Here’s your piece of paper, Donna.

12:57
But if you also want to see something cool, log into the service.

13:04
I think that takes us to our demo.

13:08
Oh no.

13:08
One last thing I want to talk about.

13:11
We’re almost to the demo.

13:12
I promise you templates.

13:15
One of the very first things you should do in in paginated report and Report Builder is build yourself a template.

13:21
OK, because the number one factor to getting people to use tools like this is consistency.

13:35
It doesn’t matter.

13:36
You might have 10 people in your organization who are building reports.

13:39
OK.

13:41
Your end user should have no clue from which of the 10 people the report came.

13:48
There should be a consistent look and feel.

13:50
There should be a consistent message.

13:53
There are people in your organization known as your branding department, your, your identity department, or you know, in the olden days, your marketing department, who have spent a long time coming up with the exact perfect shade of blue that all of your fonts should be.

14:11
It’s now their time to shine.

14:13
Okay.

14:14
We’re going to we’re going to give it to them.

14:17
All those years they spent looking at kerning, this is their moment in the sun.

14:22
Don’t take it away from them.

14:24
So you want to have a template.

14:26
You can publish that template out to your service.

14:29
That way anybody who builds a report can download that template and everybody starts from the same starting point.

14:36
And guess what I’m going to do today?

14:39
I’m going to start from a template because I am going to put my money where my mouth is or my mouth where my money is.

14:47
However you want to look at it.

14:49
All right, here’s where things are going to get tricky because again, I’m not on my usual environment.

14:55
I’m going to do my gosh darn best welcome to Report Builder.

15:02
And there’s at least one of you right now.

15:05
There’s at least one of you saying that that looks a lot, that looks a lot like something I used 20 years ago.

15:13
Barry, you know what?

15:15
You’re right.

15:18
If this looks a lot like SSRS to you, it’s because it is.

15:24
It’s SSRS rebranded.

15:26
Andrew, you nailed it right on the head.

15:28
It is this, this is SSRS gang.

15:31
So if it looks very familiar, yeah.

15:37
And even Microsoft straight up acknowledges Report Builder is an updated, quote UN quote version of SSRS, SQL Services, Report Services, Report Studio, whatever the heck it was stands for.

15:55
But yeah, the old tool that came with SQL Server.

16:00
Yes.

16:00
Thank you SQL Server Reporting Services.

16:03
Thank you, Scott.

16:04
Yes, sorry again.

16:06
I’m not used to having to turn my head.

16:09
You’re all over here on another screen, on another computer.

16:13
That’s why I’m connected to this twice.

16:16
Such a headache.

16:17
So here you go, ladies and gentlemen, look at this.

16:20
I’ve got a template.

16:21
I’ve got a dynamic template too.

16:24
Notice that I used a function for my report name.

16:28
Look at that over here.

16:29
You can have functions.

16:32
So when this gets saved, this gets automatically put in.

16:36
Look at my handy dandy logo.

16:40
Look at my nice little execution time down here and I can move these things around.

16:44
You know, I can adjust them.

16:45
I can move them where I want them.

16:47
You’ll also notice that, hey, why isn’t your work area taking up the size of a page?

16:56
Well if you take a look over here at properties, I’ve actually got my page property set to 8 1/2 by 11.

17:06
So I don’t need to adjust my canvas necessarily.

17:10
Because once I put in the data fields on my canvas, the data will go and fill an 8 1/2 by 11 set of set of specs.

17:23
So I don’t have to adjust my canvas, I have to adjust my page, my work canvas.

17:31
What?

17:31
I don’t even know what my work canvas is set for.

17:33
That’s the header.

17:35
7 1/2 by 2 is what I have my work area set for.

17:41
All right, so that’s something to keep in mind.

17:43
All right, we’re going to connect to data two different ways here.

17:47
The 1st way I’m going to connect to data is through a database.

17:51
So over here on the left I have data sources and data sets.

17:57
In order to create a data set I have to 1st connect to a data source.

18:02
So I’m going to add a data source and in the second demo I’m going to use a Power BI data set.

18:07
In the first one, though, I’m going to call this Adventure works, and we’re going to connect up to an Azure SQL database, and we’re all going to cross our fingers that I can get past the past and the firewalls and all those fun things.

18:35
Dad.

18:35
Nay, no, not that.

18:37
We’re going to do a SQL Server login and give me 3 seconds while I switch screens so I can see what the heck I’m looking at.

18:45
Got it?

18:53
Nope.

18:57
That’s better.

18:58
Don’t worry, I didn’t finish the password, so nobody’s going to see my super-secret password, All right.

19:05
Moment of truth, Eve.

19:31
I got it.

19:31
We’re good.

19:33
Everybody, everybody breathe.

19:34
We’re good.

19:35
So I’m connecting up straight to a database.

19:40
As you can see, my connection is successful.

19:43
Now I’m going to create a data set and what you see here is we have it and it wants to know.

19:53
So I’m going to call this DS Main.

19:56
OK, I’m going to pick my Adventure works data set and now I’m going to go here to query designer.

20:05
OK, query designer and look at that.

20:11
There is my whole database.

20:13
So I’m going to go to Dim Employee.

20:17
I’m going to take first name, last name, title, e-mail address.

20:26
Phone and here’s a cool one.

20:33
I am going to use a BLOB of employee photo.

20:41
Now I’m also going to go down here to sales territory and I’m going to bring in the sales territory region and the sales territory group.

20:50
So I can go ahead and I can bring these in because there is a an underlying joint because there is an underlying join here of sales territory key and of employee key all going through the fact table.

21:03
Well, there is definitely a join in the background.

21:06
If I did not have an explicit join, I would have to bring over the fields.

21:11
I need to make sure that that join works OK What I’m also going to do is I’m going to set a filter.

21:18
So here under applied Filters I’m going to add a filter and I’m going to bring in a field I’m not using which is salesperson flag and I’m going to say is true.

21:29
What I’m going to build is I’m going to build a salesperson report and I’m going to build an employee directory report and I’m going to test this query.

21:39
Look at that and notice here I have SQL.

21:48
I have SQL if I needed to change something, if I wanted to add an ORDER by, if I wanted to add a group by, I can type in directly here so I could type in an order by statement if I wanted to.

22:04
This is just Sequel, which means if you already have something you could copy and paste that sequel in here.

22:11
That’s kind of cool.

22:14
We could also add calculated fields.

22:17
So let’s go here to our fields and I’m going to add a calculated field.

22:21
OK, I can add a query field, so another one from the data source, or I can add a calculated one.

22:27
And I am going to call this salesperson and I’m going to write an expression.

22:37
Now here’s where I would normally copy and paste, but sadly I cannot today.

22:44
So I’m going to do a U Case and Last name dot value.

22:55
I’m just doing a basic concatenation here and notice that I’m uppercasing and I’m pulling back the field Last Name value.

23:03
It’s pretty straight forward.

23:04
Again, if you used SSRS, none of this should be any surprise to you.

23:10
Adding in a comma and a space, another & and then fields, bang, first name, dot, value.

23:26
There are built in fields, there are operators.

23:28
There are all sorts of things I could do that are built in.

23:31
Hopefully I didn’t mistype any of that because you know I’m going to add a second calculated field and I’m going to call this one group region something.

23:46
You’ll also notice that I’m not using and that there aren’t any spaces because this is SSRS because this is an older tool, no spaces.

23:55
If any of these field names that had spaces it would have automatically put in underscores in here so it also if there’s non standard characters, question marks, percentages, things like that it will replace those with underscores.

24:13
OK, so I’m going to do this and for this one fields!Sales.TerritoryGroup.Value & “: “ & fields!Sales.TerritoryRegion.Value again.

24:54
Hopefully I didn’t mess any of that up.

24:56
Hopefully I got everything typed in correctly.

25:00
Cross your fingers.

25:02
All right, now that we’ve got that, now that we’ve got our data set, do OK and look at that.

25:08
There’s our data set all ready to go.

25:11
Time to build.

25:14
So the first thing I’m going to do is I’m going to right click in here and I’m going to insert a list.

25:19
Now when I say these are pixel perfect, here’s what we’re talking about.

25:24
One of the things that I think some of you may get frustrated with this tool.

25:29
There’s a lot of tedium involved because now what I have to do is come down here to the location and size properties.

25:36
And yeah, I have to manually set 00, and I want my width to be 7.5.

25:51
I want my height to be 1.6.

25:56
So yeah, you’re going to have to manually do those things.

26:00
Okay, Aaron, I am not aware of any bursting capabilities that are built in here.

26:11
The question was asked, can you burst this based on somebody’s e-mail or something like that That would be needed to be done within the service itself.

26:21
So yes, something like Power Automate would be a good way to do that.

26:25
If I’m wrong on that, please somebody tell me.

26:27
But I’m not aware of any bursting capabilities in here.

26:31
OK, which is what you were asking about course.

26:38
And yeah, Andrew, it is tedious and I want everybody to be aware of that.

26:42
This is tedious and I’m not even going.

26:44
I’m going to not.

26:45
I may not get this completely finished because it is that tedious and I want to make sure I have enough time to show you the other one.

26:51
So I’m going to drag salesperson into this list and notice that it put it basically wherever the heck I wanted it.

26:59
So I’m going to make sure that I get this all set up.

27:03
I’m going to select that text box, I’m going to set it to an 18 point font, and now I get to do this fun stuff location.

27:15
I want the left to be 1.2.

27:20
I want the top to be 0 and width will be 6.3 also.

27:37
There’s no magic in this gang.

27:39
I hate to tell you this but there’s no magic in this.

27:41
This is trial and error.

27:44
OK, it is trial and error till you get this right.

27:47
Now I’m going to quickly add the rest of these.

27:54
I’m going to try to get them where I want them without spending a lot of time.

28:00
Group, region, title, phone, e-mail address.

28:20
And for all of these.

28:31
For all of these, Yeah, that’s the other thing.

28:38
There are some things that can and cannot be done multiple.

28:42
Isn’t that fun?

28:45
6.3, 25 group region top 3 Title.

29:05
And again, I just want you to see when we say this is tedious, I’m not joking around 0.55, 6.3, etc

29:26
All right, now we’re going to add a sort to this.

29:29
So I’m going to right click inside here and I’m going to select my entire tablets.

29:34
My tablets is my list.

29:38
So here it’s called the tablets and what I’ve selected is the entire list.

29:43
All right, all right, I’m going to make sure the height is 1.6.

29:49
Check and I’m going to change the sort expression.

29:55
So under other here’s under other sort expression, I’m going to add in sorting and I’m going to add.

30:06
I’m going to base it on salesperson A-Z.

30:10
So now I’ve got sorting in there.

30:12
You know what I need to do these two at least.

30:18
Oops, don’t screw that up, 6.3 OK, so there we go.

30:31
The next thing is, hey, let’s add in our image, because I can add images into this.

30:37
All right?

30:38
Which is pretty cool.

30:39
So I’m going to right click in here.

30:40
I’m going to insert an image.

30:44
All right, we’re going to call this employee photo.

30:51
I could add a tool tip if I wanted.

30:53
I’m going to pull this from the database and I’m going to use the field Employee photo and I’m going to tell it what the MIME type is.

31:04
And I think this is a JPEG.

31:06
Yeah, the JPEG.

31:08
Look at that.

31:10
Nifty.

31:11
Come on, tell me that’s nifty.

31:14
You know it is.

31:20
And thank you, it is nifty.

31:27
So I’m going to set that to 00.

31:29
All right, thank you for that.

31:35
Ooh ah, it is nifty.

31:38
My width, it’s going to be 1.

31:41
By height it’s going to be 1.4.

31:45
There we go.

31:47
Now what I want to line in between each of these.

31:50
I want to line in between each of my sales people.

31:53
So look at all the other things I can insert here.

31:55
And again, this is what I want you to see that it’s not just boring stuff on the page.

32:01
I can insert a sub report, a chart, a gauge.

32:05
Well, for this one I’m going to insert a line because I want a line between them and for my line we’re going to go 1.2, 1.4 my endpoint horizontal 7.5, verticals 1.4.

32:35
All right.

32:35
I don’t see any questions come in, but I’m checking.

32:39
So Andre, yes I could drag this but notice look, I dragged in phone and e-mail address and if you look closely do you see how I’m not exactly on do you see that Andre, This is why I want you to see that there is going to be a lot of that pixel perfect type stuff where you’re going to have to spend the time in the TDM getting things lined up.

33:08
And OK, Stephanie, so images can come from different places, they can come from external.

33:20
OK, So I can pick I can put in like a URL or something along those lines.

33:25
They can come from embedded so I can import it, I can import it from my local machine.

33:31
External would be a URL, or I can pull it from a database.

33:37
So those are the places where I can pull those things from.

33:39
Stephanie.

33:44
All right, now that I’ve got all that, let’s see what this looks like.

33:49
In order to preview my report, I’m going to run it.

33:56
Look at that everybody.

34:01
3 pages 1,2,3.

34:07
Look at that.

34:08
And if we look at the print layout, here’s what it would look like in print.

34:20
I could export it to any of these formats.

34:28
I could still export it to Word.

34:30
I could export it to PowerPoint, Excel.

34:34
I can still export it to all these different things so I can e-mail it, so I can bring it out there, everything else.

34:41
But again, it’s more than just something on a page.

34:55
Well, I don’t know if that may just be a zoom issue.

34:58
It may be because I did not put in exact positioning on why the e-mail is not showing Adam.

35:09
It may be off, it may be off the printed page.

35:14
So again, This is why we want to take the time to do exact locations on these things.

35:20
Because I set it to a certain width, it may be getting dropped off.

35:26
OK, Oscar, if I export this to Excel, let’s take a look at it.

35:38
What do you say?

35:38
What do you say we take a look at it together?

35:51
Here’s what we get in Excel, single tab.

35:55
I even get the image still.

36:00
Thank you Andrew.

36:01
Thanks for stopping by.

36:02
So I’m going to do one more demo that’s very similar, but I’m going to use a different data source so you guys can see a very specific difference.

36:08
But otherwise you’ve seen it.

36:13
But Oscar, does that answer your question?

36:15
It is a single sheet, it’s 1, 1 dump.

36:23
Each one of these is a cell.

36:25
The image is in its own cell.

36:30
And Adam again, hopefully that answered your question of why that was getting cut off, because notice that when I do export it, it’s not getting cut off.

36:37
So I think it has to do with the fact that I didn’t explicitly put it where I want it and that’s why it’s getting cut off.

36:43
All right, so let’s take a look at another one and what I’m going to do.

36:56
I don’t want to save that.

37:01
I’m going to open it up again.

37:07
Do to do, I’m going to blank report now.

37:11
I should show that again real quick.

37:14
When you do new IT does give you a few built in options.

37:20
It gives you ability to start from a table, from a chart, from a map.

37:24
If you decide to come to our paginated reports class we will show we do use the wizard and we do have a whole class on this.

37:33
It’s a.

37:33
It’s a one day class and we go through quite a bit more than what I’m doing here today and in 45 minutes.

37:43
But you can see there are different options all right, so I’m going to open up my template again.

37:51
Come on, look at that.

37:55
We all start from the same place.

37:57
This needs to be moved over.

37:59
I need to resave my template now I’m going to build 1 even faster.

38:04
This time however, I’m going to use a Power BI data set connection, which is probably what some of you came to see.

38:12
But notice I’m connecting my service.

38:15
I’m going into a workspace and I’m using a data set that has been previously published from Desktop Okay.

38:26
Notice the nice fun name it gives it.

38:32
I’m going to add a data set.

38:34
Once again, I’m going to call it Domain.

38:39
I’m going to pick that and watch this difference When we click Query Designer this time do.

38:54
When I click Query Designer this time.

38:56
Hint.

39:01
So how’s everybody doing?

39:03
OK, there we go.

39:05
What is this look instead of SQL?

39:10
Now we’re working with either DAX or MDX depending on the data set.

39:16
Look at that.

39:18
We’re working with DAX.

39:20
That’s where you say ooh, ah again.

39:25
So that’s our big difference when we do it this way.

39:28
We’re working with DAX.

39:30
And you see that I can actually work with member data.

39:37
I can work with member data when I work from a published data set.

39:45
Come on, where’s my ooh, and on my eyes.

39:48
You’re letting me down here.

39:50
All right, over here on the left, we’re going to expand out salesperson.

39:57
We’re going to expand out the sales people hierarchy.

40:03
Thank you for that.

40:04
Ooh, on.

40:07
And I’m going to bring over Country Salesperson.

40:15
And then from my measures, I’m going to bring from sales, I’m going to bring over sales from target and from and variance percentage and from KPI, I’m going to bring over this field sales performance status.

40:35
I’m going to use that.

40:37
Given enough time, I’m going to use that to create an icon.

40:45
The KPI icon.

40:47
OK, we’re also going to add a filter.

40:51
So up here in the top I’m going to put a filter on here from date.

40:58
OK, this is all I want.

41:03
It is I want fiscal year equal.

41:11
And notice that I can make this a parameter and I am going to make this 2021.

41:18
Yep.

41:20
And I’m also going to parameterize this so I can add parameters here, gang.

41:25
So again, this is that interactivity.

41:27
If they log in and they do it online, they get things like parameters.

41:34
There’s my data.

41:37
We’ll use that.

41:38
We’ll use that for our visual indicator and we’re going to do OK And again, there’s my DAX, so if I wanted to type anything in DAX, I could.

41:52
We will also see if we go to fields.

41:55
This is what I was talking about before.

41:57
Notice that it changed the name of this.

42:01
It got rid of the percentage.

42:03
And I’m going to change the name of this one status.

42:11
I’m going to go to my parameters, my date of my fiscal year, and I’m going to go to the parameter.

42:17
Oops, I’m going to do OK there.

42:18
I’m going to go here to my parameters and I’m going to go to the parameter properties and notice that I can set certain things.

42:24
So I’m not going to allow blank values and I’m not going to allow multiple values.

42:29
This will force my users to have to pick a single year.

42:32
OK, I’m going to leave it.

42:38
I’m leaving the name for right now.

42:41
That’s fine.

42:42
I’m not going to add the subtitle.

42:44
I could add a subtitle up here.

42:47
What I am going to add is a table, all right?

42:52
And I’m going to put that table right here at 00, please.

43:00
Thank you.

43:02
OK, that’s good enough.

43:03
I’m going to use my field picker and I’m going to pick my fields.

43:10
Well, I’m going to add salesperson, then I’m going to add sales target, I’m going to insert more columns, I’m going to add variance percentage and that should be everything, right?

43:32
Right now I can format all these if I wanted to.

43:35
I can select multiple of them and I can change things like the background color.

43:41
I can change the font color, I can change the lines, I can change all those things.

43:45
I can change my alignment, make them all nice and pretty.

43:48
And if I just wanted to change these three, say I wanted to right align those and right align those.

43:55
Now I can also quickly set the format on these boxes.

43:58
Over here I have a format and I can do something very easy and nice.

44:03
Instead of having to deal with format, I can just say C0 current C0 decimal places.

44:10
Same thing here, format C0 and then this last box, format P2 for percentage 2 decimal places.

44:27
All right, we got about 5 more minutes gang, and I’m going to get you through this, I promise.

44:32
And so we’ve got something pretty straightforward, pretty easy.

44:35
I’m going to add in some conditional formatting.

44:37
So I’m going to right click this box right here, and I am going to go to the text box properties and look at this under fill.

44:48
I can do a function.

44:51
Nope, not fill font.

44:52
Try that again.

44:55
Text box properties.

44:57
Font.

44:59
And I want color.

45:04
There we go.

45:05
Going to use a function and I’m going to say equals and I’m just going to do an IF(FieldVariance_pct.value<0, ‘red’, ‘black’).

45:32
Nothing fancy there and nothing fancy at all.

45:39
Let’s take a look, let’s preview that real quick.

45:47
Look at that, look at that.

45:52
And as I said, up here, we’ve got our parameter, so I could pick a different year, view, report, boom.

46:05
Not too shabby, right?

46:07
The last thing I want to add in is I want to add in one more column and I want to put in some indicators, OK?

46:19
And then all will be done.

46:25
And he is correct.

46:26
Aaron, there’s a whole set of those language files that you can find in the in the docs for this.

46:33
So yes, Aaron asked that I put the field name dot value.

46:38
There are other dot things that can be put after that.

46:44
So yes, but dot value.

46:46
Think of this as this is this is VB, right?

46:49
This is this is VB from SSRS days.

46:53
So Aaron, if you ever wrote any Visual Basic or you know, script language like that, same thing, same language, Jitendra.

47:10
I logged in to my service under my service account to pull that data set back.

47:17
As far as it depends on your data source.

47:20
OK to use the data set I’m going to have to be in the service.

47:23
For others I could have if I’m doing it with a SQL Server or something like that, I’d have to have a username and password.

47:30
I can also do Windows authentication.

47:31
I can do Azure authentication.

47:33
If I’m doing a data set, I’ve got to be able to log into the service.

47:38
OK, all right.

47:40
So the last thing I want to do here is I’m going to add a new column.

47:48
Now again, I would have renamed these.

47:50
I would have made these prettier.

47:53
And for this one, I’m going to right click this box and I’m going to insert an indicator.

48:04
Look at that.

48:04
I’m going to insert an indicator.

48:06
I’m going to pick this three.

48:11
I’m going to click on that indicator and I’m going to go here.

48:17
I want the properties.

48:18
Indicator properties.

48:22
Oops.

48:22
Nope.

48:23
Cancel.

48:25
I need to set this to status.

48:27
There we go.

48:28
Now I’m going to go to Indicator properties.

48:32
Under values.

48:33
I’m going to set this to numeric and just like this.

48:42
Remember those statuses I had in there?

48:45
I’m going to use those for this.

48:50
So I’m going to read the database field.

48:52
I’m going to see what’s going on.

48:57
Boom.

49:05
Look at that.

49:07
Tada.

49:08
Now again, I would have changed the width on these.

49:10
I would have made these look a little nicer.

49:12
I would have changed the names of these columns.

49:15
But I wanted you to see, given the time that we had, that, hey, it’s more than just a dump.

49:21
I’m pulling things in from a database.

49:23
I’m doing it from a data set.

49:24
I’m adding visual indicators.

49:27
What do we think?

49:28
Do we like this?

49:30
We think this is pretty nifty, Kyle.

49:38
Kyle asked what’s missing from Cognos or how does this change from Cognos.

49:43
The biggest advantage here, Kyle, is the ability to access different data sets and to be able to access different data sources, whereas I really would need to have a model or something.

49:53
The other difference too is that there is depending on your source data, you’re going to have issues in terms of performance because a lot of people use Cognos to do it against transactional models and that’s why you end up with these giant data dumps.

50:10
This still wasn’t necessarily meant for data dumping.

50:12
OK.

50:13
And Kyle, I would recommend that if you want to talk more about this, Scott, could you please dump your chat with Scott thing in there into the chat window for everybody please.

50:24
So what I would suggest Kyle is set up a little meeting with Scott and we can talk more about this.

50:30
So because there’s a lot more to my answer, but yes I would this is going to be a lot more effective and efficient in some respects because of your options that are available in terms of data sets.

50:45
Now what was asked earlier, I can burst from Cognos a lot easier than I can from here.

50:52
But again, if I wrap it into a fabric world, then I’ve got a different story.

50:57
OK, all right gang, we got 3 minutes left.

51:00
Just in summary, as you saw, we can do pixel perfect reporting.

51:05
It’s an easy way to get power to get users moved into Power BI.

51:09
They still get what they want and they can still print, they can share, they can, they can do whatever they want.

51:17
But we get them engaged and we get them on board and we get them into using the service and we get them away from those types of things.

51:27
Again, if you want to learn more, hey, I do a whole day on this.

51:31
And in that class we build these reports, we add interactivity, we add drill through drill downs, we publish, we maintain.

51:39
Talk about licensing because there are issues with licensing with this.

51:44
You got to make sure you have the right license, you got to make sure you have the right PPU’s, all those sorts of things.

51:48
So come to the full day class again.

51:50
If you want to sign up for the class, you can go here to the Senturus website.

51:54
You can also send Scott a message and we can talk about even customizations of classes, okay upcoming events.

52:04
So we’re going to be doing the Fabric workshop.

52:07
Yeah, I’m working on a Fabric workshop.

52:11
I’ve been putting it together.

52:13
Yeah, and guess what?

52:15
Probably going to do these in person.

52:18
So if you’ve ever wanted to actually see me in person, I know it’s a scary thought.

52:25
We’re going to have Fabric workshops, All right, We’re also, we’ve also put up a 2023 BI Tools guide.

52:33
So Kevin or Kyle, excuse me, Kyle, this would be something that you might be interested in.

52:38
Take a look at this.

52:40
This at least shows you all the differences in the Microsoft analytics tools, So take a look at that.

52:46
But again, we can talk more because your question has more.

52:49
We’ve got a lot more out on the website.

52:51
We’ve got product views, we’ve got tips, we’ve got tricks, we’ve got everything you’ve ever wanted to know and nothing you’ve never wanted to know.

52:58
If you want to hear more about stuff like this, on September 20th, I’ll be talking about doing proper framework Manager modeling, why your models are horrible and how to fix them.

53:10
OK, Thank you, Aaron.

53:14
Get the heck out of here.

53:16
We’ll be doing a drag and drop cognitive data into Power BI.

53:19
So Kyle, there’s one you might want to go to.

53:22
Then again, we’re talking about jump starting to fabric and we’re going to do our fabric workshop.

53:26
But we got a lot of stuff coming up.

53:28
We got a lot of stuff between now and the end of the year.

53:30
Also, towards the end of the year, I’m going to be doing a session on planning out 2024.

53:35
You don’t want to miss that one either.

53:37
Hey, here at Senturus, we’ve been doing everything for a very long time.

53:40
We’re awesome.

53:41
You know it.

53:41
We know it.

53:43
We’ve things in a green arrow.

53:44
They come out of blue Arrow.

53:45
But look at all this stuff we do.

53:47
It’s wonderful.

53:48
It’s awesome.

53:49
22 years, 1400 clients, 3000 projects.

53:53
Look at all these people.

53:53
Some of you are here, some of you are on this list.

53:58
Ooh, Ah, good job.

54:03
You’d like to join us if you’d like to actually work with somebody as crazy as me.

54:06
Hey look, we’re hiring a managing consultant and BI consultant.

54:10
You can find that on the website.

54:11
You can also send it to [email protected].

54:15
I think I got all the questions.

54:17
I think so Again, you can always send a message to [email protected] and we will answer it and look at that.

54:24
It is 3:00 on the dot.

54:25
Thank you all very much.

54:27
I hope you enjoyed this.

54:29
Thank you for giving me an hour of your day.

54:32
Get the heck out of here.

54:35
You were all wonderful.

54:36
I hope you enjoyed it all.

54:37
I hope we see you in a Power BI Paginated Reports class.

54:44
Take care everybody.

54:46
Thank you all for stopping by.

54:52
You were all wonderful.

Connect with Senturus

Sign up to be notified about our upcoming events

Back to top