Differences

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

Link to this comparison view

sales_by_x_report_standard [2019/01/17 09:04] (current)
Line 1: Line 1:
 +{$page} ​
 +
 +
 +
 +The Reports Menu in Control allows you to choose nine options for the report: ​
 +
 +
 +
 +{{::​salesbyxmenu.jpg?​nolink&​|}}
 +
 +
 +
 +Note that the "​Date"​ menu option expanded by the "Show Detail"​ button requires the selection of two dates for the Beginning and Ending dates which are used for filtering values for the EntryDateTime from the Ledger table. ​
 +
 +
 +
 +{{::​salesbyxmenuexpanded.jpg?​nolink&​|}}
 +
 +
 +
 +One and only one of the Sort Options must be selected from the OptionalSort menu option ​
 +
 +
 +
 +The custom designed Header for the report displays a Logo and Company information taken from the Store table. The Header also displays the Report Title and each of the values that were chosen as report options.
 +
 +
 +
 +The "​CustomerFrequency"​ option for sorting is depicted in the image of a report shown below. For the purpose of explanation,​ a column without a column header is shown in this image. For the Customer Frequency option, Companies are sorted according to the number of distinct orders having activity in the Ledger for the specified date range. The Company shown as Rank 1 has the largest number of distinct orders for the period. The Company shown as Rank 5 in the below image has 46 New Orders and 8 Modified Orders within the date range; however, the added column reflects that the Company'​s Rank is determined by the value of 132 orders. That value (132) is also used to determine the Rank of the Company for orders from the Las Vegas Division show on page 6 of the report and may therefore not reflect the Company'​s actual rank within a particular Division.
 +
 +
 +
 +{{::​salesbyxpage1.jpg_width800_height287?​nolink&​|}}
 +
 +
 +
 +{{::​salesbyxpage6.jpg_width800_height214?​nolink&​|}}
 +
 +
 +
 +Purpose ​
 +
 +
 +
 +This report shows the sales between a specific time totaled according to any of a number of different options. These options include:
 +  * Customer Origin
 +  * Industry
 +  * Order Origin
 +  * Postal Code
 +  * Product
 +  * Product Category
 +  * Income Account
 +  * Customer (sorted by Volume)
 +  * Customer (sorted by Frequency)
 +  * Salesperson 1
 +  * Salesperson 2
 +  * Salesperson 3
 +  * Entered By
 +
 +
 +
 +The sales reflect both new sales as well as adjustments to previous sales. All sales numbers are pulled from the GL view.
 +
 +
 +
 +Information Structure ​
 +
 +
 +
 +Because the report also shows adjustments to orders, the data must be pulled from the Ledger and not from the TransHeader table.
 +
 +
 +
 +The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:
 +  * The General Ledger (GL view of the Ledger)
 +    * The order record (TransHeader).
 +      * The 1st Salesperson for the order (Employee).
 +      * The 2nd Salesperson for the order (Employee).
 +      * The 3rd Salesperson for the order (Employee).
 +      * The person that entered the order (Employee).
 +    * The Invoice Zip Code (Address)
 +    * The Order'​s Origin (MarketingListItem)
 +    * The line item (TransDetail)
 +      * The product (CustomerGoodsItem)
 +        * The product category (PricingElement)
 +    * The Company record (Account).
 +      * The Customer'​s default Origin (MarketingListItem)
 +      * The Customer'​s Industry (MarketingListItem)
 +    * The Income Account of the Sale (GLAccount)
 +    * The Division Name for the Orders (EmployeGroup).
 +
 +
 +
 +Tables Accessed ​
 +
 +
 +
 +||~ Alias ||~ Table ||~ Linkage or Filter Information ||
 +|| GL || Ledger || EntryDateTime between //?​StartDate//​ and //? EndDate//​and ClassificationType between 4000 and 4999 \\ and OffBalanceSheet ​ 0 ||
 +|| Order Info || TransHeader || **Left Join** Order_Info.ID ​ GL.TransactionID ||
 +|| Salesperson1 || Employee || **Left Join** Salesperson1.ID ​ Order_Info.Salesperson1ID ||
 +|| Salesperson2 || Employee || **Left Join** Salesperson2.ID ​ Order_Info.Salesperson2ID ||
 +|| Salesperson3 || Employee || **Left Join** Salesperson3.ID ​ Order_Info.Salesperson3ID ||
 +|| EnteredBy || Employee || **Left Join** EnteredBy.ID ​ Order_Info.EnteredByID ||
 +|| OrderOrigin || MarketingListItem || **Left Join** OrderOrigin.ID ​ Order_Info.OrderOriginID \\ Where OrderOrigin.MarketingListID ​ 13 ||
 +|| ZipCode || Address || **Left Join** Account.BillingAddressID ​ ZipCode.ID ||
 +|| LineItem || TransDetail || **Left Join** LineItem.ID ​ GL.TransDetailID ||
 +|| Product || CustomerGoodsItem || **Left Join** lineItem.GoodsItemID ​ Product.ID ||
 +|| Product Category || PricingElement || **Left Join** ProductCategory.ID ​ Product.CategoryID \\ where ProductCategory.ClassTypeID ​ 12020 ||
 +|| Customer_Info || Account || **Left Join** Account.ID ​ GL.AccountID ||
 +|| CustomerOrigin || MarketingListItem || **Left Join** CustomerOrigin.ID ​ Account.OriginID \\ Where CustomerOrigin.MarketingListID ​ 11 ||
 +|| CustomerIndustry || MarketingListItem || **Left Join** CustomerIndustry.ID ​ Account.IndustryID \\ Where CustomerIndustry.MarketingListID ​ 10 ||
 +|| IncomeAccount || GLAccount || **Left Join** IncomeAccount.ID ​ GL.GLAccountID ||
 +|| Division || EmployeeGroup || **Left Join** Division.ID ​ GL.DivisionID ||
 +|| Store || Store || Left Join Store.StoreID ​ GL.StoreID ||
 +|| Command || SQL || to get totals from GL for each Order_Info.ID ||
 +Notes: Command SQL: \\ declare @StartDate datetime; \\ declare @EndDate datetime; \\ set @StartDate ​ {?​fx_RangeStart_Date};​ \\ set @EndDate ​ {?​fx_RangeEnd_Date};​ \\ * select TH.ID, \\ * SUM(GL.amount) as dollars, \\ * COUNT(GL.ID) as Freq \\ * from dbo.Ledger GL left join dbo.TransHeader TH \\ * ON GL.TransactionID ​ TH.ID \\ * Where GL.EntryDateTime between @StartDate and @EndDate \\ * AND GL.GLClassificationType between 4000 and 4999 \\ * AND GL.OffBalanceSheet ​ 0 \\ * Group by TH.id \\ Important Display Fields ​ \\ Top Level Display Fields \\ ||~ Item ||~ Field ||~ Notes ||
 +|| Ranking ||   || Sequence Number based on the sort, 1 for first, etc. ||
 +|| Ranked Field ||   || Display the ranked field (Salesperson,​ etc.) ||
 +|| Number of New Orders ||   || Count the number of distinct orders where GL.EntryDateTime ​ TransHeader.SaleDate ||
 +|| Number of Adjusted Orders ||   || Coune the number of distinct orders where GL.EntryDateTime ​ TransHeader.SaleDate ||
 +|| Total Sales || Sum(GL.Amount) || Sales for the ranked item for the selected period ||
 +|| % of Total Sales ||   || If possible, add a column to show what percentage of total sales this item is ||
 +Drill Down Display Fields \\ ||~ Item ||~ Field ||~ Notes ||
 +|| Order Number || Order_Infor.OrderNumber ||   ||
 +|| Sales Person || Salesperson1.FirstName + " " + Salesperson1.LastName ||
 +|| Company || Customer_Info.CompanyName ||   ||
 +|| Order Description || Order_Info.Description ||   ||
 +|| GL Date || GL.EntryDateTime ||   ||
 +|| Sale Amount || **Command.dollars** || the command returns a sum based on the values in GL.Amount with their existing sign, positive or negative. ||
 +|| Is Adjustment? || Order_Info.SalesDate < ?StartDate || Consider any sales in the report for orders previously a sale as adjustments. ||
 +|| Current Order Status || Order_Info.StatusText ||   ||
 +|| Current Order Total || Order_Info.TotalPrice ||   ||
 +|| Current Balance Due || Order_Info.BalanceDue ||   ||
 +Group/Sort By  \\ The following groups are inserted in the report design: \\ ||~ Description ||~ Level ||~ Optional ||~ Group ID field ||~ Group Display Field ||~ Notes ||
 +|| Division || 1 || No || GL.DivisionID || Division.DivisionName ||   ||
 +|| Selected Field || 2 || No ||   || Depends on Field Selected ||   ||
 +The group by field for each of the selected options would be: \\ ||~ ?​OptionalSort ||~ Group condition and sort ||~ Name displayed in Group Footer ||
 +|| CustomerOrigin || Customer_Info.OriginID || CustomerOrigin.ItemName ||
 +|| CustomerIndustry || Customer_Info.IndustryID || CustomerIndustry.ItemName ||
 +|| OrderOrigin || Order_Info.OrderOriginID || OrderOrigin.ItemName ||
 +|| PostalCode || ZipCode.PostalCode || ZipCode.PostalCode ||
 +|| Product || LineItem.GoodsItemID || Product.ItemName ||
 +|| ProductCategory || ProductCategory.ID || ProductCategory.ElementName ||
 +|| IncomeAccount || GL.GLAccountID || IncomeAccount.AccountName ||
 +|| CustomerVolume || Command.dollars || Customer_Info.CompanyName + " Rank " + Command.Freq ||
 +|| CustomerFrequency || Command.Freq || Customer_Info.CompanyName + " Rank " + Command.Freq ||
 +|| Salesperson1 || Order_Info.Salesperson1ID || Salesperson1.FirstName + " " + Salesperson1.LastName ||
 +|| Salesperson2 || Order_Info.Salesperson2ID || Salesperson2.FirstName + " " + Salesperson2.LastName ||
 +|| Salesperson3 || Order_Info.Salesperson3ID || Salesperson3.FirstName + " " + Salesperson3.LastName ||
 +|| EnteredBy || Order_Info.EnteredByID || EnteredBy.FirstName + " " + EnteredBy.LastName ||
 +The data within a group will always be sorted by these fields if they in the group by: \\ ||~ Description ||~ Sort Text ||~ Level ||~ Optional ||~ Notes ||
 +|| Division || Division.DivisionName || 1 || No ||   ||
 +|| Selected Field ||   || 2 || No ||   ||
 +The user has no sort options for this report. \\ Parameters and 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 ||~ Type ||~ Default ||~ Data Filter ||~ Notes ||
 +|| Report Title || ?​ReportTitle || String ||   ​|| ​  || used in header ||
 +|| Report Path || ?ReportPath || String ||   ​|| ​  || show on bottom of report ||
 +|| Suppress Headers || ?​SuppressHeaders || Boolean || False ||   || Used to hide report and page headers and footers ||
 +|| Sales Date Range || ReportDateRange || DateTime Range ||   ​|| ​  ​|| ​  ||
 +|| Product || ?​Product_ProductID || Integer || -1 || ((?​Product_ProductID ​ -1) or (?​Product_ProductID ​ TransDetail.GoodsItemID)) || Use -1 for All ||
 +|| Company || ?​Company_AccountID || Integer || -1 || ((?​Salesperson_EmployeeID ​ -1) or (?​Salesperson_EmployeeID ​ GL.AccountID)) || Use -1 for All ||
 +Report Formulas ​ \\ ||~ Formula Name ||~ Syntax ||~ Return value ||~ Purpose ||
 +|| @SumGLAmt ||   ​|| ​  ​|| ​  ||
 +Summary Fields ​ \\ The following summary fields are necessary for each group. \\ ||~ Description ||~ Fields ||
 +|| Number of New Orders || Count(GL.TransHeaderID) where Order Sale Date between Report Date Range ||
 +|| Number of Adjusted Orders || Count(GL.TransHeaderID) where Order Sale Date //NOT// between Report Date Range ||
 +|| Total Amount || Sum(GL.Amount) ||
 +Export ​
 +
 +
 +
 +The report data should export into clean comma-delimited/​Excel data.
 +
 +
 +
 +Test Cases 
 +
 +
 +