Canadian Sales - Sales By Customer (Group) in Canada
- April Dianne Gahuman
- Albert Saludaga
This report displays the trend of the total received, cost, and gross margin per customer per year.
Report Definition
Author
April Dianne Gahuman (Full Scale developer)
Presentation Medium
SSRS Portal
Fields (data)
Year of Invoice Date
Year
Month of Invoice Date
Month Name
Month
Sales By Customer Group in Canada
Customer
Cost
Total Received
Gross Margin
Year
Data Sources
The main data source will be the Tableau_TB_SalesView view in the eiboxoffice database (192.168.10.215).
Data Set
Data Set | Query | Description | |
|---|---|---|---|
| 1 | YearOfInvoiceDate | SELECT DISTINCT YEAR(sv.InvoiceDate) AS Year
FROM [eiboxoffice].[dbo].[Tableau_TB_SalesView] sv
WHERE YEAR(sv.InvoiceDate) IS NOT NULL
ORDER BY YEAR(sv.InvoiceDate) DESC | This gets the distinct list of years available based on the invoice date to be used as a parameter drop-down. |
| 2 | MonthOfInvoiceDate | SELECT DISTINCT
DATENAME(MONTH, DATEADD(MONTH, MONTH(sv.InvoiceDate), -1 )) AS MonthName
,MONTH(sv.InvoiceDate) AS Month
FROM [eiboxoffice].[dbo].[Tableau_TB_SalesView] sv
WHERE MONTH(sv.InvoiceDate) IS NOT NULL
ORDER BY MONTH(sv.InvoiceDate) | This gets the distinct list of months available based on the invoice date to be used as a parameter drop-down. |
| 3 | SalesByCustomerGroupInCanada | SET NOCOUNT ON;
WITH CTE AS
(
SELECT
sv.Customer
,SUM(sv.Cost) AS Cost
,SUM([Total]-[Cost]) AS GrossMargin
,SUM(sv.Total) AS TotalReceived
,YEAR(sv.InvoiceDate) AS Year
FROM [eiboxoffice].[dbo].[Tableau_TB_SalesView] sv WITH(NOLOCK)
WHERE
YEAR(sv.InvoiceDate) IN (@year)
AND MONTH(sv.InvoiceDate) IN (@month)
AND IsCancelled = 0
GROUP BY sv.Customer, sv.InvoiceDate
)
SELECT
Customer
,ROUND(SUM(Cost), 0) AS Cost
,ROUND(SUM(TotalReceived), 0) AS TotalReceived
,ROUND(SUM(GrossMargin), 0) AS GrossMargin
,Year
FROM CTE
GROUP BY Customer, Year
ORDER BY Year, Customer | This gets the data for the customer, cost, gross margin, and total received per year. |
Calculations
Column Name | Data Type | Calculation |
|---|---|---|
Gross Margin | money |
|
Filters
Filter Name | Description |
|---|---|
Year of Invoice Date | The dropdown values include the distinct list of years based on the invoice date. |
Month of Invoice Date | The dropdown values include a distinct list of months. |
Screenshot
