What are my sales?

Submitted by Ron Lawrence, December 15, 2009

Remember word problems in Algebra? My memories are not fond ones, but I did eventually figure out that word problems were teaching me a skill for turning what I wanted into numbers. I guess that’s why I became a systems analyst. Well, for nearly 20 years now, defining “Sales” for Publishers’ Assistant users has been a challenge. Different users have different definitions, or so it seems. For some publishers, all invoices are sales. Most—not all—don’t want to count consignment invoices. And for some, a book isn’t sold until it’s paid for. Even then we’re not completely sure. That’s why most distributors offer such horrendous terms. The recent addition of the “Transfer” terms to the customer order screen has introduced yet another new challenge for how we look at our sales history for useful information.

Understanding the Publishers' Assistant sales reports has been a challenge because there is no simple definiton for "sales". There might be a fairly complex definition, and I’m going to offer one up here. I am also in the process of implementing some changes to the sales reports that will make use of this new definition. So, I'm hoping that this article will convey a clearer understanding to our users about the new sales reports. I’m also hoping that it will convey why such a simple concept has been so elusive.

 

So what do we consider “sold”, anyway?

It turns out that there are several components to what we call “sales” in publishing. The most obvious and perhaps biggest chunk is:

There aren’t too many that would argue with that. A little less tangible, but close cousins are

“Shortly” can mean up to 120 days. These are your “Net 30”, “Net 60”, “Net 90”, and “Net 120” terms invoices. If your business doesn’t offer terms over, say, 60 days, then you won’t have any “Net 90” or “Net 120” day invoices.

Next, it’s worth considering which books we don’t want to count as sales. The list is long and includes the following:

Whew! O.K. we’re starting to get our arms around the situation, but what about those consignment sales? Eventually they are “sold”, so we must add…

All of the previous exclusions apply to consignment books as well. But, we’re not done yet. If we are to have an accurate and useful picture of “Sales”, then we must deduct…

Now, we’re getting close. So, “Sales” can be described as the following:

Sales =
 
 
Books that are ordered and paid for immediately
 
+ Books that are ordered for which we expect payment “shortly”
 
+ Books shipped on “Consignment” that have been paid for
 
- Books that have been returned

And we want to exclude from “Sales”:

Really, the above is not quite complete. We’re not really after the number of books we’ve sold. We’re after the amount of money that we have charged and expect to receive for the books that we have sold. So the quantities above must be multiplied by the prices we’ve charged for each item, less any discount (or deduction).

 

Anatomy of a Report

So, great! We have a working definition, if not a simple one, for “sales”. Now let’s put that definition to work in a report.

Long time users and those who are accustomed to using database products may know that Publishers’ Assistant report definitions are based on queries written in SQL. For those who don’t know, SQL stands for “Standard Query Language”. Although it is implemented slightly differently across different database products, the standard holds up quite well across all of those products. Using SQL, you can gather data with the SELECT command. I won’t go into all of the details of the SELECT command, but it’s fairly readable. Since our users can actually modify report definitions—including the SQL query—it seems worthwhile to relate our definition for “Sales” to a SQL query that will execute it.

The following is the query from a new “Title Sales by Discount” report. For current users, this report will replace the "Orders by Title (Consignment)", "Orders by Title (Non-Consignment)" and "Receipts by Title" reports.

It turns out that as long as we are going to all the trouble of implementing this definition of “Sales”, a few added considerations are in order which will present a more complete picture of our financial position. For example, in addition to knowing the amount of money we have charged for books that have been “sold”, it would be nice to know how much of that money we have already received. It is also good to get a handle on what proportion of our sales are discounted, and by what amount. So, the following query will list our sales by discount level. Here we go:

 

SQL Query

 

SELECT ;
        UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;
        ordritem.title_id AS Sort2, ;
        1-(orders.discount+ordritem.discount)/100 AS rate, ;
        ordritem.quantity AS order_qty, ;
        orders.order_no, ;
        orders.invoice_no, ;
        orders.order_type, ;
        orders.shptocntct, ;
        orders.terms, ;
        orders.discount AS odiscount, ;
        orders.ship_date, ;
        ordritem.* ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE ordritem.item_no > 0 ;
        AND orders.order_type  = "C"  ;
        AND orders.order_no > 0 ;
        AND orders.terms NOT IN ("Consignment", "Pro Forma", "Review Copy", "Transfer") ;
        AND orders.discount + ordritem.discount < 100 ;
        AND orders.ship_date >= m.start_date ;
        AND orders.ship_date <= m.end_date ;
        AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
        <<select_str>> ;
