Generate Multi-Sheet Excel File from SSRS Report in D365FO
on
Get link
Facebook
X
Pinterest
Email
Other Apps
Purpose
The purpose of this document is to explain how to generate a single Excel workbook containing two worksheets from an SSRS report in Microsoft Dynamics 365 Finance and Operations (D365FO).
In this example:
The Details worksheet displays the account numbers and names of the top 10 vendors, ordered by vendor account number.
The Summary worksheet displays the total number of vendors included in the Details worksheet.
Here, “top 10” means the first 10 vendors ordered by account number—not vendors ranked by balance or transaction value.
This functionality can be useful for:
Presenting detailed records and their summary in one Excel workbook.
Providing business users with a report that is easy to review.
Avoiding manual preparation of separate detail and summary files.
Demonstrating multiple-worksheet Excel output from a single SSRS report.
Product
Microsoft Dynamics 365 Finance and Operations (D365FO)
Development Approach
The solution uses the SSRS Report Data Provider approach and includes:
An InMemory temporary table to store vendor details, and summary data.
An X++ data-provider class to retrieve up to 10 vendors.
An SSRS report design containing Details and Summary sections.
A page break to place the Summary section on a separate Excel worksheet.
A controller class to configure Excel output.
This implementation uses SSRS to generate the workbook; it does not require OfficeOpenXml..
1).Create the Temporary Tables
2. Create the Data-Provider Class
Create a class named SsrsTwoSheetsDP.
The class selects vendors in ascending account-number order and inserts a maximum of 10 records into the temporary table.
The two sheets come from the report design, because the SSRS Excel renderer makes one worksheet for each explicit page break. This part can’t be written in X++, so do it in the report designer:
Add both datasets.
Put a tablix on the detail dataset (AccountNum, Name) inside a rectangle, and set its PageName to Details and its PageBreak to End.
Put a tablix on the summary dataset (Description, TotalValue) inside a second rectangle, with PageName set to Summary.
Build and deploy the report, create an Output menu item that points to SsrsTwoSheetsController, and run it.
4. Create the Controller Class
Create SsrsTwoSheetsController to run the report with Excel as the output format.
class SsrsTwoSheetsController extends SrsReportRunController
{
public static void main(Args _args)
{
SsrsTwoSheetsController controller;
SRSPrintDestinationSettings printSettings;
controller = new SsrsTwoSheetsController();
controller.parmArgs(_args);
controller.parmReportName(
ssrsReportStr(
SsrsTwoSheets,
Report));
// Do not reload the user's previously saved PDF settings.
controller.parmLoadFromSysLastValue(false);
// Run directly without the report parameter dialog.
ObjectType = Class
Object = SsrsTwoSheetsController
Add it to the required menu and security privilege.
6. Build, Deploy, and Test
Build the project.
Synchronize the database.
Deploy TwoSheetsReport.
Run the Action menu item interactively.
Open the generated workbook.
Conclusion
This example demonstrates how to export the first 10 vendors and their total count to separate worksheets in a single Excel workbook using an SSRS report in D365FO. The Details worksheet provides the vendor records, while the Summary worksheet provides a count of those same records.
Comments
Post a Comment