Sales By X Report (Standard)

Sales By X Report (Standard)


The Reports Menu in Control allows you to choose nine options for the report:

SalesByXMenu.jpg

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

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

SalesByXPage6.jpg

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
((?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


See Also