Edit

ER Configure format to do counting and summing (Part 2 - Configure computations)

Note

Community interest groups have now moved from Yammer to Microsoft Viva Engage. To join a Viva Engage community and take part in the latest discussions, fill out the Request access to Finance and Operations Viva Engage Community form and choose the community you want to join.

The following steps explain how a user assigned to the system administrator or electronic reporting developer role can configure an Electronic reporting (ER) format to do counting and summing based on data of the already generated text output. You can perform these steps in any company.

To complete these steps, you must first complete the steps in the "ER Configure format to do counting and summing (Part 1: Create format)" procedure.

This procedure is for a feature that was added in Dynamics 365 for Operations version 1611.

Create a format configuration to add counting and summing details

  1. Go to Organization administration > Workspaces > Electronic reporting.
  2. Select Reporting configurations.
  3. In the tree, expand Intrastat model.
  4. In the tree, select Intrastat model\Intrastat (DE).
    • To customize the format that Microsoft provides, add lines with summary details at the end of the Intrastat report. Derive your own instance of the Intrastat configuration from the Microsoft instance to make modifications.
  5. Select Create configuration to open the dialog.
  6. In the New field, enter Intrastat (DE), Microsoft.
  7. In the Name field, type Intrastat (DE) with counting & summing.
  8. Select Create configuration.

Configure this report to count and sum based on output details

  1. Select Designer.
  2. Select Yes in the Collect output details field.
    • This flag activates at run-time the process of collecting output details for generating the Intrastat file.
    • You need to count for different Intrastat directions, so add a dedicated model enumeration to the data sources' list of this format configuration.
  3. Select the Mapping tab.
  4. Select Add root to open the drop dialog.
  5. In the tree, select Data model\Enumeration.
  6. In the Name field, type Direction.
  7. In the Model enumeration field, enter or select a value.
    • Select the value Direction.
  8. Select OK.
  9. Select Add root to open the drop dialog.
  10. In the tree, select Functions\Calculated field.
  11. In the Name field, type $BlockName.
  12. Select Edit formula.
  13. In the Formula field, enter "block".
  14. Select Save.
  15. Close the page.
  16. Select OK.
  17. Select Add root to open the drop dialog.
  18. In the tree, select Functions\Calculated field.
  19. In the Name field, type $RecName.
  20. Select Edit formula.
  21. In the Formula field, enter "record".
  22. Select Save.
  23. Close the page.
  24. Select OK.
  25. Select Add root to open the drop dialog.
  26. In the tree, select Functions\Calculated field.
  27. In the Name field, type $InvName.
  28. Select Edit formula.
  29. In the Formula field, enter "InvoicedAmountEUR".
  30. Select Save.
  31. Close the page.
  32. Select OK.
  33. In the tree, select Intrastat\Data.
  34. Select Edit for the Collected data key name field.
  35. Select Add data source.
    • Use $BlockName.
  36. Select Save.
  37. Close the page.
  38. Select Edit for the Collected data key value field.
  39. In the Formula field, enter IF(Intrastat.CommodityRecord.Direction=Direction.Import, "Import", "Export").
    • Use IF(Intrastat.CommodityRecord.Direction=Direction.Import, "Import", "Export").
  40. Select Save.
  41. Close the page.
    • Count the lines of this sequence. The results are used with the name block separately for different directions. Value Import is used for any arrivals Intrastat transactions. The value Export is used for any Intrastat dispatches transactions. Consider this to be a virtual Excel spreadsheet. For each transaction a row where the first column block is filled with the values Import and Export accordingly.
  42. In the tree, expand Intrastat\Data: Sequence.
  43. In the tree, select Intrastat\Data: Sequence\Arrivals?.
  44. Select Edit for the Collected data key name field.
    • Count the lines of this sequence. The results are stored using the name record.
  45. In the tree, select $RecName.
  46. Select Add data source.
  47. Select Save.
  48. Close the page.
  49. Select Edit for the Collected data key value field.
  50. In the Formula field, enter Intrastat.CommodityRecord.CommodityCode.
  51. Select Save.
  52. Close the page.
    • Count the lines in this sequence. Use the results with the name "record" for different commodity codes. Consider this count as a virtual Excel spreadsheet. For each transaction, create a row where the first column "block" contains the values "Import" and "Export" accordingly, and the second column "record" contains the commodity code value.
  53. In the tree, select Intrastat\Data: Sequence\Dispatches?.
  54. Select Edit for the Collected data key name field.
  55. In the tree, select $RecName.
  56. Select Add data source.
  57. Select Save.
  58. Close the page.
  59. Select Edit for the Collected data key value field.
  60. In the Formula field, enter Intrastat.CommodityRecord.CommodityCode.
  61. Select Save.
  62. Close the page.
  63. In the tree, expand Intrastat\Data: Sequence\Dispatches: Sequence?.
  64. In the tree, expand Intrastat\Data: Sequence\Dispatches: Sequence?\Record = Intrastat.CommodityRecord.
  65. Select the Format tab.
  66. In the tree, select Intrastat\Data\Dispatches\Record\Invoice amount EUR.
  67. Select the Mapping tab.
  68. Select Edit for the Collected data key name field.
  69. In the tree, select $InvName.
  70. Select Add data source.
  71. Select Save.
  72. Close the page.
    • Summarize the invoiced amount values for lines in this sequence. Use the results with the name "InvoicedAmountEUR" for different Intrastat directions and commodity codes. Consider this summary as a virtual creation in an Excel spreadsheet. For each transaction, create a row where the first column "block" contains the values "Import" and "Export" accordingly. The second column "record" contains the commodity code value, and the third column "InvoicedAmountEUR" contains the invoice amount value.
  73. In the tree, expand Intrastat\Data\Arrivals?.
  74. In the tree, expand Intrastat\Data\Arrivals?\Record = Intrastat.CommodityRecord.
  75. Select the Format tab.
  76. In the tree, select Intrastat\Data\Arrivals\Record\Invoice amount EUR.
  77. Select the Mapping tab.
  78. Select Edit for the Collected data key name field.
  79. In the tree, select $InvName.
  80. Select Add data source.
  81. Select Save.
  82. Close the page.
  83. Select Save.
  84. Close the page.
  • Last updated on