Sage Nominal Ledger Reporting via ODBC In Excel

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)
Patrick O'DeaAsked:
Who is Participating?
 
Bruce DenneyConnect With a Mentor Sage 50 Consultant and IntegratorCommented:
sorry, "month to date budgets" would be the 13th column...

here is a quick sample



sample.xls
0
 
TommySzalapskiCommented:
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
 
Patrick O'DeaAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
The other thing to do would be to use CASE WHEN to pick it.
0
 
Bruce DenneySage 50 Consultant and IntegratorCommented:
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
 
Patrick O'DeaAuthor Commented:
Thanks brucedenney,

I never used "offset" before.

It will be a handy little function,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.