Part of the the series: Doing Power BI the Right Way (link)
It has been a few weeks since I last posted, and a while since I started the promised blog series “Doing Power BI the Right Way”. I’ve received a lot of positive comments on the posts in the series this far, and people wondering why it’s taking so long. Well, I am busy working on a book about Paginated Reports and SQL Server Reporting Services. This will be the second edition of a book I co-authored ten years ago and this edition is also a collaborative effort with multiple contributors. We have quite a lot of excellent material that is just starting to go through the process of editorial and technical review. Writing a technical book always takes more time and effort than imagined, and working on this one in my “spare time” is a laughable notion but this is going to be a great book.
Writing this blog series while juggling other commitments has brought me to two realizations: 1. Posting articles in the series will be sporadic, and 2. The topics will be in fairly random order based on the projects I’m working on and the topics that are most top-of-mind at the time.
This brings me to the subject of this post: Paginated and Analytic reports.
Before we had Power BI, we had Reporting Services. When the focus of my career and consulting practice became Business Intelligence, starting about fifteen years ago, most of the “BI reports” I created were in SQL Server Reporting Services (SSRS). We could create a variety of charts and reports that allowed users to drill-down from summary to details. We could create reports that allowed a user to drill-through, from one record or report item to another report that presented more details. With a little creativity, we could create reports that allowed users to click items that would filter other items on the same report. It took some work and time to create these interactive “drill-through-to-self” reports. Today, after transforming data into the right format for a tabular data model, this type of interactive functionality just magically happens in Power BI with very little report design effort. But, Power BI is primarily a tool for analyst users to create their own reports and to get answers to business questions rather than for IT developers to create reports that print or export perfectly to a PDF or Excel. Although it is possible to create fairly advanced paginated reports, most bread-and-butter operational reports are really just what I call “lists of stuff”… the results of SQL queries presented as a table or matrix; perhaps with a few groupings and subtotals. Simple business processes and task often require simple reports to help manage them.
Paginated reports, by definition, are reports that allow a lot of data to be presented on multiple pages. Paginated reports can be viewed online, printed or exported to a variety of file formats. Becasue there is so much overlap in the capabilities of SSRS/Paginated Reports and Power BI, the choice between these two report tools is not always clear. I can cite many cases when using Power BI Desktop is clearly the right choice to create an analytic report, and cases where it would make more sense to create an operational report using Paginated Report Builder. I think these are the easier decision points but I would like to explore those cases where we could argue either way. What are the pros and cons of using Power BI to create traditional reports? When would it make more sense to replace a static, paginated report with an interactive report created with Power BI?
The integration of Paginated Reports into the Power BI cloud service continues to march forward at a rapid pace. Just yesterday, Chris Finlan teased a blog post announcing new Power Automate integrations that will allow report to be distributed and exported using flows. Listen up for some big announcements at Microsoft Ignite about Paginated Reports in the Power BI service. Whether you host operational reports on your own report server or in the cloud, Paginated Reports are an important part of most business reporting portfolios.
When do you choose to use Paginated Reports or SSRS rather than Power BI? Do you design reports differently than you would have 5 years ago? Do your users prefer interactive, self-service reports now or do they still want made-to-order static reports?
What are the deciding factors between creating a paginated report or an interactive report?
Do you create paginated reports with a Power BI dataset or SSAS model as the data source or do you only use SQL queries for those reports?
I’m interested in your ideas and questions. Please post them in the comments below this post and then I will extend this post with your input and my thoughts.
Hi Paul, I had some thoughts here: https://blog.greglow.com/2020/02/11/opinion-sql-server-reporting-services-the-reports-of-my-death/
Thank you for sharing this, Greg. I always appreciate your insights, and encourage others to read this. Your point that the implementation of Paginated Reports isn’t quite on par with SSRS. I think we’re moving slowly in that direction.
My big question regarding power bi vs. paginated is does Paginated reports with Power BI Premium allow for real time access that doesn’t need ETL or star schema? For example, if we extract data out of our ERP and put the data in a sql server DB, can Paginated Reports in Premium access that directly (live access) and is that recommended? This would be more for operational type reports. I know live access against an ERP Database is not usually recommended.
Paul, Thanks for the series! I think a related topic is “What if you need both paginated reports and Power BI reports?” Optimally, you could have both types of reports in a single portal. Unfortunately, the current version of Power BI Report Server lacks a lot of Power BI functionality and the Power BI service lacks the ability to have shared Reporting Services data sources and data sets and a few other Reporting Services capabilities. So it appears that you can have 2 out of 3: (1) full featured Power BI, (2) full featured Reporting Services, (3) a single portal for both Power BI and Reporting Services reports. It looks like if all 3 are needed then perhaps you can use SharePoint (or another portal) with links back to reports in the Power BI service and Reporting Services server. Have I missed something?
Good to hear from you, Scott. I think your assessment about having 2 out of 3 is pretty fair right now. I don’t ever expect Power BI Report Server to offer the same capabilities as the service. Microsoft’s strategy is “cloud first” and they are not going to invest as much into the boxed products – and they really want everyone using Power BI Premium. The product leadership have made it pretty clear that only after they have invested most of their resources into supporting cloud services, they will move features on-prem only when it is convenient and close-effective. Reporting Services was borne from on-prem and Power BI was built to run in the cloud. The integrated Paginated Reports story will keep getting better but it will continue to be a slow journey.
Wow! For the last 5 months, I searched the Internet and asked so many so-called experts on this matter before I now finally found this article. Thanks a lot for sharing this information!
I still have a question. It seems like the post-fixes rp: and rdl: are not in use when you publish the report on the on-prem Report Server. Why? This question leads to my next question. I would like to use the postfix rdl:format=EXCELOPENXML. Can you show how this would be integrated into the URL you produced?
Reference doc: https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-url-pass-parameters
Hi Paul –
I’d like thoughts on whether you think a paginated report is the best solution based on the following requirements: 1) the report must contain data in a matrix and utilize conditional formatting rules to highlight exceptions. 2) the report must export to Excel in the same structure/format as it appears in the matrix including any applied filters to the underlying data. 3) the report must feature up to 20 filters for a user to slice by.
Tim, I think it would be very difficult to meet those requirements with a Power BI report, so Yes, I’d use a Paginated report.
Paul, thanks for your comment.
How would you suggest going about requirement #3 in a paginated report? I’m leaning towards dataset filters, but would like to have each of them be dynamic where the user can “select” which filters to apply. It would be beneficial to have similar functionality to a drop down control like a slicer in a Power BI report.
There is also an efficiency and performance trade-off when using dataset vs query parameters. In an extreme example, you could fetch a million rows from the data source with an unfiltered query and show ten of them in your report. However, performance could be better with dataset filters, than when issuing a new query, after the results have been cached once. As a rule, I consider using query parameters first and then look for creative ways to tweak the solution afterward. Dataset filter expressions are also quirky and can be hard to debug.
HTH
For our “operational” needs we use paginated reports. However when we have high-level analytic needs we rely on Power BI visuals. I believe it ultimately depends on your audience.
Hi Paul, Is Report Builder (Paginated Reports) appropriate if you are wanting to include ‘traditional’ looking Financial Reports, like Balance Sheet, P&L & Cash Flow, in Power BI? What are the pro & cons in using Paginated Reports vs Desktop, e.g. report structure/layout, ability for user interaction etc.?
Paginated report with SQL queries are best fit, because dynamic way of parametrize the source data instantly & usage of store procs. whereas we have few challenges in PBI dataset when we need to change or modify the parameter (to pull data & refresh the dataset)
Hi Paul,
Do you think Paginated Reports should be made available in Power BI Pro? (rather than just in Power BI Premium). And do you think is this the main reason why small/medium companies are not implementing Paginated Reports along side Power BI dashboards.