I have released a deployable solution based on this post.  You can find it here.

A common request with Dynamics 365 is the ability to email an SSRS report on a recurring basis, which is possible in the on-premises version but not online.  There are several solutions available that let you schedule "reports" in Dynamics 365, but they all have one problem -- they don't actually schedule a report!  They do let you schedule Advanced Find queries, and while that may work if you just want basic tabular data, what do you do if you want to schedule an SSRS report?  Everyone will tell you that it's not possible, but it actually is possible using a combination of Azure Functions and Flow.

Microsoft released Flow about a year and a half ago, and since then they have made some great improvements to it.  It's pretty clear that Flow is the future of workflows in Dynamics 365 which should be even more apparent when you consider that the workflow designer built into D365 hasn't been updated since 2011.  One of the many benefits that Flow has over workflows is that you can easily setup recurring flows.  Although Flow has an out-of-the-box connector for Dynamics 365, it does not currently have the ability to generate a report.  With that in mind, we're going to build a custom connector for Flow which will connect to D365 and generate a report using the built-in report server.

In this first post, we will walk through creating the Azure Function and testing it.  In the second post, we will set the function up as a custom connector in Microsoft Flow and create a flow to use it.

Edit: After I posted this, I discovered that the Azure Function adds unnecessary complexity -- you can instead host the report rendering piece as a plugin/action and call it directly from Flow!  You are, of course, constrained by the two-minute execution limit of plugins, but if you run into that you can still use the Azure Function.

First, let's open up Visual Studio and create a new Azure Functions project.  If you do not see this as a project type, you may need to add the Azure Development workload.  To keep our code clean, we will create a class that will handle the rendering of the report.  The code in this class should be fairly familiar if you've seen any of the code samples online that generate a report using JavaScript.  The main difference is that since we are not in the context of the browser we have to manage the cookies ourselves, otherwise the call to the ReportViewerWebControl will fail.  Fortunately this is very easy by using a CookieContainer -- cookies from the first response will automatically be added to the container and used by subsequent requests.

Here's the code for the ReportRenderer class.

namespace BGuidinger.Samples
{
    using System.Collections.Generic;
    using System.IO;
    using System.Net;
    using System.Text;
    using System.Threading.Tasks;

    public class ReportRenderer
    {
        private CookieContainer _cookies = new CookieContainer();

        private readonly string _baseUrl;
        private readonly string _accessToken;

        public ReportRenderer(string baseUrl, string accessToken)
        {
            _baseUrl = baseUrl;
            _accessToken = accessToken;
        }

        private async Task<(string, string)> GetSession(string reportId)
        {
            var url = "/CRMReports/RSViewer/ReportViewer.aspx";
            var data = new Dictionary<string, string>()
            {
                ["id"] = "{" + reportId + "}",
                ["iscustomreport"] = "true"
            };

            var response = Encoding.UTF8.GetString(await GetResponse(GetRequest("POST", url, data)));

            var sessionId = response.Substring(response.LastIndexOf("ReportSession=") + 14, 24);
            var controlId = response.Substring(response.LastIndexOf("ControlID=") + 10, 32);

            return (sessionId, controlId);
        }

        public async Task<byte[]> Render(string reportId, string format, string filename)
        {
            var (sessionId, controlId) = await GetSession(reportId);

            var url = "/Reserved.ReportViewerWebControl.axd";
            var data = new Dictionary<string, string>()
            {
                ["OpType"] = "Export",
                ["Format"] = format,
                ["ContentDisposition"] = "AlwaysAttachment",
                ["FileName"] = filename,
                ["Culture"] = "1033",
                ["CultureOverrides"] = "True",
                ["UICulture"] = "1033",
                ["UICultureOverrides"] = "True",
                ["ReportSession"] = sessionId,
                ["ControlID"] = controlId
            };

            return await GetResponse(GetRequest("GET", $"{url}?{data.UrlEncode()}"));
        }

        private HttpWebRequest GetRequest(string method, string url, Dictionary<string, string> data = null)
        {
            var request = WebRequest.CreateHttp($"{_baseUrl}{url}");
            request.Method = method;
            request.CookieContainer = _cookies;
            request.Headers.Add("Authorization", $"Bearer {_accessToken}");
            request.AutomaticDecompression = DecompressionMethods.GZip;

            if (data != null)
            {
                var body = Encoding.ASCII.GetBytes(data.UrlEncode());

                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = body.Length;

                using (var stream = request.GetRequestStream())
                {
                    stream.Write(body, 0, body.Length);
                }
            }

            return request;
        }

        private async Task<byte[]> GetResponse(HttpWebRequest request)
        {
            using (var response = await request.GetResponseAsync() as HttpWebResponse)
            {
                using (var stream = response.GetResponseStream())
                using (var stream2 = new MemoryStream())
                {
                    await stream.CopyToAsync(stream2);
                    return stream2.ToArray();
                }
            }
        }
    }
}

