Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit 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