Solved

Sage Nominal Ledger Reporting via ODBC In Excel

Posted on 2010-11-08
7
1,561 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
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AWS EC2 Microstrategy Amazon Redshift connection 2 794
ERP Solution Needed 6 524
Run SQL Query on Sage 50 Database 12 3,870
Hiring a Canadian to work in Canada for my U.S. business 3 106
A frequent question here in the MAS90 Zone is, How do you get data out of MAS90 for analysis, reporting or using the data on your web pages?   This is an introductory level tutorial to getting data out of MAS90 using ODBC. Data can be accessed by…
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. …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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