Estimate Report


This is the primary estimate or quotation report.

Information Structure

The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:

  • The Header Record (TransHeader)
    • The Variation of the estimate.
    • The [top-level] Line Items and Child Line Items[TransDetail].This is the “detail” level of the report.
    • The Company record (Account).
      • The company's Payment Terms (PaymentTerms).
    • The Primary Contact record (AccountContact).
    • The Salesperson for the estimate (Employee).
    • The Employee who Entered the Estimate (Employee).
    • The Billing Address for the Estimate (Address).
    • The Division Name for the Estimate (EmployeeGroup).

Tables Accessed

Purpose Table Linkage Information Filter Information
Estimate Level TransHeader TransHeaderIDparam
Estimate Variation TransVariation TransVariation.TransHeaderID
Line Item Level TransDetail TransDetail.VariationID
10400 or ShowChildItemsTrue)
Customer Info Account Left Join Account.ID
Contact Info AccountContact Left JoinAccountContact.ID
Billing Address Address as
Billing Address
Left Join BillingAddress.ID
Payment Terms PaymentTerms Left Join PaymentTerms.ID
Salesperson Employee as
Left Join Salesperson.ID
Entered By Employee as
Left Join EnteredBy.ID
Dvision EmployeeGroup
as Division
Left Join Division.ID

Notes: TransDetail records includes both parent and child items.Parent Items are always shown.Child Items are only shown when the appropriate parameter is set.Top Level Line Items have a ParentClassTypeID 10400 (variations).Child Items have a ParentClassTypeID 10100 (line items).
Important Data Fields
||Item || Field || Notes ||

Variation Name TransVariation.VariationName Suppress display if only one variation
Line Item Description TransDetail.Description
Product TransDetail.GoodsItemCode
Quantity TransDetail.Quantity
Line Item Description TransDetail.HTMLShortFormat This must be formatted as HTML in Crystal Reports
and set to auto-expand
Line Item Subtotal TransDetail.SubTotalPrice or
If suppressing child items, use the
MeAndSonSubtTotalPrice, otherwise
display the SubTotalPrice
Line Item Number TransDetail.LineItemNumber
Estimate Created Date TransHeader.CreatedDate
Company Name Account.CompanyName
Salesperson Employee.FirstName+“”+

Group By
The following group by options should be supported:
||Description ||Level ||Optional ||Group ID field ||Group Display Field ||Notes ||

Estimate 1 No TransHeader.ID TransHeader.EstimateNumber
Variation 2 No TransVariation.ID TransVariation.VariationName

Sort Options
The data within a group will always been sorted by the non-optional fields below and (if present) the user is given options to sort by the other fields.
||Description ||Sort Text ||Level ||Optional ||Notes ||

Estimate TransHeader.EstimateNumber 1 No
Variation TransVariation.VariationName 2 No If only one variation, suppress
the displaying of the variation
Line Item TransDetail.LineItemNumber 3 No

There are no user defined sort of options in this report.
User Filter Options
The following options need to be implemented to filter the data and/or provide information to reconfigure the report display.
||Description ||Parameter Name ||Default ||Data Filter ||Notes ||

Show Child Line Items False (TransDetail.ParentClassTypeID
or ShowChildItemsTrue)
Used to include child line items. Top level items have variations (10400) as their parent items.
Note:If Child Line Items are not shown, the line item must show TransDetail.MeandSonsSubTotalPrice, otherwise it displays the TransDetail.SubTotalPrice
Show Line
Item Totals
True n/a Hide or Display the MeAndSonSubTotalPrice or the SubTotalPrice on the individual line item.
Show Unit Price True n/a Hide or Display the unit price (MeAndSonSubTotalPrice/Quantity or SubTotalPrice/Quantity) for each line item. Note: You must check for Quantity0 and avoid division by zero. Also if Quantity0, display “n/a” as the unit price for that line.
True n/a Hide or Display the Deposit Required text at the bottom of the estimate
True n/a Hide of Display the Payment Terms at the bottom of the estimate
Hide Line
True n/a Hide or Display the HTML Line item description
True n/a Hide or Display the TransHeader.Notes at the bottom of the estimate
Entered By
True n/a Hide or Display the Entered By Information
Show Tax
True n/a Hide or Display the customers tax exemption information. Account.TaxNumber and Account.TaxNumberExpDate if not null. Note:if TaxNumber is null, do not display date either.
Show Store
Tax ID
True n/a Hide or Display the Store's Tax Exemption ID at the top of the estimate

Summary Fields

There are no summary fields for this report.


The TransDetail data should export into clean comma-delimited/Excel data.

Test Cases

Fields in Current Report

The current estimate SQL returns the following fields (in no particular order):

TransHeader.ID,TransDetail.LineItemIndex, Account.CompanyName, TransDetail.SubTotalPrice,TransDetail.UnitParamName, TransDetail.UnitParamValue, Contact.FirstName,Contact.LastName, TransDetail.ParentClassTypeID, Contact.Position,TransHeader.EstimateCreatedDate, TransDetail.LineItemNumber,TransVariation.SortIndex, TransVariation.VariationName,TransDetail.VariationID, TransHeader.TaxNumber, Salesperson.FirstName,Salesperson.LastName, Contact.EmailAddress, Salesperson.EmailAddress,TransHeader.PONumber, TransVariation.TotalPrice, TransVariation.TaxesPrice,TransVariation.SubTotalPrice, TransDetail.MeAndSonsSubTotalPrice,TransVariation.DiscountPrice, TransDetail.Description,Salesperson.PrimaryNumber, Salesperson.SecondaryNumber, Salesperson.PriNumberTypeText,Salesperson.SecNumberTypeText, Contact.PrimaryNumber,Contact.PriNumberTypeText, Account.PrimaryNumber, Account.PriNumberTypeText,Contact.SecondaryNumber, Contact.SecNumberTypeText, Account.SecondaryNumber,Account.SecNumberTypeText, TransHeader.EstimateNumber,InvoiceAddress.StreetAddress1, InvoiceAddress.StreetAddress2,InvoiceAddress.City, InvoiceAddress.State, InvoiceAddress.PostalCode,EnteredBy.FirstName, EnteredBy.LastName, TransHeader.TaxesPrice,TransDetail.ImageParamID, TransDetail.ImageParamClassTypeID, TransDetail.ID,CustomerGoodsItem.PrintAs, CustomerGoodsItem.ItemName,TransHeader.DiscountPrice, TransDetail.MeAndSonsDiscountPrice,TransDetail.DiscountPrice, TransHeader.OrderNotes,TransHeader.EstimateGreeting, TransHeader.Description,TransDetailGraphic.DetailImage, TransDetail.HTMLShortFormat,PaymentTerms.MessageText