This class is a simlpified version of what it would be in a real-world scenario - it hardcodes the language code ID's, does not accept parameters, only works for custom reports, etc.  However, implementing that functionality, if needed, should be fairly easy.

Now that we have the class to render the report, we can call it from our Azure Function.

namespace BGuidinger.Samples
{
    using System.Configuration;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Net.Http.Headers;
    using System.Threading.Tasks;
    using Microsoft.Azure.WebJobs;
    using Microsoft.Azure.WebJobs.Extensions.Http;
    using Microsoft.Azure.WebJobs.Host;

    public static class Report
    {
        [FunctionName("Report")]
        public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Anonymous, "GET")]HttpRequestMessage req, TraceWriter log)
        {
            req.Headers.TryGetValues("X-MS-TOKEN-AAD-ACCESS-TOKEN", out var accessTokens);
            if (accessTokens != null)
            {
                var baseUrl = ConfigurationManager.AppSettings["BaseUrl"];
                var accessToken = accessTokens.FirstOrDefault();

                var queryString = req.GetQueryNameValuePairs();

                var reportId = queryString.FirstOrDefault(q => q.Key == "reportId").Value;
                var format = queryString.FirstOrDefault(q => q.Key == "format").Value;
                var filename = format == "PDF" ? "report.pdf" : "report.xls";
                var mimeType = format == "PDF" ? "application/pdf" : "application/vnd.ms-excel";

                var renderer = new ReportRenderer(baseUrl, accessToken);
                var report = await renderer.Render(reportId, format, filename);

                var response = new HttpResponseMessage(HttpStatusCode.OK);
                response.Content = new ByteArrayContent(report);
                response.Content.Headers.ContentType = new MediaTypeHeaderValue(mimeType);
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentDisposition.FileName = filename;
                return response;
            }
            else
            {
                return req.CreateResponse(HttpStatusCode.BadRequest, "Access token not found.");
            }
        }
    }
}

You may have noticed that we set this function to use Anonymous authentication.  Rather than securing the function by a key in the query string (i.e. AuthorizationLevel.Function), we will instead secure the App Service itself with Azure AD.  The benefit of doing this is that the OAuth access token is actually passed in through the requset headers directly to our Function.  You can find many examples online that use other methods of getting the token, but those methods typically require either storing a username/password or a secret key.  Since we need the token in order to make authenticated calls to the Dynamics 365 API, we can eliminate an unncessary token request.

With the code for our Azure Function created, we can simply use the Publish option in Visual Studio to create a new App Service and publish the code to Azure.  You may need to create a Resource Group/Storage Account if you do not already have one.

After the solution is published, head over to the Azure portal and open the App Service you created.  As mentioned above, we are going to secure the service with Azure AD.  To do this, go to the Platform Features tab and click the Authentication / Authorization link.  Turn on App Service Authentication, change the action from Allow Anonymous to Log in with Azure Active Directory, and click the Azure Active Directory, Not Configured link.  Choose the Management Mode of Express, leave the default options, and click OK.  Then click Save.

Now, close the Authentication / Authorization blade and re-open it (this is necessary to refresh it).  Click on the Azure Active Directory provider again.  You'll notice that we now have an option to configure the permissions for the app.  Since we are connecting to D365, we will add the Dynamics CRM Online API and grant the delegated permission for our app to access Dynamics 365 as organization users.

Even though we granted this application permission to Dynamics 365, we also have to make a modification to allow our service to request access to the resource (i.e. CRM).  Chris Gillum from the Azure App Service team has more details on this.  As Chris mentions, hopefully Microsoft does add this to the UI in the future.  Until then, go to the Platform Features tab, open up the Resource Explorer, expand the config node, and edit the authsettings.  Change the additionalLoginParams from null to ["resource=https://{your_organization}.crm.dynamics.com"], and click Put.  This allows the token generated for the Azure Function to also work with Dynamics 365.

With that, the setup is complete, and all we have to do is test out our function.  Since we set it up as a GET method, we can test it right from the browser.  Access the function via its URL, passing in the reportId and format as query string parameters.  Since this is the first time you are accessing the service, you will be promted to consent to the requested permissions.  You'll notice that this includes Dynamics 365 as we configured.

After you accept the consent, the browser should spin for a few seconds, and eventually you will be prompted to download your report!  I did run into some permission issues when setting this up for the first time.  If you do too, try closing all of your browser windows and starting fresh -- OAuth can be very finicky (although maybe not quite as bad as Kerberos).

In the next post, we will walk though hooking this function up to Microsoft Flow as a custom connector.  This will allow us to use it in a flow to send the file via email on a recurring basis.