Ad Hoc Reporting in Dataverse with PowerShell and SQL

I do a lot of work in Dataverse and I see others pulling data out of it. In my experience, many use the oData connector and the API, especially if ports are blocked that allow you to use the TDS endpoint. Some may use the Dataverse connector, I have seen a lot of people not taking advantage of SQL and query folding.

If you are not in a Fabric environment and are a SysAdmin and need custom reporting, it can be a lot of work to create a Power BI report for ad hoc reports. For example, I recently wrote a script to pull all contacts in Dynamics and check whether the emails are still valid via the Graph API. I really did not need a dedicated report in Power BI and this was for a really small audience. I just did not have the energy to make a report in this instance and many other one-offs that happen when you are in a SysAdmin role.

So what did I do? I used SQL and PowerShell (and even a little Power Automate).

SQL

The first tool I use in this is SQL4CDS. It is a fantastic XRMToolbox plugin where you can, like the name implies, query Dataverse using SQL. I use it all the time.

But this is really just a SQL IDE. How do you actually programmatically get the results out?

There is a PowerShell library in GitHub that takes advantage of SQL4CDS and does just that.

After creating a connection (which you can do with Get-DataverseConnection) you can run a SQL query with something as simple as:

Invoke-DataverseSql -connection $connection -sql "SELECT TOP 1 createdon FROM Contact WHERE lastname=@lastname" -parameters @{lastname="Smith"}

You can export the results to a CSV and be on your merry way. But what else can you do?

More PowerShell

Are there better ways to make adhoc reports? I stumbled upon PSWriteHTML . It provides a very simple way to generate high-quality reports and get a ton of features in the prototypical PowerShell one-liner.

Here is a great article on how to generate a tabular report. I generate a report version to a common directory in a SharePoint library and then use Power Automate to message it out.

Power Automate!?

Once the report is saved to the common directory in SharePoint, you can kick off a flow to send an Adaptive Card in Teams to whoever you want. Adaptive cards are pretty cool, but coding the JSON may not be so straightforward. An easy way to get well-styled JSON is to prompt it (I’ve found you usually get a pretty good response).

I used ChatGPT to generate this:

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "type": "AdaptiveCard",
  "version": "1.5",
  "body": [
    {
      "type": "TextBlock",
      "text": "📋 Contacts Report Ready",
      "weight": "Bolder",
      "size": "Large",
      "wrap": true,
      "color": "Accent"
    },
    {
      "type": "TextBlock",
      "text": "The latest **Contacts Report** has been generated and is now ready for your review.",
      "wrap": true,
      "spacing": "Small"
    },
    {
      "type": "TextBlock",
      "text": "Please click the button below to view the report.",
      "wrap": true,
      "spacing": "Small"
    },
    {
      "type": "Container",
      "spacing": "Medium",
      "items": [
        {
          "type": "ActionSet",
          "actions": [
            {
              "type": "Action.OpenUrl",
              "title": "📎 View Report",
              "url": "https://yourreportlink.com/contacts",
              "style": "positive"
            }
          ]
        }
      ]
    }
  ],
  "msteams": {
    "width": "Full"
  }
}

Pasting this into Adaptive Card Designer (free tool to help design Adaptive Cards), I see this:

In the flow, all you have to make sure to do is replace this part of the json with the url of the file created in SharePoint:

"url": "https://yourreportlink.com/contacts"

Users should get this notification in Teams and be able to click a link to a well-styled HTML report. In each of the scripts I have written to do ad hoc reporting from Dataverse, each file is probably around 30 lines of code, give or take. I haven’t written a module yet, but I could see cutting each file down even more (if I ever have time).

Wrapping Up

Ad hoc reporting does not need to be super complicated. If you know SQL and a little PowerShell, you can do a lot. I have used this with Dataverse, but could even see this being used with dba-tools and SQL Server.