Creating a sales report - which database fact tables to use?

SOLVED

Hi there,

To those who have undoubtedly treaded 1000 steps ahead of me...:

What I am doing:
Building my own sales report by going directly into the database and using a selection of tables.

My report needs to answer:
"what are we selling, to whom, from what location and which is the most profitable
"

 My Question simply is... 
Which are the main fact tables to use in order to create this report


A History of what tried:

  1. First I used the Sales History table. But in this table, there were a lot of Items which had zero cost. Which obviously isn't helpful when you need to report on margin. 
  2. So then I read these articles here and here, and saw that the "Shipping Table" (Posting Journal table) has more information about cost. Hooray.
  3. But then I saw that although the total revenue from sold items in both tables (Sales History and Posting Journal) matched, the total qty of sold items differed. I had more in my Sales History table. 
  4. So then I posted a question here about how the SalesHistory and PostingJournal work together, I was way off track, and  kindly put me back.
  5. And now my question remains, which fact table do I use, if not SalseHistory or the PostingJournals, to create the report I need to create.

As always, thank you to anyone who got to the bottom of my thread.
Michelle

Parents
  • +1
    verified answer

    Sales history (OESHHD) and /or sales history details (OESHDT) have everything you need in simple tables and are by far the easier starting point.

    Maybe better to use sales history details (OESHDT) given it goes to sales document level in the detail.  This has the item (what you are selling), the customer (to whom), the location and the sales/cost values (profitability).  You can do everything by starting with this table.

    If you don't have costs in this table then you are not getting costs into inventory at time of sale so either there is a setup problem or a process problem.

  • 0 in reply to Stephen Bagnall

    Super, thank you Stephen. Just a question (and happy to switch over to the OESHDT table), but, I have started to visualise on the Posting Journal (OEAUDD, OEAUDH). On these tables I removed Shipment type transactions and also adjusted to negative values when it is a credit note. Would these tables also be okay to use, and do you see any advantage of using them over the Sales History tables?

  • 0 in reply to Empy

    Posting audit journals are indexed by type, day end# and entry.  They contain tons of data, much of which is redundant or irrelevant for your purposes.  OESHDT is a small table indexed by Customer, Item, Year and Period, making it a more efficient table for what you want, unless you also need to include Miscellaneous Charges, which have no Item# so they're excluded from that table.  Both approaches can get the job done, but OESHDT will probably run faster, especially as data grows over time.  I just looked up both:  OEAUDD has 115 fields while OESHDT has 36, making it easier to find and test the fields you want.

  • 0 in reply to wheumann

    I agree.  The OESHDT table is simple.  If you need more detail on some aspects (customers, invoices, inventory, etc) you can always join to other tables.  

  • 0 in reply to Stephen Bagnall

    Thank you both Stephen and Wheumann. This forum is really great. Thank you both.

    , just so I can ensure I'm not excluding a huge chunk of sales numbers. Could you please help me with the relevant SQL? That is, 

    SELECT sum(col)
    FROM (the table)
    WHERE (the condition I can use to determine its not in my OESHDT)

Reply Children
No Data