In this blog post, I will explain how to develop a new custom SSRS report from scratch using DataContract, Controller, UIBuilder and Report Data Provider (RDP) classes in D365FnO.
Reports are an essential part of any business application. They help users to analyze data, monitor performance, and make informed decisions. In Dynamics 365 Finance and Operations (D365FnO), there are different types of reports that can be used for various purposes. One of them is the SQL Server Reporting Services (SSRS) report.
To develop an SSRS report in D365FnO, you need to follow these steps:
Report Requirements:
Let’s say we want to develop a new report that will show the Daily Sales Data against different parameters. This report will display data from Customer Invoice Journal.
Let’s say we want to develop a new report that will show the Daily Sales Data against different parameters. This report will display data from Customer Invoice Journal.
Create Temporary Table:
Table is a fundamental component of report development in d365FnO. It defines the data source and the structure of the report.
To create a table in D365FnO, you need to follow these steps:
1. Open Visual Studio and connect to your development environment (Model).
2. In the Solution Explorer, right-click the report project and select Add > New Item.
3. In the Add New Item dialog box, select Dynamics 365 Items > Data Model > Table and enter a name for the table.
4. Click Add to create the table.
5. In the Table Designer, you can add fields that are required on the report, methods and other properties to the table.
6. Set the Table Type to TempDB from the properties.
7. Save and synchronize the table with the database.
Create Data Contract class:
A data contract class is an X++ class that defines the parameters for a report data provider (RDP) class. An RDP class is used to access and process data for a report from D365FnO. A data contract class has attributes and methods.
A data contract class is an X++ class that defines the parameters for a report data provider (RDP) class. An RDP class is used to access and process data for a report from D365FnO. A data contract class has attributes and methods.
To create a class in D365FnO, you need to follow these steps:
1. Open Visual Studio and connect to your D365FnO environment (Model).
2. In the Solution Explorer, right-click on the project where you want to add the class and select Add > New Item.
3. In the Add New Item dialog box, select Dynamics 365 Items > Code > Class and enter a name for the class. The name should start with an uppercase letter and follow the PascalCase convention. For example, MyClass.
2. In the Solution Explorer, right-click on the project where you want to add the class and select Add > New Item.
3. In the Add New Item dialog box, select Dynamics 365 Items > Code > Class and enter a name for the class. The name should start with an uppercase letter and follow the PascalCase convention. For example, MyClass.
4. Click Add to create the class file. The file will contain some boilerplate code.
5. In the class file, you can define the parameters, methods and attributes of your data contract class.
6. Complie and save the class.
[
DataContractAttribute,
SysOperationGroupAttribute('From Date',"From Date",'1'),
SysOperationGroupAttribute('To Date',"To Date",'2'),
SysOperationGroupAttribute('Warehouse',"Warehouse",'4'),
SysOperationGroupAttribute('User Id', "User Id", '6',FormArrangeMethod::HorizontalFlushRight),
SysOperationContractProcessingAttribute(classstr(DSRReportUIBuilder),
SysOperationDataContractProcessingMode::CreateUIBuilderForRootContractOnly)
]
public class DSRReportDC
{
TransDate fromDate, toDate;
InventLocationId inventLocationId;
[
DataMemberAttribute('FromDate'),
SysOperationLabelAttribute(literalstr("@SYS5209")),
SysOperationHelpTextAttribute(literalstr("@SYS5209")),
SysOperationGroupMemberAttribute('FromDate'),
SysOperationDisplayOrderAttribute('1')
]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}
[
DataMemberAttribute('ToDate'),
SysOperationLabelAttribute(literalstr("@SYS36680")),
SysOperationHelpTextAttribute(literalstr("@SYS36680")),
SysOperationGroupMemberAttribute('Date'),
SysOperationDisplayOrderAttribute('2')
]
public TransDate parmToDate(TransDate _ToDate = ToDate)
{
ToDate = _ToDate;
return ToDate;
}
[
DataMemberAttribute('Warehouse'),
SysOperationLabelAttribute(literalstr("Warehouse")),
SysOperationHelpTextAttribute(literalstr("Warehouse")),
SysOperationGroupMemberAttribute('Warehouse'),
SysOperationDisplayOrderAttribute('5')
]
public InventLocationId parmWarehouse(InventLocationId _inventLocationId = inventLocationId)
{
inventLocationId = _inventLocationId;
return inventLocationId;
}
// Validate method for parameters/filters input values
public boolean validate()
{
boolean ok = true;
if(fromdate && Todate)
{
if(fromdate > Todate)
{
ok = checkFailed("To date must be greater from date.");
}
}
if(fromdate)
{
if(!Todate)
{
ok = checkFailed("To date must be specified.");
}
}
if(Todate)
{
if(!fromdate)
{
ok = checkFailed("From date must be specified.");
}
}
return ok;
}
}Create UIBuilder class:
UI Builder class is a class that helps you to customize the dialog box that appears when you run a report in D365 FnO. It allows you to add or modify the parameters and controls of the report. UI Builder class is useful when you want to create a user-friendly and dynamic interface for your report. You can create class: Right-click on the project > Add > New Item > Dynamics 365 Items > Code > Class > Enter the name of class > Click Add. For reference see the data contract class screenshot.
class DSRReportUIBuilder extends SrsReportDataContractUIBuilder
{
DSRReportDC rdpcontract;
DialogField dialogfromdate;
DialogField dialogtodate;
DialogField warehouse;
public void build()
{
rdpcontract = this.dataContractObject() as DSRReportDC;
dialogfromdate = this.addDialogField(methodStr(DSRReportDC,parmFromDate),rdpContract);
dialogtodate = this.addDialogField(methodStr(DSRReportDC,parmToDate),rdpContract);
warehouse = this.addDialogField(methodStr(DSRReportDC,parmWarehouse),rdpContract);
}
public void postBuild()
{
Dialog localDaialogObj = this.dialog();
super();
dialogfromdate = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(DSRReportDC,parmFromDate));
dialogtodate = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(DSRReportDC,parmToDate));
warehouse = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(DSRReportDC,parmwarehouse));
warehouse.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(DSRReportUIBuilder,warehouseLookup), this);
}
Public void PostRun()
{
DSRReportDC contract = this.dataContractObject();
}
public void warehouselookup(FormStringControl _control)
{
Query queryProjectLookup;
QueryBuildDataSource qbds_Project, qbds_ProjName;
QueryBuildRange qbr;
SysTableLookup sysTableLookup;
queryProjectLookup = new Query();
sysTableLookup = SysTableLookup::newParameters(tableNum(InventLocation), _control);
qbds_Project = queryProjectLookup.addDataSource(tableNum(InventLocation));
qbr = qbds_Project.addRange(fieldNum(InventLocation, InventLocationType));
qbr.value(queryValue(InventLocationType::Standard));
qbds_Project.orderMode(OrderMode::OrderBy);
qbds_Project.addGroupByField(fieldNum(InventLocation, InventLocationId));
qbds_Project.addGroupByField(fieldNum(InventLocation, Name));
sysTableLookup.parmQuery(queryProjectLookup);
sysTableLookup.addLookupField(fieldNum(InventLocation, InventLocationId));
sysTableLookup.addLookupfield(fieldNum(InventLocation, Name));
sysTableLookup.performFormLookup();
}
}Note: Don’t forget to add the reference of the UI Builder class in the Data Contract class.
Create Report Data Provider class:
Report Data Provider is a class that can be used to access and process data for a report in D365FnO. An RDP class is useful when you need to query data from Microsoft Dynamics that cannot be directly retrieved by a simple query. For example, you may need to apply some business logic or calculations on the data before displaying it on the report. You can create class: Right-click on the project > Add > New Item > Dynamics 365 Items > Code > Class > Enter the name of class > Click Add. For reference see the data contract class screenshot.
[
// Add the reference of data contract class
SRSReportParameterAttribute(classstr(DSRReportDC))
]
class DSRReportDP extends SRSReportDataProviderBase
{
DSRReportDC dsrReportDC;
TransDate fromdate;
TransDate todate;
InventLocationId inventLocationId;
TmpCarInvoice tmpCarInvoice;
LedgerJournalTrans ledgerJournalTrans;
[
// Add the reference of report temp table
SRSReportDataSetAttribute(tablestr("TmpCarInvoice"))
]
public TmpCarInvoice getTempTable()
{
select * from tmpCarInvoice;
return tmpCarInvoice;
}
[SysEntryPointAttribute(false)]
public void processReport()
{
Query query;
QueryRun queryRun;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
CustInvoiceJour carInvoice;
dsrReportDC = new DSRReportDC();
dsrReportDC = this.parmDataContract() as DSRReportDC;
fromdate = dsrReportDC.parmFromDate();
todate = dsrReportDC.parmToDate();
inventLocationId = dsrReportDC.parmWarehouse();
query = new Query();
// Add CustInvoiceJour as a report data source in a query
queryBuildDataSource = query.addDataSource(tableNum(CustInvoiceJour));
// Check the dates & warehouse and set the ranges
if(fromdate && todate)
{
query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,InvoiceDate)).value(queryRange(fromdate,ToDate));
}
if(inventLocationId)
{
query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,InventLocationId)).value(queryValue(inventLocationId));
}
query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,SalesType)).value(queryValue(SalesType::Sales));
query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,SalesType)).value(queryValue(SalesType::ReturnItem));
//query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,InterCompanyCompanyId)).value(SysQuery::valueEmptyString());
queryRun = new QueryRun(query);
while (queryRun.next())
{
// Get the table from the query
carInvoice = queryRun.get(tableNum(CustInvoiceJour));
tmpCarInvoice.InvoiceDate = carInvoice.InvoiceDate;
tmpCarInvoice.InvoiceId = carInvoice.InvoiceId;
tmpCarInvoice.CustAccount = carInvoice.OrderAccount;
tmpCarInvoice.CustName = CustTable::find(carInvoice.OrderAccount).name();
tmpCarInvoice.CustGroupId = carInvoice.CustGroup;
tmpCarInvoice.CustomerRegion = CustTable::find(carInvoice.OrderAccount).countryName();
tmpCarInvoice.inventLocationId = carInvoice.inventLocationId;
tmpCarInvoice.userId = carInvoice.createdBy;
tmpCarInvoice.LineAmount = carInvoice.InvoiceAmountMST;
tmpCarInvoice.SalesId = carInvoice.SalesId;
// insert the record into report table
tmpCarInvoice.insert();
}
}
public static void main(Args args)
{
DSRReportDP dsrReportDP = new DSRReportDP();
dsrReportDP.processReport();
}
}