Dynamics Interactive Dashboards – Workaround to Default to “All” Records
Background on the Issue
I wrote a previous blog singing the praises of interactive dashboards in Dynamics 365. I have always been a fan of interactive dashboards. However, I’ve been frustrated with the limitations of the default Time Frame. For each interactive dashboard, Dynamics prompts you to choose a Filter Date and a default Time Frame.
- The Filter Date can be any of the date fields in the featured Filter Entity.
- The default Time Frame can be any of the Time Frames listed below.
Often, users want the interactive dashboard to open with an overview of all the targeted entity data records. This cannot be easily configured using a combination of the available Filter Dates and default Time Frame selections. Even though the user has the option to select Custom Time Frame, the Custom Time Frame cannot be configured as the default Time Frame. But, I will walk you through the steps to create a workaround to this problem.
What is the Workaround?
The idea is to configure the interactive dashboard using a Filter Date that is always set to “today” and a default Time Frame set to “Today” or “This Quarter”. Both of these Time Frames will work. This combination will result in the interactive dashboard displaying all entity records by default – no need to update the Custom Time Frame every time the dashboard is opened!
To accomplish this, I implemented the following steps in our Dynamics 365 demo environment:
- Create a custom “Date Only” field for your entity called something like Dashboard Filter Date.
- Set up a daily recurring Power Automate Flow, scheduled to run in the very early morning, to update the Dashboard Filter Date with the current date.
- Select the Dashboard Filter Date to be the Filter Date for the interactive dashboard and “This Quarter” (or “Today”) to be the default Time Frame.
The Power Automate Process
I don’t have a lot experience with Power Automate. But, with the help of few blogs, this Power Automate Flow was not difficult to create.
- The first step is to set up the details of the recurrence. For testing purposes, I chose every day at 2:00 PM. When I’m ready to move this to production, I will change the time to 2:00 AM.
- Then, in the “List records” action, you need to identify the entity that you want to target. In this case, it is Orders.
- Next, choose the “Apply to each” control to loop through all of the Order records.
- Lastly, “Update a record” – identify the Order Item ID field – update the Dashboard Filter Date with the utcNow() expression.
On a daily basis, this Flow will update the Dashboard Filter Date to the current date on all the Order records as shown below:
The Interactive Dashboard
As previously stated, my goal was to create an interactive dashboard that displays all of the targeted entity records by default. This can now be achieved by selecting the Dashboard Filter Date and “This Quarter” in the dashboard configuration.
“All” records includes both active and inactive records. In the Power Automate, the “List records” action does not have any filters. As a result, the Dashboard Filter Dates on inactive Order records are updated as well. This is ideal for this dashboard that features the “All Orders” view. There is an Orders by Status Reason chart that the user can use to filter on the active and/or inactive records.
What are the Caveats?
- The Power Automate process will also update the Modified On date. Typically, the Modified On date is not a meaningful date to users, but this should be noted.
- Depending on the targeted entity, an “All” records dashboard may run into issues with record limits. Dashboard charts, and charts in general, are limited to 50,000 records.
- Check your Power Automate plan license to be aware of any cost to your organization to run the recurring Flow.