UNION ;
SELECT ;
        UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;
        ordritem.title_id AS Sort2, ;
        itemrcpt.amount/itemrcpt.quantity/ordritem.price AS rate, ;
        SUM(itemrcpt.quantity) AS order_qty, ;
        orders.order_no, ;
        orders.invoice_no, ;
        orders.order_type, ;
        orders.shptocntct, ;
        orders.terms, ;
        orders.discount AS odiscount, ;
        receipt.recpt_date AS ship_date, ;
        ordritem.* ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN itemrcpt ON itemrcpt.item_no = ordritem.item_no ;
    JOIN receipt ON receipt.receipt_no = itemrcpt.receipt_no ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE ordritem.item_no > 0 ;
        AND orders.order_type  = "C"  ;
        AND orders.order_no > 0 ;
        AND orders.terms = "Consignment" ;
        AND orders.ship_date >= m.start_date ;
        AND orders.ship_date <= m.end_date ;
        AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
        <<select_str>> ;
  GROUP BY ordritem.item_no ;
UNION ;
SELECT ;
        UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;
        ordritem.title_id AS Sort2, ;
        1-(orders.discount+ordritem.discount)/100 AS rate, ;
        IIF(orders.terms="Consignment", 0, -ordritem.quantity) ;
                AS order_qty, ;
        orders.order_no, ;
        orders.invoice_no, ;
        orders.order_type, ;
        orders.shptocntct, ;
        orders.terms, ;
        orders.discount AS odiscount, ;
        orders.ship_date, ;
        ordritem.* ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE ordritem.item_no > 0 ;
        AND orders.order_type  = "R"  ;
        AND orders.order_no > 0 ;
        AND orders.terms != "Transfer" ;
        AND orders.discount + ordritem.discount < 100 ;
        AND orders.ship_date >= m.start_date ;
        AND orders.ship_date <= m.end_date ;
        AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
        <<select_str>> ;
  ORDER BY 1, 2, 3 DESC ;
  INTO CURSOR sales

O.K. Stay with me here! Since our definition is complex, it stands to reason that the query will be complex as well. I hope this gives you a little appreciation for what Publishers’ Assistant is doing for you. (It may also give you some insight into why its primary developer is so wacky!) When you break it down, however, it’s really not that bad.

Let’s try that so we can relate it back to our definition of “sales”. For starters, our query is not just one SQL SELECT command, it's three. These three selections gather sales data from slightly different sources into the same structure so we can combine them and sort them. The first SELECT command gathers non-consignment "sales". The second gathers consignment sales that have been paid for. And the third SELECT command gathers returns.

The following table will juxtapose the query code with comments that relate to our definition.

Query Code

Description

SELECT

SELECT ;

 

The first SELECT gathers sales data for non-consignment invoices.

UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;

These fields are for sorting without regard to case

ordritem.title_id AS Sort2, ;

1-(orders.discount+ordritem.discount)/100 AS rate, ;

This is the effective discount also used for sorting

ordritem.quantity AS order_qty, ;

These are various fields that will be useful for the report.

orders.order_no, ;

orders.invoice_no, ;

orders.order_type, ;

orders.shptocntct, ;

orders.terms, ;

orders.discount AS odiscount, ;

orders.ship_date, ;

ordritem.* ;

This pulls all fields associated with the order item.

FROM ordritem ;

Records taken from ordritem, orders, and title tables.

JOIN orders ON orders.order_key = ordritem.order_key ;

JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;

WHERE ordritem.item_no > 0 ;

Excludes blank items.

AND orders.order_type = "C" ;

Selects only customer orders

AND orders.order_no > 0 ;

Excludes blank orders.

AND orders.terms NOT IN ("Consignment", "Pro Forma", "Review Copy", "Transfer") ;

Excludes Consignment, Pro Forma, Review Copy and Transfer orders

AND orders.discount + ordritem.discount < 100 ;

Excludes items that are discounted 100%

AND orders.ship_date >= m.start_date ;

Date range for the reporting period

AND orders.ship_date <= m.end_date ;

AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;

Excludes inventory corrections, backorders, and cancelled items.

