Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: Import Data from Sage 200 MMS into Excel. By David Carter

by
29th Feb 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

This is for readers of AccountingWEB who use Sage MMS (or Sage 200 as it is now called).

[Note: if you have an early version of Sage MMS, before version 3.5, it probably uses the old Retrieve database. In this case the instructions below don’t apply. Instead, use the instructions for Sage Line 100.

Sage MMS/200 runs under Microsoft SQL Server. The first task is to set up MMS/200 as SQL Server data source in Windows. Then we can use the Import External Data command to pull the data into Excel.

Before we start, a note of caution. SQL Server is a complex beast. If you have a resident techie in the company it would be a good idea to have him/her by your side as you work through this.

1. Pre-start - find your SQL Server name
During the setup, you will be asked for your SQL Server name, so you need to find your server name before you start.

Click on Start – Programs – SQL Server 2005 – SQL Server Management Studio. On my machine it says:

Server Type : Database engine
Server Name : YOUR-CE19999999
Authentication : Windows Authentication

Make note of the Server Name.

2. Set up Sage MMS as a Data Source in Control Panel
Now Start menu again, then Settings - Control Panel - Click on the ODBC32 icon or (Windows XP) Administrative Tools - Data Sources (ODBC).

The ODBC Data Source Administrator box appears.

Switch to the System DSN tab. Add.

From the list of drivers, select: SQL Server

The Create a New Data Source to SQL Server box appears:

Name: Sage MMS Description: Sage MMS

Server: Type in the name of your Server

Next

The next screen is about authentication. Next to accept the defaults.

At the top of the next screen it should show your user database as the default.
If it doesn’t, select yours from the drop-down list.

Next.

When the “Change the Language” box appears, click Finish to accept the defaults.

A grey screen appears. Click on Test Data Source. If it’s successful, OK to continue.

You are back at the ODBC Data Source Administrator box. Sage MMS has been added to the list of Data Sources. OK to close.

3. Go back into Excel
Start up Excel; open a new workbook. Click onto cell A1. Then:

  • Excel 2003: Data – Import External Data – New Database Query
  • Excel 2007: Data – Get External Data – From Other Sources – From Microsoft Query
  • Excel 2000 or earlier: Data - Get External Data – Create Database Query.

    In the Choose Data Source box, highlight Sage MMS. OK.

    Hopefully, you now see the Query Wizard – Choose columns box, with a list of tables in alphabetical order.

    You are in. These are the data tables for MMS, and there’s dozens of them!

    4. A journey of discovery
    As with most accounts packages, the data in MMS is “normalised”, i.e. scattered around in numerous data tables.

    Probably only 1-2% of this data will actually be useful for reporting. You now have to embark on a journey of discovery to find out where that 1-2% is located in MMS.

    I’d suggest you download the tables that look interesting. Download them into Excel one at a time, then see what data each one contains and identify the important fields. Some important tables are:

    if you are interested in Nominal (GL) Reporting

  • NLPostedNominalTran
  • NLNominalAccount
  • NLCostCentre

    if you are interested in Sales and Margin Analysis

  • Transaction_History
  • SLedger
  • StockItem

    5. Download the NLPostedTran table
    Start with the NLPostedTran table. Scroll down and highlight NLPostedTran.

    Select all the fields within the table. To do this, highlight the table name, then click on the right facing chevron. All the fields appear in the right hand box. Next.

    You are now in the Filter screen. You don't want to bring over records from years back, only the more recent ones. Therefore :

    Highlight TransactionDate. greater than 6 months ago. Next

    In the Sort screen, choose: Transaction Date – Descending.

    This means that only the last 6 months transactions will come over, and the most recent ones will appear at the top of the screen.

    Next. Then just press Enter to Return the Data to Excel.

    6. Use Paste Special – Transpose
    Once you’ve downloaded the data table into Excel, highlight the first 4 or 5 rows, then Edit – Copy. Go into another worksheet, cell A1, then Edit – Paste Special – Transpose.

    The field names are copied into column A and are much easier to read.

    Warning As usual, nothing in Sage is simple. In the NLPostedTran table there’s a field called NLNominalAccountID. You might think that this was the Nominal Account Code, but it’s not. It’s the number of the record within the NLNominalAccount table which holds the details of this particular nominal account.

    So any field ending with the letters ID will not display data that means anything to you. It is simply an internal Sage record number.

    END OF SESSION

    Related material
    David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
    Reporting tutorials index: Use MS Query to extract and analyse accounts data
    Reporting Tools #4: Introduction to summary reporting
    Towards an analysis-ready file for reporting #1
    Towards an analysis-ready file for reporting #2
    Management Reporting in Sage Line 50 and Instant
    Want to learn more about pivot tables? Start here
    Improve your reporting skills with self-teach tutorials
    An introduction to Excel-driven reporting tools

    Subscribe to the ExcelZone newswire
    To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

  • Tags:

    Replies (1)

    Please login or register to join the discussion.

    avatar
    By bpatel
    26th Jul 2010 14:16

    Sage 200 on SQL Server

    Hi,

    We have sage200 on SQL server, And I couldn't set up ODBC connectivity,

    First problem was to find out the server name.

    We don't have SQL Studio, How can I know the name?

    If I don't have direct access to sageserver (ie I can access sage but I can't access the server itself), can i still perform SQL Query?

    thanks,

    Thanks (0)