Greetings
Hey, blog fam! 🎉 Apologies for the quiet spell. 🙏 Happy New Year 2024! 🥳 2023 was a year of hustle, and I appreciate your support. Here’s to an awesome 2024 together! 🚀
Problem
Microsoft Report Builder is a tool for creating and designing paginated report, It can connect to various data source. Unfortunately, There are no option to connect to Microsoft Dataverse.
Don’t worry! There is a way to archive that I will show you!
Microsoft Report Builder is not the same with Power BI Report Builder!
But How?
We’re all familiar with Dataverse, the built-in database within the Power Platform. Whether you’re a developer, IT staff, or even a super user, creating Dataverse tables is a breeze through the Power Apps Maker Portal.
In essence, Dataverse is backed by Azure SQL Database. Yes!, it’s an Azure SQL Database with a suite of additional tools.
Additionally, Dataverse provides support for the TDS Endpoint (Tabular Data Stream), enabling us to establish connections from software like Microsoft SQL Server Management Studio (SSMS) in read-only mode. It’s worth noting that this feature is automatically enabled in all Dataverse environments.
Surprisingly, while there isn’t any official documentation on this, I stumbled upon the fact that we can connect to the TDS Endpoint using an OLE DB connection. Conveniently, Microsoft Report Builder supports OLE DB connections, making this a valuable discovery. That’s the gist of it!
Let’s start
1. Create App Registration in Microsoft Entra ID (previously Azure AD)
As I already try connect using my Microsoft 365 user login but its not work!
So, it’s turn out that App Registration was the easiest (and may be the best) way to do this
Create any app registration in Microsoft Entra and all you need is just client id and client secret
Tutorial about how to do this: HERE (You don’t need any API permission, just your client id and client secret, that’s it.)
2. Get your Dataverse endpoint URL
Every Dataverse environment have its endpoint URL (Not the environment id)
The easiest way to get this is by just open Power Apps Maker Portal and navigate to environment you want and then go to Advanced Settings
It will open Dynamics 365 page and the URL is your Endpoint URL
3. Grant your app a permission to read data
Now you need to grant a permission to your app so your app can read data from your Dataverse table
Head to Power Platform Admin Center and then go to Settings of your desired environment
Then, go to Application users
Now, Click on New app user and search for your app registration and then click Add
Don’t forget to add Business unit and security roles to your app (Imagine your app is just like one of your users, They need permission to access the data!)
In this case, I will assign role System Administrator to my app so it can read all data.
Then click Save and Create.
Now you’re done here, your app registration now have access to Dataverse table
4. Install Microsoft OLE DB Driver for SQL Server
You need a drive in order to use OLE DB connection. This must be installed on client machine that will have Microsoft Report Builder running.
You can download it from HERE
You also need to install Microsoft Visual C++ Redistributable for Visual Studio 2022 (Both x32 and x64)
Even you’re on x64 system You need to install both x64 and x32
5. Finally, let’s connect to Dataverse!
Now, everything is setup and ready for Microsoft Report Builder to connect to Dataverse data.
Head to Add Data Source
- Connection type – Choose OLE DB
- Connection string – Using this template, you need to replace the red part with your data from previous step
Provider=MSOLEDBSQL19;Data Source=[Your Endpoint URL];Authentication=ActiveDirectoryServicePrincipal;User ID=[Your App Client Id]
;Password=[Your App Client Secret];Use Encryption for Data=Mandatory
You can click Test Connection to check If everything config correctly and Microsoft Report Builder can now access Dataverse endpoint
After Data Source successfully added, Now you can add any Dataverse table you want to Microsoft Report Builder under Datasets
Let’s config your dataset
- Name – any name you want, this will be the name you see in Microsoft Report Builder
- Data source – select Dataverse that we just created
- Query type – Table
- Table name – Logical name of your Dataverse table
All done! Now you can use Dataverse data in your report!
Conclusion
Even the Dataverse is not listed in supported Data Source of Microsoft Report Builder, This still possible with a help of OLE DB with a little bit configuration 🙂
