Electronic Reporting - Get started
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).
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:
- Mapping from database to data model
- 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 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 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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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 |