Solved

Sage Nominal Ledger Reporting via ODBC In Excel

Posted on 2010-11-08
7
1,566 Views
Last Modified: 2012-05-10
Hi all,

Assume I have a nominal ledger in Sage with balances , budgets etc.
Assume I also have an ODBC link from Excel into Sage.

Question : Is there any neat way of showing a budget year to date figure in Excel.
I.e. the problem being that in March my budget year to date is sum of 3 months data.  But is April it is the sum of 4 months data etc.

Do I need to change my ODBC queries each month to reflect a different period.  Can I automate this change neatly??

My goal is simply to click a button each month without fiddling around with queries.

(I hope the above is clear)
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34089122
Yeah, say your date in the database is called paymentDate. SQL has a GETDATE() function that gets the current date so do something like:
WHERE YEAR(GETDATE()) = YEAR(paymentDate) AND MONTH(GETDATE())>MONTH(paymentDate)
This should return only the records that were added this year in any month prior to the current month.
To make January pull the entire last year do
WHERE (YEAR(GETDATE()) = YEAR(paymentDate) AND MONTH(GETDATE())>MONTH(paymentDate))
OR (MONTH(GETDATE()) = 1 AND YEAR(GETDATE()) = YEAR(paymentDate) + 1)
0
 

Author Comment

by:Patrick O'Dea
ID: 34089208
Thanks TommySZalapski,

However, I am not sure your solution will work.
The ODBC link is with Sage financial system.

It is not really a matter of selecting specific records.  In fact one record will hold a balance figure for each of 12 months.
So a single record will have multiple fields including , Budget_Mth1, Budget_MTh2, Budget_Mth3 etc.

How can I get the SQL to identify which of these budget fields to use??




0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34089268
You could look at the query they use to get those numbers and modify it to work like what I posted. At least in Peachtree, Sage lets you view all the queries they use for their reports.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34089271
The other thing to do would be to use CASE WHEN to pick it.
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 34091240
Using the ODBC link you can link in the budgets from the nominal ledger table.

Assuming we have the nominal code then 12 columns B-M representing the months add 12 column to be the "month to date budgets"

these contain a formula with a locked end N2 would be =SUM($B2:B2) copy this across the 12 columns Y2 would be =SUM($B2:M2)

Now you just need to select the right column for the month you are in.

Place the Month (1-12) in Cell AB1

In Cell Z2 place the formula =OFFSET(M2,0,$AB$1)

Now on your linked data range properties enable the option to "fill down formulas in columns adjacent"

Refresh your data and all the formulas in row 2 should copy down the length of your data and column Z contains this months cumulative budget.



0
 
LVL 10

Accepted Solution

by:
Bruce Denney earned 500 total points
ID: 34091432
sorry, "month to date budgets" would be the 13th column...

here is a quick sample



sample.xls
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 34092357
Thanks brucedenney,

I never used "offset" before.

It will be a handy little function,
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GP 2010 - Payroll checks 5 329
Update the register on my quickbooks 2013 5 428
Great Plains (GP) - General Ledger Inquiry 3 163
Peachtree - Sage 50 migration 1 812
Hi, I've just fixed a problem with Sage 50 Accounts 2010 crashing when creating a new Company.  I've spent nearly 4 hours on it including three calls to the Sage helpdesk (they didn't work it out in the end - I did!) ... so I thought I'd share th…
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. …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question