How to create a new custom SSRS report in D365 F&O

on

 In this article, we will explain how to develop a new custom SSRS report from scratch using a Report Data Provider (RDP) approach in Dynamics 365 for Finance and Operations.

The objects we are going to create

  • A new SSRS report with a single (Precision) design.
  • A DP (Data Provider) class that will provide three data sets for the SSRS report. The same class will also provide the information about report parameters to the report’s RDL through an attribute that links a DP class with the corresponding report Data Contract.
  • A Data Contract class that will define and carry the values of the report parameters. We will have one hidden parameter: a sales agreement’s RecId.
  • Three temporary tables to define and carry the report data. They will be filled and exposed to the SSRS report by the DP class.
  • A Controller class that will handle the report dialog form, setting the SSRS report design and the value of the hidden parameter.
  • A menu item that opens the report in the viewer.
  • A menu item that opens the report dialog form.

Step 1: Create report temporary tables

For this report the data are divided into three data sets: Header data, Lines data, and Customer contacts data, so we need to create three temporary tables.

Create temporary tables by clicking Add > New Items > Dynamics 365 Items > Data Model > Table:

Add the following three temporary tables:

  • DocAgreementHeader – used for a sales agreement’s header data.
  • DocAgreementLine – used for a sales agreement’s lines data.
  • DocAgreementContact – used for a sales agreement’s customer contact data.

Set the TableType property to InMemory or TempDB for each table. This way the tables will act as temporary tables,

for more information about InMemory or TempDB table click here…

We will now add all the needed fields to our temporary tables.

Step 2: Create the Controller class:

  • A Controller class extends SrsReportRunController.
  • A Data Contract class should be decorated with the DataContractAttribute attribute.
  • A Data Provider class extends SRSReportDataProviderBase and implements at least the processReport() method. Each Data Provider class is decorated with the SRSReportParameterAttribute attribute that points to a particular Data Contract class, and optionally with the SRSReportQueryAttribute attribute that points to a dynamic query if such exists. Additionally, for each of the data sets a method decorated with the SRSReportDataSetAttribute attribute should be implemented.

In order to add a new class, go to Add > New Items > Dynamics 365 Items > Code > Class.

We are going to create three classes here at once:

  • DocAgreementDocumentController – the Controller class.
  • DocAgreementDocumentContract – the Data Contract class.
  • DocAgreementDocumentDP – the Data Provider class.

Implementation of the Controller class

A Controller class orchestrates the report execution. In our case, the controller will handle whether the report dialog form should open, set up the SSRS report design, and the value of the hidden parameter. Each controller class should extend the SrsReportRunController.

X++ source code example:

class DocAgreementDocumentController extends SrsReportRunController{

public static DocAgreementDocumentController construct()    

{        return new DocAgreementDocumentController();    }

public static void main(Args _args)    {        

DocAgreementDocumentController docAgreementDocumentController =            DocAgreementDocumentController::construct();                                                

docAgreementDocumentController.parmReportName(ssrsReportStr(DocAgreementDocument, Report));    docAgreementDocumentController.parmArgs(_args);        

docAgreementDocumentController.parmDialogCaption("Sales agreement document");        

docAgreementDocumentController.startOperation();    }

protected void prePromptModifyContract()    {        

DocAgreementDocumentContract contract = this.parmReportContract().parmRdpContract();        

// Set the hidden parameter value.        

contract.parmSalesAgreementRecId(args.record().RecId);        

// Set the report design name.        

this.parmReportContract().parmReportName(ssrsReportStr(DocAgreementDocument, Report));        

boolean isPreview = args.menuItemName() == menuItemOutputStr(DocAgreementDocumentPreview);        // Set the target print destination to Screen if we are previewing the report.        

if (isPreview)        {           

this.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::Screen);     }        

// Don't show the report dialog if the report menu item is DocAgreementDocumentPreview        

// and don't save to SysLastValue either.        

this.parmShowDialog(!isPreview);        this.parmLoadFromSysLastValue(!isPreview);    }

}

Step 3: Create the Data Contract class

