I want to extract product data from my L50 2011 professional.
I want to show total sales of selected product, to be compared over several years, by month. each year would have a column, each month a line. Is this possible
If you are always creating the reports in the same format then you can record a macro to record your formatting the first time that you set the report up and then tie that Macro to a key so that each time in future that you run an extract the Macro will pull in the data and format the report for you.
Its not difficult but it goes beyond what you could teach someone to do from this site.
There are plenty of tutorials out there on how to record Macro's. Also the QUE special edition book on Using Excel is pretty good.
If you want to apply date filters then another alternative to Macro's to build the report is to create a pivot table from the data. Of course, you may still first need to set up a Macro to get the data into the format that you want in order to create the pivot table.
For that one I would recommend the Microsoft press book Business analysis and data modeling using Excel by Wayne Winston.
Don't know about the 2010 edition of the book but the 2007 version that I have has a data CD with it so that you are able to practice this sort of thing.
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
My recommended route is as follows (for Excel 2013, other versions will be very similar):
1. Open Excel 2. Go to Data > From other sources 3. Select Microsoft Query (or ODBC) - See screen clip here: https://skydrive.live.com/?cid=dfe3fb3d8825db15&id=DFE3FB3D8825DB15!2983&sff=1&authkey=!AG8uusxzNN2Lb9g 4. In the Choose Data Source dialog box choose "SageLine50v19" or equivalent for your version 5. You should then be able the select the tables and columns you require (likely you want the table AUDIT JOURNAL) 6. After a couple more steps your data is inserted into Excel as a table 7. Click in the table and press CTRL-A 8. On the top menu choose Insert Pivot Table
From this point you need to play around with the data you want, filters, date ranges etc.
I used to think Pivot Tables were some kind of scary dark science but they are actually pretty easy to work with once you have a play around with them for a bit. And they are VERY VERY powerful for analysing and reporting on large volumes of transactional data.
Good luck!
Regards Adrian
-- Edited by Topaccountants on Friday 8th of February 2013 10:42:31 PM
__________________
Adrian Pearson Blog: www.adrianpearson.com | Mobile: 07944 970997 | Email: adrianpearson@outlook.com Xero data conversion specialist. Founder of Movemybooks and Ledgerscope.
I think that pre 2007 they really were a scary dark art but one of the redeeming things that Microsoft did to get us to forgive them for the ribbon (which wasn't that bad once you got used to it) was to greatly simplify pivot tables.
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
