By: Ken Adams
Delivering Business Intelligence Content to Users
How to best deliver business intelligence content to users in some form other than a URL or application is a recurring question for BI professionals.
Key2 Consulting has years of experience developing and delivering content in SQL Server Reporting Services (SSRS). In the past, paginated-style reports lend themselves well to delivering content via a PDF or Excel file.
Formatting the report was a challenge at times but subscriptions could be set up easily to have those files sent via email. We did this for years with success but paginated reports waned and interactive reports came in vogue.
In this post, I’d like to talk about delivering Microsoft Power BI reports via email.
On a recent engagement, we encountered a requirement to push out Power BI reports to end users. These users were operations folks who preferred to have PDFs delivered to their email.
This had been supported for years and in some cases was necessary due to poor internet coverage. These users were also constrained by the number of Power BI licenses they had to assign to users, so report distribution was needed.
Here were the specific requirements:
- Email a copy of report pages as a PDF to specified users
- Set up different schedules for different reports
- Pass in parameters (week-to-date, month-to-date, etc.)
- …and of course in a cost-effective manner
We spent some time researching our options and came up with what we determined was the best fit. I’ll talk a bit about what we felt were the best options along with the implementation path and cost concerns. Keep in mind your mileage may vary based on the client.
1. SSRS as a Separate Service
SSRS has performed well in this space for years and allows for easy control of export and print – the terminology used is “pixel-perfect”.
This could have worked well as the client had SQL Server licenses and hardware available. The plan would have been to set up the report server on-premise, develop reports in SQL Server Data Tools, and then deploy those reports to the SSRS report server.
At that point, setting up subscriptions would have been trivial. It would have checked all the boxes noted above but was a step back in terms of where the client was going. They were moving away from paginated reports. It would have been cost-effective in this case as the hardware existed and licenses were in place to support the work.
2. Paginated Reports Using Power BI Report Builder
You can now create paginated reports in Power BI and have those hosted in the Power BI service. In reality, you are creating SSRS .rdl files in Power BI Report Builder and publishing those to the service. This would have allowed the report to live side-by-side with the interactive reports in the workspace.
Paginated reports is a premium feature and would have required a move to a premium tier for this client. At that time, they had a block of pro licenses and preferred to stay with the model of delving out pro licenses to specific users. So this option was not cost-effective and thus a non-starter.
This option also required developing a paginated view of certain reports which seemed like a step back and not a step forward. The associated cost would have been around $5000 to move to premium at minimum.
The options above deal with developing different reports in a paginated format and distributing them as a PDF. The next two options do not require developing separate reports but rather taking a screenshot of an existing Power BI report and distributing them as PDFs.
3. Export as PDF using Power Automate
This option would have kept the report in Power BI. A screenshot of the report would have been taken and delivered as a PDF using Microsoft Power Automate. Power Automate allows you to automate business processes with a no-to-low code platform.
For our purposes, we would have used the Power BI export API to screenshot the reports and deliver them in the necessary format. The real issue here was our own unfamiliarity as well as the cost.
It would have required premium capacity as well as some cost for Power Automate. The Power Automate cost would have required further investigation.
4. PowerBI Robots
PowerBI Robots is a third-party tool that allows for scheduling screenshots and delivery of Power BI reports and dashboards.
In our case, the reports would be kept in the Power BI format. It came into focus for us as a sister company in Europe was already using the product. The interface for PowerBI Robots is an online GUI along with an on-premise service that is required to use the product.
The on-premise component communicates with the online component to download existing reports locally and distribute those accordingly. It does not come without a cost, although compared with the others was pretty reasonable. The cost annually is around $1100.
I’m sure you can tell that we ultimately decided to use PowerBI Robots. The client purchased the product and within a few days of purchasing, we were able to establish subscriptions (“playlists” in the PowerBI Robots lexicon) for several different reports.
The sending of the reports was easily set to the desired cadence and slicer selection allowed for the presentation of different views. At this point, I’d certainly recommend PowerBI Robots to future clients.
What are Your Experiences?
Have you struggled in finding a way to distribute reports via email? What solution did you ultimately choose? Were you frustrated by the lack of “out of the box” Power BI functionality?
We’d love to discuss this with you and even share our experiences. In a future post, I’ll try to go into more detail about our implementation of PowerBI Robots.
Additional Links
- https://powerbi.microsoft.com/en-us/blog/power-automate-actions-for-exporting-power-bi-and-paginated-reports-now-available/
- https://wordpress.devscope.net/2018/03/22/powerbi-robots-101-everything-you-need-to-get-started/
Questions?
Thanks for reading! We hope you found this blog post useful. Feel free to let us know if you have any questions about this article by simply leaving a comment below. We will reply as quickly as we can.
Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!
