Reporting in SAP Business One
I notice you posted this to AccountingWEB last April.
If you are still looking, we have developed an Excel-based reporting package called BVXL which is similar to Vision. More details on our website www.bvxlsolutions.com
It is futile to ask users to understand the underlying data tables, make joins etc, so we organise all the data for them in BVXL,
We've installed versions for Sage, Dynamics NAV, Access Dimensions - all the main mid-range accounts packages.
We also have a Business One version for accounting and stock; If you want to produce monthly P and L's vs budget for multiple companies, that is standard stuff and very straightforward..
If it sounds interesting, feel, free to give me a buzz on 01442-216778 and i can fill you in on the details. But have a look at the website first to make sure it's the sort of reporting tool you are after.
If it's financials, you probably need NAV rather than AX.
Try Profile tel: 08000 195101 www.profile.co.uk
Tell them I recommended you!!
Many thanks mr.mischief !
Some good arguments here. I will feel much more confident when suggesting this approach in future.
question from a non-accountant
As an IT person rather than an accountant, can I ask a question on this?
A few years back I installed my first Job Costing system and it seemed very complicated, as with the current system all costs were posted to the Balance Sheet, then recharged to the P&L when the job was finished.
WIP in the Balance Sheet was an amorphous mass of transactions and recently the accountant had got into trouble because he found some WIP which should have been recharged earlier and hadn't, and was now going to knock down the company's profits for the year.
Anyhow, we decided to stop posting to the Balance Sheet and post all costs to the P&L. At the end of the month we simply ran a report of all costs incurred on UNCOMPLETED jobs and posted this as a once-off figure to the Balance Sheet.
To an IT-person like myself, on a common-sense view it was the same principle as running a physical stocktake to calculate Closing Stock. And it saved a vast amount of time re-recharging from Balance Sheet to P&L.
The accountant seemed quite happy with it, but I've always wondered if this was OK in terms of accounting principles. And all the job costing systems I've sent subsequently, people are still posting costs first to the Balance sheet, then bringing them down to the P&L later.
So can I ask the wise men out there: were we OK in posting straight to the P&L, rather than Balance Sheet first?
Combine all the worksheets into one, then use a pivot table
Assuming each order form has the fields PartNo and QtyOrd in them and are 50 rows deep.
In each worksheet insert a new column CustNo, Then insert a Customer code down the 50 rows using Copy Down.
Assuming you have 20 customers, do this on all 20.
Now cut and paste all 20 x 3 worksheets into a new worksheet called Master. This will end up containing 1000 rows.
Create a pivot table from these 1000 rows. Excel 2007 Insert - Pivot Table - Row Value = PartNo, Data Value = QtyOrd
If you want to see which customers have bought which products, put CustNo in the Row Value after PartNo
Have to use the Report Writer?
My knowledge may be out of date but in my day OLE only applied to balances.
To get transactions I went into the Report Writer and selected Document Detail (TL-type) transactions. Then output as CSV.
Maybe try Sage?
Since you already have Sage 50 surely it's worth having a look at the Project Costing facilities it has? They are available (I think) on Accountant Plus and Financial Controller and pretty comprehensive (you have to switch them on via Settings - Company Preferences - Parameters).
Here's a review of Exchequer
I reviewed Exchequer for AccountingWEB a few years back. Link here:
On the SOP and POP side, frankly Sage is pitiful when compared with Exchequer.
On the accounting side you can certainly change the accounts around; Exchequer has won regular prizes as the mid-range system preferred by FD's.
So you may end up being a bit embarrassed when comparing the two packages. But anyone who can master Exchequer will certainly be able to master Sage. And Sage has its own charms: if the basic requirement is fairly simple why spend a lot of money when Sage is perfectly up to the job?
Look for the people, not the software
It sounds like you deal in clothing; if so, each stock item has variants on style-size-colour and you will require a specialist stock control package.
Whenever you want to automate an operational part of the business, the key thing is to find a reseller who understands your business. Forget about the software. Find someone who seems familiar with the way you work, then accept the software they recommend.
Since the stock control system will integrate with the accounts, their system may not work with Sage and you will have to change to whatever accounts package they link to.
I'm afraid I haven't done a clothing implementation for a long time so I don't know the main suppliers. But they will advertise in your trade magazines.
Maybe you need Job Costing not Stock Control
Your numbers are very small. And you say parts are often being purchased for a particular car.
So maybe you don't need Stock Control software at all, but Job Costing software so you can track all the costs associated with building each car.
Sage would handle it, I should think. Switch on the Project Costing module and treat each car as a project. (It can also handle Stock Issues to jobs.)