How to Pull Depreciation Data into an Excel Spreadsheet...thanks to Crystal

“I can tell you I don't have money, but what I do have are a very particular set of skills; skills I have acquired over a very long career.” – Retired CIA Agent Bryan Mills portrayed by Liam Neeson, from the movie Taken.   

Being able to work with data in a Microsoft Excel spreadsheet requires a certain set of skills.  And some of the most skilled people I have ever met were on the Accounting staff. I would not at all be surprised to learn that you are considered a guru of sorts in your office when it comes to using Excel files. And if that’s the case, you may have wondered to yourself, “How can I get this data from the Sage Depreciation program into an Excel spreadsheet where I can work with it -- build a pivot table, or whatever?”

If you are an experienced user in the Depreciation program, you probably already know about the Custom Export feature. We teach that procedure in the Intermediate Depreciation class offered from Sage U. But it is limited to exporting fields one book at a time. Here below are steps you can follow and capture numerous database fields from multiple books1.

The “trick” to performing these steps, however, is that you must have the Crystal application installed which is part of the Sage Fixed Assets--Reporting module. This is a free perk to those who are licensed to use the Premier Depreciation software. Otherwise, the Reporting license is an additional purchase.

To begin with, open a new Crystal report from within the company you want to pull the data from. You may choose to change the orientation from portrait to landscape in order to accommodate even more database fields because you will be inserting fields across the span of the report, from left to right. 

Our Sage programmers have built a template to help you get started with your new Crystal report and it already includes a couple of database fields -- System Number and Extension Number.  In this example, I will keep those fields, but you can always delete them if you don’t need them in your spreadsheet.

When working in Crystal, you are working with objects and just as you can do when editing a PowerPoint slide, you can layer the objects, i.e., put one on top of another. I will do this here and slide the Extension database object on top of the System Number database object and on its right side.

Next, I am going to open the Field Explorer and select some general fields unrelated to any particular book.  Here, I will select the:

  • Activity Code
  • Acquisition Date
  • Asset Id
  • Description

 As I insert these fields into the Details section, I will again stack them.

Next, I will select fields from the Tax book, specifically the following:

  • Property Type
  • Placed in Service Date
  • Acquisition Value
  • Depreciation Method
  • Estimated Life
  • Current Through Date
  • Current YTD
  • Current Accum
  • Net Book Value

As I insert these fields into the Details section, I will again stack them trying to place each successive field on top of, and to the right side 2 of, the preceding field.

Next, I will select fields from the Internal book, specifically these:

  • Placed in Service Date
  • Acquisition Value
  • Depreciation Method
  • Estimated Life
  • Current Through Date
  • Current YTD
  • Current Accum
  • Net Book Value

As I insert these fields into the Details section, I will again stack them trying to place each successive field on top of, and to the right side, of the preceding field.

I’ve been performing my work in the Design tab. And the stacking of the objects, as a way to squeeze more fields into the report, gives it a messy look. This is also true when looking at the data in the Preview tab.

But the purpose was not to make it look pretty here. The purpose was to send this information to an Excel file3. And when I do that, here is the final result:

Despite the overlay of fields in the Crystal report, the Excel application will allocate each field into a separate column resulting in a neat array of the data although you will likely want to widen the columns. Here, I widened all of the columns in the spreadsheet for a clear display of the information and their headers. In this example, I captured a total of 23 columns of data, far too many to fit into the screenshot above.  And with a little more stacking, I could have added more.

Bob McElroy

Certified Trainer

Sage University


[1] If you have never worked with a Crystal report, I recommend that you enroll in the Basic Reporting class offered from Sage U.+

[2] If you layer an object on top of another but slip it to the left side of the object beneath, you will simply flip the order of the columns as they will appear in the spreadsheet.

[3] The export tool can be found on the Standard Toolbar in Crystal and is the same icon found on the far-left hand side of the toolbar when viewing a report in the Depreciation program.  It resembles a computer box with a bent arrow coming out of it.

Anonymous