Electronic Reporting Explained in D365 FO

 

In this post we’ll be looking at a classical data export scenario and showing how it can be implemented using the Electronic Reporting (ER) feature in Dynamics 365 for Finance and Supply Chain Management (D365F&SCM).

1. Introduction
2. Minimal Configuration of ER Parameters
3. Defining the Data Model
4. Mapping from Database to Data Model
5. Mapping from Data Model to XML Format
6. Starting the Export
7. Conclusion

Introduction

As Microsoft mentioned, the ER framework replaces 20 or so existing frameworks, so it’s expected that the complexity of its configuration is high and the learning curve is steep. Others have covered the advanced setup in depth. What we’re trying to achieve here is to add some clarity and show how a simple export is done using ER by linking the concepts specific to ER with more familiar terms used in the power-user and development worlds like data selection, serialization or transformation.

The scenario we’ll be looking at is very simple: exporting some info about a given purchase order (PO), as well as all its lines. In other words, the input is the PO number (e.g. 0000042) and the output is an XML file in the format shown below. Of course, there are many ways of achieving the same result, such as using the data management workspace, OData, Logic Apps, etc. We’ll be addressing the benefits of ER in the last section.

Figure 1. Input data - Purchase order with two lines

Figure 2. Desired output in XML format

From a user’s perspective, they would just open up the Purchase Order form, select a PO and press a button labelled “ER export”. This will trigger the export and produce an XML file. The file can then either be downloaded or sent to downstream integrations, like a Logic App that would orchestrate the link to another system.

Figure 3. System-level view of the Electronic Reporting export scenario

The following sections will go through each step of the process depicted below.

In case you’re wondering what “data model” means in the world of ER, you can think of it as a class definition. It includes fields of various data types. Data from the PurchTable and its related tables will be selected by D365FO and saved as an intermediate object in the format specified by the data model. Then, similar to how an object gets serialised, the data model will be converted into a target format, in our case XML.

Minimal configuration of ER parameters

To get started, we need to set up a few parameters. The minimum configuration to enable our simple export scenario includes:

• Creating a new Configuration provider
• Creating a new Reporting configuration

We won’t discuss the topic of Configuration providers or the Application Lifecycle Management (ALM) story for ER reports in this post. We’ll cover the versioning and deployment using LCS in a future post. Microsoft provides all this info in their documentation.

In ER parlance, what we need to do to reach our goal of exporting POs to XML is to create a Reporting configuration, which holds the data model and all the mapping rules, as well as versioning information for each.

The first operation is creating a configuration provider and setting it active, which is done through the Electronic reporting workspace (under the Organisation administration module). The reason we’ve included this setup in the Minimal configuration section is because you can't create the Reporting configuration unless there's an active Configuration provider.

Figure 4. Electronic Reporting workspace – link Configuration providers

Figure 5. Creating a new configuration provider

Figure 6. Setting a provider as active

Now that we’ve got an active configuration provider, we can create the reporting configuration:

• In the Electronic Reporting workspace, select Reporting configurations
• Click + Create configuration and choose Root
• Provide a name and a description
• Notice that the active Configuration provider was selected automatically (ContosoTest)
• Press Create configuration

Figure 7. Creating a new Reporting configuration

Defining the data model

Now that we’ve created the Reporting configuration, we can start defining the data model. Remember that the data model is like a class definition for the intermediary format used in ER. In other words, after data is selected from PurchTable, PurchLine, etc., it needs to be put in this format. This is why even for this simple data export scenario we’ll need to define two mappings:

1. Mapping from database to data model
2. Mapping from data model to XML file

