How We Built a Self-Running Daily CRM Pipeline Report and How You Can Rebuild It
As a professional services organization, our ability to plan, staff, and deliver client work depends on having a clear view of what's coming next. At Stoneridge Software, that starts with the sales pipeline. It's the heartbeat of the business and directly shapes how we prepare for future projects.
Our sales pipeline lives in Dynamics 365 Customer Engagement but turning it into a clean daily report used to mean someone logging in, running the same queries, and exporting by hand.
I worked with Copilot Cowork to automate the daily reporting process so we could eliminate the manual pulls, reduce inconsistencies, and make sure the same snapshot is available every day without someone having to think about it.
This post walks through exactly how it's built
The architecture behind the automated report is deliberately simple: a scheduled Power Automate cloud flow queries Dynamics 365 once a day, splits the pipeline into Open / Won / Lost, and writes each slice as a timestamped JSON file into a SharePoint folder. That folder becomes a daily archive we can point any reporting tool at.
The flow itself is straightforward — the real work is getting the CRM views and queries right. That's where I'll spend most of this post.
Part 1) The Real Work: Build the Right Opportunity Views in Dynamics 365
Before you automate anything, you have to be precise about what you're pulling. In Dynamics, "the pipeline" isn't one thing, it's three different slices of the opportunity table, each defined by a state.
Step 1: Define What Data You Actually Need
The most important step in this process is determining what exactly you want to pull and limiting the data to only what’s necessary. For this report, I wanted to have a daily tally of 3 different data sets:
- Open Opportunities: for this I wanted to pull all the detail about deals that we expect to close in the next 6 months, so I have a filter set to pull deals where close date is 6 months from today or less
- Won Opportunities: I want any deal we’ve won in the past 35 days because I want to be able to compare wins between months in the future
- Lost Opportunities: I want to pull any deal we’ve lost in the last year so I can pull information about the reason we lost certain deals
This is the single most important decision in the whole build making sure you are pulling in enough data to give you what you want, without pulling too much data. Any extra data is more that Cowork has to page through and it could create confusion, so you want to keep it as tight as possible.
Step 2: Build and validate each view in the UI first
Don't hand-write query logic blind. Build each view in the CRM using the “Edit Filter” and “Edit Columns” links on the top of the CRM grid view:
- Open the Opportunities Entity → create three views: Daily Report – Open, Daily Report – Won, Daily Report – Lost.
- Set the filter to the matching statecode (Open, Won, Lost) for each.
- Add the columns you actually want in the report (more on that in step 3).
- Sort by estimated/actual value descending so the biggest deals surface first.
- Eyeball the results. Do the row counts and totals match what leadership sees in the CRM dashboards? If not, fix the filter now not after it's automated.
Step 3: Deliberately Choose the Columns for the Report
The columns are the difference between a report people use and one they ignore.
Here’s what I chose for the report:
- Open: name, estimatedvalue, estimatedclosedate, salesstage / stepname, ownerid, parent account name, createdon
- Won: name, actualvalue, actualclosedate, ownerid, parent account name
- Lost: name, estimatedvalue, actualclosedate, statuscode (the lost reason), ownerid, parent account name
There are a few deliberate calls here. We pull the parent account via a link to the account table rather than the polymorphic customerid field (which can be an account or a contact), and we keep statuscode on the Lost report because the reason a deal died is the whole point of tracking losses.
Step 4: Extract the FetchXML from Each View
Once a view returns exactly the right rows, grab its FetchXML either by downloading it from the view's Edit columns → Download FetchXML option, or by authoring it directly. This FetchXML is what you'll paste into Power Automate. You will need to have System Customizer access in your CRM system to be able to see the Download FetchXML link.
Step 5: Know the Common Issues Before They Bite You
A handful of things will trip up a first build:
- The 5,000-row cap: A single FetchXML call returns 5,000 rows max. If any bucket exceeds that, enable pagination in the List Rows action (or add count/paging-cookie handling). Open pipeline is the one most likely to blow past it.
- Dates are UTC: createdon and close dates serialize in UTC. Decide up front whether your report should localize them. This is a small detail, but a big source of the "Why is this dated yesterday?" confusion.
Part 2) Automate the Daily Report with Power Automate
With the three queries validated, the flow is the easy part:
- Recurrence trigger: runs daily at a fixed time
- we picked early morning so the snapshot reflects end-of-prior-day).
- Initialize a date variable : capture utcNow() once and format it
- e.g. formatDateTime(utcNow(), 'yyyy-MM-dd_HHmm')).
- Reusing one timestamp keeps all three files consistent.
- List rows (Dataverse) ×3 : one action per bucket, each pasting in the FetchXML from Part 1.
- Create file (SharePoint) ×3 : write each result set as JSON, naming the file with the date variable.
- e.g. Opportunities_Open_2026-06-…600.json.
That's the whole engine. No custom code, no server.
Part 3) Build the Daily Report in Copilot Cowork
Because every run is timestamped and nothing overwrites the prior day, the SharePoint folder becomes a chronological record of the pipeline. That history is what makes day-over-day diffs possible, which deals moved to Won overnight, what slipped, how open pipeline trended across the quarter. A live dashboard can't tell you that; a daily archive can.
Writing the Query That Powers the Daily Report
I created the query in Cowork to look at that SharePoint site to read in those JSON files.
Here’s the query I asked Cowork to execute on:
“I'd like to build a set of reports based off data I am downloading every day from our opportunities list in our CRM. I'd like you to create a daily report which shows the following information: Wins (from the won opportunity file), Losses (from the lost opportunity file), overall change in pipeline opportunities, weighted and total opportunity amounts (from the Open Opportunities file) and then a review of any changes to ongoing opportunities using green and red to show any deals that have moved up, pushed out or where the opportunity amount has gone up or down. The files are located in a Teams channel - <insert your location here> under the Cowork files folder. Let's run this update every day at 7:45 CT each morning and send the update via email to <insert your recipients>. I'll add some more weekly and monthly requests after we complete the daily report.”
Cowork does the rest from here it gave me the report with formatting better than I would’ve expected:
It goes into the “Scheduled” area of Cowork so if you ever make changes to it, you will need to restart it as Cowork now stops any process once you change it. I’ve had to add a few recipients over time, but I haven’t had to make meaningful changes to the process. It’s been working great!
Putting It All Together
What makes this process work isn’t the automation itself, it’s having the right data defined upfront. Once the views are built correctly and the logic is dialed in, the rest becomes repeatable.
A simple flow and a reliable daily snapshot of your pipeline removes the need for manual reporting and gives you a reliable way to track changes over time.
If you're working through a similar challenge or running into issues building out your own reporting process in Dynamics 365, Stoneridge Software is always here to help.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.