Sage 200 data tables help

Sage 200 data tables help

Didn't find your answer?

Hi,

Can someone help me?

I need a pivot table in Excel from Sage 200 which has nominals and cost centres down the x axis and periods along the y axis and period balances in the data Table nlaccountperiodbalancevw2 gives me the actual period balances, periods and account number so I just need help to bring in the cost centre field from another table and the nominal account name if possible. NLNominal Accounts looks like a likely candidate of a table to link to but it's bringing up a relationship box (why can't I just use query and drag across the fields from one box to another?) so I highlight the common fields but I am getting an error message saying I am picking duplicate fields or similiar which I thought was the purpose of linkage? Groan, can someone help me move this along?

Thanks in advance

Matthew

Replies (10)

Please login or register to join the discussion.

Routemaster image
By tom123
23rd Jun 2015 14:53

I have done this

You need to be using:

 

NLNominalAccount

NLDepartment

NLCostCentre

NLAccountPeriodValue

SYSAccountingPeriod

SYSFinancialYear

 

That's what I do.

Thanks (1)
avatar
By MDK45
23rd Jun 2015 16:08

Off which table?

Edited. Ignore me. Thanks, I have imported these tables now, thanks for your prompt helpful reply!

Thanks (0)
avatar
By MDK45
23rd Jun 2015 16:18

I have done it, great thanks!

Can I be so bold to ask two more questions? 1. I need a financial year field e.g. 2014 instead of 1 that is showing and for account numbers, just the posting accounts and not the memorandum ones?

Thanks (0)
Routemaster image
By tom123
23rd Jun 2015 16:33

SYS Financial Year

SYSFinancialYear table includes the start date for the year.

We don't use memorandum accounts, but I guess NLAccountTypeID in table NLNominalAccount may well link to a table that gives the account types to then permit some filtering.

Thanks (0)
avatar
By MDK45
23rd Jun 2015 16:41

Ok, have incoporated those now. This table can now drop into the management accounts to supersede the old PSF one. Can I make these pivot table read off multiple databases as well? I think the answer may be no because I had to specify the company as part of the data import wizard 'thingie'.

Thanks (0)
Routemaster image
By tom123
23rd Jun 2015 19:04

Save a copy of the query

As well as saving my Excel workbooks, which will have the queries in them, I tend to also save a copy of the query. You can then re-use this query in another spreadsheet.

I only have one 'operational' company - but I am sure you can change the query.

My way would be to create a new ODBC source for each company, and then use that for the database connection when creating a new spreadsheet, but re-using your first query

Haven't tried that though, and it may not be the most technical or neat approach.

I have S200 2011, and Excel 2007 (I think) so you may be ahead of me in versions.

Thanks (1)
avatar
By MDK45
23rd Jun 2015 19:11

I will try that tomorrow, thank you. I also thought of pulling data in via cell formula. I use to do that in exchequer and dimensions. Can you do that in sage 200 do you know? I had a scout around the net for sage 200 excel formulas to do that to no avail. I remember exchequer had a vast range of formula to bring in actuals, budgets month to date, year to date etc etc

Thanks (0)
Routemaster image
By tom123
23rd Jun 2015 22:38

Not familiar with cell formulas, I'm afraid. I tend to do all my reporting with Pivot tables on queries.

If you want to send me a PM, I will send you a document I found online, probably published by Sage, called 'Understanding the Sage 200 database' which explains a lot about all the tables.

This really helped me with building my queries.

Thanks (1)
avatar
By MDK45
24th Jun 2015 07:12

Will do.

Thanks (0)
avatar
By kiwilondon99
24th Jun 2015 10:13

3rd party software

are there any 3rd party  software which could produce what the OP requires depts + several entities[sage datasets]  ie like say possibly qlickview or infor, pulling data from various sage db + possibly also say an ERP ?

Thanks (0)