Blogs

Generating PDF/Excel, or SSRS Reports for Selected Records Using Power Automate in Dynamics 365

Inkey Solutions, May 15, 202649 Views

When you’re working with reports in Microsoft Dynamics 365, one common challenge is generating SSRS reports for only selected records. Out-of-the-box capabilities often require predefined filters or manual effort, making the process inefficient for dynamic business needs.

This is where Power Automate steps in to bridge the gap. By combining Power Automate with SSRS, you can dynamically generate reports for specific records and export them as PDF or Excel all in an automated and scalable way.

Why This Approach is Important

Standard SSRS reporting in Dynamics 365 has limitations:

• Reports are typically static or pre-filtered
• No direct way to pass multiple selected record IDs dynamically
• Manual execution can be time-consuming

With Power Automate, you can:

• Generate reports for selected records only
• Automate report execution
• Export in PDF or Excel format
• Save or distribute reports automatically

 

How the Solution Works

The idea is simple but powerful:

• Use Power Automate to accept selected record IDs
• Convert those IDs into a FetchXML filter
• Execute the SSRS report using internal CRM endpoints
• Download the generated report
• Save or share it automatically

 

Understanding the Core Flow

The flow follows a structured pattern:

• Trigger the flow manually with selected record IDs
• Process and format the IDs
• Build a FetchXML filter
• Execute the SSRS report via HTTP request
• Extract session details (ReportSession & ControlID)
• Export the report in required format
• Save the file

 

Practical Implementation

1. Trigger the Flow

Create an Instant Cloud Flow with a manual trigger. Add a text input to pass comma-separated record IDs.

Example:

id1,id2,id3

 

2. Prepare the Record Filter

Split the input IDs and convert them into XML format required by SSRS:

<value>id1</value>
<value>id2</value>

This helps in dynamically filtering records inside the report.

 

3. Build FetchXML

Create a FetchXML filter that includes only the selected records:

<fetch>
<entity name=”contact”>
<filter>
<condition attribute=”contactid” operator=”in”>
<!– dynamic values –>
</condition>
</filter>
</entity>
</fetch>

This ensures the report runs only for the intended dataset.

 

4. Execute the SSRS Report

Use an HTTP POST request to trigger the report:

/CRMReports/rsviewer/reportviewer.aspx

Pass required parameters like:

• Report ID
• FetchXML filter
• Report type

This step initiates report generation on the CRM server.

 

5. Extract Report Session Details

From the response, extract:

• ReportSession
  • substring(body(‘Invoke_an_HTTP_request’),add(int(lastIndexOf(
  • body(‘Invoke_an_HTTP_request’),
  • ‘ReportSession=’
  • )),14),24)
• ControlID
  • substring(body(‘Invoke_an_HTTP_request’),add(int(lastIndexOf(
    body(‘Invoke_an_HTTP_request’),
    ‘ControlID=’
    )),10),32)

These values are required to fetch the generated report.

 

6. Export the Report

Make a second HTTP GET request:

/Reserved.ReportViewerWebControl.axd

Choose the format:

• For Excel:

Format=EXCELOPENXML

• For PDF:

Format=PDF

 

7. Save or Share the Report

Convert the response into a file and store it using OneDrive or SharePoint:

base64ToBinary(outputs(‘Compose’))

You can also email the report or store it in a document library.

 

Real-World Use Cases

This solution is highly effective in scenarios such as:

• Exporting selected contacts or accounts
• Generating reports for specific cases or tickets
• Creating filtered reports for management
• Automating invoice or statement generation

 

Challenges and Considerations

While powerful, there are a few things to keep in mind:

• Authentication must be configured correctly
• Report IDs are often hardcoded
• FetchXML must align with the report dataset
• Some environments may restrict HTTP calls

 

Best Practices

To make your implementation robust and maintainable:

• Keep FetchXML clean and optimized
• Add error handling for HTTP failures
• Allow dynamic selection of report format
• Use meaningful file names with timestamps
• Document each step in your flow

 

Conclusion

Generating SSRS reports for selected records in Dynamics 365 can be challenging using standard methods. However, with Power Automate, you can build a flexible and automated solution that handles this requirement efficiently.

By adopting this approach, you can:

• Automate report generation
• Improve efficiency
• Deliver reports instantly in multiple formats

The key takeaway is simple:
 Let Dynamics 365 handle the data,  and let Power Automate handle the automation.