From Dynamics365 Team Blog: How to add custom fields on Fixed asset roll forward report.

Title: How to add custom fields on Fixed asset roll forward report

Description: With this document you can add fields form fixed asset table and books

Repro steps

1. Electronic reporting – Repositories – LCS – Open -download Fixed asset roll forward and fixed assets model

2. Electronic reporting – Report configurations –
a. Select fixed assets model and click create configuration – select Derive from Name: Fixed assets model, Microsoft, add name: fixed assets model custom – create configuration

b. Select fixed asset roll forward and click create configuration – select Derive from Name: Fixed asset roll forward, Microsoft –  add name: fixed asset roll forward custom and select data model: fixed assets model custom – create configuration

Remark: Do all below changes on derive configuration.
3. Electronic reporting – Report configurations – select fixed asset roll forward custom – click Attachments and Open and Save it with some new name (i.e. FixedAssetRollForward_custom)
4. Open the excel file and modify it
a. Add new column – select row 8 and 9 under new column and click ‘marge &Center’

b. Add the text, i.e. [Fixed_asset_search_label] in merged row 8/9

c. Click Formulas > Name manager > and click New >
– Name: Fixed_asset_search_label
– Scope: Workbook
– Refers to: =Report!$C$8(this need to refer to the column/row that has been added)

d. Click Formulas > Name manager > and click New >
– Name: Fixed_asset_search_value
– Scope: Workbook
– Refers to: =Report!$C$10 (this need to refer to the column that has been added and one row below)

e. Repeat the steps from a-d for below fields:
– Type_lable and type_value
– Location_label and location_value
– Serial_number_label and serial_number_value
– Model_year_label and model_year_value
– Percentage_label and percentage_value
– CostCenter_label and costCenter_value

f. Save the excel file and close it
Remarks: Make sure that all excel documents are closed.
5. Electronic reporting – Report configurations – select fixed assets model custom – Designer –
a. select Asset roll forward and click New ‘Child of an active node’ with type String, called CostCenter under Asset rollforward

b. Select CostCenter and click map model to datasource > Designer
– Select calculated field (in right column) and Add it under AssetRollForwardTmp, name: $Book and 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')

– Save it and go back
– Select calculated field and Add it under $Book, name: $CostCenter

– Add formula and Save it
FIRSTORNULL(WHERE(@.'DefaultDimension.Dimension'.'Main account and dimensions', @.'DefaultDimension.Dimension'.'Main account and dimensions'.Definition.Name="CostCenter"))

c. In third column ‘Data model’, select CostCenter from Asset roll forward and click Edit 

– Add Formula: AssetRollForwardTmp.'$Book'.'$CostCenter'.Value.Description

d. Save all changes
6. Electronic reporting – Report configurations – select fixed assets model custom – Designer
a. select Asset roll forward and click New ‘Child of an active node’ for each:
– with type String, called SearchName
– with type String, called Location
– with type String, called SerialNumber
– with type String, called ModelYear
– with type Real, called Percentage
– with type String, called MainAssetID

b. Click map model to dataSource – Designer – third column ‘Data model’, extend Asset roll forward, find each of above string and click Edit
– SearchName > AssetRollForwardTmp.'>Relations'.AssetTable.NameAlias

– Location > AssetRollForwardTmp.'>Relations'.AssetTable.Location

– SerialNumber > AssetRollForwardTmp.'>Relations'.AssetTable.SerialNum

– ModelYear > AssetRollForwardTmp.'>Relations'.AssetTable.ModelYear

– Percentage > @.'>Relations'.AssetBookId.'depreciationProfile()'.Percentage

– MainAssetId > AssetRollForwardTmp.'>Relations'.AssetTable.MainAssetId

– Save all changes
7. Electronic reporting – Report configurations – select fixed assets model custom – Designer –
a. select Asset roll forward and click New ‘Child of an active node’
– with item type: String, called: AssetTypeLabel

– with item type: Enum, called: AssetType

b. click Map Model to datasource – Designer –
– select Calculated filed in column “data source types” and click Add root – Name: AssetTypeAsList

– Eidt Formula > LISTOFFIELDS(AssetType)

– extend the AssetTypeAsList – select Calculated filed in column “data source types” and click Add – Name: Enum 

– click Edit add formula: GETENUMVALUEBYNAME(AssetType, @.Name)

– select Calculated filed in column “data source types” and click Add under AssetRollForwardTmp – Name: $AssetTypeRec – click Edit formula: FIRSTORNULL(WHERE(AssetTypeAsList, AssetTypeAsList.Enum = @.'>Relations'.AssetTable.AssetType))