A Data Contract class defines and stores the values of the report parameters. In our case, we will have one hidden parameter: The sales agreement’s RecId. Note that each Data Provider class is decorated with the DataContractAttribute attribute, and each of the methods representing a particular report parameter is decorated with the DataMemberAttribute attribute.

             [DataContractAttribute]public class DocAgreementDocumentContract{    
RecId  salesAgreementRecId;        
[DataMemberAttribute('SalesAgreementRecId'),SysOperationLabelAttribute(literalStr("@SYS190134")),SysOperationControlVisibilityAttribute(false)]    
public RecId parmSalesAgreementRecId(RecId _salesAgreementRecId = salesAgreementRecId){
        salesAgreementRecId = _salesAgreementRecId;        
return salesAgreementRecId;    
}
}

Step 4: Create the Data Provider class

A Data Provider class should extend SRSReportDataProviderBase (or SrsReportDataProviderPreProcess for pre-processed reports) and implement at least the processReport() method. Each Data Provider class is decorated with the SRSReportParameterAttribute attribute that points to a particular Data Contract class, and optionally with the SRSReportQueryAttribute attribute that points to a dynamic query if such exists. Additionally, for each of the data sets a method decorated with the SRSReportDataSetAttribute attribute should be implemented.

The processReport() method is called by Reporting Services to fetch the report data and fill the temporary tables. The same temporary tables should be exposed via methods decorated with the SRSReportDataSetAttribute attribute, for example:

[SRSReportDataSetAttribute(tablestr(DocAgreementHeader))]
public DocAgreementHeader getDocAgreementHeader()

Implementation of the Data Provider class

                      [SRSReportParameterAttribute(classStr(DocAgreementDocumentContract))]
class DocAgreementDocumentDP extends SRSReportDataProviderBase{
// Contains Agreement header data.    DocAgreementHeader  docAgreementHeader;
// Contains Agreement lines data.    DocAgreementLine    docAgreementLine;
// Contains Agreement customer contact data.    
DocAgreementContact docAgreementContact;        
[SRSReportDataSetAttribute(tablestr(DocAgreementHeader))]    
public DocAgreementHeader getDocAgreementHeader()    {
select docAgreementHeader;        
return docAgreementHeader;    
}        
[SRSReportDataSetAttribute(tablestr(DocAgreementLine))]    
public DocAgreementLine getDocAgreementLine()    {        
select docAgreementLine;        
return docAgreementLine;    }    
[SRSReportDataSetAttribute(tablestr(DocAgreementContact))]    
public DocAgreementContact getDocAgreementContact()    {        
select docAgreementContact;        
return docAgreementContact;    
}    
/// <summary>    /// Process report data.    /// </summary>    public void processReport()    {        
DocAgreementDocumentContract contract;        
SalesAgreementHeader salesAgreementHeader;        
contract = this.parmDataContract() as DocAgreementDocumentContract;        
// Get sales agreement header record from the contract parameter        
salesAgreementHeader =            SalesAgreementHeader::find(contract.parmSalesAgreementRecId());                
if (salesAgreementHeader)        {            // Populate sales agreement header            
this.populateSalesAgreementHeader(salesAgreementHeader);// Populate sales agreement line            
this.populateSalesAgreementLine(salesAgreementHeader);// Populate sales agreement customer contacts            
this.populateSalesAgreementCustomerContact(salesAgreementHeader);        
}    
}   
}

Step 5: Create the SSRS report

To add a new SSRS report, go to Add > New Items > Dynamics 365 Items > Reports > Report.

Set the report data source to a DP class

Now add a dataset to the report and set its Data Source Type property to Report Data Provider, then click the small button available on the Query property. The list of all DP classes available in the AOT will be shown; locate and select our DocAgreementDocumentDP class > DocAgreementTable. Set the properties as shown in the image below.

Now repeat the same procedure for two more data sets – for sales agreement lines and customer contacts.

Create the report design

Add a new precision design; a standard SSRS designer will open. You can see the Report Items toolbar from where you can drag and drop the controls onto the design surface.

How to make SSRS report designs more readable

In our SSRS report design, we used placeholders to display the value of each expression. Otherwise, our design would look very messy, hard to understand, and afterward, hard to update.

You can use placeholders by clicking on Expression or selecting and right-clicking it; in both cases, the Placeholder window will open.

This is how it looks when an expression uses a placeholder:

And this is the same expression without any placeholder:

Step 6: Create the report menu items

Create a new extension for the SalesAgreement form for adding two new menu items. The first of them (Preview document) will be used for opening the report on Screen and the second one (Print document) should open the report dialog form, where a user can select the target print destination.

Step 7: Execute the report

Navigate to Accounts receivable > Orders > Sales agreements and open any sales agreement. Click the Preview document to see the result.

Comments

Post a Comment