Guide To Macola Sales History Reports
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 4
Gain Focus Technologies• PO Box 1463 • Mobile, Al 36633 • Phone 251.459.3005
www.gainfocus.biz
GAIN FOCUS TECHNOLOGIES
Reporting on Sales History Tables in Macola using Crystal Reports
Writing Crystal Reports for Sales History against Macola data is probably the single most
common type of crystal report request we get. The purpose of this document is to
show some common approaches to make this type of reporting easier.
Writing the report: Tables and Linking
The first thing is to get to the tables containing sales info. For the purposes of this
document, these tables are OEHDRHST_SQL and OELINHST_SQL. There are a number
of other tables you can link to these of course, but these are the main tables.
There is one way and one way only to correctly link these tables:
Ord_Type Ord_Type
Ord_no Ord_no
Inv_No Inv_No
Always link from OEHDRHST_SQL to OELINHST_SQL, and always use a left outer join.
I will briefly touch on some other sales tables later in this document.
Some “Gotchas”
At this point you are ready to write a sales report in crystal. However there are several
gotchas to be aware of:
Qty Sold
Macola has the quantity sold in 2 different fields, dependant upon if the orig_ord_type
is C for credit memo or not. These fields are qty_to_ship and qty_ret_to_stk. So I
always write a formula for qty sold as follows:
{OELINHST_SQL.qty_to_ship}-{OELINHST_SQL.qty_ret_to_stk}
Gain Focus Technologies• PO Box 1463 • Mobile, Al 36633 • Phone 251.459.3005
www.gainfocus.biz
Sales Dollars
If you are writing against the OELINHST_SQL table, this is a snap, no formula is
needed, just use the field {OELINHST_SQL.sales_amt}.
If you are writing against the OEHDRHST_SQL, this is tricky. The OEHDRHST_SQL
table stores the sales dollars as a positive number regardless if the order was a credit
memo or an invoice. Also, all order types (except Q for quotes) are converted to O in
the OEHDRHST_SQL table. So to overcome this, you must test the field
{OEHDRHST_SQL.orig_ord_type} to see if this was originally a type C or credit memo
as follows:
(If {OEHDRHST_SQL.orig_ord_type}=”C” then -1 else 1) *
{OEHDRHST_SQL.tot_sales_amt}
Please take note of the use of parentheses () in the formula above.
Please also note that all the other fields that roll up into the total amount of the invoice
are similarly stored as positive numbers and must be converted to negative numbers if
the original order type is C.
Record selection formula:
Most people are not aware of this, but if you are using quotes in Macola, and if you
delete the quote and give the quote a lost sale code, the quote (order type = Q) gets
written to the OEHDRHST_SQL and OELINHST_SQL tables. This is the source date for
the lost sales report in Macola. For the most part, you NEVER want this data in your
sales report.
The use of quotes in Macola does not apply to everyone, but this happens often
enough, and even if not in current use could start at any point in the future, so I always
start my record selection formulas as follows:
{OEHDRHST_SQL.ord_type}<>”Q” and
Other Tables: OE Sales History Table – OESLSHST_SQL
This table does not get populated in Macola unless you are running the OE Process
‘Post Sales History Trx’ on a monthly basis. This table contains summary data, so you
could query this data rather than running a detailed query on the OELINHST_SQL table
if your report design allows it.
The use of the OESLSHST_SQL table is particularly effective if you have a large
database or if you are running Pervasive.
Gain Focus Technologies• PO Box 1463 • Mobile, Al 36633 • Phone 251.459.3005
www.gainfocus.biz
Audit Trail Tables – OEHDRAUD_SQL and OELINAUD_SQL:
These tables get populated only if you have the audit trail turned on in OE Setup. The
primary use for this table for a crystal sales report is the ability to write a bookings
report. There are 4 different values for audit action in this table: A-Add, B-Before
Change, C-After Change and D-Delete.
To write a bookings report, test the value of the {OELINAUD_SQL.aud_action} field.
Add the As and Cs, and subtract the Bs and Ds.
The Order Inquiry Table (OEINQORD_SQL)
This is the table that contains the data that you see in the consolidated order view
screen in Macola. This data contains a flag indicating if the order is in History orders, in
Open orders, or Both.
So to write a report on orders that looks at both history and open, link this table to the
open orders and order history tables, test for the value of the {OEINQORD_SQL.ord_fg}
field, and write formulas that either pull the data from the open orders tables, the order
history tables, or adds the data from the two tables together.
Open Orders (OEORDHDR_SQL and OEORDLIN_SQL)
The proper link between these two tables should always be:
Ord_Type Ord_Type
Ord_no Ord_no
The biggest problem people typically face with open orders reports is the open dollar
amount, which is not stored by line item like it is in the OELINHST_SQL table. While
this simple formula:
{OEORDLIN_SQL.qty_ordered}*{OEORDLIN_SQL.unit_price}
Will suffice for many people, there are discounts and conversion factors for differences
in stocking vs selling units of measure to take into account. The following formula will
work for all possible combinations in Macola:
{OEORDLIN_SQL.qty_ordered}*{OEORDLIN_SQL.unit_price}*(100-
{OEORDLIN_SQL.disc_pct)/100*{OELINHST_SQL.uom_ratio}
Did we miss anything?
We hope this document helps. If you have a question on sales reporting in Macola with
crystal, please feel free to ask. If we missed anything, we will modify this document to
cover the topic and give you an updated copy of it.
Gain Focus Technologies• PO Box 1463 • Mobile, Al 36633 • Phone 251.459.3005
www.gainfocus.biz
Best regards and good luck with your crystal reporting.
Don Gilsdorf
Gain Focus Technologies
www.gainfocus.biz
251.459.3005
