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.
I have done this
You need to be using:
NLNominalAccount
NLDepartment
NLCostCentre
NLAccountPeriodValue
SYSAccountingPeriod
SYSFinancialYear
That's what I do.
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.
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.
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.
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 ?