{$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


1)
?Product_ProductID -1) or (?Product_ProductID TransDetail.GoodsItemID
2)
?Salesperson_EmployeeID -1) or (?Salesperson_EmployeeID GL.AccountID