.png)
Simply put, ER is a framework built on-top of the existing SSRS reporting framework in D365
It allows users to generate reports (configurable business documents) and easily define formats that comply with regional legal requirements
Targeted at business users instead of developers
Microsoft continually publishes configurations that you can use and customize
ER allows custom reports to be:
Created from scratch
Derived from an existing ER configuration
ER components:
• Data Model Component: abstract representation of information the report will contain
• Data Sources Component: defines where the reporting data comes from
• Format Component: the report design, containing placeholders for your report data
ER status:
• Draft: the configuration is in development
• Complete: the configuration is visible to the D365 reporting framework
• Shared: the configuration is uploaded to the project’s “asset library” in LCS
Overview:
ER lets you define electronic format structures and data models. First, you can define data models from scratch and/or adjust existing ones that come OTB. Here, you can use a formula language that resembles the Excel language for data transformation. Following, with ER you can set up how the format structures should be filled by data fields from such data model.
You can use ER to configure formats for both incoming and outgoing electronic documents in accordance with the legal requirements of various countries and regions.
ER currently supports the TEXT, XML, JSON, PDF, Microsoft Word, Microsoft Excel, and OPENXML worksheet formats.
Configuration instead of coding: The ER engine is targeted at techno-functional business users instead of developers.
Easy upgradeable: The Microsoft Dynamics Lifecycle Services (LCS) Assets library can be used as a repository for ER configurations, for version exchange.
The ER engine has the following capabilities:
• It represents a single shared tool for electronic reporting in different domains and replaces more than 20 different engines that do some type of electronic reporting for Finance and Operations.
• It makes a report's format insulated from the current implementation. In other words, the format is applicable for different versions.
• It supports the creation of a custom format that is based on an original format. It also includes capabilities for automatically upgrading the customized format when the original format is changed because of localization/customization requirements.
• It becomes the primary standard tool to support localization requirements in electronic reporting, both for Microsoft and for Microsoft partners.
• It supports the ability to distribute formats to partners and customers through Microsoft Dynamics Lifecycle Services (LCS).
Configurations
1. Creating a new configuration provider
Under Org admin/ER, we create a new configuration provider. (Here: Electronic Reporting Configuration Provider) The ER provider is the party identifier that is used to indicate the author (owner) of each ER configuration. ER lets you manage the list of configuration providers. Format configurations that are released for electronic documents as part of the Finance and Operations solution are marked as owned by the Microsoft configuration provider. For custom ER documents we always want to create a separate configuration provider.
After doing so, we set such as active. Under repositories, create a new configuration repository. Click “Open” on the action pane to import OTB templates provided by MSFT (via MSFT configuration provider)
An ER repository stores ER configurations. The following types of ER repositories are currently supported:
LCS shared library
LCS project
File system
RCS
Operations resources
Global repository
2. Creating a new reporting configuration
An ER configuration is the wrapper of a particular ER component. That component can be either a data model component or a format component. A configuration can include different versions of an ER component. Each configuration is marked as owned by a specific configuration provider. Each model configuration contains a data model component. A new format configuration can be derived from a specific data model configuration. In the configuration tree, the format configuration that is created appears as a child of the original data model configuration. The format configuration that is created contains a format component. The data model component of the original model configuration is automatically inserted into the format component of the child format configuration as a default data source. An ER configuration is shared for application companies.
In the Electronic Reporting workspace, select the Reporting configurations tile and follow the steps below:
Click “Create configuration” on the action pane and choose Root
Provide a name and a description (Here: “Purchase Order Model”)
Notice that the active Configuration provider was selected automatically (Here: Electronic Reporting Configuration Provider)
Click “Create configuration”
Process flow:
a) outbound:
b) inbound:
1. Defining the Data model:
Create a mode root node for your data model.
Model data (add fields) as child nodes to the root node created above.
Create the data model means that we defined how data is stored when extracted from the database.
Select Data model on configurations form under the configuration components tab
Click “+ New” to add new data containers and design data model via parent-child node structure
Check the “Is enumeration” flag if true
Add label and description as well as enumeration if applicable
Specify the correct data type (Note: This is important for the mapping later)
After you are done with the data model change status to complete at the configurations form in the versions tab.
2. Mapping from database to data model
In the previous step we defined how the data is stored. The next step is to define which data we’re selecting from the database. (only for data elements that are not flagged as enumeration) We are doing such via data source mapping. In the data model form where we’ve defined the data model previously, click “Map model to datasource” on the actionpane.
Press “New” to create a new data mapping model.
Under Definition, select the root node of the data model we’ve just created prior.
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.
Now, under Designer on the action pane add the correct table via “add root” as data source.
Excurse: Find the correct table name as well as field name by navigating to the data field that you want to reference and right clicking/form information. See below.
In this example, we added the following:
The “PurchTable” as Table record data source type.
A user input parameter data source for the “PurchaseOrderID” field.
A calculated field data source for “SelectedPO” Here, we added a filter formula:
Filter(PurchTable,PurchTable.PurchId='paramaters.PurchId')
As a next step, we will bind each of the prior specified data points from the data model with the correct fields in the data sources just added. If the types are identical, the Bind button is enabled. Otherwise, you will need to use the Edit button for conversion. (means, applying conversion formulas)
See end result below:
As a next step, we can try out our data model by clicking “Run” on the model to datasource mapping form. The below xml file is the result.
See the xml output for PO 000032.
3. Mapping from data model to Excel output format
As a next step we want to map the data model to an excel output format. Therefore, we select the node of the data model that we just defined and then create a new child configuration as “Format based on the model”. Before we do that, we need to mark the parent reporting configuration as not draft anymore by clicking – Change status/Complete.
As a next step, we will select the newly created format child configuration node and map the data sources to the format under actionpane/Design. In the format designer, we first add the excel file format as root element. It asks you to upload the excel format here. Via “attachments”.
In the excel sheet itself make sure you define the cells with names as well as ranges with names. (via right click/define in excel) This is required for the cell/range mapping in D365FO later. See for example for the Purchase order header range below.
As a next step, we add format elements as a child element via “add” function as follows for our demo example:
Sheet
Header
Range
Cell
Cell
Cell
Range
Cell
Cell
Cell
Cell
Cell
Footer
The end results should look like:
Note: We referenced the ranges/cells that we defined in excel prior, in the excel range field here on the right-hand side for each format element.
As a next step, we are mapping the format elements to the data model via bind function.
As a last step, we add validation rules to determine how ER should react when certain conditions are met/not met (e.g., empty records). Here:
NOT(ISEMPTY(model.’Purchase order lines’))
We validate and run the export.
See below the final excel extract for PO 000014.
