Add dimensions to Excel templates

Important

Dynamics 365 for Finance and Operations is now being licensed as Dynamics 365 Finance and Dynamics 365 Supply Chain Management. For more information about these licensing changes, see Dynamics 365 Licensing Update.

This topic provides information about dimensions, dimensions that have entities, and the dimension controls that are available.

The only value that is present on Microsoft Excel templates after installation is the MainAccount. This is the only dimension that all customers will have. To add the dimensions to Microsoft Excel templates you need to complete the following steps:

  1. Add dimensions to the DimensionCombinationEntity or the DimensionSet entity.
  2. Add the dimensions to each template where you want dimensions in separate columns. For more information, see Create Open in Excel experiences.
  3. Add the capability to look up financial dimension values in Excel.
  4. Publish the template.

This topic shows how to modify DimensionCombinationEntity to enable the dimensions in columns for Excel. The same steps can be used to modify the DimensionSet entity.

Note

This information is subject to change for each release. Therefore, be sure to check back frequently for the most up-to-date information.

Add dimensions before Dynamics 365 for Finance and Operations

To support interactions with dimensions as columns, for example, in the Microsoft Excel integration, you must first create the dimension columns through a customization.

  1. Open the Application Explorer in Visual Studio (View > Application Explorer).

  2. Navigate to DimensionCombinationEntity (AOT > Data Model > Data Entities).

  3. Right-click on the entity and choose Customize.

    5

  4. Open the designer for the entity that you want to modify, in this example DimensionCombinationEntity.

  5. Create a new private static method that returns a str named departmentValue.

  6. In this method, you must get the dimension's value from DimensionAttributeValueCombination. The final method will look something like this.

    /// <summary>
    /// This method returns the value of Department.
    /// </summary>
    private static str departmentValue()
    {     
        Name dimensionName = 'Department';
        str sqlStatement;
    
        DimensionAttribute dimensionAttribute = DimensionAttribute::findByName(dimensionName);
    
        if (!dimensionAttribute)
        {
            sqlStatement = SysComputedColumn::returnLiteral('');
        }
        else
        {
            sqlStatement = strFmt('SELECT TOP 1 T1.%1 ', dimensionAttribute.DimensionValueColumnName);
        }
    
        return sqlStatement;
    }
    
  7. Create a new "string unmapped field" on the entity:

    • Set the Name property to the dimension name, Department.

    • Set the Extended Data Type property to DimensionValue.

    • Set the DataEntityView Method property to the method that you created earlier (for example, departmentValue).

    • Set the Label property to the dimension name Department.

      6

  8. Repeat steps 5-7 for each dimension that you want to add, changing the dimension name to the appropriate dimension.

  9. Compile the project, and then synchronize it with the database.

    8

  10. Your customization is now complete. You can test it in SQL using the following statement.

    select * from DIMENSIONCOMBINATIONENTITY
    

Additional resources

Migrate default dimensions controls to Dimension Entry controls

Uptake of Dimension Entry controls

Extensibility home page