Add lookup values for financial dimensions to Excel templates

This article provides information about how you can add the ability to look up dimension values in Microsoft Excel templates.

The only value that is present on Microsoft Excel templates after installation is MainAccount. This is the only dimension that all customers will have. To add the dimensions to Microsoft Excel templates, you need to complete the steps in the Add dimensions to Excel templates article. After you have added the dimensions, if you want the ability to look up a list of dimension values, complete the steps in this article.

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.

     Financial dimension 'Use values from'	Related entity
     < Custom dimension >	DimAttributeFinancialTagEntity
     Agreements	DimAttributeAgreementHeaderExt_RUEntity
     Bank accounts	DimAttributeBankAccountTableEntity
     BusinessUnits	DimAttributeOMBusinessUnitEntity
     Campaigns	DimAttributeSmmCampaignTableEntity
     Cash accounts	DimAttributeRCashTable_RUEntity
     Cost centers	DimAttributeOMCostCenterEntity
     Customer groups	DimAttributeCustGroupEntity
     Customers	DimAttributeCustTableEntity
     Deferrals	DimAttributeRDeferralsTable_RUEntity
     Departments	DimAttributeOMDepartmentEntity
     Expense and income codes	DimAttributeRTax25ProfitTable_RUEntity
     Expense Purposes	DimAttributeTrvTravelTxtEntity
     Fiscal establishments	DimAttributeFiscalEstablishment_BREntity
     Fixed asset groups	DimAttributeAssetGroupEntity
     Fixed assets	DimAttributeAssetTableEntity
     Fixed assets (Russia)	DimAttributeRAssetTable_RUEntity
     Funds	DimAttributeLedgerFund_PSN
     Item groups	DimAttributeInventItemGroupEntity
     Items	DimAttributeInventTableEntity
     Jobs	DimAttributeHcmJobEntity
     Legal entities	DimAttributeCompanyInfoEntity
     POS registers	DimAttributeRetailTerminalEntity
     Positions	DimAttributeHcmPositionEntity
     Project contracts	DimAttributeProjInvoiceTableEntity
     Project groups	DimAttributeProjGroupEntity
     Projects	DimAttributeProjTableEntity
     Prospects	DimAttributeSmmBusRelTableEntity
     Resource groups	DimAttributeWrkCtrResourceGroupEntity
     Resources	DimAttributeWrkCtrTableEntity
     Stores	DimAttributeRetailStoreEntity
     Value streams	DimAttributeOMValueStreamEntity
     Vendor groups	DimAttributeVendGroupEntity
     Vendors	DimAttributeVendTableEntity
     Workers	DimAttributeHcmWorkerEntity

   • 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. In the Related field, type Value. The new relation is similar to the following example.

   DimensionCombinationEntity.DimensionIntegration.Department==DimAttributeOMDepartmentEntity.Value
   

   

9. Build the project and then synchronize it with the database.

Additional resources

Add lookup values for financial dimensions to Excel templates

Extensibility home page

Create Open in Excel experiences