Article summary
Not too long ago, due to government funding issues, I was working on a project whose timeline had to be revisited. And part of the features we were going to get to before President Trump’s administration pulled funding was in-app reporting for the clients: a place where they could generate and export stats on specific data pools and share those reports across the company. Nice idea, right? Reporting tools like this are probably something we can imagine a lot of clients asking for.
So, how’d we tackle hitting this goal with the newly adjusted timeline? We looked at what out-of-the-box reporting tools we could leverage. Specifically, this client is rather intertwined with Microsoft products, so we started there.
Funny thing is, as big as Microsoft is, they sure could use some work on their product naming conventions. Their world of reporting tools mainly falls under “Power BI” (PBI). But within that overloaded name, there are a handful of options and decisions that were initially a mess to navigate through. Specifically:
PBI Desktop vs. PBI Report Builder vs. PBI Report Server vs. PBI Service
So let one project dev help another; here’s the straightforward breakdown of Microsoft’s reporting tool options that I wish I’d had from the start:
Report Creation Options
Report generation, whether there are a plethora of variants or not, means that someone has to lay out the report templates. But what type of reports are we looking to create? Interactive reports that can be drilled down and explored, or paginated reports that can be exported with regulated formatting? Well, that last question will be the guide to navigating between these two options…
1. PowerBI Report Builder (saved as .rdl files)
If you’ve heard of SSRS, PowerBI Report Builder is a rather parallel experience. This Microsoft desktop app, while more outdated than PBI Desktop, is great at “pixel-perfect” formatting. So if you’re looking to generate reports that:
- Can be exported to Excel with formatting, tabs, and graphs respected
- Are generated with set parameters when run
Report Builder might just be the answer.
2. PowerBI Desktop (saved as .pbix files)
Now, PowerBI Desktop, on the other hand, is a more dynamic and dashboard-friendly option. To be clear, you can still create tables and graphs, and formatted reports. But the catch we hit is that exporting to Excel means bringing the raw data of the visual (ex. table) over. Desktop reports do transfer to quality PDFS. But if you want more sharing options with better formatting respect, the only Desktop solution comes up in “sharing options” below. So if you’re looking to create reports that:
- Are reactive to users (ie, filtering, clicking into data to look at deeper stats, visuals that dynamically react to each other)
- Can be sustainable and highly compatible with Microsoft’s newest work
PowerBI Desktop is an exciting option.
Sharing Report Options
Report templates have been created; it’s time to get those files where they need to be! So, how could we go about generating reports? In our case, bulk report generation was a great need, while PII data security was a big concern. Once the reports are made, it’s time to figure out how we’re setting parameters, running reports, and sharing said files. Plenty of steps that beg for automation! Luckily, Microsoft offers scheduling bulk report generation (“subscriptions”), email sending, and pdf saving for both of the following options. But with a variety of concerns in hand, you’ll find the two possible paths hold alternating pros and cons.
1. PowerBI Report Server
Report Server, while again less frequently maintained by Microsoft, is an on-prem way to host both types of reports (.pbix and .rdl). Its experience is somewhat like a shared directory that one can navigate through to get to a report template, set any parameters, and generate a report. The main features of note include:
- The data security appeal of on-prem hosting
- Having an option to save bulk-generated reports to Windows directories
2. PowerBI Service
Now this is the one Google wants to tell you the most about. Again, be diligent when given any feature details about “Power BI”. For a while, I thought Desktop had things that it only truly has when the .pbix file is shared to the PBI Service. So how does the Service compare to Report Server? Well, a quick search will sell you on many things, but for the sake of clarity, I’ll note the following features:
- Cloud-hosted sharing site
- More options to export a .pbix file’s table data with formatting intact
- Having an option to save bulk-generated reports to OneDrive or SharePoint
- It’s greatly upkept with improvements pushed ~12 a year (vs Report Server’s ~3x annually)
- Users, with licenses (or not if a high-enough license is purchased), can interact with the Desktop reports, add visuals, ask questions to drill into data, all without changing the shared report template
This sharing option is a very exciting and robust one at that. I’m not opposed to Microsoft’s passions. But I will say, after learning the ins and outs of the sharing and report-building Power BI options, PBI Service wasn’t the shoe that fit. Our client’s use-case was just another reason why it’s important to understand the entirety of tools and their give-and-takes when considering.
And that’s that!
Why Microsoft has documentation that’s a muddled lake to swim through, I’m not sure. Probably so they can push customers to the newest shiniest thing they’re working on, but I digress. One would hope that reading through documentation, or even simple Google searches, wouldn’t result in consistently conflated answers. Nonetheless, more often than not, feature details would be given in sentences starting with “Power BI has”. Which Power BI, you ask?.. That was the journey I went on, and now, hopefully, you won’t have to.