I am trying to generate a report to show the total sales of a given customer for 1 calendar year showing what parts the bought, how many of each,  total sales per part, and total sales for the year. Can anyone please help!!

Sorry.. I'm not very familiar with the tables or what is contained in them. I tried looking at the excell spreadsheet but it doesn't help me. I just need to know what I need to use to link the tables together. I would love to see that file please.   I apologize to everyone but I'm still new to this

Quote from aricon   on 02/07/05 at 06:35:32:

 
How about a Data Flow Diagram drawn on a napkin?  

Actually, you should be able to get all that information from the 4 invoice data files BKARINV (open SO header file), BKARINVL (open SO line item file), BKARHINV (invoiced SO header file), and BKARHIVL (invoiced SO line item file). You can link on Customer Code field.
 
Oh, and GasGiant....I asked a looooongg time ago if there was even THAT much, and the answer was no. Although I am sure you are free to create such if you have a bunch of time and napkins available....

BKARINV is to BKARINVL as
BKARHINV is to BKARHINVL
 
If you only want to report on invoiced sales (closed bookings), you only need to look in the
BKARHINV and BKARHINVL files.  These contain the invoice history.
 
The link looks like this in crystal reports:
 

For open (booked) orders, the info is BKARINV (header) and BKARINVL (lines) with the notes in BKARDESC. These are keyed to the SO number, since the invoice number does not yet exist. The field for the actual ship date (bkar_invl_asd) is the requested date and the INVNUM field in BKARINVL is actually the SO num.
 
Invoiced orders (confusingly) are in separate tables that have the exact same field names, except for the notes, which stay where they were. The invoiced info is in BKARHINV (header) and BKARHIVL (lines), but now the INVNUM in the lines table is the invoice number.
 
Transaction info is also posted in INVTXN, BKARINVT, SERIAL, LOT, and to the GLs, etc., but you should not need these for your report.
 
If you would like help writing any of these queries, there are a couple of us here who can help. Let us know what you want included and we'll come up with something that should do the trick.  
 
Here at FHC we have something named the "Flash Report", which lists the results of bookings and invoices by day and by month, with previous month results graphed. I've created this report in a web page so that it is available to all employees in real time.
 
BTW, I did receive your email and would be happy to help. My preference would be to do something that is freely available to everyone, if that will work for you.

Our flash report has been customized from what was in Evo2Web, yes, but it does not include prior year results. For that type of view we use Excel to gather quarterly metrics. Backlog is not something we look at very often, but our version of Evo2Web, which we simply call "data mining", includes the ability to look at open orders by SO or by line, with or without dollar values. Gathering this much info at once might be too heavy for a browser app... depending how it is written. I can see how it would be beneficial to our operation, as well, so I'll create something... a few more questions, though.
 
Does "current and one year ago" mean day, week, month, or quarter?
 
Current backlog is obvious, but what period of booked/shipped would be most helpful? We seem to be interested in current month results, but other periods may make sense.
 
Would prior year results as a number be good or would a graph be more helpful. Our flash report has current month results for two companies, with detail by day and a graph of current FY results by month.
 
Any other ideas would be helpful.

Wow, we use Excel for many of our metrics. Your interest probably run to sales figures, so I'll explain that a little.
 
One biggie is "On Time Delivery", for which I need both the SO data and the Invoice data. I pull the item class, actual ship date, promised date (ESD) requested date (ASD from the SO table) and some more identifying data and plot percent on-time shipping to both promised and requested dates. These results are broken out by classes (almost like work centers) and graphed with three years of results. Pivot tables are used to make sense of the data for graphing. The same data feeds into Pareto charts once we assign a reason to every late shipment, which is a manual operation at this point.
 
Another metric is breaking out a percentage of quarterly revenue coming from new products vs established items. We have added a user-defined field to items so that we can enter a date when the product went on the market. Using that field I can pull all sales by quarter and break out the sales of new products, again using pivot tables, so that we can graph percentage of sales that are coming from new products. This helps us judge the return on R&D.
 
There are many other ways we use Excel, including sales by territory, by class, and by customer. We also chart profit margin by class, by clinical vs. research products, and other fun P&L stuff. The options are endless