呆子獅的夢想世界

The Excel integration is one of the powerful tools in Microsoft Dynamics 365 for Finance and Operations. This is helpful tool for accountants to push journal entries through Excel to Microsoft Dynamics 365 for Finance and Operations. The out of the box Excel integration does not have the financial dimensions values, but it could be exposed to the Excel integrations. Of course, the importance of financial dimensions specially the entry will be validated based on the account structure setup.

This blog post will illustrate step by step how to Expose the Financial dimensions on Excel integration. This will need to follow certain steps on Dynamics 365 for Finance and Operations client, Visual Studio, and Excel.

Microsoft Dynamics 365 FFO client

• Add financial dimension configuration for integrating applications

Visual studio

• Add financial, dimensions for OData
• Build and synchronize the model

Excel

• Add dimensions fields.

• 

Go to D365 FFO, General ledger| Chart of accounts| Dimensions| Financial dimension configuration for integrating applications. Ensure that the required financial dimensions added on integration applications form.

Then move to Visual studio| Dynamics 365| Add-ins| Add financial dimensions for OData

Add financial dimensions, and link it to Dimensions model. (choose your own model instead of dimension model)

Dimension fields have been added to dimension integration entity.

Build and synchronize the model

Move back to Microsoft Dynamics 365 for Finance and Operations client, and create a new General journal, then jump to Excel integration.

Enable editing on Excel file, then move to Design, complete data entry and click publish

Select the following values of which represents your financial dimensions and add them to the selected fields.

Financial dimensions represents account

Account.BusinessUnit
Account.CostCenter
Account.Department

Financial dimensions represents offset account

OffsetAccount.BusinessUnit
OffsetAccount.CostCenter
OffsetAccount.Department

The financial dimensions have been added to Excel template, select the proper values and publish it to Microsoft Dynamics 365 for Finance and Operations

Go back to Microsoft Dynamics 365 for Finance and Operations client, the general journal entry is updated,  and it has financial dimensions.

Ref:  https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/dimensions-overview

--------------------------------------------------------------------------------------------

Add lookup values for financial dimensions to Excel templates

1. In Visual Studio, open the project where you modified DimensionCombinationEntity or DimensionSetEntity.
2. Right-click DimensionCombinationEntity or DimensionSetEntity. Select Open.
3. Right click Relations. Select New and then click Relation.
4. In the Properties pane, set the following properties.
   • Validate - No
   • Cardinality - ZeroMore
   • Name - Enter the name of the financial dimension, such as Department.
   • Related Data Entity - Select the entity for the financial dimension that you entered in the Name field. The following table contains a list of the financial dimensions and the related entities.

• Related Data Entity Cardinality - ZeroOne
• Related Data Entity Role - Enter a unique name, such as "Dimension Department Lookup".
• Relationship Type - Association
• Role - Enter a unique name, such as Dimension Department.

5. Right-click the Financial dimension name under Relations.
6. Select New, and then click Normal.
7. In the Properties pane, choose the name of the Financial dimension in the Field
8. 
9. ​​ 
10. Build the project and then synchronize it with the database.

Ref:  https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/add-dimensions-excel-templates