Solved

Sage Nominal Ledger Reporting via ODBC In Excel

Posted on 2010-11-08
7
1,559 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

If you need to import sales transactions from another system into Microsoft Dynamics GP as Sales Order Invoices, you may need to store some additional data related to the customer, the transaction, or the line items.  Even if your company is manuall…
As an accountant it is essential that I am able to provide accurate and timely information to management and staff.  One of the challenges that I have faced is the need to report on a time period, whether it be a month, quarter, or year, and wanting…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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