[Laura McNicholas]

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
——————————

[Charles Allen]

?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
——————————
——————————————-

[Laura McNicholas]

Thank you. We will try the SmartList Builder.

 

 

Laura McNicholas

Accountant

Maryland Hospital Association

 

 

——Original Message——

?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
——————————

[Jo deRuiter]

Hi ?

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)

——————————
——————————————-

[Laura McNicholas]

Thank you. We will try this query.

 

Laura McNicholas

Accountant

Maryland Hospital Association

 

 

——Original Message——

Hi ?

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)

——————————