Lately, I’ve been exploring “not-so-new” requirements where I want to download SSRS reports from Dataverse and use them elsewhere (if you want to use the low-code method, you also can read Nick Hance’s blog post that implements the same thing. The difference here is I’m using pro-code instead and I’ll try my best to explain the off parts of this implementation). Without further ado, let’s learn how to implement it! 😁
The “Not-Good” Parts
Before we begin, I need to address the things that little bit off. During the implementation, I realized that when we were accessing “/crmreports/rsviewer/reportviewer.aspx” using Application User, the page would eventually return an “Error” page:
Error Page when accessing the reportviewer.aspx using Application User’s Auth Token
Because of this reason, we need to create a user without MFA (Multi-Factor Authentication) > assign the license to this user, and set the security role. With this approach, you know that it will lead to:
- Security issue, but we need to use this method as we will get the access token via the backend.
- All the reports generated will use this special User permission. So, if your requirement needs to be matched with the invoke user, this solution is not for you.
Environment Variable
If you already have the user. Next, we need to create an Environment Variable with the below value:
{
"grant_type": "password",
"client_id": "51f81489-12ee-4a9e-aaae-a2591f45987d",
"scope": "openid offline_access profile https://yourcrmorg.crm.dynamics.com//user_impersonation",
"username": "your_user_email",
"password": "your_password",
"tenant_id": "your_tenant_id",
"client_info": "1",
"base_url": "https://yourcrmorg.crm.dynamics.com/"
}On “base_url“, the value needs to be the same. If not, you will encounter an error later.
Then, here is the Environment Variable I created:
ReportConnectionString Environment Variable
Custom API
Here is the code for the Custom API:
using BlogPackage;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
public class DownloadReportApi : PluginBase
{
public readonly string ReportNameParameter = "ReportName";
public readonly string ReportXmlParameter = "ReportXml";
public readonly string ResultParameter = "Result";
public DownloadReportApi() : base(typeof(DownloadReportApi))
{
}
protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
{
var reportName = localPluginContext.PluginExecutionContext.InputParameterOrDefault<string>(ReportNameParameter);
var reportXml = localPluginContext.PluginExecutionContext.InputParameterOrDefault<string>(ReportXmlParameter);
var reportConnectionStringText = localPluginContext.AdminService.GetEnvironmentVariableValue<string>("tmy_ReportConnectionString");
var reportConnection = System.Text.Json.JsonSerializer.Deserialize<ReportConnectionString>(reportConnectionStringText);
var crmReport = GetCrmReport(localPluginContext.AdminService, reportName) ?? throw new InvalidPluginExecutionException($"Report '{reportName}' not found.");
var crmToken = GetAccessToken(reportConnection);
using (var httpClient = new HttpClient { BaseAddress = new Uri(reportConnection.base_url) })
{
httpClient.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", crmToken);
var collection = new List<KeyValuePair<string, string>>
{
new KeyValuePair<string, string>("id", crmReport.Id.ToString()),
new KeyValuePair<string, string>("iscustomreport", crmReport.GetAttributeValue<bool>("iscustomreport").ToString()),
new KeyValuePair<string, string>("reportnameonsrs", crmReport.GetAttributeValue<string>("reportnameonsrs")),
new KeyValuePair<string, string>("reporttypecode", crmReport.GetAttributeValue<OptionSetValue>("reporttypecode")?.Value.ToString()),
new KeyValuePair<string, string>("CRM_Filter", reportXml)
};
var content = new FormUrlEncodedContent(collection);
var response = httpClient.PostAsync("/crmreports/rsviewer/reportviewer.aspx", content).Result;
response.EnsureSuccessStatusCode();
var result = response.Content.ReadAsStringAsync().Result;
var downloadUrl = GetStringBetween(result, "\"PdfDownloadUrl\":\"", ",\"PdfPreviewUrl\":\"").Replace("\\u0026", "&");
var pdfResponse = httpClient.GetAsync(downloadUrl).Result;
pdfResponse.EnsureSuccessStatusCode();
var pdfByteArray = pdfResponse.Content.ReadAsByteArrayAsync().Result;
localPluginContext.PluginExecutionContext.OutputParameters[ResultParameter] = Convert.ToBase64String(pdfByteArray);
}
}
public string GetStringBetween(string source, string startString, string endString)
{
int start, end;
if (!string.IsNullOrEmpty(source) && !string.IsNullOrEmpty(startString) && !string.IsNullOrEmpty(endString))
{
start = source.IndexOf(startString, StringComparison.Ordinal);
if (start != -1) // Start string found
{
start += startString.Length;
end = source.IndexOf(endString, start, StringComparison.Ordinal);
if (end != -1) // End string found
{
return source.Substring(start, end - start);
}
}
}
return string.Empty; // Not found
}
private string GetAccessToken(ReportConnectionString connectionString)
{
using (var httpClient = new HttpClient { BaseAddress = new Uri("https://login.microsoftonline.com/") })
{
var collection = new List<KeyValuePair<string, string>>
{
new KeyValuePair<string, string>("grant_type", connectionString.grant_type),
new KeyValuePair<string, string>("client_id", connectionString.client_id),
new KeyValuePair<string, string>("scope", connectionString.scope),
new KeyValuePair<string, string>("username", connectionString.username),
new KeyValuePair<string, string>("password", connectionString.password),
new KeyValuePair<string, string>("client_info", connectionString.client_info)
};
var content = new FormUrlEncodedContent(collection);
var response = httpClient.PostAsync($"{connectionString.tenant_id}/oauth2/v2.0/token", content).Result;
response.EnsureSuccessStatusCode();
var result = System.Text.Json.JsonSerializer.Deserialize<AccessToken>(response.Content.ReadAsStringAsync().Result);
return result.access_token;
}
}
private Entity GetCrmReport(IOrganizationService service, string reportName)
{
var query = new QueryExpression("report")
{
ColumnSet = new ColumnSet("reportid", "iscustomreport", "reportnameonsrs", "reporttypecode"),
TopCount = 1,
NoLock = true
};
query.Criteria.AddCondition("name", ConditionOperator.Equal, reportName);
var result = service.RetrieveMultiple(query);
return result.Entities.FirstOrDefault();
}
public class AccessToken
{
public string access_token { get; set; }
}
public class ReportConnectionString
{
public string grant_type { get; set; }
public string client_id { get; set; }
public string base_url { get; set; }
public string scope { get; set; }
public string username { get; set; }
public string password { get; set; }
public string tenant_id { get; set; }
public string client_info { get; set; }
}
}
public static class OrganizationServiceExtensions
{
public static TData GetEnvironmentVariableValue<TData>(this IOrganizationService service, string environmentVariableName)
{
var query = new QueryExpression("environmentvariabledefinition")
{
ColumnSet = new ColumnSet("defaultvalue", "schemaname"),
TopCount = 1
};
query.Criteria.AddCondition("schemaname", ConditionOperator.Equal, environmentVariableName);
var childLink =
query.AddLink("environmentvariablevalue", "environmentvariabledefinitionid", "environmentvariabledefinitionid");
childLink.EntityAlias = "ev";
childLink.Columns = new ColumnSet("environmentvariablevalueid", "schemaname", "value");
var result = service.RetrieveMultiple(query);
var row = result.Entities.FirstOrDefault() ?? new Entity();
return row.GetAttributeValue<AliasedValue>("ev.value") != null ?
(TData)row.GetAttributeValue<AliasedValue>("ev.value").Value : row.GetAttributeValue<TData>("defaultvalue");
}
}
In the code above, the Custom API needed 2 input parameters and 1 output parameters:
- Report Name
- Report XML for filtering the data
- Result for the Output of the API
On line 26, we will retrieve the Environment Variable in the previous part and use it to get the access token in line 29. This access token will be used when we need to process the report later.
On lines 30-31, we set the Access Token to the httpClient. So, every time we are processing the report, we will use this token.
On lines 31 – 46, we will open the reportviewer.aspx with all the attributes for the specified report. Then, as the response, the system will return back an HTML with the specified “PdfDownloadUrl” string:
PdfDownloadUrl
Then, we need to extract this value. That is why GetStringBetween is being used here to get the URL.
Once we get the PDF Download URL, we just need to call the HTTP GET method and we are supposed to get the PDF content already. Last, we just need to Convert the content to Base64String and return it.
Up until this point, the plugin is already done. Next, we just need to create the Custom API (using my favorite tool – Custom API Manager by David Rivard):
Create the Custom API
Demo
For demo purposes, I created a simple Power Automate. Wait, is not mean that this is a necessary step. If you want to directly send Email from Dataverse, you can do it as well. 😁
First, I created a “Manually trigger a flow” to invoke the Flow. Then, in the next action, we need to call the custom API that we already created using “Perform an unbound action”. There, you need to supply Report Name and Report Xml:
Call the Custom API
If you are wondering how to get the Report Name and Report XML, when you want to generate the report manually, you need to open your Developer Tools in the browser > go to the “Network” tab:
Open the Network Tab in Developer Tools
Then when you already filled in the necessary parameters, you can click the “Run Report” button and you will see the first execution of “reportviewer.aspx” > go the the Payload tab. There you can get the reportName and also CRM_Filter which are needed for the Custom API to run:
Get the ReportName and ReportXML
Last, we need to send the email which I’m using Gmail:
Send Email Action
And, here is my testing result:
Demo Result
Happy CRM-ing!
Temmy Wahyu Raharjo 