in Sage August 21, 2017
Sage Insights: Exporting Reports to Excel
Exporting data from Sage 200 to Excel is a key tool that allows users to easily manipulate the desired data outside of Sage 200. Exporting reports to excel, however, isn’t as simple as you’re like – at least not to do so in a way that gives you an Excel file you can work with. Exporting a report ‘out of the box’ will give you the raw data, yes, but takes several minutes to ‘tidy up’ (unmerge fields, copy data across into consistent columns, delete header & footer rows, etc.) before it could be used in any meaningful fashion.
This blog post will guide you through simple steps that will allow you to export report (from either Sage 50 or Sage 200) to Excel that will give the user a file that can be worked from immediately.
Note: The screenshots use the Aged Creditor Report, but the method will work on any Sage report.
Exporting to Excel Guide
1. Our first step is to open the report in the Report Designer which can be accessed by the Tools icon on the top menu bar in Sage 200. Consider the example of the Aged Creditors Report – open it by navigating to the File Explorer > Defaults > Reports > Purchase > Purchase Ledger Aged Creditor Report (Summary). The Aged Creditors Report should look like the image below.
2. We can limit which sections of the report actually go to Excel when exported. To do this, we have to start by determining which section of the report holds the majority of the data we want. In this example “Supplier Account Number footer 2” holds the aged balances we want to extract.
3. Having identified the majority of the data we want to export to excel our next step is to consider if there is any data that we are missing. In this case Supplier Account Number footer 2 doesn’t contain the Supplier Account Number / Name meaning that if we only exported that section then we wouldn’t be able to determine which supplier each balance related to. To remedy this copy/paste the missing data fields into the chosen section. For this example, we want to copy and paste Supplier Account and Supplier Account Name fields into Supplier Account Number footer 2.
Note: Any unnecessary data fields in our chosen section can be deleted to make room and clean up the overall data extraction. For example in the image below you will notice that Contact Name and Contact Telephone Number fields have been deleted to make space.
4. To make sure that the report only exports our chosen section we will need to amend the Excel Export settings. To open these settings click in the grey area around the report before navigating to the Properties pane on the right-hand side (see images below).
Note: If the properties pane isn’t on the right-hand side go to the toolbar and select View > Properties.
Clicking on Excel Export Options in Properties will open the below screen. De-select each section you don’t want to be exported to Excel (i.e. only leave the section with the desired data for export ticked). Once chosen click “OK” to exit the window.
5. Finally, go to File > Save As to save the edited report. As Sage believed that the edited document is a copy of the Aged Creditors report it will rename the document to Copy of Purchase Ledger Aged Creditors (Summary) in the custom folder. To make this copy the new default version, the delete the words “Copy of” from the File Name. When you next run this report & export to Excel you’ll now have something far more user-friendly to work with.
Thank you for reading our blog post and make sure to check back for more Sage Insights articles every month!