• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1603
  • Last Modified:

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'Dea
Patrick O'Dea
  • 3
  • 2
  • 2
1 Solution
Yeah, say your date in the database is called paymentDate. SQL has a GETDATE() function that gets the current date so do something like:
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
OR (MONTH(GETDATE()) = 1 AND YEAR(GETDATE()) = YEAR(paymentDate) + 1)
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??

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.
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.

The other thing to do would be to use CASE WHEN to pick it.
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.

Bruce DenneySage 50 Consultant and IntegratorCommented:
sorry, "month to date budgets" would be the 13th column...

here is a quick sample

Patrick O'DeaAuthor Commented:
Thanks brucedenney,

I never used "offset" before.

It will be a handy little function,
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now