Can Paginated Reports ease Dynamics 365 FO report development?

7. February 2023 Leave a comment

Dynamics 365 Finance and Supply Chain Management uses SQL Server Reporting Services for reporting and document generation. In 2022 Microsoft included SSRS (aka. Paginated Reports) in PowerBI Pro license. One may ask if it is possible to use PowerBI Paginated Reports as well for reporting purpose in Dynamics 365 F/SCM.

X++ SSRS	PowerBI Paginated Reports
+ Direct access to transaction DB + Business logic in X++ + Integrated with Dynamics 365 FO – Complex development – Embedded in Dynamics 365 FO deployment cycle	+ Simple development using Report Builder + Integrated with Business Intelligence + Reuse existing data models – Limited development possibilities – No direct data access

Pros and Cons for Paginated Reports

SSRS vs. Paginated Report — Side-to-side comparison SSRS X++ report vs. PowerBI Paginated Report

Data Access

Paginated reports in PowerBI have only a limited capability to access data. There is no Dynamics 365 FO connector or OData access to entities. You could use different approaches to get Dynamics 365 FO data.

Entity Export. Configure the periodic export of data using enties into an Azure SQL database.It’s easy to access a SQL database and execute queries. Entity export is batched and not real-time and you may need to extend Dynamics 365 FO entities to get the required data.
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/export-entities-to-your-own-database
Datalake. Dynamics 365 FO can export data in near-time to a datalake storage but the report builder cannot access it directly. You could use a tool to transform the flat files from datalake back into a relational model.
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/azure-data-lake-ga-version-overview
Dataverse. Dynamics 365 FO can be integrated with Dataverse (~ “Power Platform database”) which is used by Dynamics 365 customer engagement apps like sales using Dual Write. I’d not recommend to setup Dual Write for reporting purpose, but if you have a working Dual Write setup this may be an option.
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/dual-write/lcs-setup
PowerBI dataset. The report builder can access PowerBI datasets. If you don’t have real time requirements for the paginated reports, this may be the preferred approach. Use the ETL functionality in Power Query to prepare the dataset or use an existing one.

Development

Paginated reports are designed by using the Report Builder. It is an adapted version of the SSRS Report Builder with some more data access features. In theory you could develop an SSRS report using Visual Studio and upload the .RDL file. However, PowerBI Paginated Reports are a limited version of SSRS and you might get errors.

Paginated Reports - Report Builder for PowerBI

Fazit

Paginated Reports are a reduced SSRS feature in PowerBI. The Report Builder is very limited. You can define data access, defined parameters and place data on the report sheet. If you want to create printable documents without real-time requirement the Paginated Reports may be an option for you.