<

Go Premium for a chance to win a PS4. Enter to Win

x

Getting Data Out of Sage MAS90 MAS200 Using Microsoft Excel and ODBC

Published on
23,147 Points
16,747 Views
4 Endorsements
Last Modified:
Getting data out of MAS90 doesn’t have to be tedious or time consuming.  With any version of Excel using the included Microsoft Query function, you can access just about any data set or combination of data sets stored in your MAS90 MAS200 Software.

To accomplish this task, you need two things, a valid and working connection to your MAS90 MAS200 Software and your user ID and Password to log in.  

When your MAS90 MAS200 software was installed on your workstation, the MAS90 ODBC "SOTAMAS90" driver was also installed.

Some data may not be accessible to every user, if you encounter a problem, please verify your access with the person charged with administering your MAS90 MAS200 Security.

With the inclusion of the ALE Lookup Engine in the later versions of the software, V3.71 and up, some of these tasks can be accomplished using that built in functionality.  But, sometimes, you may just want to see what else hanging out in your data files.

Tasks that I have used this for:
  •  Analyzing Customer and Vendor Data
  •  Troubleshooting Errors and File Corruption
  •  A snapshot of what is in the file -- helpful if you are doing imports using Visual Integrator

The list goes on.

This functionality works with both the legacy version, 3.71 and below, and the new “NORMALIZED” program structure, V4.0 and above.  You will need to have a general understanding of how the files relate to each other in order to make this little trick useful.

Customers are in one file, the invoice history behind the Customer is in another file.  Vendors are the same way.  Inventory Items are in one Table, the Extended Descriptions are in another.  For Sales Orders, you have a header file, and then you have a detail file; the header includes the Customer Name and number, the order date, terms, total dollars and the Detail file contains the line information... etc.  

You can access table information and listings of the items contained in them via the Detail View Button the MAS90 TASK Bar,Mas90 Task Bar  and then select the File Layouts and Program information.File Layouts and Program Info

Once you have decided which data table you want to work with, load Microsoft Excel.

(1.) Select the Data menu. Excel Data Menu(2.) Then select “From Other Sources”.Excel From other Sources(3.) A dropdown will appear, choose, From Microsoft Query. Query Menu(4.) Select SOTAMAS90 from the list.Excel Choose Data Source(5.) Then click OK.

(6.) Your Mas90 Sign on Screen will appear. Choose the Company you want the data for and enter your User ID and Password, then click OK.Mas90 Signon(7.) You will now have the opportunity to scroll through the Mas90 Data Tables. Oppty to Scroll Either Highlight the Table and click the ">" button to move the selection to your Qwery, or Expand the table by clicking on the "+" sign in front of the Table name.

(8.) For this example, I expanded the selection and only chose the AR Division and Customer Number. Expand Selection(9.) You can filter the data you want to appear in your results by utilizing the drop down filter selection criteria.  If you do not include a filter, than all data will appear. Click Next.  Note: If no data appears then you will need to check your selection. QW Filter Data(10.) The Sort Order Screen will appear and give you the opportunity to pre sort your data.  This functionality already exsists in Excel so, as a personal preference, I do not select any sort criteria. Click Next QW Sort Data(11.)  The Query Wizard Finsh Screen will now appear.  At this point you have the opportunity to save the query for future use, or just invoke it.  I recommend just clicking the finish button until you have the query fine tuned to your liking.  Click the Finish Button. QW Finish(12.) The Excel Import Data Pop up will appear indicating which position on your worksheet the data will appear and give you the opportunity to select different views when it is imported into Excel.  You can accept the default, or make another selection. Click OK. Excel Import Data(13.) You will again be asked to log into your MAS90 MAS200 Database.  Click OK. Mas90 Signon(14.) Congradulations!  Your data has now been transfered to Excel and is ready for you to Sort, Graph, Pivot, Print or Save. Excel Finished

® Sage Software the Sage Software logos, and the Sage Software product and service names mentioned herein are registered tradenames or trademarks of Sage Software, Inc. and/or its affiliated entities.  Microsoft Programs, products and service names mentioned above are registered tradenames, or trademarks of Microsoft Corporation and/or its affiliated entities. Reed Consulting Group, Inc. http://www.reedcg.com.

This article is available for download in its original PDF format here: Mas90ODBC.pdf  
4
Comment
2 Comments
 
LVL 1

Expert Comment

by:H-SC
SageMas90_200guy:

I have a friend that runs a small business and I stand in as their technology support. They are moving from SAGE MAS 90 (4.40.0.1) to Quickbooks Premier Contractor 2012 to take care of all of their billing and payroll.

I am attempting to help them with their migration. Currently they only use SAGE MAS 90 for payroll, and would like to export the employee information such as SSN, address, hire date, etc. (they are not concerned with gross pay, taxes, etc as they will begin with Quickbooks Jan 1, with a clean slate). I read the above article and assume that this method is the best way to export the employee data? Are you familiar with what table this data is stored in?

I don't know anything about SAGE to be honest...

Any help is appreciated!

SD
0
 
LVL 5

Author Comment

by:SageMas90_200guy
The Employee Master File is PR1XXX.soa  (xxx=Company Code)  You will need a Mas90 Login to access this or any table.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month