Dynamics Community Forum Thread Details

Hello Biju,

Yes, you can definitely schedule the download of an SSRS report as an Excel file and send it to users using Power Automate, and you can also achieve this programmatically using .NET. Here's a breakdown of both approaches:

Option 1: Scheduling SSRS Report Download and Sending via Power Automate

Directly triggering an SSRS report download as an Excel file from Power Automate in a scheduled manner is not a straightforward, out-of-the-box feature. Power Automate's standard connectors for Dynamics 365 and SSRS don't have a direct action to export an SSRS report in a specific format on a schedule.

However, you can achieve this with a workaround involving a custom API or Azure Function that you trigger from Power Automate:

Workaround Steps:

Create a Custom API or Azure Function:
- This custom component will be responsible for:
  - Authenticating with your Dynamics 365 CRM (v9).
  - Calling the SSRS web service to execute the desired report.
  - Specifying the output format as Excel.
  - Retrieving the generated Excel file.
- Using .NET for the Custom API/Azure Function: You can leverage the System.Net.Http library to make SOAP requests to the SSRS web service or use the Reporting Services API if available in your environment. You'll need to handle authentication with Dynamics 365 (e.g., using OAuth or connection strings).
Implement the SSRS Report Execution Logic (.NET Example for Custom API/Azure Function):
using System;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

public async Task<byte[]> DownloadExcelReport(string reportServerUrl, string reportPath, string username, string password)
{
using (var httpClient = new HttpClient())
{
var requestContent = new StringContent($@"<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:rs=""http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"">
<soapenv:Header/>
<soapenv:Body>
<rs:Render>
<Report xsi:nil=""true"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">{reportPath}</Report>
<Format>EXCELOPENXML</Format>
<ReportParameters>
</ReportParameters>
<DeviceInfo>
<Toolbar>False</Toolbar>
<ExcelWorkbookTabName>Report Data</ExcelWorkbookTabName>
</DeviceInfo>
</rs:Render>
</soapenv:Body>
</soapenv:Envelope>", Encoding.UTF8, "text/xml");

var credentials = new NetworkCredential(username, password);
var handler = new HttpClientHandler { Credentials = credentials };
using (var clientWithAuth = new HttpClient(handler))
{
var response = await clientWithAuth.PostAsync($"{reportServerUrl}/ReportService2005.asmx", requestContent);
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsByteArrayAsync();
}
}
}
- reportServerUrl: Your SSRS Report Server URL (e.g., http://your-report-server/ReportServer).
- reportPath: The full path to your report in SSRS (e.g., /SharedReports/YourReport).
- username, password: Credentials to access the SSRS server.
- Format: Set to EXCELOPENXML for .xlsx format. You might need to adjust for older Excel versions.
- DeviceInfo: Allows you to configure Excel-specific settings.
Deploy the Custom API or Azure Function: Host your custom API or deploy your Azure Function so that Power Automate can access it.
Create a Scheduled Flow in Power Automate:
- Create a new Scheduled cloud flow.
- Set the recurrence (e.g., weekly).
- Add an action to call your Custom API or Azure Function: Use the appropriate connector (e.g., "HTTP" for a custom API, the Azure Functions connector).
- Pass necessary parameters: Report path, SSRS credentials (securely), etc.
- Add an action to send the email: Use the "Office 365 Outlook" or "Mail" connector.
- Attach the Excel file: The output of your custom API/Azure Function will be the Excel file content (as a byte array). Use the "Attachments" section of the email action to attach this data. You'll need to provide a filename with the .xlsx extension.

Option 2: Achieving this using .NET Directly (Scheduled Task)

You can create a standalone .NET application (console application or Windows Service) to achieve this without Power Automate's scheduling:

Create a .NET Application: Use Visual Studio to create a new console application or Windows Service project.
Reference Necessary Libraries: You'll need to reference libraries for interacting with Dynamics 365 (using the CRM SDK - although v9 support might be limited, you might need to use Web API) and for making web service calls to SSRS (as shown in the .NET example above).

Implement the Logic:
- Authenticate with Dynamics 365: If you need to pass Dynamics 365 context or parameters to the report, you'll need to authenticate with CRM to retrieve that data.
- Call SSRS Web Service: Use the .NET code (similar to the Azure Function example) to execute the report and get the Excel output.
- Send Email: Use the System.Net.Mail namespace to send an email with the generated Excel file as an attachment.

Schedule the .NET Application:
- For a console application, you can use Windows Task Scheduler to schedule it to run at your desired interval (e.g., weekly).
- For a Windows Service, you can configure it to run continuously and have a timer to trigger the report generation and email sending.

Guidance and Considerations:

Authentication: Securely manage credentials for both Dynamics 365 and SSRS in your custom code or Power Automate connections. Consider using Azure Key Vault for storing sensitive information.
Error Handling: Implement robust error handling in your custom code and Power Automate flow to catch and log any issues.
SSRS Permissions: Ensure the user account used to access SSRS has the necessary permissions to execute the report.
Dynamics 365 Licensing: Be mindful of any licensing implications when accessing Dynamics 365 data programmatically.
Complexity: The Power Automate workaround involves building and maintaining a custom API or Azure Function, which adds complexity. The direct .NET approach requires development and deployment of a separate application.
Version Compatibility (v9): Ensure the .NET libraries and SDK you use are compatible with Dynamics 365 v9. The Web API is generally the recommended approach for interacting with later versions of Dynamics 365.

Which Approach to Choose?

Power Automate with Custom API/Azure Function: Might be preferable if you want to manage the scheduling and email sending within the Power Platform and have the development skills to create the custom component.
.NET Directly with Task Scheduler: Suitable if you prefer a standalone solution and have strong .NET development skills.

Both options require custom development to bridge the gap between scheduled execution and SSRS report export in Excel format. The direct Power Automate connectors don't offer this functionality out of the box.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak

Community site session details

Scheduling SSRS Report Download as Excel in D365 CRM

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Featured topics

Product updates