PAWIT.PW

My dedicated space, focusing on Power Apps and other Power Platform stuff 😉

Connect Dataverse data to Microsoft Report Builder (NOT Power BI!)

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

Don’t worry! There is a way to archive that I will show you!


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.

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

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

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.

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

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 nameLogical 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 🙂


Leave a Reply

Your email address will not be published. Required fields are marked *