Tag Archives: Paginated
Power BI and Paginated Reports: Better Together
Wise Professionals See the Big Picture
Bringing it Home
Today we’ll be concluding our 3-part series on Analysis versus Reporting, Power BI versus SSRS and Paginated Reports, and looking closely at our habit of using the word “versus” so liberally:
-
Part One summarized the history of the industry, its long-stagnant roots in Reporting, and the recent “triumph” of Analysis.
-
Part Two examined the relative strengths of Power BI reports and Paginated (SSRS) Reports, and why you need both.
-
Today, we’ll explore how you can use both Power BI and Paginated Reports together, as part of a wholistic system.
What Does “Triumph” Mean?
I’d like to start by clarifying what I mean when I say Analysis has “won.” Even today, traditional reporting is STILL the majority of the “biomass” in the BI world. For example, right now around the world, there are probably three people using a Traditional report for every one person using an Analytical one.
So Analysis isn’t dominating usage today, but it IS dominating mindshare. No one is BUYING platforms which are 100% traditional reporting – not anymore. And in fact, lots of people ARE buying pure Analysis platforms like Tableau. Of course, what they SHOULD be doing is buying platforms capable of BOTH, which is kinda the focus of today’s article.
Analysis Will Keep Stealing Usage Share for Awhile
So we have a ways to go before Analysis equals or eclipses Reporting in usage, but the wind has most definitely shifted. Keep in mind that a LARGE percentage of “usage” of Reporting is not “looking at it with our eyeballs” usage, but rather EXPORT usage, which powers ANALYTICAL work in Excel. Below the radar of IT and invisible, and crude compared to the Analysis you can perform in Power BI, but Analysis nonetheless.
Until the world stops using Reporting as a crude form of database access (to power its Excel analysis), the fight is not over. And we have a LONG ways to go to get there. Even today, a client told me a story about a team member who last week printed out a 30-page report and was planning to go line by line through it, re-keying a subset of the data into Excel (manually!) to get a total of how many units of Product X they’d sold in the prior month. Said individual was thrilled to be shown the Power BI report where a single click provided the answer. So we still have some work to do, awareness-wise.
Where Will We Land?
At most, I expect the inverse of today: three Analysis reports for every Traditional report. If we slowly convert the “Traditional reports which pretend to be informative but are really just export sources” into Analytical (Power BI) reports, that should get us there – and really, that’s ALL that we SHOULD do.
And if you read Part Two, you know there are certain kinds of reports that Power BI is “bad” at. We still need SOMETHING for those, and I expect they will still constitute, say, 25% of all reporting usage once we reach equilibrium.
Switching from “Or” to “And”
Part Two covered when to use X versus Y, but even a firm grasp of that leaves something important on the table: what are the ways you can use BOTH in an integrated manner, thus getting a result which is more than the sum of its parts? Ah yes, the “Better Together” cliché! But there is substance here.
Microsoft should be leaning HARD into this aspect of their platform – that it has STRONG products in both worlds, because so few of their competitors have anything to offer in the Traditional space, even as said competitors are already behind in the Analysis space. The combination of the two – when well-integrated, brings at least three very interesting classes of benefits:
-
Cross-report drillthrough in either direction.
-
Both kinds of reports in a single portal.
-
Shared analytical model powering both flavors of reports.
Let’s take these one at a time.
Bi-Directional Drillthrough
This is the most fun, so I’ll cover it first. Consider the following wireframe of a Power BI report:
We’re Going Wireframe Today – No Time for Distracting Myself with Hyper-realistic Demo Building 
I hope it’s easy to imagine the interactive features of the above report. Click on a bar in one of the charts, for instance, and watch the rest of the report filter down to that Category or Customer, etc.
Imagine seeing Cust2 and thinking “hey, why is Customer 2 so low this month? Aren’t they normally buying a lot more from us? Let’s see what they actually bought – show me the invoices!” Well, here ya go…
Select Customer 2’s Bar, Click the Icon/Link
And then you get navigated to a Paginated Report to view the actual Invoices which were sent to Customer 2, because the filter selection you made in the Power BI report is “transmitted” as part of the URL:
Paginated Report Displays the Authentic Invoices
(The system “remembers” which customer you selected in Power BI – by passing it in the URL)
“Wait, Couldn’t I Build That Invoice Viewer in Power BI?”
Yeah, smart aleck, you COULD build precisely THAT one pictured above. But a couple things. One, a more realistic invoice might span multiple pages, with page breaks and intermediate subtotals, and Power BI isn’t good at that paginated stuff. Two, if you want to be absolutely sure you are looking at PRECISELY THE INVOICE which was sent, you would want to go to the source which generates and prints said invoices yes? And since said invoices often need to be pixel perfect, the original source again cannot be Power BI.
“Wait, How Do You Pass ‘Cust2’ in the URL?”
This isn’t a technical how-to article, and I’m trying to stay above the weeds, but here’s a quick series of screenshots which should help:
Format Shape –> Action –> Web URL –> fx –> Choose a Measure
Example of a Measure Which Constructs a URL to a Paginated Report
(Comes from a slightly more complex example than our wireframe demo)
And Now the Reverse Direction!
OK, so you’re looking at the invoice, and you spot the change. “They only bought ONE Flux Capacitor this month! Don’t they normally buy more??”
Aha! Well NOW you’re back to an Analysis kind of task! You COULD thumb backwards through invoices using the “Prev Invoice” link, but if the invoices are multi-page, that’s gonna be tedious, AND it’s hard to spot trends that way. Power BI is REALLY good at this kind of thing.
If only there were a way to seamlessly navigate to a Power BI report, pre-filtered to answering my question…
Ooh! It’s a Link!
Navigated back from Paginated to Power BI, With Product Category=”Flux Capacitor” and Customer=”Cust2”
(A different Power BI report than the one we started on, but of course depending on actual needs, it COULD have been the original)
“OK, Spill It – How’d You Do the URL THIS Time?”
Shockingly similar on the Paginated side (in Report Builder) to the Power BI side, actually…
In Report Builder, Select Text Box Properties
“Go to URL”
Build Yourself an Expression
(Again though I’m taking a pass on the weedy details today – trying to keep it as concise as we can around here)
Pretty Neat Huh?
Let’s see that whole flow one more time, in a more concise manner:
Glorious: You Bounce Back and Forth Across “the Line of Versus” with Agility
***BONUS THOUGHT: As cool as that is, I wonder if even TIGHTER integration makes sense in some cases. The custom visual framework in Power BI, in theory, could support an embedded Paginated Report control right? Would there be use cases for that? Or would the clarity of being in a completely separate report always be preferable? I’d bet that it would at least be interesting in SOME cases, but I need to think about it more. Leave a comment if you have an opinion, please!
Benefit Two: Single Portal
Not as sexy as cross-technology drillthrough, but a real benefit for everyone’s sanity as well as usage: both kinds of reports can be published to a single Workspace:
OMG, BOTH TYPES of Reports in the Same Workspace? THAT’S PETER VENKMAN’S MUSIC!
Back off, man. I’m a data scientist.
Don’t sleep on the little details like this. They make a big difference.
Benefit Three: Powered by the Same Analytical Model
SSAS Tabular (Power BI’s analytical engine) brings MANY advancements relative to its predecessor (SSAS MD), and one of them is the ability to return hyper-detailed rowsets without the performance penalty we suffered in the old days. If you want the data to populate an Invoice, you can fetch what you need with a DAX query and not sit there waiting forever for it to return. In fact, things like the Table visual already do this within Power BI itself.
So if you’ve invested in a robust Power BI analytical model to power your analysis, you might find yourself reaping unexpected Paginated Reporting benefits. If you don’t NEED to go build new, traditional SQL-powered plumbing to power a more traditional report, great! By all means, connect it to your Power BI model (aka Dataset), and off you go!
Your Data Model Can Power BOTH Kinds of Reports
And folks, that wraps up our series! What did you think? Did I leave anything out? Wait, what’s that you say? There IS something I missed? Lemme see…
“Hey Rob, How About a Good Microsoft Rant?”
How about more than one? I mean, I’m so glad you asked, cuz I’ve got two…
Integration Shouldn’t Cost So Much
First, why is it so expensive to get an integrated environment? Best I can tell, you can’t get a Power BI + Paginated integrated system for anything close to the price of getting them separately. 100 users of Power BI Pro would cost $ 1k a month, and an SSRS On-Prem server is a one-time payment of, say, $ 6500. So to run those two – separately – for two years, it would cost you about $ 30k all-in.
But to get them integrated, today you’d have to plunk down $ 5k a month for Premium, and that pushes your 2-year price to $ 120k – 4x having the same capabilities separately. Now, I’m pretty sure I can get benefits 1 and 3 (drillthrough and powering both from a shared data model) at the $ 30k two-year price, so what the extra $ 90k gets me is… benefit two? Side-by-side publishing of both report types in a single Workspace? Steep price jump for just that.
On the on-prem front, you can go Power BI Report Server, but that’s also a premium price point. Enterprise Edition plus Software Assurance is required, apparently, and that’s 100% an Enterprise SKU – well out of the reach of SMB’s, and just as importantly… out of Departmental reach within the Enterprise itself.
In fairness to Microsoft, the pricing for Enterprise customers DOES need to drive their thinking in large part. And that comes with benefits! Most of Microsoft’s data stack is actually quite underpriced for SMB’s and Departments, and we never complain about THAT, do we? At P3 for instance we can run all of our MS data stuff for a FRACTION of what we pay for Salesforce (which we deeply regret getting hooked into, but that’s a story for another day isn’t it)? There’s a lot more value in the MS platform than Salesforce, and yet the prices are reversed.
So this may be a sensible pricing strategy for the Enterprise, but this is one of those cases where it prohibits SMB’s (and again, the Departmental level of the Enterprise, which is crucial for “land and expand”) from playing the game properly.
It’s a massive strength for the MS platform versus the competition, and it seems a shame that Departmental/SMB is accidentally priced out of the most elegant version today.
OMG Naming!
Authentic Action Photo of Microsoft Naming Their Software
Oh, “Paginated Reports,” how I LOATHE thee as a name! This name caused me SO MUCH TROUBLE for a very long time. “How does this relate to SSRS?” is the question which kept plaguing me. Is it a new thing? If someone knows SSRS really well, will this be a foreign experience – a brand new technology – or super familiar? Is it not capable of pixel perfect document generation, and instead ONLY leans into the truly Paginated benefits of SSRS? Ugh.
Here’s what it REALLY is: it’s SSRS technology living within the Power BI environment. You still get Report Builder, and the whole thing still uses RDL. If you knew SSRS, Paginated Reports are for you. Which is good news – SSRS was, and is, a market leader in its space. Super robust and capable. Refined by TWO DECADES of development and experience.
Now, again, I kinda DO get why they’re doing this. Power BI is “the brand” now, and everything needs to line up under that banner. Years from now, this naming scheme will be a good one. “Power BI just has two kinds of reports, great!” But I think they underestimate the utter carnage this wreaks in the meantime, and how many times we’re going to have to explain to people that “it’s just SSRS in Power BI.”
Remember, I’m the guy who tried to rename PivotTables when I worked on Excel at Microsoft. So I’m definitely cut from the same cloth originally – I’m just trying to heal, and to atone for my past sins. But “yeah,” said Rob circa 2004, “PivotTable is such a wacky and scary name which has nothing to do with its primary function, which is to summarize/aggregate data.” I wanted to rename it SummaryTable. Just like I explained above, if you fast forward ten years, that’s a better name. Zero question. But is it worth the transition cost? In the end, wiser heads talked me out of it.
While we’re at it… can we rename Power Pivot and Power Query to be… Power BI in Excel? Because just like we’re explaining Paginated Reports = SSRS in Power BI, we have to explain the Excel relationship over and over and over…
Nah, too sensible.
Paginated Reporting and Power BI: Why You Probably Need Both
Recap of Part One
If you missed Tuesday’s history lesson AKA Part One, don’t worry, here’s the summary:
- For a long time, Reporting was the dominant force in BI, with Interactive Analysis a distant second
- This wasn’t because Reporting was better – it was just because Reporting was easier
- Reporting only provided the business with a pinhole-sized field of view
- This led to a proliferation of reports AND an overwhelming dependence on Excel
- Tableau was a breakthrough of Interactivity for the business, expanding the field of view considerably – but still insufficient
- Power BI marries Interactivity with an underlying Analytical Model, providing the most comprehensive field of view
This is a Good Thing, But…
Yes this represents tremendous progress! The purpose of BI is too see what is going on, and then use that vision to drive improvement. Pinhole-sized fields of view (Reporting) don’t cut it, and neither do spotlight-sized fields of view (Tableau). You need to see as much as possible. “See EVERYTHING” is a difficult goal to attain, but it IS the only goal which makes sense.
But let’s revisit the other diagram from Tuesday, because there’s something significant to be gleaned:
Interactive Dashboard Tools Aren’t Good at Everything
Interactivity Tradeoff #1 – Not Pixel Perfect
Technologies always come with tradeoffs, and an interactive canvas is ultimately a place where you arrange a bunch of pre-built objects. In Power BI, the objects in question are mostly called “Visuals,” like charts and slicers, but we also have Textboxes, Images, Buttons, and Shapes.
Pre-built objects are GREAT building blocks for building interactive dashboards! I don’t want to design a chart from scratch. I just want to drag it onto the canvas and then tweak the formatting to suit my needs. And then I want it to offer the user things like filtering controls.
But pre-built means “less than 100% flexible.” The things a graphic designer can come up with frequently will NOT quite be doable in a drag-and-drop canvas environment – but ARE feasible in a pure HTML environment. Power BI has done an amazing job making their visuals flexible, and you can get some incredibly-polished results, but they cannot possible think of, nor implement, every possibility. “As flexible as HTML” will never be attainable, not even close.
Here’s something that’s not the BEST example of Pixel Perfect, because it’s simple enough (design-wise) that Power BI could get you probably 95% there, but it’s a handy example and not client-sensitive. It’s the email report I got from the very last time I went to the gym before COVID scared me off:
Yeah, you could get CLOSE to this in Power BI, but there would be a few things “off.”
(Not to mention the aspect ratio – this is several screens tall)
Plus, Interactivity ITSELF necessarily introduces some “clutter” in the form of on-hover controls and effects. We need these for interaction purposes, but in an airtight, graphic-designer-driven environment, that sort of clutter can spoil an otherwise perfect design.
Three On-Hover Elements on a Simple Chart:
1) Legend and data labels 2) Indicator line for the X-axis 3) Filter controls
In a rudimentary chart like the above, those 3 elements of clutter are No Big Deal, but in a highly-stylized situation, they might be quite unwelcome. (Imagine the filter controls hovering PRECISELY where the designer would like the company logo to be). And even when Power BI lets us turn those sorts of things off, it still highlights the tradeoff between “total control over design” and interactivity – you get one or the other, never both. (BTW, the real-world scenario above with redacted labels reflects a TRIPLING in effective advertising costs in reaction to COVID-19 disruption in a particular market. June’s figures so far seem to indicate a reversion TOWARD normal, but still significantly more expensive than pre-COVID).
Interactivity Tradeoff #2 – No Pagination
Dashboards are meant to be… dashboards. As in, everything you need to see – IN ONE PLACE. It would be kinda the opposite of the Dashboard Ethos to have them… scroll. Or to have them feature “go to next page” type of controls. If you need that stuff, it’s not a dashboard.
Plus, interactivity loses its punch when you have to scroll all over the place. Click a filter, then scroll to see the impact. Oh wait, that’s not the right selection. Scroll back up to the filter, repeat. Nope, not a good experience. So neither Power BI nor Tableau support this kind of thing.
Here are a few examples which illustrate the loosely-defined concept of Pagination:
Multiple Pages Long and Contains Variable Number of Sections. Can’t Be Done with Matrix or Table Visuals.
Pretty Much Impossible in Dashboarding Tools like Power BI.
Could ALMOST be Done With a Single Table Visual in Power BI – Which DOES Scroll.
But… There’s a Page Break and a Footer on Each Page. And a Next/Back Page Nav. Nope, Power BI Can’t Do It.
Next/Back Page Nav Again (400 of 563!). Clean Page Breaks for Printing. Flirting with Pixel Perfect for Added Difficulty.
Again: Pretty Much Impossible in Dashboarding Tools like Power BI.
It turns out, most businesses have significant need for things like the above. And if Power BI reports were your only tool, you’d end up like our friends from the top of this article – flooded or grounded, depending on which metaphor you prefer.
If You Try to Build the Reports Pictured Above in Power BI, You Won’t Get Far.
Also? Mo Details, Mo Pages
Another “meta characteristic” of paginated reports is that they TEND to display details rather than aggregations. EX: specific transactions rather than emergent trends. In paginated reports, you’re MORE likely (but not guaranteed!) to be looking at “raw” rows of data from the original database, whereas in a Power BI report, you’re more likely (but again, not guaranteed!) to NOT be seeing raw individual rows, but rather intelligent aggregations of MANY rows. But either way, more detail means you’re more likely to need multiple pages.
And since details seem to be more self-evident in terms of action they imply, it’s a good time to bring back my flowchart from part one…
An Action-Oriented Distinction
While they are perfectly-useful distinctions, I’m far from the first to write about “Pixel Perfect” and “Paginated” as factors tilting you toward the Traditional Reporting end of the spectrum. And at P3, one of our rallying cries is that BI is for Action, and information is merely a means to an end. So I thought I’d try to contribute something new, as a second lens, by contrasting the two in terms of “where will your report fit into the Action flow?”
Click for Larger Version
Thus far in this article, I’ve talked about things which fall on the right side of that flowchart, whereas Part One focused primarily on the left side. If you didn’t read part one, and aren’t interested in doing so, no worries – this flowchart is the last piece of part one you’d be missing. You’re welcome. Now let’s move on to some important actions of a different flavor – namely, Do’s and Don’ts for your organization.
DON’T Use Power BI as the New Traditional Reports!
(AKA Power BI is NOT a Visualization Tool)
Pet peeve time, or actually, “Oh My God Please Don’t EVER Do This” time: don’t fall into the trap of using Power BI as just the latest reporting tool. When you hear someone refer to Power BI as a “visualization” tool, you should be very suspicious, because either they have no idea what Power BI can do, OR they are actively trying to suppress its adoption. We have seen plenty of both.
Here’s a quick acid test. Open a PBIX and and flip it to Model View. Does it look like this?
If Your Power BI Model View Looks Like This, You’re Not Getting the Full Benefit of Power BI – NOT EVEN CLOSE
(And yet, this is very common, because old habit die hard)
This is 100% worth calling out, because it’s usually a dead giveaway, and all too common. Most of the time (with a few exceptions), a model view that looks like the above indicates a lack of understanding of Power BI. But it’s often “perpetrated” by IT professionals with long histories in BI, which is understandable given the history of the industry (OK, maybe there’s a reason to read Part One after all). Given the long-dominant Reporting mindset of “do some SQL, make flat views, then format as reports,” a model view like the above is precisely what we should expect if a longtime BI practitioner just assumes Power BI is some newfangled version of the tools they’ve used forever. Which it’s not.
True story time: P3 was involved in a very large enterprise project in which we were not the only consulting firm engaged. The other half of the project was staffed by a Big Four firm, and the Big Four firm had brought out the Big Guns. They had sent one of their “absolute best Microsoft BI experts” to lead their half of the project, and we believed them. A month went by, and our half of the project was going well, but the other half kept floundering. We offered to help, and were told in no uncertain terms to Stay In Our Lane. And we good-naturedly did. We assumed the other half of the project was just a lot more complicated than we realized, and went back to work. Another month passed, and the other half of the project STILL lagged well behind schedule. Late one night, after hours, the project sponsor quietly asked us to take a look. And we saw a model view that looked like the one above. Their Microsoft BI Expert might have indeed been the real deal in 2009, but knew absolutely nothing about Power BI or SSAS Tabular. We had their problem fixed in three days.
I can’t show you an example of bad without showing you an example of good, so here ya go…
It Doesn’t Have to Look Exactly Like This, Nor Even This Complicated – But You Get the Idea
DO Have Both Tools in Your Repertoire!
Same Quality Table, Now With 33% More Columns!
Pretty obvious: if your org has both kinds of needs, you need to know both tools. Not just so that you can DO both kinds of work, but just as critically: knowing both helps you CHOOSE the right one at the right time. For that reason, having both skills on staff, but living in their own siloes, will still be problematic. The ability to tell the difference, and utilize the right tool at the right time, DOES seem to be a bit lacking right now, which was the whole trigger of this multi-part series of articles.
Ready for Anything – Where You Want to Be
(But You Still Need to Know When to Use the Wheels or the Propeller)
Coming in Part Three!
On Wednesday June 17, in the third and final (I promise!) installment of this series, I will:
-
Go on a bit of a rant about product naming
-
Question whether Microsoft even “gets” all of this
-
Provide some examples of how the two toolsets can integrate – the Better Together cliche, yes, but there’s a lot of truth to it here
Hope to see you back next week 
Power BI and Paginated Reports: Have We Come Full Circle?
This is where we’re headed in Part Two. Today: taking stock of how we got here.
(Click image for larger version)
Summary of this 2-part piece:
- There’s always been a blurry line between Analysis and Reporting
- 20 years ago, BI was dominated by Reporting, with Analysis as an upstart
- Today, Analysis seems to have “won,” and this DOES reflect major progress!
- But I also think we’ve overcorrected quite a bit, and it’s time to take a fresh look
- Old habits die hard, and old use cases die even harder
- In an era of Action, we have the chance to hybridize in ways that were impossible before
The Thick Grey Line
There are few stories as tortured and twisted as my relationship with Analysis and Reporting. In 2002, as part of the Excel team at Microsoft, I was introduced to the concept of Business Intelligence. This is back when the term (BI) was fresh. It sounded cool, like Jack Ryan in a darkened room at the CIA. (The stodgy 2000s-era, IT-bottlenecked reality would not dawn on me until much later).
Excel was about to make a large investment in BI-related capabilities, and the powers that be had selected me to lead our part in it. I was excited, but now I needed a crash course in “what the hell is BI?” I was given multiple tutors, and they all were quick to introduce the concept of Analysis versus Reporting. The “versus” seemed to be pretty important. It wasn’t an “and” – no, the “versus” was chosen deliberately in these sermons. You see, these were Two Very Different Things.
I struggled mightily to grasp this difference. I was told that interactive things like PivotTables were Analysis tools – NOT Reporting tools! Reports were something completely different. “But,” I pointed out, “they’re called ‘Insert PivotTable Report’ on the Excel menu today!” (This was Excel 2003). “Yeah,” said the mentors, “…we might want to fix that.”
I tested the fences. “If interactivity is the key differentiator between Analysis and Reporting,” I asked, “if I just LOOK at the same PivotTable every day, and I NEVER manipulate it in ANY way, then THAT’S a report, yeah?” “No,” said the mentors with the tone of an exasperated Yoda addressing an impetuous Luke, “it’s still a PivotTable, and that’s an Analysis tool.” “OK then, how about an Excel report worksheet that’s assembled with formulas alone, and therefore non-interactive. Is THAT a report?” “Rob,” they said, “you’re just gonna need to get it through your head that Excel is an Analysis tool. We have work to do and we can’t get hung up on this.”
Based on my mentors’ evasive answers, I concluded that this was merely a Distinction Without a Difference. “Reports are things which inform people with data” became my motto – whether they were interactive or not. “And EVERYTHING should be interactive,” was the second half of my motto. It seemed to me that the distinction between Analysis and Reporting was an artifact of historically inadequate software. As a species to that point, we’d built some interactive tools, and some non-interactive ones, and each of them lacked some of the features of the other. It was time to Evolve. We were going to be Modern, and blur the line. “Good looking AND interactive – we shouldn’t have to choose.” And off I went.
(Aside: one of my top 50 memories of working at Microsoft follows. Early in this process, the leader of the SQL Server Reporting Services team told me in a meeting I was not allowed to refer to Excel documents as “reports,” and that only SSRS reports were reports. He instructed me that I could use the words “document,” “spreadsheet,” or “workbook,” but “report” was off limits. I found it amusing that someone thought they could trademark a basic noun, and dedicated myself to a campaign of good-natured rebellion. Since he had no political control over me whatsoever, for YEARS I then made a point to ALWAYS call Excel docs “reports” in his presence, turning each time to slyly grin at him after delivering the irritant).
How Did Excel 2007 Do? (At blurring the line)
It was a noble cause, and we made some progress. PivotTables became MUCH better-looking… but they were still PivotTables. Excel charts got prettier, but hardly set the world on fire. We added Cube Formulas to enable cell-by-cell layout of reports, but cell-by-cell is a far cry from the pixel-perfect capabilities of SSRS.
Ironically, I now give us worse marks on interactivity than aesthetics! Being told over and over that “Excel is interactive analysis” must have taken its toll, because we took interactivity for granted. Yes we added some cool filtering features to PivotTables, but that was basically it. Hell, we didn’t even think to add Slicers! (Those came in 2010).
Analysis in the Shadow of Reporting
Let’s get back to 2002: since Excel was on Team Analysis, I got partnered up with the SQL Server Analysis Services (SSAS) team. The best way to describe SSAS at the time is/was “SSRS’s smarter but younger brother.” SSAS was a super smart product built to tackle enormous and important challenges. Even the circa-2002 multi-dimensional version was one of the wonders of the software world (and today Tabular is even better). A brainy product built for brainy people tackling real-world problems of nearly every flavor.
SSRS is/was a much simpler beast, but it was FAR more successful in the market at the time. Even though it tackled a more modest set of problems, and quietly left MANY business problems unaddressed (more on this later), it outsold SSAS by a multiple. There’s really no mystery as to why: the world is FULL of people who know SQL, and if you give people a way to turn SQL queries into pixel-perfect reports (and distribute them), you’re gonna get a lot of takers.
Reporting’s dominance over Analysis lasted a long time. Even in 2010, when I spoke at a user group dedicated to professionals working in Microsoft BI, almost no one in the audience had any significant experience with SSAS. I was stunned, but it was a powerful lesson: most “BI” professionals were actually “Data Warehousing and Reporting” professionals.
Why this Was a Problem
SSRS was more popular than SSAS because reports are just easier to build than analytical models. SSRS reports basically answer a NARROW set of questions from a SINGLE data source, whereas SSAS models attempt to answer EVERY question, powered by MULTIPLE data sources. The latter is an exponentially more ambitious undertaking, AND it requires you to learn skills beyond SQL, so yeah… I get it. “Path of least resistance” might be the world’s most powerful force.
One little problem though: the world is an “every question from multiple data sources” kind of world. Everyone has/had that problem, and Reporting did nothing for it. Worse, Reporting gave us a false signal that we WERE doing a good job of BI. “What do you mean, you don’t have the data you need to answer your biz questions? What about those jillion reports we built you?”
I’ve written about this problem extensively – here and here for example, but the gist is this: ignoring those unanswered questions doesn’t make them go away. You either manage from your gut instincts, which is basically just guessing, or you answer them in Excel.
So Business Intelligence was still sorely lacking in… Intelligence. A Reporting-dominated world is a sad one – for the Business.
The Part Where I Say Something Nice About Tableau
Gather round children, because this doesn’t happen often: Tableau (and Qlik, and Spotfire, etc.) did us all a huge favor by breaking Reporting’s stranglehold on BI, and for bringing Interactivity to the forefront. We may take it for granted today, but the idea of a report canvas where damn-near EVERY element is interactable… just wasn’t anything near a mainstream idea before the rise of Tableau.
Tableau completely reset the reporting world’s expectations for interactivity – from “minimal” to “ubiquitous.” In the Reporting world, the developer had to go out of their way to enable every single point of interactivity – so we never got much (if any). But in Tableau, interactivity was the DEFAULT. If the Age of Reporting was the Dark Ages, Tableau was the Renaissance.
Interactivity is crucial – because without it, you cannot answer followup questions. A traditional SSRS report might tell you there’s a problem, for instance, but NOT help you decide on a course of action. Sometimes, all you needed to do was look at a different report, but very often, that report didn’t exist, because this particular problem had not been anticipated. So you request a new report. And wait. And wait. And then maybe use it once – or never, because the window for making a decision passes before the report arrived. (I think this is a big reason why every company has hundreds, if not thousands, of “active” reports which haven’t been used in years – they were one-offs to answer a specific one-time question. Interactivity cuts down on this problem dramatically).
Non-interactive Reporting was never going to cut it again – not once the Biz had seen Tableau. Something needed to shake us out of the Reporting-Dominated world, and we got it. For that, we should all be very thankful.
Fast Forward to Today
The illustration above gives you the gist: it’s all about field of vision. The “pinhole” view of traditional reporting was never sufficient to power the needs of the Business. Tableau’s more interactive approach was/is captivating by comparison, precisely because it enhanced end users’ (biz users’) field of vision – WITHOUT requiring additional IT intervention. But alas, Tableau also still relies on flat-table backends – it’s still “just” fundamentally another tool on top of SQL, and therefore only goes so far before you’re back waiting on IT.
If Tableau marked the Renaissance, Power BI is the Industrial Revolution. The multi-touch interactive approach established by Tableau married with the power of SSAS Tabular models under the hood (plus agile ETL in the form of Power Query/M) is the culmination of our multi-decade story. (I’m sure the world of data will continue to advance, but I expect the next breakthrough to be a Black Swan moment, rather than following the lineage of the story here).
Here’s another “slice” at the same diagram, this time a bit more specific to the capabilities of each genre:
I believe this is what progress looks like
Wait, didn’t Tableau just add a data model?
Why yes! Yes they did. And that actually reinforces my point rather than diluting it: you NEED an analytical data model engine under the hood, else you’re still bottlenecked. I’m glad to see Tableau acknowledging this, because it’s fundamentally good for the world. Heck, it even makes OUR messaging easier, since we can now say “yep, Tableau agrees, and they’re playing catchup.”
BTW, I’ve long said that our company (P3) is committed to using the best tools out there, and that if something better comes along, we’ll use it. So we’ll be keeping a close eye on Tableau but… I don’t expect them to be a credible competitor with their data model any time soon. Microsoft has a tremendous lead here – arguably twenty years or more – and we all know how that played out for Google vs. Bing. Plus, I’m skeptical that you can retrofit a data model into such a long-established “single table” product without serious compromises.
Analysis has Usurped Reporting, But…
Analysis (aka Interactivity – plus a true Analytical Model under the hood) has now risen to dominant position, relegating Reporting to the backburner. You can’t be a credible BI competitor these days with just a Reporting tool. You have to bring that sweet Interactive Dashboard love if you want to be considered.
But even though “my team” won, I think the industry is oversteering, and now neglecting Reporting to its own detriment. The SSRS bloodline is valuable and deserves to be part of your toolbox – in parallel with, and integrated with, Power BI. It’s time to talk some detail, and about the Way Forward, and we’ll do that in Part Two coming Thursday (June 11).
You Wouldn’t Use a Skateboard as a Surfboard (or Vice Versa).
We’ll talk about that in part two on Thursday.
Embed paginated reports in your own application for your customers (Preview)
Paginated reports bring a new set of enterprise reporting capabilities to the Power BI service. They enable developers and BI professionals to create and distribute highly formatted, pixel-perfect reports. When leveraged using Power BI, paginated reports become the first cloud BI solution that combines the power of self-service BI, with the needs and capabilities of traditional Enterprise BI scenarios.
One of the most popular scenarios with SQL Server Reporting Services is embedding paginated reports into other applications. Today we are thrilled to announce the public preview of embedding paginated reports in your own application for your customers (also known as the ‘app owns data’ model).
Embedding paginated reports for customers, allows ISVs and developers additional flexibility when embedding intelligence into their applications, using Power BI APIs. ISVs and developers can take advantage of minimal development effort to achieve faster time to market. They can also differentiate themselves by infusing into their applications, Microsoft’s world-class analytics engine which already includes paginated reports on top of it. In addition, developers can spend time focusing on their solution to meet customer demands, instead of developing visual analytics features.
Tutorial: Embed Power BI paginated reports into an application for your customers demonstrates how to integrate a paginated report into an application using the Power BI .NET SDK with the Power BI JavaScript API.
The following set of capabilities supports the scenario of embedding paginated reports in your own application for your customers:
- Row-level security in embedded paginated reports – When you embed a paginated report, you can control which data is displayed. This allows tailoring the displayed information per user and provides a secure way of displaying a subset of the data, in a way that does not compromise the rest of data. It resembles the Row-level security feature, which provides a secure way of displaying data in Power BI reports.
Learn more about row-level security in embedded paginated reports
- URL parameters in embedded paginated reports – This capability, already supported in Power BI service, allows ISVs and developers to control their users experience by appending extra parameters to the generated Embed URL. You can try this by using the Power BI Embedded Playground sample tool, clicking ‘Try it’ on the Sample Paginated Report, manipulating the Embed URL, and clicking ‘Run’.
- Automation of paginated report tasks and operations – The powerful Power BI REST API calls were extended to support important, yet repeatable tasks, related to paginated reports. For example, the deployment workflow shown in the diagram below can be automated by using the following APIs:
Please use the following resources to learn more about the product, get your questions answered, and give us feedback and suggestions:
What are paginated reports in Power BI
Power BI Paginated Report Builder
Embed paginated reports in your application for your customers
Implementing row-level security in embedded paginated reports
Power BI community for developers
Paginated Reports in Power BI September 2019 Feature Summary
Welcome back to another “New Feature Friday” for Paginated Reports in the Power BI service. We’re pleased to announce three new features rolling out around paginated reports this week.
Comments for Paginated Reports
You may now add comments to your paginated reports in Power BI. To add a comment, just click the Comments button in the report toolbar and type your comment in the right-hand pane. Unlike Power BI reports, however, these are supported at the report level only, and not per visual.
Secure Embed support
You may now easily embed your paginated reports in other applications or BI portals using the Secure Embed feature just as you can other Power BI content. This feature includes support for URL parameters, which we announced support for in August, for your scenarios as well. You may choose this option from the File menu in the report toolbar.
RDL Migration Tool on Github
To assist customers with moving reports from SQL Server Reporting Services to Power BI, we’ve released our summer intern project to the community on Github. As part of the migration process, the tool will:
• Convert any shared data sources and datasets in these report files to be embedded in the report and save the files locally to disk.
• Check for unsupported datasources or report components when uploading to Power BI
• Save the converted files that pass these checks to a specified Power BI Workspace.
• Provide a summary of the successful and unsuccessful assets migrated
It is available now, so feel free to check it out on github where you can even contribute to the ongoing development of the tool and help expand its capabilities.
Thanks as always for your feedback and please let us know what you think about these latest updates in the comments below. Look for a blog post in the very near future around the September 2019 release of Power BI Report Server as well!
URL parameters for Paginated Reports are now available
“New Feature Friday” returns as we’re thrilled to introduce support for URL parameters for paginated reports in the Power BI service. This capability allows report authors to send commands to paginated reports in Power BI by adding a parameter to a URL. For example, you can pass report parameters to a report by including them in a paginated report URL, and even construct this URL dynamically in a Power BI report and drillthrough to a paginated report by using a DAX measure.
To use report parameters, you need to prefix the parameter name with rp: for Power BI to recognize it in the URL. The parameter name is the one set by the author when creating the report parameter in Power BI Report Builder.
The URL patterns for paginated reports closely follow those of SQL Server Reporting Services today. For example, to set a report parameter within a URL, use the following syntax:
rp:parameter=value
So if I want to specify a value for the “Salesperson” parameter for my report in an app, I’d use the following URL:
https://app.powerbi.com/groups/me/apps/xxxxxxx-c4c4-4217-afd9-3920a0d1e2b0/rdlreports/b1d5e659-639e-41d0-b733-05d2bca9853c?rp:Salesperson=Tiggee
If “Salesperson” was instead a multi-value parameter that accepted multiple values, I can use an & to add an additional parameter value to my URL like so –
https://app.powerbi.com/groups/me/rdlreports/xxxxxxx-abc7-40f0-b456-febzf9cdda4d?rp:Salesperson=Tie+Bear&rp:Salesperson=Mickey
We’re also rolling out a new feature next week to simplify the discovery of the parameter names by adding an item in the File menu called “Generate Link”. This will generate a link of the current report view with the parameter values appended to the URL.
In addition to report parameters, you may also set the export format of your paginated report directly on the URL. So when you click the report url, it will render and download the report in the selected format without any user interaction required. You just need to use the rdl: prefix when setting the output format.
For example, if I wanted to export my report to PDF when I clicked the URL, it would look like this:
https://app.powerbi.com/groups/me/apps/xxxxxxx-c4c4-4217-afd9-3920a0d1e2b0/rdlreports/b1d5e659-639e-41d0-b733-05d2bca9853c?rp:Salesperson=Tiggee&rdl:format=PDF
Please be sure to review the documentation to see several additional examples of URL parameters.
We’re really excited to see what you do with this feature, so be sure to leave us your feedback in the comments below and let us know what you think.
Paginated Reports August 2019 Feature Update – Report Builder Update, SaaS embedding support and what’s coming soon
As we head into August, we’ve released two feature updates for Paginated Reports in Power BI, with several more on the way.
Power BI Report Builder Update
Later today, we’re dropping a new update for Power BI Report Builder that includes an enhanced experience when you’re connecting to Power BI datasets. Now, when you’re browsing your Power BI workspaces to connect to your Power BI datasets, you’ll see tags to indicate whether or not they’re a certified or promoted dataset. Additionally, we also now show you the connection type the dataset is using – if it’s either an imported dataset, uses Direct Query or a Live Connection to Analysis Services.
Download Power BI Report Builder
SaaS embedding of Paginated Reports now available
One of the most popular scenarios with SQL Server Reporting Services was embedding those reports into other applications. Now, with Paginated Reports in Power BI, support for internal app and portal embedding scenarios is now available. Make sure you check out the full announcement and documentation in the blogpost from the Power BI embedded team.
What’s coming next for Paginated Reports and E-Mail Subscriptions
Here’s a sneak preview of some exciting new features coming in the weeks ahead –
URL parameter support – This highly requested feature will allow you to craft a URL to a paginated report with parameter values directly in the address bar. As these parameters can be set dynamically, it will allow you to setup drill-through scenarios from one report to another, including the ability to pass filter and slicer context from a Power BI report to a Paginated Report. We’ll also have support for setting the render format as part of the parameters, so your URL can automatically re-direct users and download the report output to PDF, Excel, or any of the supported export formats.
Secure Embed support – With the SaaS embed support in place, the ability to easily embed secure Paginated Report content in internal portals or websites will be available soon. It’ll make it easy for non-developers to quickly add Paginated Reports in these scenarios.
Monthly Subscription Support – We’ll have the ability to select a monthly option for e-mail subscriptions for both Paginated Reports and Power BI reports/dashboards. With support for hourly subscriptions coming for Power BI reports and dashboards later this week, soon every content type in Power BI will have the same flexible scheduling options for e-mail subscriptions as Reporting Services users have enjoyed for years.
There’s one additional feature that we’re planning I’ll hold off on talking about for now, but look for more information on it and all the items mentioned in the sneak preview in the coming weeks.
As always, please leave us feedback in the comments below, and thanks for reading!
Announcing support for non-Premium Power BI datasets in Power BI Paginated Report Builder
To kick off July, we’ve dropped the latest update to Power BI Report Builder in the download center. It includes, for the first time, the ability to connect to your Power BI datasets, whether or not they are backed by premium capacity. This means any Power BI Pro user can connect to and author, render and export reports locally against her or his Power BI datasets with Power BI Report Builder.
NOTE: For report authors that have been using the XMLA endpoint to connect to Power BI datasets, that functionality will continue to be available, though we expect most authors will use the new workflow we’re announcing here today.
Download Power BI Report Builder
To start authoring reports against your Power BI dataset, you’ll need to create a new connection to it in your paginated report. You can create a new connection by right-clicking on the Data Sources folder and choosing “Add Power BI Dataset Connection”, or selecting this same option from the “New” drop-down menu in your left-hand toolbar.
Once selected, you’ll need to authenticate to the Power BI service. Then you’ll be presented with a dialog where you can browse all of the workspaces you have access to, along with the Power BI datasets inside each of them.
Please note that you currently won’t see any datasets that are using a Live Connection to Analysis Services. You may connect to those directly using the underlying Analysis Services connection for that instead. We also don’t yet show the tags for certified datasets in the UI, but we’ll have that available later this month in another update.
You can search inside a selected workspace by using the Search box and typing the name of the dataset you’re looking for.
To connect, simply select the name of the Power BI dataset and hit the Select button. You’ll now see a connection under your Data Sources folder with the name of the workspace and dataset you are connected to. Keep in mind that you may connect to multiple Power BI datasets, along with other supported data sources, in the same report. For example, in the picture below I am connected to a Power BI dataset and an “Enter Data” data source.
Once you’ve made your connection(s), you’re ready to start creating your paginated report. If you’ve never created a paginated report before, make sure you read the tutorial in the documentation on how to get started – Tutorial: Create a paginated report and upload it to the Power BI service
Though we don’t yet have support to publish Paginated Reports back to non-premium Power BI workspaces in the service, we are still encouraging all Power BI authors to download and use Power BI Report Builder and take advantage of the capabilities the tool offers locally.
NOTE: As the service side functionality is still rolling out, you may need to wait up to 24 hours before you can publish any reports you’ve created with Power BI datasets back to the service .
We’re excited to see what you think of this latest update, so please make sure to leave your feedback in the comments below. Thanks!
Power BI Paginated Reports June 2019 Feature Update Summary
We’re almost three weeks post-GA of Paginated Reports in Power BI, and we wanted let you know about several updates we’ve made since then –
E-Mail Subscription Enhancements
There are two updates we wanted to highlight in regards to e-mail subscriptions with paginated reports.
Edit Parameters in your subscription
You can now see the parameter values currently set for the selected subscription by expanding the “View parameters” item in the subscription pane
If you want to change these values, you may now update the parameters for your selected subscription with new report parameter values. Simply change the drop down in the subscription pane to “Use current (rendered report) values” to change the parameters to those you’ve just rendered your report with.
Include Preview Image
We’ve also added the ability to view the first page of your Paginated Report in the e-mail body as an image, just as you do with a Power BI report. This is optional, and is always in addition to your report attachment. To add it, simply check the “Also include preview image” checkbox that now appears in the subscription pane.
Admin Portal Updates
We’ve made some updates around administering the paginated reports workload in your capacity. For all newly created capacities, the feature will be enabled by default, but will not consume memory or resources until you or your users publish a paginated report. Also, you may now assign above 50% (up to 100%) of the capacity memory to the Paginated Reports workload if you chose. Simply update the assigned memory in the Capacity settings page in the Admin Portal
Azure SQL database oAuth support
We’re now rolling out to all regions support for oAuth for Azure SQL database data sources. This allows you to enforce RLS for reports that use Azure SQL database as a data source. To enable this for your reports in Power BI, make sure you properly set the provider in the Reports tab in your app workspace.
Auto Refresh for Paginated Reports
As demonstrated the BI Power Hour at MBAS, we’ve enabled the ability for report authors to use the Auto Refresh property that has long been available for paginated reports. To use this feature, open your report in Power BI Report Builder and specify the rate in seconds at which the report page will automatically refresh in the properties pane. When you publish the report to Power BI, the report will update in the browser at that interval automatically when you’re viewing it.
New Power BI Report Builder update coming Monday
I know many folks were looking forward to an announcement around the latest release of Power BI Report Builder, which includes support for non-Premium datasets. We’re going to release that on Monday AM along with a blog post covering everything you need to know, so look for it then.
Thanks and have a great weekend!
Report Parameter Support for Paginated Report E-Mail Subscriptions are Now Available
The Paginated Reports team closes out May with another “New Feature Friday” blog post announcing one of the biggest enhancements to e-mail subscriptions in Power BI to date. With today’s update, Paginated Reports users can set up an e-mail subscription for themselves and their colleagues and choose the parameter values they wish to use for each e-mail subscription they set up! Just as they’ve been able to do in SQL Server Reporting Services for years, customers can select their parameter values for the specific view of the data they want and send it to themselves and others as one of six different attachment formats. Want to set up three subscriptions with different parameter combinations for each group of recipients? Now you can!
To get started, head to one of your paginated reports in Power BI and click on the “Subscribe” button in the toolbar
You’ll see a new drop-down menu in your subscription pane that allows you to select one of two values when you’re creating your subscription if you have selectable parameters in the report. By default, it will use the current parameter values you have selected to view your report. Make sure you select your values and run your report prior to creating your subscription to ensure the correct parameter values are captured. If the report author has setup default parameter values for all of the selectable parameter options, you will have the option to use those instead.
Now, when your e-mail is delivered to its recipients, it will always reflect the parameter value(s) you’d selected to be associated with that particular subscription.
Because this feature was so highly requested amongst our preview customers, we released this as soon as we felt comfortable, but a few additional capabilities aren’t quite there yet. These include the ability to edit the parameters for your subscription and being directed back to the view of the report with parameter values set from the link in your mail. These are both coming in the next couple of weeks, and we’ll update you accordingly when they’re available.
Thanks again for all the great discussions to date about the Paginated Reports features and posts. Please be sure to continue to provide this feedback in the comments below, and have a great weekend!