Leverage Electronic Reporting & MS Excel to extend the standard D365FO six (06) aging buckets - Part 2
Hello and welcome to this new blog post series on Leveraging Electronic Reporting & MS Excel to extend the standard D365FO six (06) aging buckets.
In the Part 1, I've demonstrated how to use Electronic Reporting (ER) and MS Excel to extend the standard D365FO six (06) aging buckets based on CustomerAgingDataStorage Data Entity as Data source within ER.
In the today blog post, I will be using the CustTransOpen Table as Data source.
It is important to keep in mind that the CustTransOpen table always shows the total open customer balance regardless any key date. Saying that, use the CustTransOpen table to perform a direct reconciliation against out-of-the box D365FO customer aging report could lead to a reconciliation issue as per a specific cut-off date.
As I did in the Part 1, the way how the proposed solution is realized is in three steps:
??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. ??
Step 1: Prepare the MS Excel Template
I will be using the MS Excel Template below:
In this scenario, I've added the CustAccountResp field since my client would like to have the information about the Employee responsible (see the screenshot below):
Below are the MS Excel formulas used for the black fields:
Step 2: Create the Electronic Reporting configuration
2.1 - Create the ER configuration model and change the status to Completed as shown below:
2.2 - Create the ER Model mapping
a) Add the following Data sources to the Model mapping designer:
b) Add the CF_CustAccountResp calculated field to CustTransOpen table as shown below:
领英推荐
c) Bind Data source to Data model accordingly and change the status to Completed as shown below:
2.3 - Create the ER Format
a) Create the ER format configuration, import the MS Excel Template created in step 1) and perform the bindings accordingly as shown below:
b) Define the file name and change the status to Completed
Step 3: Run the Electronic Reporting customer aging report
At this stage the ER configuration is done and we are ready to go.
Run the ER Customer aging report and enjoy!
That concludes our ER & MS Excel Customer aging report blog post series.
I will be back soon for the new blog posts on #ER within #D365FO.
Jean Donald KOUADIO Robert Steiner Martin Sengel Laure Ouvrelle Pierre Fournier Bruno Castagnetti Patrick PHRASSIAS Sylvain Bouet Matthieu Ricard Rémi SALMON Vacaba DIABY Marie Michèle André Julien HUMBERT Aurélien CLERE Aurelien Brungard Guillaume FAGES
ERP Consultant #Freelance #D365FO #Finance and much more
1 年Hello Arnaud, just one comment regarding the formula you have mentioned to set the file name. I think you can do simplier like this: CONCATENATE(??AYO_ARblabla*??, DATEFORMAT(TODAY(), ??MMddyyyy??)). Should be enough. ??
Dynamics 365 ERP Director chez Inetum
1 年Top Arnaud Duplex YOUKAM NONO !!