Episode 181: Paginated Reports in Power BI

Episode 181: Paginated Reports in Power BI

Episode 181: Paginated Reports in Power BI 560 420 Carlos L Chacon

In the second quarter of 2019, it was announced that Power BI would support paginated reports–at first this was assumed to be connected to an announcement that Power BI would support the RDL files from Reporting Services. The guidance however, was to continue building paginated reports in Power BI. In the fourth quarter of 2019, more information emerged about paginated reports and in this episode we drill into the offering of paginated reports in Power BI and…shhh…we won’t spoil it, but an old friend just might play a role in this ‘new’ offering.

Never Miss An Episode

Subscribe to get podcast notifications by email.

“I feel like at some point, marketing went to the Power BI team and went, “alright, apparently, we still need SQL Server Reporting Services, but we’re not allowed to say the word Reporting Services ever again.”

Eugene Meidinger
Episode 181: Paginated Reports in Power BI
  • Carlos:             Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon, and I am joined by my motley crew here. I’ve got Kevin.

    Kevin:              Oh wow, hello. Can I be the drummer?

    Carlos:             You’re on a first name basis, Kevin. No last name required, anymore. It’s just The Kevin.

    Kevin:              That is true.

    Eugene:           Aw, poor Kevin Kline just got demoted to Kline.

    Kevin:              To ‘Other Kevin’.

    Carlos:             To ‘Other Kev’– I would say ‘Bald Kevin’, but you know tha–

    Kevin:              I’ll go with that. I like hair.

    Carlos:             There you go. And Eugene Meidinger is here.

    Eugene:           Yes, I’m feeling very modeled.

    Kevin:              Eugene still has a last name.

    Eugene:           I don’t know, Meidinger’s pretty unique. There’s like three Eugene Meidingers in the United States.

    Carlos:             There you go. So if you want to commit identity theft, ladies and gentlemen–

    Eugene:           Oh, great. My social security number is 123–

    Kevin:              The other two are 80 years old, so you’ll know–

    Carlos:             Yeah, they’re going to die any moment. Okay so compañeros, we are glad to have Kevin here with us, because just before we hit the record button, we started talking about all of his travels and he has been experiencing some overseas pleasantries.

    Kevin:              Conference season is in full swing. The overseas portion is done. That was England and Australia, but it’s okay, because I had an entire day in the US in between.

    Carlos:             Oh wow, so you actually came back.

    Kevin:              I did. It was about $3000 cheaper to fly back to Raleigh and then fly to Sidney, instead of going London to Sidney.

    Eugene:           Oh my.

    Carlos:             You’re like, “okay, for $3000, I can make it happen.”

    Eugene:           That’s hub and spoke to an extreme, I gotta say.

    Carlos:             Right. Okay, so this episode, compañeros, well, I think we’re going to be doing a little bit of, I don’t know, song and dance is not quite the right word. But we’ve talked about doing a comparison between SQL Server Reporting Services and Power BI and maybe that’s not quite the right idea for what we want to do here. However, we’ve been talking quite a bit about Power BI, and many of you may be saying, “well, what about Reporting Services? What’s going to happen with Reporting Services? I have thousands of reports in Reporting Services, what’s Microsoft going to do for me?”

    Eugene:           Not much.

    Carlos:             Yeah, I was going to say, the answer is not completely clear. However, to add another dynamic to the mix, we have a presentation called Power BI Deployed, talking about the different variations of Power BI. As of May of this year, there was still a slide in there that said, “if you want to use paginated reports, layout reports, continue to use SQL Server Reporting Services.” Well, that’s now changed. So two things have changed. Now, they announced this last year, and I think they actually announced it at Summit, I want to say, that Power BI would be able to receive RDL files, so that’s number one. And then number two in that they would now support paginated files. It sounds like there’s just going to be a break. They’re just going to be like, “okay, if you have Reporting Services, I think that will continue to live with the SKU of SQL Server, but yeah, you don’t really need it and Power BI can just consume all that stuff.” Is that fair?

    Eugene:           I would say it’s a little bit more nuanced.

    Carlos:             A little more nuanced, okay. And that’s why we have this podcast episode, compañeros.

    Eugene:           Right? Providing nuance and references to old philosophers and old movies.

    Kevin:              That’s my job.

    Eugene:           Yeah.

    Kevin:              I’m not the nuance guy.

    Eugene:           It’s about division of labor. Yeah, it’s interesting, because I feel like at some point, marketing went to the Power BI team and went, “alright, apparently, we still need SQL Server Reporting Services, but we’re not allowed to say the word Reporting Services ever again. So, we’re just going to say ‘paginated reports’ and we’re going to rename SSRS, Power BI Report Server, even though it’s essentially the same exact product, and then we’re just going to call them paginated reports. We’re not going to call them Reporting Services or SSRS or any of that kind of stuff.” And so, I don’t want to be too harsh on the Microsoft team, but they kind of just took an existing product and they slapped it in with Power BI as kind of an immediate solution. And it’s fine, but like literally, if you install Power BI Report Server and you start poking around in the JavaScript code, there’s literally like an angular switch that says PBIRS branding. And if you turn that to ‘off’, suddenly it looks like SSRS again. It’s all the same underneath. The other thing, too, is the only way that you can render paginated reports alongside Power BI is by having a lot of money. Like a lot, a lot of money, because there’s two ways to do it. There’s Power BI Premium, so you either need one of the P-level SKUs, which starts at $5000/month, or you need the, I think A4, which also starts at like $5000 something per month, to have the Premium capacity to render the paginated reports. So, you don’t get it with vanilla Power BI, as of yet.

    Carlos:             Okay, so ultimately, we’re talking about paginated reports, make sure that I understand.

    Eugene:           It’s rebranded SSRS.

    Carlos:             That’s right, so it’s really an SSRS RDL file that is loaded into Power BI.

    Eugene:           Yes. Yeah, either the Power BI service, or Power BI Report Server, which again is just SSRS, but they rebranded it and added Power BI rendering capabilities. That’s literally it.

    Carlos:             That’s the idea. Tada. So, listener Joseph Nagime. I do apologize, Joseph, if I got that wrong. Chimed in and he was asking, he’s like, “hey, I’m looking for paginated reports, what should I start doing?” And I’m like, “well, I think you should go ahead and just consider Power BI. If you don’t already have SSRS, just go with Power BI and start looking at that, because that’s the future. And he’s like, “oh, okay, that’s great. I can have paginated report support ‘now’, but am I still developing those reports in SSRS, essentially, that interface and then uploading them to Power BI?”

    Eugene:           Yeah, I mean, so the tooling’s rebranded. So again, we’re not allowed to ever say SSRS ever again. So, it’s something like Power BI Paginated Report Builder or something like that.

    Carlos:             Okay, so you get a separate tool to build paginated reports.

    Eugene:           Correct, and it’s really just SSRS Report Builder. It’s the same thing. When the first like CTP came out, David Eldersveld found some registry flag that he could change and suddenly all the branding changed. So yeah, it’s all the same. And you’re right, my general opinion with this kind of stuff is ‘follow the money’. And just look at where Microsoft is putting their money, and then invest your career there, and it’s in Power BI. Now, I think there’s a set of use cases that Power BI is probably never going to be good at, and so I don’t see them killing off paginated reports. And one of those that I think is really striking is I never, never expect anyone to ever create an invoice in Power BI. It would be a fool’s errand, because you don’t have that level of control, the detail-level reporting isn’t particularly great. The printing options are not particularly great. Well, I haven’t tried PDF Export, so maybe that’s gotten better, but it’s just not designed for it.

    Kevin:              Yeah, at the end of the day, it’s a dashboarding tool. It’s not a report building tool.

    Eugene:           Right, like when I talk about tools and specializations, I ask people, “well, you know, could you cook a piece of toast with a butane torch?” It’s like, yeah, if you offered me $10,000, I’d get it done.

    Kevin:              I listen to the Sous Vide channel, I know all about this, now.

    Eugene:           But yeah, I mean, so really, there were like two or three use cases at my last job where SSRS was a no-brainer. So one was, okay, it literally needs to be printed out.

    Carlos:             Sure, cause back in the day, that’s what you did.

    Eugene:           Well, I mean, even in the modern day, that’s what we did. The other use case where that was important was, we used reports as operating documents, as evidence of work or as Kanban’s in the original sense, you know, this physical token. And so that would be worker schedules, that would be work orders, that would be invoices that get mailed out or emailed out or whatever. These were documents that represented the business in motion. And so Power BI is absolutely terrible at creating these artifacts that can be passed around, that can represent the business in action, the business in motion. SSRS or paginated reports is really good for that, and I don’t see that going away.

    Carlos:             Yeah, and that’s, I think, the other piece there to remember is that you already own the SKU. If you have SQL Server, you have the paginated reports SKU. Now, granted, you get to a certain scale, most people are separating that out and, you know, etc, etc. that line can wear pretty thin, pretty quickly.

    Eugene:           Yeah, so in my mind, there’s always going to be a place for SSRS, because one of the ways I like to think about it is there’s kind of this pyramid of business intelligence. So, you have kind of operational reports at the bottom, which are just descriptive. And then you have analytical reports in the middle of this pyramid that require more maturity of your BI team and all of that, and it’s all proscriptive. It’s like, what should we be doing? How’s the business doing? Just kind of answering questions more than just detail stuff. And then, at the peak of that pyramid is kind of the machine learning piece, or the predictive stuff. It’s like, “okay, what do we think is going to happen? And I don’t see that bottom foundation of the pyramid ever going away. I think maybe that middle part’s going to get fatter and more approachable to people, but I just don’t see operational reporting ever truly disappearing for businesses.

    Carlos:             Right, it’s probably also worth considering, and I’ll admit that I think Report Builder got a little bit better in this last incarnation, it was 2016, but I still remember it just kind of being a nightmare to work with. We talk a lot about Power BI and having a data model and things ready to go. I feel like that’s ten times more important when you start talking about using Report Builder, and if that’s the tool that we have to use in a lot of that, then nowhere near as approachable, in my mind as Power BI currently is. You definitely have to have that model down and have something for the user so that they can more easily consume. Because you’re pretty much just looking at tables and, I don’t know, the tooling in Power BI, at least to make the JOINs and kind of figure out some of that stuff is there, whereas from Report Builder, you don’t have that parachute, if you will.

    Eugene:           Yeah, I agree, I mean, so Power BI has more of a slope that you can go along as you move from data access, to data cleansing, to data modeling, to presentation layer. Like, it has a chunk for each of those, and you can learn those independently or concurrently and kind of work your way through, whereas SSRS is very firmly in that presentation layer. It does data access, but not much of the data cleansing, data modeling. You’re usually going to be doing that back in SQL, and that can be a bit of a jump. The other one of two things that’s a challenge with learning SSRS is one, it kind of follows the 1990’s Visual Studio approach of just giving you 60 different property flags for changing stuff, which when you’re trying to get started, is overwhelming and difficult. But the other issue is that what your measures or your values mean is very context-sensitive to where you are on the report, which is not so much the case with Power BI. So, if you you include a column, like let’s say, ‘sales’. Well, whether you’re inside of a Tablix control or outside of it or in a header, completely changes what it means, wo now, either you’re looking at detail or summary or everything.

    Carlos:             Sure, because it’s almost like cell-oriented, in a sense?

    Eugene:           Yeah, which is fine, but then when you’re trying to learn how to add–

    Carlos:             Lots of control.

    Eugene:           Yeah, when you’re trying to learn how to add grouping and headers and manipulate that and you accidently add a column outside of a table, all that stuff, there’s no good debugging method, because it’s this visual canvas that has this meaning based on where you put it, where you don’t get that with Power BI. You accidentally add a pie chart 5 cm to the left, and it’s still looking at the entirety of the data model. That’s not true with SSRS, so it’s a challenge.

    Carlos:             Yeah, so I guess I’m curious, you know, Kevin, what has been your transition as you’ve had reports in SSRS? Are you seeing a migration, is it just kind of like two divided camps?

    Kevin:              So, where I’m at now, we’ve pretty much never used Reporting Services for much. I have used it a lot at prior companies, but Reporting Services was essentially for some internal reporting, like database-level measures. It was never really built out for customers to see any data. That was, ingrown is probably the right neologism for that reporting technologies. So, Power BI has come in as a completely separate model, that it is more of a business intelligence, dashboarding tool, as opposed to an ersatz reporting tool that we try to cram in pixel-perfect results and yell at the product when it doesn’t give you that, because it was never intended for that. So, we’ve come in knowing the separation between these ideas and using it for just the things that it was really intended for.

    Carlos:             I think that’s a pretty good distinction. Yes, dashboarding can be internal, executive-level teams, particularly, but that very, very easily translates into outside in terms of auditors or investors or what have you. And thinking of SSRS as being more internal, I think that would probably work for a lot of folks. Although, on the flip side, we were talking about invoices and whatnot, which are–

    Kevin:              Yeah, yeah, I liked Eugene’s distinction of if it’s something that you would see printing out, then Reporting Services is probably the better starting choice, all other things being equal, because I’m typically not going to print out a dashboard picture.

    Carlos:             Sure.

    Kevin:              I can come up with something silly of reasons why you might, but yeah, generally, that’s the type of thing you look at. You glean information and you go on with your life, or you look at it, you get angry, because you have to do something, and then do something about it.

    Carlos:             Yeah. So it is interesting, and I think, now this is actually not as new as I was thinking. So, I guess Gartner is now separating traditional Enterprise BI from self-service BI, so it will also be interesting to see how the market, if you will, responds to this, and again, Eugene’s idea of ‘follow the money’ is an interesting idea.

    Kevin:              Yeah, though as far as self-service versus Enterprise BI goes, remember, Reporting Services tried to bill itself as self-service. You have the poor man’s equivalent of things, this was like the train full of hobos equivalent of self-service BI. So yeah, you could actually get on there and do self-service BI with Reporting Services, but you’d start smelling like hobo urine.

    Carlos:             Yeah, that’s fair. Particularly talking about like Report Builder, like, “hey, your team can build all these models and then you have this HTML layer that you can start working with.

    Kevin:              And I’ve seen one company make use of that.

    Carlos:             There you go. I wonder if we could find any other– I’m sure other people have done that, and actually, so compañeros, if you have had good experiences building models and then having your team use Report Builder, I want to talk with you.

    Kevin:              To be fair, I didn’t say they were good experiences.

    Eugene:           I was waiting for you to be like, if you’ve had good experiences with Reporting Services ever, let us know.”

    Kevin:              I’ve had good experiences with Reporting Services.

    Eugene:           Same, same.

    Carlos:             Yeah, I was going to say, me too, yeah.

    Kevin:              It took a while.

    Carlos:             That’s right, and generally not for the end user, this is definitely IT building these reports.

    Kevin:              Oh yeah.

    Carlos:             And not self-service. So, interesting. Then I guess that experience is very similar. For some reason, I had in my mind– I guess that the marketing is doing its job, that it was slightly different, but if ultimately, I’m using Report Builder to do paginated reports, then maybe that gives me pause.

    Eugene:           So I mean, there’s going to be differences in the margins. Right, so I think there’s some differences in how some of the report refreshing works, some of the data sources you have access to.

    Kevin:              The visuals you can lay out.

    Eugene:           Yeah, what you can kind of do email subscriptions from the online service. I guess the way that I would think about it is if you’re making this big checkbox list of all the different features that are available or not available, yeah, there’s going to be checkboxes that aren’t checked for paginated reports or that are for SSRS and visa versa. But at it’s core, at it’s bones, it’s the same exact technology.

    Carlos:             Sure. Well, now, that’s interesting, okay, so one of the other pieces that we kind of hit on, but then with that is, we talked about SSRS requiring a SQL Server license, you get it with the SKU. So in this case, are they now breaking off Report Builder so that like Power BI, you can get the desktop for free and start building reports. I’m assuming there’s a change there, right? They’re not going to hit you up with– I mean, that would be, in your true-up?

    Eugene:           Yeah, yeah.

    Carlos:             I see that you have Report Builder here, that’s a SQL Server license.

    Eugene:           Well, I mean, so Report Builder’s even less of a report consumption tool than Power BI Desktop is. So, with Power BI Desktop, you can kind of get away with passing around pbix files and viewing reports in there, but I think you would never do that with Report Builder. The way the licensing is, for hosting paginated reports that can sit alongside Power BI reports, you either need Power BI Report Server or you need Power BI Premium, and so, Power BI Report Server, you get that one of two ways. You either have Enterprise Edition SQL Server cores plus Software Assurance, which is a lot of money, or you have Power BI Premium and you get core licenses included with that, which again, is a lot of money, so either way, you’re going to be spending tens of thousands of dollars over a period of time, but no, the audit police are not going to come and say, “oh, you’ve got Report Builder, please give us $17,000.”

    Carlos:             Sure. Yeah, that makes sense to me. Okay. Interesting, so we’ve kind of focused in on obviously the paginated report pieces. I guess we talked about licensing. Are there other pieces that we may need to consider, here, in terms of differences?

    Eugene:           It’s not huge, but I think the one thing that we kind of touched on is just “should I be investing time in this?” And the comparison I like to make is look at DAX versus MDX. So, look at SSAS Multidimensional versus Tabular. I feel like Multidimensional is pretty much on life-support in the sense that you don’t see a lot of new stuff coming out for it. When you look at Azure Analysis Services, only DAX or Tabular mode is supported right now. And so that’s an example of, okay, Microsoft made a technology, and then they made a better technology in some ways, and now they’re just slowly coming off the old technology, but they’ve got too many legacy customers. That’s not the case with SSRS. They’re still doing active development. It’s not where all the cool new features go, but they’re still adding features, especially for the cloud rendering portion of paginated reports. And so, I think that putting some time into learning SSRS is still a valuable pursuit, but it definitely should come second to putting time and energy into Power BI, because again, that’s where Microsoft’s putting it’s money. So, I wrote a blog post about some of this stuff a while back, and I made a comparison to the Phipps Conservatory, here in Pittsburgh, because there’s some trees that they have signs that say, “I’m not dead, I’m just dormant.” That’s how I feel about SSRS. It’s not dead, just a little dormant right now.

    Carlos:             Interesting, there you go. Well, I think that does it and so compañeros, if you have other questions, or there are things that you think we should be talking about, reach out to us. That’s going to have to do it for today’s episode. Somebody, he who shall not be named, has a dentist appointment that he needs to get to.

    Kevin:              Clue is, it’s not me.

    Carlos:             We’re going to have to wrap it up, here. But as always, compañeros, if you want to reach out to us, let us know what you’re thinking or talking about, and you can do that in a variety of ways. Eugene?

    Eugene:           Yeah, you can find me on Twitter @sqlgene and you can find my blog at sqlgene.com.

    Carlos:             Kevin?

    Kevin:              My WeChat handle is on page 88 of your invoicing report.

    Carlos:             Or if you’re really desperate, you can throw a bottle into the ocean and next time Kevin’s in Australia, he can pick it up. And I, compañeros, am on LinkedIn at Carlos L Chacon. Thanks again for tuning in and I’ll see you on the SQL Trail.

Listen to Learn

00:38     Intro to the team
02:19     What is going to happen with Reporting Services?
05:55     Are you developing reports in SSRS and then uploading them to Power BI?
07:55     Use cases when SSRS is a no-brainer
09:50     Using Report Builder is not the same as Power BI
12:18     Kevin’s experiences with Reporting Services and Power BI
14:28     You might start smelling like hobo urine
16:02     You wouldn’t use Report Builder without a SQL Server license…but you could
18:10     SSRS or Power BI…where you should be investing your time
19:41     Closing Thoughts

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top