Defining the data model is similar to creating a class in standard development (e.g. C# or X++). You need to define member fields, with names and data types. Some of these fields are themselves objects of other classes or arrays of objects.

To define the data model, we need to select the root Reporting configuration we’ve just created and press the Designer button, which opens the data model designer.

We’ll be creating the fields with names and types that will enable a close to 1:1 mapping to the target XML format.

Figure 8. Creating the first node

Figure 9. Adding a String child node

A tricky part is how to define arrays. In the ER data model designer, this is done through the Record list and Record types. So, as a Purchase order contains a Lines element with zero or more Line elements, we’ll create:

• Lines as Record list
• Line as Record
• Properties of each PO line under the Line record

Mapping from data source to data model will then address data selection and create the array automatically.

Figure 10. Create Lines Record list

Figure 11. Create Line record

Figure 12. Defining properties of Line record and remaining fields

Mapping from database to data model

Until now, we've created a data model, which means we've defined how data is stored when extracted from the database. The next step is to define which data we’re selecting from the database. This is accomplished using a model to data source mapping. In the data model form where we’ve defined the data model, select “Map model to datasource”. Then:

• Press +New to create a new entry
• Under Definition, select the root node of the data model we’ve just created, “PurchaseOrder”
• Provide a name and description
• Ensure Direction is “To model”. This means that the output is an object of the class defined by the data model.

Figure 13. Creating a new datasource mapping

Figure 14. Model mapping designer - so many options

Hitting Designer brings up one of the most complex screens in ER. It’s understandable if you feel a bit overwhelmed at this point and think that there are too many options. So, let’s remember our purpose for this step - we need to:

• Select data from PurchTable
• Join with PurchLine and other related tables
• Filter on one given PO number
• Match fields from input tables to fields from data model

 To achieve that, we’ll take the following steps:

• Add a Table records data source pointing to PurchTable
• Create a User input parameter of type PurchaseOrderId, which will hold the filtered PO number
• Create a Calculated field, which in fact will be a record list by applying the FILTER function against the PurchTable data source

Figure 15. Creating the Table records data source linked to table PurchTable

Figure 16. Press Show details to see field types

Figure 17. Create the User input parameter

Figure 18. Create Calculated field, press Edit formula

Figure 19. Editing formula to filter PurchTable datasource based on user input parameter

Notice that the Calculated field we’ve just created, SelectedPO, is now a Record list. We can start field mapping now. What this involves is going through the fields from the data model one by one and finding the corresponding field from the SelectedPO filtered data source. If the types are also identical, the Bind button is enabled and should be used. Otherwise, if some conversion is needed the Edit button can be used.

Figure 20. Matching PurchId field from PurchTable to ID field from data model - press Bind button

Figure 21. Bound fields are shown in the data model

For field Approval status, the Bind button is not active because the types are different. We’ve defined it as a string in the data model but it’s an enum in the data source. So, we need to convert enum to string – we’ll use the TEXT() function for now, although better options exist. The same applies for field POStatus.

Figure 22. Binding fields that need conversion

Now the tricky part: how to include PO lines? We’ll need to bind the Lines record list from the data model to the PurchLine record list relation of the SelectedPO datasource.

Figure 23. Mapping PurchLine relation to Lines Record list

Figure 24. Mapping Purchase line fields

Figure 25. Full mapping

At this point we can just run the export and it will produce an XML file. Just that the format is not what we need. To see the output, we can press Run in the Model to datasource mapping form and provide the PO number in the input parameter.

Figure 26. Exporting without formatting

Figure 27. XML output without formatting

Mapping from data model to XML format

Now that we’ve defined the temporary format (aka the data model) and we’ve also given the mapping rules (datasource to model mapping), we'll be defining the output format.

To achieve this in ER, we need to create a new child configuration, as a Format based on the model we’ve just defined. Before we do that, we need to mark the parent reporting configuration as not draft anymore by pressing – Change status – Complete.

Figure 28. Marking parent model as complete

Figure 29. Creating new format based on existing model – format type = XML

Now, if we press Design on the new format, we’ll see the Format designer form. This is where we’ll define the XML element structure (parent-child relationships) and data types.

Figure 30. Format designer form

Figure 31. Creating root XML element

Figure 32. Adding child element and string value under element

Figure 33. Adding remaining XML elements and values

Figure 34. Binding XML values to data model fields

Figure 35. Mapping more fields

Figure 36. Checking current export format

Figure 37. Finalising XML field mapping

Starting the export

We’ve come so far and were able to create such a complex mapping without writing any line of code. You’d be forgiven for expecting that the export is enabled automatically, just like it is for related data entities on forms (e.g., Export to Excel). Unfortunately, to start the export we need to write some code.

What we’ll do is:

• Extend the PurchTable form
• Add a new button named “ER Export”
• When pressing ER Export, the selected PO is exported to XML using the format we’ve just defined

The format definitions are stored in the ERFormatMappingTable and we can find them by name. In the code below, we’ll provide the ID of the format as input when running the ER export.

Figure 38. Format configurations stored in the database

Figure 39. Adding new button to PurchTable form

class ERExporter

{

[FormControlEventHandler(formControlStr(PurchTable, buttonERExport), FormControlEventType::Clicked)]

public static void buttonERExport_OnClicked(FormControl sender, FormControlEventArgs e)

{

FormDataSource purchTable_DS = sender.formRun().dataSource("PurchTable");

PurchTable purchTable = purchTable_DS.cursor();

ERExporter::ExportPO(purchTable.PurchId);

}

public static void ExportPO(PurchId _purchId)

{

Info(strFmt("Exporting: %1", _purchId));

Name formatName = "POtoXML format";

Name erParamPurchId = 'model/parameters.PurchId';

ERFormatMappingTable formatMappingTable;

select formatMappingTable where formatMappingTable.Name == formatName;

ERIFormatMappingRun runner = ERObjectsFactory::createFormatMappingRunByFormatMappingId(formatMappingTable.RecId, '', false);

ERModelDefinitionInputParametersAction modelDefinitionInputParametersAction = new ERModelDefinitionInputParametersAction();

modelDefinitionInputParametersAction.addParameter(erParamPurchId, _purchId);

runner.withParameter(modelDefinitionInputParametersAction).run();

}

}

Figure 40. Running the export form the new custom button

As further improvements, rather than providing the file to the user for download, we can intercept it in the code as well and post it to another integration like a web service or Logic App. Microsoft provides the code sample and necessary setup (i.e. special document type) for achieving this.

Conclusion

Looking back at this simple export scenario, we can clearly see some key benefits of using Electronic Reporting:

• We’ve implemented the first two parts of a typical ETL (extract – transform – load) process using standard D365F&SCM configuration
• We’ve managed to export an XML file in a desired format without writing any code (the code was written for a nicer integration with the Purchase orders form).
• We can select any table, any relation, any data type
• We can support more than one export format (e.g. use Excel instead of XML) and multiple versions of the same format

Although not covered here, the deployment of solutions to multiple environments or to different customers integrates well with LCS, so the ALM story is pretty well-defined. Microsoft already provides many standard formats (e.g. SEPA, UK BACS format) to be downloaded from their Configuration provider.

We’ve also covered some topics that may seem obvious to an ER expert but are not as intuitive for someone just getting started. Here is a table with the key concepts to remember.

Concept	Meaning	See section
Configuration provider	Registration providing a link to a repository of reporting configurations. Useful for versioning and deployment of configurations through LCS.	Minimal configuration of ER parameters, Conclusion
Reporting configuration	Holds the data model and all the mapping rules, as well as versioning information for each.	Minimal configuration of ER parameters
Data model	Similar to the concept of a class definition in object-oriented programming. It includes fields of various data types.	Defining the data model
Record list	Field type in a data model used for storing records. Similar to an array.	Defining the data model, Mapping from database to data model
Record	Field type in a data model used for storing complex types. Similar to a struct in C++.	Defining the data model, Mapping from database to data model