In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
Customer engagement. It's a simple pairing of words that can mean so much to so many organizations, both large and small. When it comes to 1:1 engagement and gaining real insights from customers, there are a lot of great tools out there. However, few are as value-adding and easy to use as PowerObjects' PowerPack Add-Ons, specifically PowerSurveyPlus.
If you're unfamiliar, PowerSurveyPlus is a Dynamics 365 add-on that allows users to very quickly and very easily build and distribute polished, robust surveys that pull response data right into Dynamics 365. This allows for insights at a customer level, as well as the ability to report on broader data sets that highlight trends, ratings, verbose information, and more.
Because of the data structure in Dynamics 365 with respect to parent/child (or 1:N) record relationships (especially if there's a more robust data hierarchy at hand), sometimes visual reporting and analytics requires a little more by way of configuration or maybe even custom reports that tools like SSRS can provide. That said, with the advent of Excel Templates in Dynamics 365, reporting on slightly more complex data has never been as easy or as elegant. As a unique use case, we'll take a look at engagement with internal customers; that is, using PowerSurveyPlus to surface outstanding employees for an organization (as opposed to direct consumers/external customers).
Assuming we have our PowerSurveyPlus solution installed, registered, and configured in Dynamics 365, our first step will be to set up the survey basics. Things like survey name, link text (the text that shows up instead of the URL in distributed emails), related campaign, and more administrative pieces should be set from the get-go:
Once complete, we can jump into designing the survey:
For more detailed, step-by-step information on how to design surveys, jump to our PowerSurveyPlus user guide.
For the substance of our survey, we're asking four straightforward questions with standardized, uniform responses as radio button types. This uniformity is important for how we'll query and report on that data within an Excel Template, as we can aggregate the uniform data in like groups.
Once we have our survey built and distributed, we'll start to see responses roll into Dynamics 365 as PO Survey Responses. We can build dashboards or lean on the out-of-the-box report wizard to generate what we need, but there are several reasons users may want to go down the Excel Templates route: non-Dynamics 365 users who need to work with/digest the data easily, advanced analytics and slicing/dicing capabilities, enhanced chart customizations and customizability, and team-based collaboration or data work that needs to exist outside of Dynamics 365, to name a few.
For our purposes, we'll want to generate an Excel Template for PO Survey Responses record type. As described here, you can generate Excel Templates in two ways.
Once a template is exported, we can start setting up proper visuals. In this example, we'll work with two primary features:
Note: If you've never worked with Excel PivotTables and PivotCharts before, it will be helpful to do a little digging before moving through the following steps.
For working specifically with PowerSurveyPlus data in this use case, we'll focus on two primary fields and one function:
The data was moved to a separate, standalone tab. Once moved, we can select the whole table, and insert any type of PivotChart we'd like- a bar graph is used here. After a little bit of background work, the setup will mirror something similar to this:
Note: the template we just created can be used against any data set for that respective entity in Dynamics 365. That, and it's a best practice to make sure the data refreshes automatically upon creating future exports from this template, so you'll want to make sure to select 'Refresh data when opening the file' under the Data tab in the PivotChart options (accessible by right-clicking the chart).
Upload your template back into Dynamics 365 and you'll be able to use it to report on PO Survey Response data!
We'll want to isolate the data to just the responses from our survey, not any others that will muddy the reporting waters. To do this, Advanced Find comes in handy to query against related entities and drill down into exactly the data set we need.
To properly silo the data we need, we can work between three layers of parent/child data: the survey record itself, the distributed survey activities (the 'instance' of the survey), and the associated survey responses. What's nice about PO Survey Response records (the records we need to analyze) is that while they're further down the data hierarchy, they carry a direct N:1 relationship against the top-level survey record.
Because of that, we can set up our query very easily, though there are other ways to find the same data using other relationships:
Once we have our data set, we can run our Excel template from that list view and see our Employee of the Month results. Here's the data and from where to run the Excel Template:
…and here's the template in action:
Note: Based on the results, it seems like the leader of Queen Consolidated might have had some help in winning this thing…maybe another round of surveying is in order to find the true victor.
To learn more about the awesome features that our PowerPack add-ons offer, check out these recent blog posts: