Hello there,
i am currently having an Issue using SQL Server Reporting Services to get the Marketing Insights Data from an Dynamics 365 CE Online (v9.1) Instance.
I am bulding the Report with Reporting Authoring Extension in Visual Studio 2015.
I need to get all Marketing Insights Data (Email clicked, send etc) according to a single Contact in Dynamics 365 due to GDPR policy.
There is a specific API Action Call for this purpose (msdyncrm_LoadInteractionsPublic). I am able to access this Action trough SSRS using a XML Query against the Dynamics API and passing Parameters to it.
Because of Limitation i can only use anonymus Authentification or integrated Security Windows Authenification.
When i want to exeute the Query against the API Action using anonymus Auth i get a 401 Unauthorized Error back.
If i use a LogIn prompt and using my Dev user (which is a Azure Active Directory User) i get the response that my credentials are wrong.
As far as i figured out, i have to use a Azure AD Authentification becuase the Marketing Insights Data are stored somewhere in the Azure Cloud.
I registered an app/the user(?) in the Azure Portal to use Common Data Services to get a Client ID but i dont know how to use it with SSRS.
Is there any possibility to connect SSRS to use Azure AD Authentification?
Or is there another way to get these data?
Thankful for every hint.
best regard
Daniel
Hi Daniel,
Could the solution work for you?
Regards,
Clofly
Hey Daniel GZ
If you are looking to create reports based on interaction data like Email clicked etc. then you will first need to export it out to Azure Blob storage using this capability-
Prepare for analytic reporting with Power BI (Dynamics 365 Marketing) | Microsoft Docs
You can then pull it into your reporting tool of choice. The
The steps above from Clofly are for pulling data from CDS which will contain the individual entity types like the email being sent etc. but might not have all the interaction data (as described here: Glossary of common insights terms (Dynamics 365 Marketing) | Microsoft Docs) If you need to report all all the interaction insights then consider using the blob export functionality and reporting on that.
Thanks
Hi Daniel,
The process to SSRS integration with Web API is a bit complicated: firstly we need to run localhost webform.
In the webform class file, with ActiveDirectory library for AAD authentication to connect to Dynamics 365 to execute msdyncrm_LoadInteractionsPublic API, then convert the response data to XML format.
https://www.codeproject.com/Tips/986958/SSRS-Report-Integration-With-Web-Api
Alternatively, I would suggest calling the API directly in flow and create a csv from response data.
Overview of the flow
Action 1
msdyncrm_LoadInteractionsPublic is an unbound action
Action 2
Parse the Data property of response to JSON with sample JSON payload.
[ { "SendingId": "121764e4-62b1-1b89-ef36-5b779aedb5e9", "ContactId": "bf5c52e1-022b-eb11-a813-000d3aa080ec", "EmailAddressUsed": "clofly@test.com", "EmailDomain": "test.com", "MessageId": "8a68c12e-b027-eb11-a813-000d3aa080ec", "CustomerJourneyId": "8273746d-572d-eb11-a813-000d3aa080ec", "CustomerJourneyIterationId": "5e663f77-4531-4532-b70b-da4473319ce1", "UsageType": "CustomerJourney", "ActivityId": "bf845412-1acc-d44d-422e-364519cfe9d6", "OrganizationId": "26d382eb-326c-47f8-93f4-4ecd22c245a8", "InteractionType": "EmailSent", "Timestamp": "2020-11-23T06:46:48.6080000 00:00" }, { "SendingId": "d2b5f0f6-1c76-6918-be7e-ae01a82d5f89", "ContactId": "bf5c52e1-022b-eb11-a813-000d3aa080ec", "EmailAddressUsed": "clofly@test.com", "EmailDomain": "test.com", "MessageId": "8a68c12e-b027-eb11-a813-000d3aa080ec", "CustomerJourneyId": "57edf92a-5a2d-eb11-a813-000d3aa080ec", "CustomerJourneyIterationId": "6c776ac5-6e38-4817-b805-0d03bc362b1a", "UsageType": "CustomerJourney", "ActivityId": "b77b8f42-4e7f-300b-68f3-c835337bb8b9", "OrganizationId": "26d382eb-326c-47f8-93f4-4ecd22c245a8", "InteractionType": "EmailSent", "Timestamp": "2020-11-23T07:06:06.4000000 00:00" } ]
Action 3:
Create a CSV table from Body of the parsed JSON
Action 4:
Create a csv file from output of action 3 and save it to OneDrive.
Result
The converted file in OneDrive folder:
CSV content:
Regards,
Clofly
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156