Reporting and document generation in Power Platform

For some reason, the part of Power Platform that used to be Dynamics CRM (and was, then, transformed into what’s called “first party” applications) has always been limited in its reporting and document generation capabilities. It’s still the case, it seems, and it’s a strange situation for an enterprise-grade platform. After all, what’s the point of having all that structural data at your disposal when you can not even report on it or, for that matter, issue a detailed invoice to the client?

Although, you might not agree with what you just read, so I’ll try to explain.

Back in the early days, we had Advanced Find and SSRS for reporting, yet we had Mail Merge for document generation.

Of course there was that reporting wizard (which is still there), but, quite frankly,  I don’t think it was ever up for the job.

The problem with all those “technologies” has always been that they were never user-friendly. There is no way a business user would ever want to write an SSRS report. Advanced Find has never been about reporting – it’s mostly a data query tool. Quite frankly, I don’t really remember a lot about Mail Merge, but, since it was deprecated  and replaced with document templates years ago, there is not a lot to talk about anyway.

In short, there was no user-friendly report generation tool which would be natively supported by Dynamics CRM in those early versions. And there was no user-friendly document generation tool either.

Even when using SSRS(and that would require a report developer), it was close to impossible to schedule reports, to send them by email, etc (on-premise was a little different, but does anyone remember there was… oh, wait… there is still is… on-premise version?)

In the recent years, we’ve got cloud version, then Power Platform, we got document templates, we still have SSRS, and we got Power BI.

But, if we take a closer look at all this variety of tools, here is what we’ll find:

• Word Templates have tons of limitations(100 related records max, 1 level of relationships only, 1 root entity, not sorting or filtering on the relationships, etc)
• It is possible to use Word Templates in Power Automate, but they don’t seem to support nested repeaters, yet they are not that well integrated with Model-Driven
• On the reporting side, it is still possible to create SSRS reports. With all the same limitations we always had there – no scheduling/automation, no “self-service” (need a developer). But, most importantly, SSRS authoring tools for Dynamics 365 have been pretty much abandoned by Microsoft – the last update was released in 2017, and, even then, it would only work with Visual Studio 2015 at best (I am writing this in Q3 of 2020). If it’s not the definition of being “abandoned” then what is?
• There is still advanced find, of course. Which is not that much of a reporting tool
• It’s possible to do certain things in Power Automate (by creating an HTML table, for example). But, again, this is not a reporting/templating tool at all

 

I guess this is where I should have mentioned Power BI, and this should have been the end of my post… if not for the licensing.

Power BI does not come with Power Platform or D365 licenses. Which means if we wanted to stick to Power Platform / D365 licenses only, we would have no reporting tool. Which is kind of interesting, since I’d argue that any decent enterprise implementation would need a reporting tool, and, given that SSRS does not look like a well supported option these days, Power BI seems to be the only other option.

However, where Power BI Pro might look relatively inexpensive, it’s not, necessarily, what we need. If we wanted to do the same kind of reporting we cold do with SSRS (so to generate PDF files, for example), we would have to get Power BI paginated reports

Actually, there is a good hint there of why SSRS might never be coming back (I’m not saying it won’t be coming back to Power Platform, but I definitely would not bet on that happening any time soon):

If Power BI Report Builder is sharing the same foundation as SSRS, why even bother to keep supporting both?

However, what it all means is that if we wanted to use Power BI (and, again, it seems there is nothing else that would be available “out of the box” and that would be able to cover reporting / document generation needs in model-driven apps), we would have to

• Get Power BI Premim for the organization (since that would allow the organization to generate pretty decent paginated documents)
• Get Power BI Pro for every user who should be able to create and share those reports

 

A fair question would be: at which point this is all becoming cost-effective and how big the organization should be to benefit from this? It seems it would have to have at least a few hundreds of of users.

Those implementing Power Platform on a smaller scale are still stuck with the same old question: what tool should we use for reporting / document generation in model-driven apps?

Actually, I don’t have a good answer to that – there are some awesome third-party tools such as Xpertdoc, for example. However, the only reason they exist is that Power Platform itself is not offering those capabilities out of the box.

PS. In the next post, I’m going to demonstrate how to create an Azure function that may help with document generation (and that can be utilized from Power Automate), but, even so, that’s not going to answer the question above. This is one of the options I looked at on the project, and it might still be my  backup plan, but I’d very much prefer for those reporting / document generating needs to be covered out of the box.

PPS (Oct 2): that Azure Function post may have to wait a little – had to dig a little more in to the paginated reports