+ Reply to Thread
Results 1 to 10 of 10

SAGE 200 SQL Query

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    Notts, England
    MS-Off Ver
    365 PRO
    Posts
    63

    SAGE 200 SQL Query

    Hi, I am trying to pull data from Sage 200 via MS Query, the only piece of information I can't seem to pull is the latest balance of any given nominal ledger account! can anyone help with me with the possible SQL code or point me in the right direction.

    Apologies for any incorrect terms - I am teaching myself SQL and MS Query and only 2 days into this first project of mine.

    Thanks in advance
    Aaron

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: SAGE 200 SQL Query

    You've picked a hard one to start with! The Sage 200 database takes some getting your head round.

    You should have a View called NLAccountPeriodBalanceVw that *I think* will get you the actual value for a given account number and Accounting Period

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: SAGE 200 SQL Query

    Actually, you might need this to give you a rolling total http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=191091

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: SAGE 200 SQL Query

    Off-topic. Lately, Sage has been bragging it has now changed it's back-end to SQL-Server as if it is a new kind of technology. Why writing some common reports in Sage so horrendous is beyond me? The company got way with some unbelievable crap software for so long. I have lost counting the number of new releases over the years.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: SAGE 200 SQL Query

    I don't think it's recent. sage 200 has been sql server for years, I thing sage 50 was access based. The database isn't that bad, it's just not intuitive how the tables relate to each other, you need to know how it's supposed to work to get meaningful reports out of it. To be honest, I've never tried to use the report writer, I do all the reporting I need in SQL, the views are pretty handy for common stuff, there just aren't very many of them (in our implementation at any rate) and documentation seems somewhat lacking

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: SAGE 200 SQL Query

    Hi Kyle,
    In the past, some people were referring to sage's database as "Flat". I know now the reason- Access. Is not that scandal- a top 100 listed company had used Access database for years? I am speechless.
    If you, as a top IT wizard, find Sage reporting as cumbersome, I do not know how the mere mortals are supposed to use them. You live not far the Sage's headquarter. Any thought of giving some of your advice to Sage developers?

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: SAGE 200 SQL Query

    Having had a read, I was wrong, they used their own propriety database for Sage 50 and SQL Server for Sage 200, they presumably did this to keep licencing costs down.

    Hahha I wouldn't call myself a wizard by any stretch! - One of the reasons is that it's cumbersome is quite legitimate, a lot of the data is necessarily flat, as the saying goes "accountants don't use erasers", this means that loads of stuff needs de-normalising to capture it as it was at the time and not how it is now - there isn't really a nice way round that.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: SAGE 200 SQL Query

    Hi Kyle,

    I am really interested to find out how you are using SQL with Sage, but I am also mindful of stealing of someone's thread.
    Do you write the SQL statements in excel or in Sage, that is, as if it is a SQL Server database. Could you please point me to any resource to read or refer to? Thanks

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: SAGE 200 SQL Query

    Our use of Sage is probably different to most peoples, our in house built ERP raises invoices and posts them to Sales Ledger, so most of our SL reporting comes from our ERP. Most of the reporting I do links sage data back to our ERP to look at things like Aged debt, debtor days for credit control and cash collection. I also frequently have to do some reporting to link our PO system with the purchase and nominal ledgers. I don't do any of this in Excel, the typically I write reports in SSMS, and copy and paste them to Excel, or I data warehouse them/ I may occasionally deploy reports to SSRS if there's a recurring - especially for things like KPIs oh and there are a few refreshable worksheets using MS Query based on data warehouse views.

  10. #10
    Registered User
    Join Date
    10-29-2012
    Location
    Notts, England
    MS-Off Ver
    365 PRO
    Posts
    63

    Re: SAGE 200 SQL Query

    Thanks Kyle, that is what I am after. I looked through the vast list so many times I must have overlooked that table multiple times.

    I am not going to look into the rolling total as it's overkill for me, I can just SUMIF from the list of all period balances for each Cost Centre.

    Thanks Again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-03-2016, 11:51 AM
  2. I am after some sage advice.
    By westic in forum The Water Cooler
    Replies: 0
    Last Post: 11-10-2013, 06:12 AM
  3. ERP- MS Dynamics GP vs Sage 300
    By Sibrulotte in forum The Water Cooler
    Replies: 0
    Last Post: 10-19-2012, 09:46 AM
  4. UK Timesheet and Sage Payroll
    By squiggler47 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-22-2009, 04:25 AM
  5. Importing Data From Sage
    By macke in forum Excel General
    Replies: 5
    Last Post: 08-08-2008, 07:27 AM
  6. Sage Problems
    By kev_25 in forum Excel General
    Replies: 1
    Last Post: 11-16-2006, 08:51 AM
  7. Microsoft Query used with SAGE
    By Landmark in forum Excel General
    Replies: 0
    Last Post: 08-24-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1