Leverage Electronic Reporting & MS Excel to extend the standard D365FO six (06) aging buckets - Part 1

Background:

The background of this blog post is one of my client who asks me how to extend the standard D365FO 06 aging buckets without a system modification and without doing a lot a manipulations in Excel by exporting multiple times the standard D365FO customer aging report.

Solution:

The proposed solution described here is fictitious since its from the Microsoft Contoso trial environment where I used USMF legal entity for demo purposes by leveraging out-of the box tool within D365FO called Electronic Reporting (ER).

After a coordination session with Julien HUMBERT , I decided to split the post into two parts since I will be using two (02) different Data sources:

• ??The CustomerAgingDataStorage Data Entity - Part 1
• ??The CustTransOpen Table - Part 2

The way how the proposed solution is realized is in three steps for both parts:

??Step 1: Prepare the MS Excel Template

??Step 2: Create the Electronic Reporting configuration

???? Step 2.1: Create the ER Model

???? Step 2.2: Create the ER Model Mapping

???? Step 2.3: Create the ER Format

??Step 3: Run the Electronic Reporting customer aging report

Now, let's go through the solution step by step. ;)

Part 1: ER customer aging report based on CustomerAgingDataStorage data entity

Step 1: Prepare the MS Excel Template

The MS Excel Template used in this first part looks like the one shown on the print screen below:

The green fields come from Electronic reporting and the black one are calculated within MS Excel.

• Find below the MS Excel Name Manager:

• Find below the MS Excel formulas used for the black fields:

??Step 2: Create the Electronic Reporting configuration

Follow the below steps in order to create the corresponding ER configuration:

Step 2.1: Create the ER Model

1. Create the ER configuration Model as shown below:

2. Change the ER Model status to Completed

? Step 2.2: Create the ER Model Mapping

1. Create the ER configuration Model Mapping as shown below:

2. Add the Data Sources to the Model mapping designer screen and map the Data source to the Data model as shown below (here the interesting things start):

The CustomerAgingDataStorage Data entity and CustTrans Table Data sources are added to the Model mapping designer.

3. Add the following calculated fields to CustomerAgingDataStorage Data entity data source >> use Add button after selection CustomerAgingDataStorage Data source instead of Add root this time.

• DocDate

• TransDate

• DueDate

4. Add $CustomerAgingDataStorageEntity calculated field to the Model mapping designer in order to filter out zero balance >> This point is scenario specific.

5. Add GB_$CustomerAgingDataStorageEntity Group by field to the Model mapping designer in order to take into consideration only single transaction in case of multiple running of the Customer aging data storage on the same CutOffDate >> This point is scenario specific.

6. Map Data source to Data model by using Bind funtionality as shown below:

7. Save and validate the Model mapping for error & warning check purposes.

8. Change the ER Model mapping configuration status to Completed.

Step 2.3: Create the ER Format

1. Create the ER format as shown below:

2. Import the MS Excel Template created in step 1.

The Format designer screen should looks like below after the Template import is successful:

3. Map format to Data model and give a file name as shown below:

4. Save and validate the Format for error & warning check purposes.

5. Change the ER Format configuration status to Completed.

??Step 3: Run the Electronic Reporting customer aging report

At this stage we are ready to go.

1. Run the Customer aging data storage report by following the path as below:

2. Create the Customer aging data storage reports:

You can create many Customer aging data storage reports when possible.

3. Run the ER customer aging report.

4. Enjoy the Customer aging report in MS Excel.

You could apply filters & tables join and/or run the ER customer aging report in batch mode in case of high data volume.

That's all for today. See you in Part 2!

Thank you very much to Julien HUMBERT for the coordination session and for the trick.

Jean Donald KOUADIO Robert Steiner Pierre Fournier Bruno Castagnetti Sylvain Bouet Patrick PHRASSIAS Laure Ouvrelle Matthieu Ricard Rémi SALMON Vacaba DIABY