Open Forum

Expand all | Collapse all

Fixed Asset Projections which include Depreciation Expense account numbers

  • 1.  Fixed Asset Projections which include Depreciation Expense account numbers

    Posted Jan 11, 2019 03:04 PM
    We have multiple depreciation expense account numbers and need to run Fixed Asset projections by expense account number. Ideally, the report would be by expense account number with the assets and projections listed and totaled for each depreciation account. It does not appear that there is a "standard" projection report with this information. It does not appear that the Smartlist reports include projections. Any suggestions as to how we can get this information?
    #FixedAssets​

    ------------------------------
    Laura McNicholas
    Maryland Hospital Association, Inc
    ELKRIDGE MD
    ------------------------------
    GPUG Summit - Post


  • 2.  RE: Fixed Asset Projections which include Depreciation Expense account numbers

    TOP CONTRIBUTOR
    Posted Jan 14, 2019 01:16 AM
    ​You will want to create a custom report using SmartList Designer/Builder, SSRS, or Report Writer.

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------

    GPUG Summit - Post


  • 3.  RE: Fixed Asset Projections which include Depreciation Expense account numbers

    Posted Jan 14, 2019 11:37 AM

    Thank you. We will try the SmartList Builder.

     

     

    Laura McNicholas

    Accountant

    Maryland Hospital Association

     

     




    GPUG Summit - Post


  • 4.  RE: Fixed Asset Projections which include Depreciation Expense account numbers

    TOP CONTRIBUTOR
    Posted Jan 14, 2019 09:41 AM
    Hi @Laura McNicholas

    Here is a query to start with.  But, the first thing you should know is that Projections are kept by User ID in GP, so every user has their own projection in this table.

    I would also set this up as a bit of a Pivot table as an Excel report because you end up getting monthly numbers as opposed to a yearly projection - or have someone alter this script to add totals.

    Best of Luck!

    SELECT PROJ.USERID AS [USER], GEN.ASSETID AS [ASSET ID], GEN.ASSETIDSUF AS [ASSET SUFFIX], GEN.ASSETDESC AS [ASSET DESCRIPTION], BSETUP.BOOKID AS [BOOK ID], BSETUP.CURRFISCALYR AS [CURR BOOK YEAR], PROJ.DEPRFROMDATE AS [DEPR FROM], PROJ.DEPRTODATE AS [DEPR TO], BOOK.PLINSERVDATE AS [PLACED IN SERVICE], BOOK.DEPRBEGDATE AS [DEPRECIATION BEGAN], BOOK.COSTBASIS AS [COST BASIS], BOOK.YTDDEPRAMT AS [ACT YTD DPR], BOOK.LTDDEPRAMT AS [ACT LTD DEPR], BOOK.CURRUNDEPRAMT AS [ACT PERIOD DEPR], GEN.ASSETCLASSID AS [ASSET CLASS], GEN.ASSETTYPE AS [ASSET TYPE], GEN.ASSETSTATUS AS [ASSET STATUS], GEN.ASSETQTY AS [ASSET QTY], PROJ.FAYEAR AS [PROJ YEAR], PROJ.FAPERIOD AS [PROJ PERIOD],PROJ.YTDDEPRAMT AS [PROJ AMT], DEPREN.ACTNUMST AS [DEPR ACCT],DEPRE.ACTDESCR AS [DEPR ACCT DESCR],ACDEPRN.ACTNUMST AS [ACCUM ACCT],ACDEPR.ACTDESCR AS [ACCUM ACCT DESCR],PYEARDN.ACTNUMST AS [PRIOR YR DEPR ACCT],PYEARD.ACTDESCR AS [PRIOR YR DEPR ACCT DESCR],ACOSTN.ACTNUMST AS [COST ACCT],ACOST.ACTDESCR AS [COST ACCT DESCR],PROCEN.ACTNUMST AS [PROCEEDS ACCT],PROCE.ACTDESCR AS [PROCEEDS ACCT DESCR],GAINLN.ACTNUMST AS [GAIN LOSS ACCT],GAINL.ACTDESCR AS [GAIN LOSS ACCT DESCR],NRGAINLN.ACTNUMST AS [NR GAIN LOSS ACCT],NRGAINL.ACTDESCR AS [NR GAIN LOSS ACCT DESCR]FROM             FA41900 AS PROJ LEFT OUTER JOIN           FA40200 AS BSETUP ON BSETUP.BOOKINDX = PROJ.BOOKINDX LEFT OUTER JOIN                          FA00400 AS AACCT ON AACCT.ASSETINDEX = PROJ.ASSETINDEX LEFT JOIN FA00100 AS GEN ON GEN.ASSETINDEX = PROJ.ASSETINDEX LEFT JOIN FA00200 AS BOOK ON BOOK.ASSETINDEX = PROJ.ASSETINDEX LEFT JOIN GL00105 AS DEPREN ON DEPREN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS ACDEPRN ON ACDEPRN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS PYEARDN ON PYEARDN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS ACOSTN ON ACOSTN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS PROCEN ON PROCEN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS GAINLN ON PROCEN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00105 AS NRGAINLN ON NRGAINLN.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN                         GL00100 AS DEPRE ON DEPRE.ACTINDX = AACCT.DEPREXPACCTINDX LEFT JOIN GL00100 AS ACDEPR ON ACDEPR.ACTINDX = AACCT.DEPRRESVACCTINDX LEFT JOIN GL00100 AS PYEARD ON PYEARD.ACTINDX = AACCT.PRIORYRDEPRACCTINDX LEFT JOIN GL00100 AS ACOST ON ACOST.ACTINDX = AACCT.ASSETCOSTACCTINDX LEFT JOIN  GL00100 AS PROCE ON PROCE.ACTINDX = AACCT.PROCEEDSACCTINDX LEFT JOIN  GL00100 AS GAINL ON GAINL.ACTINDX = AACCT.RECGAINLOSSACCTINDX LEFT JOIN  GL00100 AS NRGAINL ON NRGAINL.ACTINDX = AACCT.NONRECGAINLOSSACCTINDX WHERE PROJ.USERID = 'sa'

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)

    ------------------------------

    GPUG Summit - Post


  • 5.  RE: Fixed Asset Projections which include Depreciation Expense account numbers

    Posted Jan 14, 2019 12:10 PM

    Thank you. We will try this query.

     

    Laura McNicholas

    Accountant

    Maryland Hospital Association

     

     




    GPUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role