In major ERPs, the fixed asset solution operates on a subledger-to-ledger relationship with the General Ledger. The Accounting clerk needs to control that the balances are correct in General Ledger compared to the Fixed asset balances in the subledger. It should part of the month end closing procedure.
How do we do that in D365 F&O?
We can run the Fixed asset roll forward report. However, financial dimensions are not displayed and it is missing in my opinion. It can be handy to have them to get the same analytical dimensions as in the general ledger. For instance, in the retail industry having the net book value per store is a must.
Navigate to the Electronic reporting workspace.
Select the Reporting configurations tile.
Select Fixed assets model, then Create configuration.
Tick Derive from Name: Fixed assets model, Microsoft.
Type in the model name.
Click Create configuration.
Select Designer.
Extend AssetRollForwardTotalsByGroup, then click New.
Add a field of the type string Hana_BU.
Add a field of the type string Hana_DEPT.
Select map model to datasource, then Designer.
Select calculated field (in right column), and add it under AssetRollForwardTmp.
Name $Hana_Book.
Click formula
Define below formula.
IF(ISEMPTY(@.'>Relations'.AssetTable.'<Relations'.'AssetBook.AssetTable_AssertId'),
EMPTYLIST(@.'>Relations'.AssetTable.'<Relations'.'AssetBook.AssetTable_AssertId'),
@.'>Relations'.AssetTable.'<Relations'.'AssetBook.AssetTable_AssertId')
Select calculated field and add it under $Hana_Book, name: $BU.
Add the following formula:
FIRSTORNULL(WHERE(AssetRollForwardTmp.'$Hana_Book'.'DefaultDimension.Dimension'.'Main account and dimensions',
AssetRollForwardTmp.'$Hana_Book'.'DefaultDimension.Dimension'.'Main account and dimensions'.Definition.Name="BusinessUnit"))
Repeat the same steps for the department.
In the Data model column, select Hana_BU from Asset roll forward totals by group.
Click Edit.
Repeat the step for Department.
Go back and complete the data model.
Select fixed asset roll forward, and click create configuration - select Derive from Name: Fixed
asset roll forward, Microsoft.
Add name: Hana fixed asset roll forward and select data model: Hana fixed assets model.
Click Create configuration.
Click Designer.
Click Attachments and Open and Save it with some new name (i.e. Hana_FixedAssetRollForward).
Open the excel file and modify it.
Add two new columns – select row 8 and 9 under new column and click ‘marge &Center’.
Add the text, i.e. [Business_unit] in merged row 8/9.
Click Formulas > Name manager, and click New
- Name: Business_unit_label
- Scope: Workbook
- Refers to: =Report!$C$8 (this need to refer to the column/row that has been added)
Click Formulas > Name manager, and click New
- Name: Business_unit_value
- Scope: Workbook
- Refers to: =Report!$C$10 (this need to refer to the column that has been added and one
row below)
Repeat the same steps for Department.
Save the excel file and close it.
Click Import > Update from Excel.
Extend the report design Excel > page_range > fag_range, then select the fields that has been added.
Select Business_unit_label, then click Edit formula > Translate, select BusinessUnit, and finally click Translate > Save.
Repeat the same step for Department.
Extend the report design > Excel > page_range > fag_range > vm_range, select fields that has
been added.
Business_unit_value > Edit Formula.
Go to AssetRollForwardTotalsByGroup > Lines > Hana_BU.
Click Add data source > Save.
Repeat the same steps for department.
Finally, complete the configuration.
To see the result, go to Fixed assets > Inquiries and reports > Transaction reports > Fixed asset roll forward
Select the custom format mapping you created.
The final result looks like that.
Note: the performance is downgraded honestly. If you have a better approach, I would love to hear about it.