Support
Screen Connect
Pay Online

SWK Technologies

Software Solutions & Services

How to Create an Excel Query in Sage 100

Erin Oldaker-Moul

Senior Consultant – Sage 100

SWK Technologies, Inc.

Your Sage 100 (formerly MAS 90) ERP features multiple options for extracting your data, including creating an Excel Query to export spreadsheets. Taking advantage of the integrations between your Sage software and Microsoft Office 365 programs, you can go directly into your go directly into the application and import figures by using the Microsoft Query functionality.

Use the walkthrough below to begin exporting the data:

Exporting Sage 100 Data to Excel

  1. Open a blank Excel document in Citrix environment. (My Tasks in Sage 100- open any Excel workbook. Create a new blank document)
  2. On the Data menu, select “Import External Data”, then “New Database Query”.

sage-100-excel-query-import-external-data

  1. Pick the SOTAMAS90 odbc driver

SOTAMAS90-odbc-driver-sage-100

  1. Enter your Sage 100 login info, including the appropriate Company
  1. You’ll then be presented with all your Sage data files. You can choose an entire data file or individual data fields within a

sage-100-data-query-wizard-excel

  1. Once you select your data file/fields, click Next
  1. You can now specify 1 or more filters, or not. (By choosing a filter it could minimize run time) Click Next

excel-query-filter-data-sage-mas-90-200

  1. Now you can specify 1 or more Sort, or not. Of course you can easily sort within Excel once your data is brought into the spreadsheet. Click
  2. Then you would typically have the data “returned to Excel”. Click Finish
  1. You can choose to put the data in your current spreadsheet, which is the typical method (into cell A1), or into a new Worksheet. Click OK. (If you click Properties, you’ll see the Options you have available.)import-data-spreadsheet-sage100
  1. You’ll then be prompted again for the Company Code and your Sage 100 login
  1. Your data will appear. Depending on the amount of data being Queried, it may take a short moment.

*TIPS:

*When you choose a table like AP_InoiceHistoryDetail you must also choose the table AP_InvoiceHisotryHeader table or you will get a blank database.ap-invoice-history-query-sage-erp-mas

*You can add columns into the database with custom formulas. (These will refresh when you refresh the data)ap-invoice-data-year-sort-excel

 

(Make sure to change the “Format” of the column)

How to Refresh Your Data

  1. Open the Excel workbook
  2. Right Click in any cell. Choose refresh. You will br prompted to enter company code and login information.sage100-excel-query-refresh-data-workbook-spreadsheet
  1. If you have any pivot tables that pull from the database query, you must also “refresh” the pivot table, after refreshing the database

Speak to the Sage 100 Experts for More Tips & Tricks

This is one of many tips and tricks SWK Technologies can provide to Sage ERP users. SWK is a top Sage Partner with intimate knowledge of Sage 100 and Sage 100cloud, and we leverage this experience to make sure you capture the best possible value from your software investment.

Contact SWK today to answer all of your questions on how to best use your Sage 100 or Sage 100cloud ERP.

Talk to the Sage 100 Support Experts

Category: , ,