Scheduling Reports in Dynamics 365 - Part 2
– 3 MinutesIn the previous post, we created an Azure Function which generates an SSRS report from Dynamics 365. We can now hook that function up to Microsoft Flow as a custom connector, where we can use it as an action in a flow to send the response (i.e. the report file) in an email.
Before we can setup the custom connector, we must create a new App Registration in the Azure Portal. Go back to the Azure Active Directory App Registrations and create a new app. Call it anything you would like and give it a login URL of https://login.windows.net. Once it's created, replace the existing reply URL with https://msmanaged-na.consent.azure-apim.net/redirect. In Required Permissions, grant it delegated access to the Azure Function app registration that was created in the first post. Also, you must grant the Read Directory permission to the Windows Azure Active Directory API. Next, create a new key and save the value somewhere. You will also want to write down the Application ID (i.e. the Client ID).
Now, we will generate the API definition and import it into Flow. To do this, go back to the Function App, click the Platform Features, and API Definition. Change from External URL to Function. Update it to match the definition below (changing the title and host, of course).
swagger: '2.0'
info:
title: bguidingerfunctions.azurewebsites.net
version: 1.0.0
host: bguidingerfunctions.azurewebsites.net
basePath: /
schemes:
- https
paths:
/api/Report:
get:
operationId: /api/Report/get
summary: Render Report
description: Render a report
produces: []
consumes: []
parameters:
- in: query
name: reportId
type: string
required: true
description: Report ID
x-ms-summary: Report ID
- in: query
name: format
type: string
required: true
description: Format (PDF | Excel)
x-ms-summary: Format
enum: [PDF, EXCEL]
responses:
default:
description: Default
schema:
type: string
title: Body
format: binary
Now, click Export to PowerApps + Flow, give the connector a Custom API Name and click OK. With the connector created, head over to Microsoft Flow, and under Settings click Custom Connectors. Click the pencil icon to edit the connector. Flip over to the Security tab and click Edit. Change the authentication type to OAuth 2.0 and set the Identity Provider to Azure Active Directory. Paste in the Client ID and Secret from your connector App Registration. Set the resource URL to the URL of your Function App Service. Click Update Connector.
Finally, click the Test tab and create a new connection and test it out! You may get a 404 initially, but if you give it 30 minutes or so, it should start working. You may also get a 401 - if you do, verify you correctly setup all the permissions on the App Registrations, and try logging out and back in.
Once the connector is tested successfully, you can create a flow with it! Here's an example of a basic one.
Notice that we can use the dynamic content feature to set the Body of the Render Report step as the Attachments Content for the send an email step. Once we save it and run it, we can see the status.
And after a few seconds, we see the email in our inbox!
How cool is that?!? You can now schedule an SSRS report to be emailed out on a recurring basis! And because of how awesome Flow is, you could instead save the reports to a SharePoint folder or an on-premises file share.
Comments
Hi BOB,
I have tried your given steps to hook up Azure function app with Microsoft flow. But it is always show (404) from last two day.
And also need to understand purpose of creating new App registration instead of using Function App Application Id and key in connector.
When I am using Function app directly from URL shared in first post working fine. Hope you can help in this.
You have to create the separate app registration because an application cannot request access to its own resource via its URL. You would have to request the resource by GUID, but in the PowerApps/Flow connector setup you can't do that since the resource field has validation to force a URL input. So the workaround is to register a new app that can request the Azure Function resource URL.
As far as the 404 error goes, I would try deleting the connector and trying again. I did run into that same issue, but it auto-resolved itself after about 30 minutes.
Hi BOB,
I have downloaded the solution from git hub and configured in flow. While running the report i am getting below error in flow. In Flow i used user summery oob report. I didnt pass any parameter. Can you please suggest me.
Something went wrong: Value cannot be null.
Parameter name: SecureConfig.
Hi Bob,
I have tried the same way you suggested on this bolg but for few of the reports which are taking more than 2 minute processing time to get the response, its failing after 4 re-tries. Would please tell me what am I missing. Also tried increasing the Timeout limit in the settings but it didn't helped me at all. Thanks.
Error Details were :
Http request failed: the server did not respond within the timeout limit. Please see logic app limits at https://aka.ms/logic-apps-limits-and-config#http-limits.
I am very much thankful to you for all the knowledge sharing with the community.
Hello bob,
I've been following your guide but I got this error: failed with error AADSTS500011: the resource principal named XXXXXXXX.crm.dynamics.com not found in the tenant named xxx-xxx-xxx-xxxx-xxxx
This appear when I'm testing the custom connector, any idea?
My account is the tenant administrator
I'd appreciate your help
Kind regards,
Juan Camilo
Could anyone help with working solution.
Hi Bob,
I recently configured the Report Renderer and it is working as expected when Multifactor Authentication is disabled for my user.
When I enable it, I'm getting 400 error.
Any idea?
Thanks,
Mohit
Having thos challenge with Post Render Report after MFA was activated in our environment. how do I go around it?
{
"error": {
"code": "0x80040265",
"message": "Something went wrong: The remote server returned an error: (400) Bad Request."
}
}
Thanks
Edwin