Differences

This shows you the differences between two versions of the page.

Link to this comparison view

estimate_report_standard [2019/01/17 09:04] (current)
Line 1: Line 1:
 +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 Join**AccountContact.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 Quantity0 and avoid division by zero. Also if Quantity0, 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
 +
 +
 +
 +