Guide To Macola Sales History Reports
User Manual:
Open the PDF directly: View PDF .
Page Count: 4
Download | |
Open PDF In Browser | View PDF |
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 Gain Focus Technologies• PO Box 1463 • Mobile, Al 36633 • Phone 251.459.3005 www.gainfocus.biz
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.3 Linearized : No Page Count : 4 Producer : BCL easyPDF 3.11.51EXIF Metadata provided by EXIF.tools