Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sage Nominal Ledger Reporting via ODBC In Excel

Posted on 2010-11-08
7
Medium Priority
?
1,580 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline
Suggested Courses

885 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