Estimate Report


Purpose

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

TransHeaderID=param
Estimate Variation
TransVariation
TransVariation.TransHeaderID=
TransHeader.ID

Line Item Level
TransDetail
TransDetail.VariationID=
TransVariation.ID
(TransDetail.ParentClassTypeID=
10400 or ShowChildItems=True)
Customer Info
Account
Left Join Account.ID=
TransHeader.AccountID

Contact Info
AccountContact
Left JoinAccountContact.ID=
TransHeader.ContactID

Billing Address
Address as
Billing Address
Left Join BillingAddress.ID=
TransHeader.InvoiceAddressID

Payment Terms
PaymentTerms
Left Join PaymentTerms.ID=
Account.PaymentTermsID

Salesperson
Employee as
Salesperson
Left Join Salesperson.ID=
TransHeader.Salesperson1ID

Entered By
Employee as
EnteredBy
Left Join EnteredBy.ID=
TransHeader.EnteredByID

Dvision
EmployeeGroup
as Division
Left Join Division.ID=
TransHeader.DivisionID

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
Estimate
Number
TransHeader.EstimateNumber

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
TransDetail.MeandSonSubTotalPrice
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+""+
Employee.LastName





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
name
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=
10400
or ShowChildItems=True)
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 Quantity=0 and avoid division by zero. Also if Quantity=0, display "n/a" as the unit price for that line.
Show
Deposit
Amount

True
n/a
Hide or Display the Deposit Required text at the bottom of the estimate
Show
Payment
Terms

True
n/a
Hide of Display the Payment Terms at the bottom of the estimate
Hide Line
Item
Description

True
n/a
Hide or Display the HTML Line item description
Show
Notes

True
n/a
Hide or Display the TransHeader.Notes at the bottom of the estimate
Show
Entered By

True
n/a
Hide or Display the Entered By Information
Show Tax
Exemption
ID

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.

Export

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