{$page}
The Reports Menu in Control allows you to choose nine options for the report:
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.
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
salesbyxpage6.jpg_width800_height214
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 ? EndDateand 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 | 1) | Use -1 for All | ||||||
Company | ?Company_AccountID | Integer | -1 | 2) | 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