<<select_str>> ;

This token is where user provided selection criteria is added to the query

UNION ;

SELECT ;

 

The second SELECT gathers sales data for consignment items that have been paid for.

UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;

This must be the same list of fields as in the first SELECT.

ordritem.title_id AS Sort2, ;

itemrcpt.amount/itemrcpt.quantity/ordritem.price AS rate, ;

Note that item receipt (paid) quantities are used rather than the order quantity.

SUM(itemrcpt.quantity) AS order_qty, ;

orders.order_no, ;

These are the same fields as above.

orders.invoice_no, ;

orders.order_type, ;

orders.shptocntct, ;

orders.terms, ;

orders.discount AS odiscount, ;

receipt.recpt_date AS ship_date, ;

Note that receipt dates are used rather than shipment dates.

ordritem.* ;

 

FROM ordritem ;

Records taken from ordritem, orders, and title tables, but items are tied to receipts and item receipts to indicate they are paid for.

JOIN orders ON orders.order_key = ordritem.order_key ;

JOIN itemrcpt ON itemrcpt.item_no = ordritem.item_no ;

JOIN receipt ON receipt.receipt_no = itemrcpt.receipt_no ;

JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;

WHERE ordritem.item_no > 0 ;

Excludes blank items.

AND orders.order_type = "C" ;

Looking again at customer orders.

AND orders.order_no > 0 ;

Excludes blank orders.

AND orders.terms = "Consignment" ;

Now selecting ONLY Consignment orders.

AND orders.ship_date >= m.start_date ;

 

AND orders.ship_date <= m.end_date ;

 

AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;

Exclude corrections, backorders, and cancelled items.

<<select_str>> ;

User-provided selection criteria

GROUP BY ordritem.item_no ;

Causes item receipt information to be summarized for each ordered item.

UNION ;

SELECT ;

 

The third SELECT gathers sales data from returns.

UPPER(LEFT(ordritem.title, 50)) AS Sort1, ;

Again, the same list of fields must be selected.

ordritem.title_id AS Sort2, ;

1-(orders.discount+ordritem.discount)/100 AS rate, ;

IIF(orders.terms="Consignment", 0, -ordritem.quantity) AS order_qty, ;

Note that a negative order quantity is used. Consignment return quantities are ignored because they are not counted as sales when shipped.

orders.order_no, ;

  

orders.invoice_no, ;

orders.order_type, ;

orders.shptocntct, ;

orders.terms, ;

orders.discount AS odiscount, ;

orders.ship_date, ;

ordritem.* ;

FROM ordritem ;

Records taken from ordritem, orders, and title tables.

JOIN orders ON orders.order_key = ordritem.order_key ;

JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;

WHERE ordritem.item_no > 0 ;

Excludes blank items.

AND orders.order_type = "R" ;

Selects only returns.

AND orders.order_no > 0 ;

Excludes blank orders.

AND orders.terms != "Transfer" ;

Excludes warehouse transfers.

AND orders.discount + ordritem.discount < 100 ;

Excludes returned items that are discounted 100%

AND orders.ship_date >= m.start_date ;

Selects returns received within the reporting period.

AND orders.ship_date <= m.end_date ;

AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;

Excludes corrections, backorders, and cancelled items.

<<select_str>> ;

User-provided selection criteria

ORDER BY 1, 2, 3 DESC ;

Sort all of these sales items by title, title_ID, and descengin discount rate.

These work across all three SELECTs.

INTO CURSOR sales

Collect selected items into a cursor called "Sales".

 

Conclusions

Well, O.K. Maybe that's still more detail than most people want to look at. The point is that "Sales" has a pretty complex definition. Even if your business defines it slightly differently, this query should provide a good starting point to work from.

It's worth stating that it's usually a good idea to keep the concept of most reports as simple as possible. Traditionally, we've tried to maintain that philosophy. For example, reporting your non-consignment invoices is a pretty good shot at understanding your "sales"; but clearly, this has never been quite good enough. This new definition of sales takes a decidedly different approach. We are forgoing the simple for something that is more meaningful with a single glance.

There are some important new features in the Report Viewer in Version 5.1. Those new features will allow us to take this complex query and use it as a basis for other reports. Then if we want to tweak this report definition, the results will automatically be perpetuated to the other reports that use this data. The result should be better consistency between reports throughout Publishers' Assistant.