– go to third column ‘Data model’, extend Asset roll forward, select AssetType and click Edit >
Add formula
CASE(@.'>Relations'.AssetId.'>Relations'.AssetTable.AssetType,
AssetType.Deferred_JP, Type.Deferred,
AssetType.Financial, Type.Financial,
AssetType.Goodwill, Type.Goodwill,
AssetType.Land_RU, Type.GroundArea,
AssetType.Intangible, Type.Intangible,
AssetType.LandBuilding, Type.LandAndBuildings,
AssetType.LowCostAssets_RU,Type.NVFA,
AssetType.Rigging_RU, Type.SpecialRigging,
AssetType.Tangible, Type.Tangible,
AssetType.Vehicle_RU, Type.Vehicle,
AssetType.Cloths_RU, Type.WorkingClothes,
AssetType.Other, Type.Other
)

– select AssetTypeLabel and click Edit > Add formula: @.'$AssetTypeRec'.Name

– save changes and close the designer for model
– On the data model make sure that You have a enumeration values

Note: If there is no enumeration list, make sure that there is item reference added to type (blue text under Node)
8. Report configuration > Select (fixed asset model custom) Fixed asset roll forward custom > Designer

a. Click Import > Update from Excel
– Select the template ‘fixed asset roll forward custom’ done in step 4 and click OK

Note: Make sure that excel is not opened

b. Extend the report design > Excel > page_range > fag_range > select fields that has been added
– Fixed_asset_search_label > Edit formula > Translate > SearchName > click Translate > Save

– CostCenter_label > Edit formula > Translate > CostCenter (define the text in en-us) > click Translate > Save

– Type_label > Edit formula > Translate > Type > click Translate > Save

– Location_label > Edit formula > Translate > Location > click Translate > Save

– Serial_number_label> Edit formula > Translate > SerialNumber > click Translate > Save

– Model_year_label > Edit formula > Translate > ModelYear > click Translate > Save

– Percentage_label > Edit formula > Translate > Percentage > click Translate > Save

– MainAssetID_label > Edit formula > Translate > MainFixedAsset> click Translate > Save

c. Extend the report design > Excel > page_range > fag_range > vm_range > select fields that has been added
– Fixed_asset_search_value > Edit Formula > $AssetByGroup > lines > SearchName > +Add data source > Save

– CostCenter_value> Edit Formula > $AssetByGroup > lines > CostCenter > +Add data source > Save

– Type_value> Edit Formula > $AssetByGroup > lines > AssetTypeLabel > +Add data source > Save

– Location_value> Edit Formula > $AssetByGroup > lines > Location > +Add data source > Save

– Serial_number_value> Edit Formula > $AssetByGroup > lines > SerialNumber > +Add data source > Save

– Model_year_value > Edit Formula > $AssetByGroup > lines > ModelYear > +Add data source > Save

– Percentage_value> Edit Formula > $AssetByGroup > lines > Percentage > +Add data source > Save

– MainAssetID_value> Edit Formula > $AssetByGroup > lines > MainAssetId > +Add data source > Save

d. Save all changes
e. Change status from Draft to Complete on report configurations
f. Make sure that ‘fixed assets model custom’ is set as default model mapping
RESULT

Additional details:
1. If You have formatting issues, with date type field, it might require to add a field under range, example for ‘Places in service’ with correct format, by performing below steps:
a. Be active on vm_range > Add new Range >
b. Fill the data as below:
Name: place_in_service_range
Excel range: place_in_service_value
c. Be active on place_in_service_range > Add > text > dateTime
d. Fill the Name: place_in_service_value > click OK
e. Move this to the correct position by using move up/down button  
2. Have the possibility to create a pivot table in excel by changing the format of cells, can be done by following steps:
a. In the excel template add a correct style and numbering format for the amounts fields and update the excel template in the GER
b. change the range/numeric format to just the cell
Example: Period acquisition have a range

– Be active on vm_range > add Cell with the datatype: Real
Name: period_acquisitions_value, excel range: period_acquisitions_value
– Move this to the proper position (below opening_acquisitions_range)
– Mapping > Edit formula > add the same that has been used in the range/numeric: @.PeriodAcquisition

– Delete the range/numeric
Result in the report designer:

Now, if we run the report – we got this and I created a small pivot table and the value is there as expected:

Note: for the rest there is this green corner which means that convert need to be done:

If You need to have this as a Number, you need to apply above changes to all fields with values.
3. Translate to other language – report works only in English, but there is a way to get this in any other language.
– Open electronic workspace > Report configuration > Fixed asset roll forward – 
– Select model and report – in each click Exchange > Export labels

– Translate the values in the file and save as xml
– select the ‘fixed assets model custom’ and click Exchange > load labels > Select the file fixed  asset roll forwardpl.xml and click OK
– Select report configuration ‘fixed asset roll forward custom’ and click Exchange > Load labels > select the labelPL file
– Select ‘fixed asset roll forward custom’ and click Designer > Language settings > Language preferences > User preferences

Once You load the labels, we get the report in user language (I did not translate all):

from Microsoft Dynamics 365 Blog https://ift.tt/30R8EYy