Generate Multi-Sheet Excel File from SSRS Report in D365FO

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:

  1. An InMemory temporary table to store vendor details, and summary data.

  2. An X++ data-provider class to retrieve up to 10 vendors.

  3. An SSRS report design containing Details and Summary sections.

  4. A page break to place the Summary section on a separate Excel worksheet.

  5. 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.


class SsrsTwoSheetsDP extends SrsReportDataProviderPreProcessTempDB
{
    SsrsTwoSheetsDetailTmp  detailTmp;
    SsrsTwoSheetsSummaryTmp summaryTmp;
 
    [SRSReportDataSetAttribute(tableStr(SsrsTwoSheetsDetailTmp))]
    public SsrsTwoSheetsDetailTmp getDetailTmp()
    {
        select detailTmp;
        return detailTmp;
    }
    [SRSReportDataSetAttribute(tableStr(SsrsTwoSheetsSummaryTmp))]
    public SsrsTwoSheetsSummaryTmp getSummaryTmp()
    {
        select summaryTmp;
        return summaryTmp;
    }
    public void processReport()
    {
        VendTable vendTable;
        int       vendorCount;
 
        while select firstOnly10 vendTable
        {
            detailTmp.clear();
            detailTmp.AccountNum = vendTable.AccountNum;
            detailTmp.Name       = vendTable.name();
            detailTmp.insert();
 
            vendorCount++;
        }
 
        summaryTmp.clear();
        summaryTmp.Description = "Total vendors";
        summaryTmp.TotalValue  = vendorCount;
        summaryTmp.insert();
    }
}

3. Create the SSRS Report and Dataset

Create an SSRS report named:







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:

  1. Add both datasets.
  2. Put a tablix on the detail dataset (AccountNum, Name) inside a rectangle, and set its PageName to Details and its PageBreak to End.
  3. Put a tablix on the summary dataset (Description, TotalValue) inside a second rectangle, with PageName set to Summary.
  4. 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.

        controller.parmShowDialog(false);


        controller.parmExecutionMode(

            SysOperationExecutionMode::Synchronous);


        // Explicitly configure Excel output.

        printSettings =

            controller.parmReportContract().parmPrintSettings();


        printSettings.printMediumType(

            SRSPrintMediumType::File);


        printSettings.fileFormat(

            SRSReportFileFormat::Excel);


        printSettings.fileName(

            "VendorDetailsAndSummary.xlsx");


        printSettings.overwriteFile(true);


        // Execute only after setting the destination.

        controller.startOperation();

    }


}

5. Create the Action Menu Item

Create an Action menu item:

Name = VendTwoSheets
ObjectType = Class Object = SsrsTwoSheetsController 

Add it to the required menu and security privilege.

6. Build, Deploy, and Test

  1. Build the project.

  2. Synchronize the database.

  3. Deploy TwoSheetsReport.

  4. Run the Action menu item interactively.

  5. 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