February 15, 2011 
Around this time of year we get a lot of questions about closing the year for the Dynamics GP Fixed Assets module, since you cannot run January depreciation until you close the prior year. And right after we start getting requests for reports of prior year depreciation. Here is a view that shows year to date, last year and life to date depreciation by asset. It also has a pretty cool example of how to group your data into columns by year.
For more detail on the Fixed Assets module, please visit my Fixed Assets Tables page. If you’re looking for more SQL code, you can find it on my GP Reports page.
create view view_FA_Depreciation_Yearly
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_FA_Depreciation_Yearly
-- Created Feb 28, 2013 by Victoria Yudin, Flexible Solutions Inc
-- For updates visit https://victoriayudin.com/gp-reports/
-- Tables used:
-- FA00100 – fm - Asset General Information Master
-- FA00902 – d - Financial Detail Master
-- FA40200 - b - Book Setup
-- FA40400 - a - Asset Account Master
-- GL00105 - am - Account Index Master
-- Updated Nov 30, 2011 to add accumulated depreciation account
-- Updated Apr 9, 2013 to add asset status
-- Updated Feb 27, 2015 to add current net book value for
-- non-retired assets
-- Updated Mar 28, 2016 to add prorated retirement date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
select
case fm.ASSETSTATUS
when 1 then 'Active'
when 2 then 'Deleted'
when 3 then 'Partial Open'
when 4 then 'Retired'
end Asset_Status,
fm.ASSETID Asset_ID,
fm.ASSETIDSUF Asset_Suffix,
fm.ASSETDESC Asset_Description,
fm.ACQDATE Acquisition_Date,
fm.Acquisition_Cost,
b.BOOKID Book_ID,
sum(d.AMOUNT) Life_to_Date_Depreciation,
sum(case
when year(getdate()) = d.FAYEAR
then d.AMOUNT
else 0 end) Year_to_Date_Depreciation,
sum(case
when year(dateadd(yy,-1,getdate()))=d.FAYEAR
then d.AMOUNT
else 0 end) Last_Year_Depreciation,
case fm.ASSETSTATUS
when 4 then 0
else bd.NETBOOKVALUE
end Net_Book_Value,
fm.ASSETCLASSID Asset_Class,
am.ACTNUMST Accum_Depr_Account,
bd.PRORATEDRETDATE Retirement_Date
from FA00902 d -- financial detail
left outer join FA00100 fm -- fa master
on fm.ASSETINDEX = d.ASSETINDEX
inner join FA40200 b -- book setup
on b.BOOKINDX = d.BOOKINDX
left outer join FA00200 bd -- book details
on bd.ASSETINDEX = d.ASSETINDEX
and bd.BOOKINDX = d.BOOKINDX
inner join FA00400 a -- account numbers
on a.ASSETINDEX = fm.ASSETINDEX
inner join GL00105 am -- GL account for accum depr
on am.ACTINDX = a.DEPRRESVACCTINDX
where d.TRANSACCTTYPE = 2 -- depreciation only
group by fm.ASSETID, fm.ASSETIDSUF, fm.ASSETDESC, b.BOOKID,
fm.ASSETCLASSID, fm.ACQDATE, fm.Acquisition_Cost,
am.ACTNUMST, fm.ASSETSTATUS, bd.NETBOOKVALUE,
bd.PRORATEDRETDATE
-- add permissions
go
grant select on view_FA_Depreciation_Yearly to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone
Victoria Yudin 
We are using the view_FA_Depreciation_Yearly in smart list and our auditors are asking to include the retirement date of those assets that are retired. Could this view be changed to include the retirement date?
I tried to add the retirement table in Smartlist builder but I was struggling with the linkage and thought changing the view might be a better option.
Thank you.
LikeLike
Hi,
I have added the prorated retirement date to the code, let me know if this is not what you were looking for.
-Victoria
LikeLike
Hi Victoria,
How can you add net book value to your query? Thanks
LikeLike
Hi Leo,
I have added a current net book value for non-retired assets. Hope that helps.
-Victoria
LikeLike
This was extremely helpful to us! My Finance people are so relieved. Thank you! We also needed to identify the disposals (aka Retired) assets which I pulled in the AssetStatus.
Is there a way to pull in or translate the number representation? I want to add this view to SmartList and it would be better if the status was 1 – Active, 2 – Deleted, 3 – Partial Open, 4 – Retired
Thanks!
LikeLike
That’s a great idea, thanks. I have updated the code to add asset status.
-Victoria
LikeLike
Hello! Your SQL Views are always helpful Victoria. Just curious if it is possible to alter this view so it shows Accumulated Depreciation as of the end of the prior fiscal year similar to how it shows Last Year Depreciation. My client has a need to see Accumulated Depreciation as of 12/31/2011, but they have already closed FA for 2011 and run depreciation through 1/31/2012. So running the standard GP reports (i.e. Depreciation Ledger) is showing the Accumulated Depreciation amount through 1/31/2012 and there is no way for them to go back and see the 12/31/2011 amount. Thoughts?
LikeLike
Lyndy,
How about adding a column for life to date minus year to date depreciation to this? You can do this by adding the following before the FROM line in my code:
Another option could be using this view instead and just summing up the needed years.
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
Thank you for all of the great resources you provide! I am trying to come up with a report or smartlist builder that will list Fixed Assets along with their Accumulated Depreciation Grouped by Account Group. The out-of-the box Fixed Asset Inventory report is actually not too bad, and it allows you to group/sort by Class, Location, or Structure.. but nothing is available by Account Group. My client has several different Accumulated Depreciation GL accounts, and they are trying to reconcile the FA Acc Depr Balances to the GL Acc Depr accounts. Any thoughts you have would be much appreciated.
LikeLike
Hi Mike,
The problem with using Account Group is that it is not saved with the asset – it is simply used as a ‘template’ to populate the account numbers initially. You can see this by opening any existing asset in GP and going to the Asset Account window – the Account Group ID will be empty. Because of this, you’re not going to find anything that can group by Account Group.
I just updated this view to add the Accumulated Depreciation account currently selected for each asset. Hopefully that will help with what you’re looking to do.
-Victoria
LikeLike