Armanino Blog
Article

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList

April 13, 2015

Dynamics GP includes a variety of reporting tools to get business information out of GP. One of the easiest to use is SmartLists, and its companion, Refreshable Excel Reports. Read on to learn about the Dynamics GP fixed assets depreciation projection query.

SmartLists are based on SQL Views and make current information from your ERP system instantly available. Dynamics GP 2015 introduced a feature to allow you to make your own SmartLists using SmartList Designer and publish them to Refreshable Excel Reports.

To be publishable to Refreshable Excel Reports, the new SmartList must be based on a SQL query, not one or more SQL tables.

Here's an example of how you might create your own SQL View that can be used in Refreshable Excel Reports. For this example, I used the Fixed Assets Depreciation Projection data.

Take a peek at the step-by-step process:

  1. Run the Fixed Assets Depreciation Projection in Dynamics GP.
  2. Create a new SQL View in SQL Server Management Studio.
  3. Create a new SmartList in SmartList Designer based on the new SQL View.
  4. Publish the new SmartList to Refreshable Excel Reports.
  5. Run the new Refreshable Excel report and create a pivot table.

Open SQL Server Management Studio to create a new SQL View. Right click on the "Views" folder and select new.

These are the tables I used. FA41900 is the main table with the depreciation projections. I added the other two to make available additional information to help the user identify the data.

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList

These are the fields selected for this particular example, but it's possible to select fewer or more.

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList

Save the query.

Open SmartList Designer and create a new SmartList.

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList Designer Screenshot

View the new SmartList in SmartLists and select "Publish" to move this to Refreshable Excel Reports.

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList

In Refreshable Excel Reports, open up the new report and view in Excel. If desired, insert the data into a pivot table. In this specific example, I used the USERID and BOOKID to filter the data.

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList -Pivot Table Fields Screenshot

And here's the result:

Dynamics GP Fixed Assets Depreciation Projection Query and SmartList - Result Screenshot

This quick video visually highlights the process for the Dynamics GP fixed assets depreciation projection query as well. Learn more about Dynamics GP and check out other posts for more GP tips and tricks.

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Resources
Related News & Insights
Armaninos 10th Anniversary Reception in Chicago
Live Event
Rooftop reception to celebrate Armanino’s 10th anniversary with the firm’s supporters and friends.

September 29, 2022 | 02:30 PM - 05:30 PM PT
Nonprofit Symposium 2021
Live and Virtual Event
Nonprofits - Get Back to [Focusing on] the Future

September 29, 2022 | 08:00 AM - 12:00 PM PT
Workday Adaptive Planning 2022 R2 Release
Webinar
Learn about Workday’s newest features and enhancements.

August 30, 2022 | 10:00 AM - 11:00 AM PT