1
Reports
1.1
Layout and Titles
Sales Analysis Report: as of now we have assumed that this shall be a
single report with multiple tabs for weekly channel, weekly web, monthly channel and monthly web sales. In case the refresh
cycles for the weekly and the monthly sales are different then we may have to consider them as separate reports.
|
Report (Tab) # | Page Layout |
Report (Tab) Name | Description | Other Considerations |
| Landscape | Portrait
| Letter Size | Legal Size |
| | | x |
|
| Monthly customer sales analysis(sales officer view) |
This particular report
gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes
including the web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is
to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis.
In this report we shall not be reporting for the corporate entities. |
|
| | | x |
| | Monthly web sales analysis(sales officer view) | This particular report gives the profitability analysis for each customer
based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get
a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis.
In this report we shall not be reporting for the corporate entities. |
|
| | | x |
| | Monthly customer sales analysis(Regional Manager’s view) | This particular report gives the profitability
analysis for each customer based on channel sales. Channel sales shall include all the class codes excluding web sales.
The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for
sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall
not be reporting for the corporate entities. | |
|
| | x |
| | Monthly web sales analysis(Regional Manager’s View) | This particular report gives the profitability
analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose
of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly
and 6 monthly basis. In this report we shall not be reporting for the corporate entities. | |
Daily Sales Report
|
Report (Tab) # | Page Layout |
Report (Tab) Name | Description | Other Considerations |
| Landscape | Portrait
| Letter Size | Legal Size |
| 1 | | x |
|
| Regional | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of regions. Comparisons shall be made from the present year with the figures
of last year. |
|
| 2 | | x |
| | Shipping demand branch | This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit
and % of profitability in terms of shipping demand branch. Comparisons shall be made from the present year with the figures
of last year. | |
| 3
|
| x
|
|
| Original demand branch | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of original demand branch. Comparisons shall be made from the present year
with the figures of last year. | |
| 4
|
| x
|
|
| Customer type | This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and %
of profitability in terms of customer type. Comparisons shall be made from the present year with the figures of last year.
|
|
| 5 |
| x
|
|
| Sales Area | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of sales area. Comparisons shall be made from the present year with the figures
of last year. | |
| 6 |
| x | | | Order
method | This particular
tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of order method.
Comparisons shall be made from the present year with the figures of last year. |
|
1.2
Report Fields
Throughout this document we shall use Sales and cost under various denominations. For the purpose
of this report sales and cost have been defined in the following manner:
- Sales is defined as value of sales order+ value of returned merchandise +value of returned
defective orders+ value of goodwill orders.
- Reporting cost can be obtained from the line item entry.
- Actual Cost is the cost from the inventory
- Gross Profit(Actual)=sales-actual cost
- Net profit(Actual)=Gross
Profit(Actual)-freight cost
- Gross Profit(Reporting)=sales-reporting cost
- Net Profit(Reporting)=sales-reporting cost-freight cost
- Freight as % age of sales=Freight cost/Sales
- Return Rate= value of returned merchandise +value of returned defective orders+ value of goodwill orders
- Return(%age sales)= Return Rate/Sales
- ‘Percent’ is Profit/Sales.
- Previous fiscal month is defined as current fiscal month-1
- Order : this refers to the order line entries.
- ‘Profit’[for the respective case scenario] is Sales-Cost
- ‘Percent’ is Profit/Sales.
Report Name: Sales Analysis report
Report
#:
1
Field Name: Customer
number
Business Description: the number of the customer
Field Name:
Name
Business Description: Customer name
Field
Name: Sales (previous
Month)
Business Description: It is the total sales for the previous month.
Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last
fiscal day of the previous fiscal month.
Field
Name: Net Sales 12 month
average
Business Description: it gives the average of the total sales over
last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the current fiscal year during the calculation then we shall continue by doing a backward calculation from
the last fiscal month of the previous fiscal year.
Field Name:
Net Sales 3 month average
Business Description: it gives the average of the total sales over
last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal
day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal
month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal
month of the previous fiscal year
Field Name:
Gross Profit 3 month average[reporting]
Business Description: it gives the average of the total gross profit(reporting)
over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last
fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Gross Profit 12 month average (reporting)
Business Description: it gives the average of the total profits(reporting)
over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last
fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Gross Profit previous month (reporting)
Business Description: It gives the gross profit (reporting) for the
previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Net Profit previous month (reporting)
Business Description: It gives the net profit (reporting) for the
previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. Net profit (reporting) is defined as sales-reporting cost-freight
cost.
Field Name:
%age freight sale previous month
Business Description: It gives the % ratio of the freight costs with
regards to the total sales. Previous month is defined as the number of fiscal days between the first fiscal day of the previous
fiscal month and the last fiscal day of the previous fiscal month.
Field Name:
Return Rate previous month
Business Description:
It gives the return rate for previous month. Return rate is defined as value of returned merchandise +value of returned defective
orders+ value of goodwill orders. Previous month is defined as the number of fiscal days between the first fiscal day of the
previous fiscal month and the last fiscal day of the previous fiscal month.
Field Name:
Customers Lost-previous month
Business Description: It gives the number of customers lost between
the first fiscal day and the last fiscal day of the previous month. Previous month is defined as the number of fiscal
days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A lost
customer shall have 0 sales for the previous fiscal month and non-zero and greater than 0 sales for the current fiscal month-2.
Field
Name: Customers gained-previous
month
Business Description: It gives the number of customers lost within
the first fiscal day and the last fiscal day of the previous month. Previous month is defined as the number of fiscal
days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A gained
customer shall have 0 sales for the current fiscal month-2 and greater than 0 sales for the previous fiscal month.
Field Name:
Growth Rate by customer segment
Business Description: This gives the sales(sales order+ value of returned merchandise
+value of returned defective orders+ value of goodwill orders)
per number of active customers for the previous month. Previous month is defined as the number of fiscal days between the
first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Active customers are defined
as those customers with sales greater than 0. This measure’s slice and dice parameters include month, customer segment
and sales area.
Field Name:
Average order size
Business Description: This gives the $ volume per order for
the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. The $ value of the order is given by the sale volume. The orders
selected should be such that the order line items should have 0 return rate (value
of returned merchandise +value of returned defective orders+ value of goodwill orders).
Break/Sort:
Salesman Number and Sales Man Name(SB)
Totaling
Options: Not specified in the word document as of now
Field
Name: Region
Business
Description: Region name
Field Name:
Sales-this date
Business Description: Sales as of a given date [sale as of date the
report has been refreshed]
Field Name:
Cost-this date
Business Description: Actual Cost as of a given date [Actual
cost as of date the report has been refreshed]
Field Name: Profit-this
date
Business Description: Actual Profit as of a given date[profit
as of date the report has been refreshed. Profit shall be defined as Sales-Actual Cost]
Field
Name: Percent-this date
Business Description:
Percentage Actual profit as of a given date
Field Name:
Sales-this date (last year)
Business Description: Sales as of same day last year [same day last
year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name: Cost-this
date (last year)
Business Description: Actual Cost as of same day last year
[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Profit-this date (last year)
Business Description: Actual Profit as of same day last
year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field
Name: Percent-this date
(last year)
Business Description: Percentage actual profit as same day
last year[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal
year]
Field Name:
Sales-cumulative this month
Business Description: Cumulative sales as of this month [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
month]
Field Name:
Sales (%age)-overall sales (this month)
Business Description: Percentage Cumulative sales as of this month
per region [cumulative sales for the number of business days between the date the report is refreshed and the first
day of the current fiscal month]
Field Name:
Cost-cumulative this month
Business Description: Cumulative actual cost as of this
month [cumulative cost for the number of business days between the date the report is refreshed
and the first day
of the current fiscal month]
Field Name:
Profit-cumulative this month
Business Description: Cumulative actual profit as of this
month [cumulative profit for the number of business days between the date the report is refreshed
and
the first day of the current fiscal month]
Field Name:
Percent-cumulative this month
Business Description: Percentage actual profit as of this
month [percentage profit for the number of business days between the date the report is refreshed
and
the first day of the current fiscal month]
Field Name:
Sales-cumulative this month (last year)
Business Description:
Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st
fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal
month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Sales (%age)-overall sales(this month last year)
Business Description:
Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this month last year [cumulative
sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous
fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in
the previous fiscal year]
Field Name:
Cost- cumulative this month (last year)
Business Description: Cumulative Actual cost as of this month last
year [cumulative cost for the number of business days between the 1st fiscal day of the same fiscal month
in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date
of refresh in the previous fiscal year]
Field Name:
Profit- cumulative this month (last year)
Business Description: Cumulative actual profit as of this month last
year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal
month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh
in the previous fiscal year]
Field Name:
Percent- cumulative this month (last year)
Business Description: Cumulative percent actual profit as of this
month last year [cumulative percent profit for the number of business days between the 1st fiscal day
of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as
the date of refresh in the previous fiscal year]
Field Name:
Sales-cumulative this year
Business Description: Cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
year]
Field Name:
Daily Average (this year)-Yearly
Business Description: Cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.
Field Name:
Sales (%age)-overall sales (this year)
Business Description: Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this year [cumulative percentage sales for the number of
business days between the date the report is refreshed and the first day if the current fiscal year]
Field Name:
Cost-cumulative this year
Business Description: Cumulative actual cost as of this
year [cumulative cost for the number of business days between the date the report is refreshed and the first day of the current
fiscal year]
Field Name:
Profit-cumulative this year
Business Description: Cumulative actual profit as of this
year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the
current fiscal year]
Field Name:
Percent-cumulative this year
Business Description: Percentage Actual profit as of this
year [percentage profit for the number of business days between the date the report is refreshed and the first day of the
current fiscal year]
Field Name:
Daily Average this year (monthly)
Business Description: Cumulative sales as of this month [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year
to the date of refresh.
Field Name:
Sales-cumulative last year
Business Description:
Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal
day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the
date of refresh in the previous fiscal year]
Field Name:
Sales (%age)-overall sales (last year)
Business Description:
Percentage (Region/customer
type/shipping demand/original demand) as of last year [cumulative sale
for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal
day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Cost- cumulative last year
Business Description: Cumulative Actual cost as of last
year [cumulative cost for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year]
Field Name:
Profit- cumulative last year
Business Description: Cumulative Actual profit as of last
year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year]
Field Name:
Percent- cumulative last year
Business Description: Cumulative percent Actual profit as
of last year [cumulative percent profit of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day (the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the
previous fiscal year]
Field Name:
Daily Average (last year)-Yearly
Business Description: Cumulative sales as of last year [cumulative
sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh
last year [the same business day for the same fiscal month and week for previous fiscal year].
Field Name:
Daily Average last year (monthly)
Business Description: Cumulative sales as of this month last year
[cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in
the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date
of refresh in the previous fiscal year] per
the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal
year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last
year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .
Field Name:
Days-month
Business Description: The number of business days beginning from
the first business day of the month to the date of report refresh in the current fiscal month of the current fiscal year.
Field Name:
Days-month (last year)
Business Description: The number of business days beginning
from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report
refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report
refresh in the same fiscal week and month of the previous fiscal year] .
Field Name:
Days-year
Business Description: The number of business days beginning from
the first business day of the current fiscal year to the date of report refresh.
Field Name:
Days-year (last)
Business Description: The number of business days beginning from
the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same
fiscal month and week for previous fiscal year].
Field Name:
Change Sales daily
Business Description: The difference between Sales as of a given
date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as
the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Change Sales daily-%age
Business Description: The %age change in daily sales(The difference
between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same
day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]) with respect
to Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month
for the previous fiscal year])
Field Name:
Change Profit Daily
Business Description: The difference between Actual Profit
as of a given date[given date is the date of report refresh] and Profit as of same day last year [same day last year
is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Change Profit Daily-%age
Business Description: The %age change in daily Actual profit
(The difference between Actual Profit as of a given date[given date is the date of report refresh] and Profit as of
same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous
fiscal year] with respect to Profit as of same day last year [same day last year is defined as the same fiscal day for the
same fiscal week and month for the previous fiscal year])
Field
Name: Change Sales monthly
Business
Description: The difference between cumulative sales as of this month [cumulative sales for the number of
business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales
as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal
month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as
the date of refresh in the previous fiscal year]
Field Name:
Change Sales monthly-%age
Business Description: The %age change in monthly sales(The difference
between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is
refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale
for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year])
with respect to cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the
previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of
refresh in the previous fiscal year
Field Name:
Change Profit Monthly
Business Description: The difference between Cumulative Actual profit as of
this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of
the current fiscal month] and cumulative actual profit as of this month last year [cumulative profit for the number
of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in
the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Profit Monthly-%age
Business Description: the % age change in monthly Actual Profit(The
difference between Cumulative profit as of this month [cumulative profit for the number of business days between the date
the report is refreshed and the first day of the current fiscal month] and cumulative profit as of this month last year [cumulative
profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and
the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect
to cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal
day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week)
as the date of refresh in the previous fiscal year]
Field Name:
Change Sales Yearly
Business Description: The difference between cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year]
and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous
fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in
the previous fiscal year]
Field Name:
Change Sales Yearly-%age
Business Description: The %age change in yearly sales(The difference
between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is
refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number
of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the
same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for
the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal
day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year
Field Name:
Change Profit Yearly
Business Description: The difference between Cumulative Actual Profit as of
this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of
the current fiscal year] and cumulative Actual Profit for last year [cumulative profit for the number of business
days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal
month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Profit Yearly-%age
Business Description: the % age change in yearly Actual
profits(The difference between Cumulative actual profit as of this year [cumulative actual profit for
the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative
actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal
day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the
date of refresh in the previous fiscal year]) with respect to cumulative actual profit for last year [cumulative
actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Daily Average (Monthly)
Business Description: Difference between cumulative sales as of this
month last year [cumulative sale for the number of business days between the 1st fiscal day of the same
fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week)
as the date of refresh in the previous fiscal year] per
the number of
business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same
fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same
fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] and cumulative sales
as of this month [cumulative sales for the number of business days between the date the report is refreshed and the
first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month
in the current fiscal year to the date of refresh.
Field Name:
Change Daily Average (Yearly)
Business Description: Difference between cumulative sales as of last
year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of
report refresh last year [the same business day for the same fiscal month and week for previous fiscal year] and cumulative
sales as of this year [cumulative sales for the number of business days between the date the report is refreshed
and the first day of the current fiscal year] per number of business days beginning from the first business day of the current
fiscal year to the date of report refresh.
Break/Sort:
Date of the report
Totaling Options:
sum sales, cost, Profit. We need to take an average of %.
In the other tabs of the report all the other fields besides the first
one shall remain the same. We shall be listing only the first field name for the description of the remaining tabs.
1.3 Prompts
Sales Analysis Report
Prompts
are the filter options. For each of the tabs 1 to 4 they need to be specified by the client.
Tab #1: Monthly customer sales
analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.
Tab
#2: Monthly web sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given
a static report.
Tab #3: Monthly customer sales analysis (Regional Manager’s view): The filter criteria are as
following:
Prompt #:
1
Tab #:
3
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
2
Tab #:
3
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Tab #4: Monthly web sales analysis (Regional Manager’s
View):
Prompt #:
3
Tab #:
4
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
4
Tab #:
4
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Daily Sales Report:
We
shall be selecting only the following as the filtering criteria.
Prompt #:
1
Tab #:
1
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
2
Tab #:
2
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
3
Tab #:
2
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt #:
4
Tab #:
3
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
5
Tab #:
3
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt #:
6
Tab #:
4
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
7
Tab #:
4
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt
#:
8
Tab #:
5
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
9
Tab #:
5
Prompt Message:
‘Please select the Region’
Prompt Conditions: =
Prompt #:
10
Tab #:
6
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
11
Tab #:
6
Prompt Message:
‘Please select the Region’
Prompt Conditions: =
2 Technical Details
2.1 Data Provider
Details
2.1.1
Result Objects
Sales Analysis Report
The following are the result objects for the ‘Sales Analysis Report’.
Sales: measure
Cost: measure
Gross Profit: measure
Net profit: measure
Month: dimension
Customer: dimension
Sales area: dimension
Sales person
(shall be used as a segment): dimension
Sales channel: dimension
Item class: dimension
Product class: dimension
Product Line: dimension
Geographic code: dimension.
Sort
details: the sorting needs to take place by the value of a measure object. Hence we cannot specify the conditions for the
same in the query.
Daily Sales Report: The following are the objects as per our anticipation as of now.
Region: dimension
Shipping branch:
dimension
Original demand branch: dimension [both original
demand branch and shipping branch form part of the supply chain dimension. Should we add additional filtering criteria for
the same?]
Sales order method (web based, channel): dimension
Fiscal calendar: dimension
Sales Area: dimension
Customer: dimension
Days: Measure
Sum of Sales: Measure
Sum of Cost: Measure object
Sort details: N/A
Query Name: N/A.
The client has clarified that it does not require additional grouping options such as geographic and
product dimensions for this report.
2.1.2
Condition Objects
Method 4: use of correlated
sub queries
Method 5: use of aggregate awareness within the universe
The option chosen by the technical team shall depend
on the following factors:
- The data load for the queries
- The slice and dice capabilities expected for each view by the client
- Whether the reports shall run on an ad hoc basis or they
shall run on a pre scheduled basis.
SQL
Sales analysis report
The SQL will involve inner join between dimension and
Fact with the filter conditions for the dimensions.
We are presenting the approximate SQL for each of the tabs in consideration. However in case aggregates
are produced then the sql shall change.
Tab
#1:
Select
sum(Sales), sum(cost), sum(profit),
month, customer, sales
area, sales person, sales channel, product groups, geographic code
from [specify the join conditions between the dimensions and the fact. /*please
see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the (fiscal month-13) of
the date of refresh
and
sales channel= specify sales channel
group by month, customer, sales area, sales channel, geographic code, item class, product class, product
line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- The sorting criteria specified by the client involves
the sum of sales. This is a measure attribute. We shall have to specify the same in the report.
Tab #2:
Select
sum(Sales), sum(cost), sum(profit),
month, customer, sales area, sales person, sales channel, product groups, geographic code
from [specify the join conditions between
the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of
the (fiscal month-13) of the date of refresh
and sales channel= specify web channel
group by month, customer, sales area, sales channel, geographic code, item class,
product class, product line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- The sorting criteria specified by the client involves
the sum of sales. This is a measure attribute. We shall have to specify the same in the report.
Tab #3:
Select
sum(Sales), sum(cost), sum(profit), count(distinct(customers))
month, customer, sales area, sales
person, sales channel, product groups, geographic code
from [specify the join conditions between the dimensions and the fact. /*please see the note at
the bottom of the page*/]
where
specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh
and sales channel= specify
web channel
group by
month, customer, sales area, sales channel, product groups, geographic code, item class, product class, product line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- The drill down/drill through
shall be as per the dimensions specified in the group by option namely: date, customer, sales area, geographic code,
sales channels, item class,
product class and product line.
- The client expects no additional ‘group by’ options with any of the
other dimensions.
- The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We
shall have to specify the same in the report.
Daily Sales Report
Tab #1:
Select region,
sum(Sales),
sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between
the prompt date and 1st day of the previous fiscal year
group by region, date, month, year
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #2:
Select shipping demand branch, region
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and
the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the
previous fiscal year
and
prompt for region
group
by shipping demand branch, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to
add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client
does not expect to drill down by the region parameter.
- The client expects no additional ‘group by’ options with any
of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #3:
Select original demand branch, region,
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and
the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the
previous fiscal year
and
prompt for region
group
by original demand branch, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to add ‘region’ as a segment in the reports.
That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
- The client expects no
additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have
been given by the client.
Tab #4:
Select order method, region,
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please
see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the previous fiscal year
and prompt for region
group by order method, date,
month, year, region
Additional details about this tab[all
the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through
any of the dimensions. This is a static view.
- The
client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region.
However the client does not expect to drill down by the region parameter.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #5:
Select customer type, region,
sum(Sales), sum(cost), date, month,
year
from [specify the
join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st
day of the previous fiscal year
and prompt for region
group by customer type, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to add ‘region’ as a segment in the reports.
That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
- The client expects no
additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria
have been given by the client.
Tab
#6:
Select sales area,
region,
sum(Sales),
sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between
the prompt date and 1st day of the previous fiscal year
and prompt for region
group by sales area, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion
with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static
view.
- The client expects to add ‘region’ as a segment
in the reports. that is why we have had to group by region. However the client does not expect to drill down by the region
parameter.
- The client expects no additional ‘group by’ options with any of the other dimensions. No slice and
dice capabilities are expected.
- No sorting criteria have been given by the client.
/* details about the join conditions
between dimensions and facts*/
The way
in which shall design the universe shall entail an inner join between dimensions and fact. A sample of the same(from one of
the author’s previous projects) is attached below:
From (("ETMC"."dbo"."fact_ledger2" "fact_ledger2"
INNER JOIN "ETMC"."dbo"."dim_account"
"dim_account" ON
"fact_ledger2"."acct_dim_id"="dim_account"."acct_dim_id")
INNER JOIN "ETMC"."dbo"."dim_company"
"dim_company" ON
"fact_ledger2"."comp_dim_id"="dim_company"."comp_dim_id")
INNER JOIN "ETMC"."dbo"."fiscal_calender"
"fiscal_calender" ON
"fact_ledger2"."CAL_id"="fiscal_calender"."cal_id"
inner join "ETMC"."dbo"."dim_dept"
"dim_dept" on
"fact_ledger2"."dept_dim_id"="dim_dept"."dept_dim_id"
2.2
Document Variables
1. %age freight sale previous month= Return Rate previous month/sales(previous month)
2. Average order size=sum(sales)/count(clean order)
3. Profit-this date= (sales-this date)-(cost-this date)
4. Percent-this date=(profit-this
date)/(Sales-this date)
5. Profit-this date(last year)= [sales-this date(last year)]-[cost-this date(last year)]
6. Percent-this date(last year)= Profit-this date(last year)/ sales-this date(last year)
7. Percent-cumulative
this month=Profit-cumulative this month/Sales-cumulative this month
8. Percent- cumulative
this month (last year)=Profit- cumulative this month (last year)/Sales-cumulative this month (last year)
9. Daily
Average (this year)-Yearly=Sales-cumulative this year/Days-year
10. Daily Average this year (monthly)=Sales-cumulative
this month/Days-month
11. Percent- cumulative last year=(Profit- cumulative last year)/(Sales-cumulative last year)
12. Daily
Average (last year)-Yearly=Sales-cumulative last year/Days-year (last)
13. Daily Average last year (monthly)= Sales-cumulative
this month (last year)/Days-month (last year)
14. Change Sales daily= (Sales-this date) - (Sales-this date (last year))
15. Change Profit daily=
(Profit-this date)-(Profit-this date (last year))
16. Change Sales daily-%age= Change Sales daily/(Sales-this date (last year))
17. Change Profit daily-%age=
Change Profit daily/( Profit-this date (last year))
18. Change Sales Yearly= (Sales-cumulative this year)-(Sales-cumulative last year)
19. Change Profit Yearly=
(Profit-cumulative this year)-(Profit-cumulative last year)
20. Change Sales monthly= (Sales-cumulative this
month)-( Sales-cumulative this month(last year))
21. Change Profit monthly=( Profit-cumulative this month)-( Profit-cumulative this month(last year))
22. Change
Profit Monthly-%age= Change Profit monthly/ Profit-cumulative this month (last year)
23. Change Sales Monthly-%age=
Change Sales monthly/( Sales-cumulative this month(last year))
24. Change Profit Yearly-%age= Change Profit yearly/
(Profit-cumulative last year)
25. Change Sales Yearly-%age=Change Sales Yearly/(Sales-cumulative last year)
26. Percent- cumulative
this year= (Profit- cumulative this year)/(Sales-cumulative this year)
27. Change Daily Average (Yearly) =
(Daily Average (last year)-Yearly)-( Daily Average (this year)-Yearly)
28. Change Daily Average
(Monthly) = (Daily Average (last year)-Monthly) - (Daily Average (this year)-Monthly)
2.3
Report Layout
Specifications
need to be given by the client.
3
Efficiency Analysis
/* this will have to be worked out with the client*/
The efficiency analysis objects below are pulled from different areas
that will help determine how efficient the report is in processing and in presentation. (Example
- Will the report complete if its YTD and will it file out to Excel successfully)
BOR # =
The BusObj Report Number issued by IT when the report has been Implemented. If the report is new then type “NEW”.
Periocity =
How often the report is scheduled to run. (Monday, First of Month, etc,.)
Time Code =
The time period code that indicates the time period that was use while testing the report. (Daily, WK, MTD, QTR, YTD, etc,.)
File Size (KB) WO/Data =
The size of the rep file without any data in KB
File Size
(MB) WO/Data = The size of the rep file without any data in MB (take
the KB size and divide it by 100000)
File Size (KB) W/Data
= The size of the rep file with test data in KB
File Size (MB) W/Data =
The size of the rep file with test data in MB (take the KB size and divide it by 100000)
Comments =
DP Name =
Name of Data Provider (if variable created from Data Provider, if not,
will be blank?)
Run
Time =
Time it took for the DP to complete for the time period that was tested
Number of Rows =
Number of Rows processed/retrieved
Tab Number =
Number assigned to the report tab
Tab Name =
Name of the report tab
Number
of Pages = The number
of pages the on the report tab.
| BOR # |
Periocity
| Time Code
| File Size
(KB) WO/Data | File Size (MB) WO/Data |
File Size (KB) W/Data
| File Size
(MB) W/Data | Comments |
| |
| | | | | | |
| DP Name | Run Time | Number of Rows |
| | |
|
| Tab
Number | Tab Name | Number of Pages |
|
| | |
4
Run Time Options
Documents can be run on the Web, on the Client, or by the
Scheduler. Ad-Hoc reports are executed on the Web using Web Intelligence (WEBI) or using Business Objects on the Client.
The following shall be the mode of running the reports:
- Daily Sales Report: Scheduled
- Sales Analysis Report(Sales Officer’s
view): scheduled
- Sales Analysis Report(Regional Manager’s view): executed on the
web using WEBI. Open for slicing and dicing.
With this we
have specified the run time environment for the reports.
4.1
Scheduler Options
| Report Name |
Periocity
| Mon
| Tue
| Wed
| Thr
| Fri
| Sat
| Sun
| Other Frequency
| Run Time
| Run Time Parameters | Intranet Navigation |
BO Category
| File
Type
|
| Sales Analysis Report | Monthly | Y | | | | |
| | N/A | Not yet decided
| Please see the list of prompts | Details not
available | N/A | Xls |
| Daily Sales Report | weekly | Y |
| | | | |
| N/A | Not yet decided | Please see the list of prompts | Details not available |
N/A | pdf |
5
Examples
Insert the object file containing the layout of the report.
This may be an Adobe .pdf, Excel worksheet .xls, Business Object Report .rep, or a text .txt file. Double clicking the
icon will open the report layout file.
(From the Toolbar: Insert / Object / Create from File / Browse for file / OK.)
6
Change log
Enter a summary of the changes that have taken place. If new document, enter “New” in Detail of Change.
| Date | Name
| Detail
of Change |
| | | |
8.
Adhoc Reporting Requirements
The
following ad hoc reporting requirements shall be fulfilled by the data mart.
In
this section of the document we shall be taking a closer look at the ad hoc requirements that were gathered form the
sales officers. Based on the model that has been prepared and the gathered report requirements we shall seek to document
whether the particular ad hoc reporting requirement shall be met or not. We shall not be looking into the exact nature of
the sql that shall be used to generate the reports. However in whatever case possible we shall elaborate on how the required
information shall be furnished.
| Report requirement
| Possible(P)/Not Possible(NP) with the data mart |
Measures |
Possible
dimensions | Any other description |
| Analysis on sales ranking | P | sum(Sales)
| Region, sales area | Ranking to be carried out in reports |
| Sales figures by product group | P | Sum(sales)
| Product group[Product_Line_Dim] | |
| Sales figures by product warehouse | P | Sum(sales) | | |
| Sales figures
by mover code | NP | Sum(sales) | We need to add
the mover code attribute in Item dimension |
|
| Customer history change-Sales by price group | NP
| | Price group is not captured in the data mart. However the list price is captured as a dimension attribute[ITEM_IMC_PRICE
in Item dimension] |
|
| Customer history change-Sales by class code | P
| Sum(sales) | Date, Item_Class_Desc(Item_class_Dim) |
|
| Customer history change-Sales by markup |
NP |
| | In the fact Item_sales_IMC_Price and Item_sales_Selling _Price are measure objects. We shall not be able to group
by them |
| Customer history change-Sales by sales price | NP
| | | In the fact Item_sales_IMC_Price
is a measure object. |
| Pricing analysis over period of time | P | Average(Sale_IMC_Price) | Date, product, product group |
|
| Product profile of a customer | P
| Sales, pricing factor | Product group, months |
|
| Sales analysis by customer category | P
| Sales | Customer category(TBL_Customer_type_dim) |
|
| Count of customers changed in 3, 6 months | P
| Count(Customer_ID) | Date, Month, Year |
|
| Frequency of buying parts | P
| Count(Item_ID) | date, month, year |
|
1
Reports
1.1
Layout and Titles
Sales Analysis Report: as of now we have assumed that this shall be a
single report with multiple tabs for weekly channel, weekly web, monthly channel and monthly web sales. In case the refresh
cycles for the weekly and the monthly sales are different then we may have to consider them as separate reports.
|
Report (Tab) # | Page Layout |
Report (Tab) Name | Description | Other Considerations |
| Landscape | Portrait
| Letter Size | Legal Size |
| | | x |
|
| Monthly customer sales analysis(sales officer view) |
This particular report
gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes
including the web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is
to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis.
In this report we shall not be reporting for the corporate entities. |
|
| | | x |
| | Monthly web sales analysis(sales officer view) | This particular report gives the profitability analysis for each customer
based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get
a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis.
In this report we shall not be reporting for the corporate entities. |
|
| | | x |
| | Monthly customer sales analysis(Regional Manager’s view) | This particular report gives the profitability
analysis for each customer based on channel sales. Channel sales shall include all the class codes excluding web sales.
The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for
sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall
not be reporting for the corporate entities. | |
|
| | x |
| | Monthly web sales analysis(Regional Manager’s View) | This particular report gives the profitability
analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose
of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly
and 6 monthly basis. In this report we shall not be reporting for the corporate entities. | |
Daily Sales Report
|
Report (Tab) # | Page Layout |
Report (Tab) Name | Description | Other Considerations |
| Landscape | Portrait
| Letter Size | Legal Size |
| 1 | | x |
|
| Regional | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of regions. Comparisons shall be made from the present year with the figures
of last year. |
|
| 2 | | x |
| | Shipping demand branch | This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit
and % of profitability in terms of shipping demand branch. Comparisons shall be made from the present year with the figures
of last year. | |
| 3
|
| x
|
|
| Original demand branch | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of original demand branch. Comparisons shall be made from the present year
with the figures of last year. | |
| 4
|
| x
|
|
| Customer type | This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and %
of profitability in terms of customer type. Comparisons shall be made from the present year with the figures of last year.
|
|
| 5 |
| x
|
|
| Sales Area | This particular tab gives the daily, monthly and the yearly analysis for sales,
cost, profit and % of profitability in terms of sales area. Comparisons shall be made from the present year with the figures
of last year. | |
| 6 |
| x | | | Order
method | This particular
tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of order method.
Comparisons shall be made from the present year with the figures of last year. |
|
1.2
Report Fields
Throughout this document we shall use Sales and cost under various denominations. For the purpose
of this report sales and cost have been defined in the following manner:
- Sales is defined as value of sales order+ value of returned merchandise +value of returned
defective orders+ value of goodwill orders.
- Reporting cost can be obtained from the line item entry.
- Actual Cost is the cost from the inventory
- Gross Profit(Actual)=sales-actual
cost
- Net profit(Actual)=Gross Profit(Actual)-freight cost
- Gross Profit(Reporting)=sales-reporting cost
- Net Profit(Reporting)=sales-reporting cost-freight cost
- Freight as % age of sales=Freight cost/Sales
- Return Rate= value of returned merchandise +value of returned defective orders+ value of
goodwill orders
- Return(%age sales)= Return Rate/Sales
- ‘Percent’ is Profit/Sales.
- Previous fiscal month is defined as current fiscal month-1
- Order : this refers to the order line entries.
- ‘Profit’[for the respective case scenario] is Sales-Cost
- ‘Percent’ is
Profit/Sales.
Report Name: Sales Analysis report
Report
#:
1
Field Name: Customer
number
Business Description: the number of the customer
Field Name:
Name
Business Description: Customer name
Field
Name: Sales (previous
Month)
Business Description: It is the total sales for the previous month.
Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last
fiscal day of the previous fiscal month.
Field
Name: Net Sales 12 month
average
Business Description: it gives the average of the total sales over
last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the current fiscal year during the calculation then we shall continue by doing a backward calculation from
the last fiscal month of the previous fiscal year.
Field Name:
Net Sales 3 month average
Business Description: it gives the average of the total sales over
last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal
day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal
month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal
month of the previous fiscal year
Field Name:
Gross Profit 3 month average[reporting]
Business Description: it gives the average of the total gross profit(reporting)
over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last
fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Gross Profit 12 month average (reporting)
Business Description: it gives the average of the total profits(reporting)
over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first
fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first
fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last
fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Gross Profit previous month (reporting)
Business Description: It gives the gross profit (reporting) for the
previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. Gross profit (reporting) is defined as sales-reporting cost.
Field Name:
Net Profit previous month (reporting)
Business Description: It gives the net profit (reporting) for the
previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. Net profit (reporting) is defined as sales-reporting cost-freight
cost.
Field Name:
%age freight sale previous month
Business Description: It gives the % ratio of the freight costs with
regards to the total sales. Previous month is defined as the number of fiscal days between the first fiscal day of the previous
fiscal month and the last fiscal day of the previous fiscal month.
Field Name:
Return Rate previous month
Business Description:
It gives the return rate for previous month. Return rate is defined as value of returned merchandise +value of returned defective
orders+ value of goodwill orders. Previous month is defined as the number of fiscal days between the first fiscal day of the
previous fiscal month and the last fiscal day of the previous fiscal month.
Field Name:
Customers Lost-previous month
Business Description: It gives the number of customers lost between
the first fiscal day and the last fiscal day of the previous month. Previous month is defined as the number of fiscal
days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A lost
customer shall have 0 sales for the previous fiscal month and non-zero and greater than 0 sales for the current fiscal month-2.
Field
Name: Customers gained-previous
month
Business Description: It gives the number of customers lost within
the first fiscal day and the last fiscal day of the previous month. Previous month is defined as the number of fiscal
days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A gained
customer shall have 0 sales for the current fiscal month-2 and greater than 0 sales for the previous fiscal month.
Field Name:
Growth Rate by customer segment
Business Description: This gives the sales(sales order+ value of returned merchandise
+value of returned defective orders+ value of goodwill orders)
per number of active customers for the previous month. Previous month is defined as the number of fiscal days between the
first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Active customers are defined
as those customers with sales greater than 0. This measure’s slice and dice parameters include month, customer segment
and sales area.
Field Name:
Average order size
Business Description: This gives the $ volume per order for
the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal
month and the last fiscal day of the previous fiscal month. The $ value of the order is given by the sale volume. The orders
selected should be such that the order line items should have 0 return rate (value
of returned merchandise +value of returned defective orders+ value of goodwill orders).
Break/Sort:
Salesman Number and Sales Man Name(SB)
Totaling
Options: Not specified in the word document as of now
Field
Name: Region
Business
Description: Region name
Field Name:
Sales-this date
Business Description: Sales as of a given date [sale as of date the
report has been refreshed]
Field Name:
Cost-this date
Business Description: Actual Cost as of a given date [Actual
cost as of date the report has been refreshed]
Field Name: Profit-this
date
Business Description: Actual Profit as of a given date[profit
as of date the report has been refreshed. Profit shall be defined as Sales-Actual Cost]
Field
Name: Percent-this date
Business Description:
Percentage Actual profit as of a given date
Field Name:
Sales-this date (last year)
Business Description: Sales as of same day last year [same day last
year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name: Cost-this
date (last year)
Business Description: Actual Cost as of same day last year
[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Profit-this date (last year)
Business Description: Actual Profit as of same day last
year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field
Name: Percent-this date
(last year)
Business Description: Percentage actual profit as same day
last year[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal
year]
Field Name:
Sales-cumulative this month
Business Description: Cumulative sales as of this month [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
month]
Field Name:
Sales (%age)-overall sales (this month)
Business Description: Percentage Cumulative sales as of this month
per region [cumulative sales for the number of business days between the date the report is refreshed and the first
day of the current fiscal month]
Field Name:
Cost-cumulative this month
Business Description: Cumulative actual cost as of this
month [cumulative cost for the number of business days between the date the report is refreshed
and the first day
of the current fiscal month]
Field Name:
Profit-cumulative this month
Business Description: Cumulative actual profit as of this
month [cumulative profit for the number of business days between the date the report is refreshed
and
the first day of the current fiscal month]
Field Name:
Percent-cumulative this month
Business Description: Percentage actual profit as of this
month [percentage profit for the number of business days between the date the report is refreshed
and
the first day of the current fiscal month]
Field Name:
Sales-cumulative this month (last year)
Business Description:
Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st
fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal
month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Sales (%age)-overall sales(this month last year)
Business Description:
Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this month last year [cumulative
sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous
fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in
the previous fiscal year]
Field Name:
Cost- cumulative this month (last year)
Business Description: Cumulative Actual cost as of this month last
year [cumulative cost for the number of business days between the 1st fiscal day of the same fiscal month
in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date
of refresh in the previous fiscal year]
Field Name:
Profit- cumulative this month (last year)
Business Description: Cumulative actual profit as of this month last
year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal
month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh
in the previous fiscal year]
Field Name:
Percent- cumulative this month (last year)
Business Description: Cumulative percent actual profit as of this
month last year [cumulative percent profit for the number of business days between the 1st fiscal day
of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as
the date of refresh in the previous fiscal year]
Field Name:
Sales-cumulative this year
Business Description: Cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
year]
Field Name:
Daily Average (this year)-Yearly
Business Description: Cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.
Field Name:
Sales (%age)-overall sales (this year)
Business Description: Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this year [cumulative percentage sales for the number of
business days between the date the report is refreshed and the first day if the current fiscal year]
Field Name:
Cost-cumulative this year
Business Description: Cumulative actual cost as of this
year [cumulative cost for the number of business days between the date the report is refreshed and the first day of the current
fiscal year]
Field Name:
Profit-cumulative this year
Business Description: Cumulative actual profit as of this
year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the
current fiscal year]
Field Name:
Percent-cumulative this year
Business Description: Percentage Actual profit as of this
year [percentage profit for the number of business days between the date the report is refreshed and the first day of the
current fiscal year]
Field Name:
Daily Average this year (monthly)
Business Description: Cumulative sales as of this month [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal
month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year
to the date of refresh.
Field Name:
Sales-cumulative last year
Business Description:
Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal
day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the
date of refresh in the previous fiscal year]
Field Name:
Sales (%age)-overall sales (last year)
Business Description:
Percentage (Region/customer
type/shipping demand/original demand) as of last year [cumulative sale
for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal
day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Cost- cumulative last year
Business Description: Cumulative Actual cost as of last
year [cumulative cost for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year]
Field Name:
Profit- cumulative last year
Business Description: Cumulative Actual profit as of last
year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year]
Field Name:
Percent- cumulative last year
Business Description: Cumulative percent Actual profit as
of last year [cumulative percent profit of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day (the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the
previous fiscal year]
Field Name:
Daily Average (last year)-Yearly
Business Description: Cumulative sales as of last year [cumulative
sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh
last year [the same business day for the same fiscal month and week for previous fiscal year].
Field Name:
Daily Average last year (monthly)
Business Description: Cumulative sales as of this month last year
[cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in
the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date
of refresh in the previous fiscal year] per
the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal
year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last
year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .
Field Name:
Days-month
Business Description: The number of business days beginning from
the first business day of the month to the date of report refresh in the current fiscal month of the current fiscal year.
Field Name:
Days-month (last year)
Business Description: The number of business days beginning
from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report
refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report
refresh in the same fiscal week and month of the previous fiscal year] .
Field Name:
Days-year
Business Description: The number of business days beginning from
the first business day of the current fiscal year to the date of report refresh.
Field Name:
Days-year (last)
Business Description: The number of business days beginning from
the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same
fiscal month and week for previous fiscal year].
Field Name:
Change Sales daily
Business Description: The difference between Sales as of a given
date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as
the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Change Sales daily-%age
Business Description: The %age change in daily sales(The difference
between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same
day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]) with respect
to Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month
for the previous fiscal year])
Field Name:
Change Profit Daily
Business Description: The difference between Actual Profit
as of a given date[given date is the date of report refresh] and Profit as of same day last year [same day last year
is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]
Field Name:
Change Profit Daily-%age
Business Description: The %age change in daily Actual profit
(The difference between Actual Profit as of a given date[given date is the date of report refresh] and Profit as of
same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous
fiscal year] with respect to Profit as of same day last year [same day last year is defined as the same fiscal day for the
same fiscal week and month for the previous fiscal year])
Field
Name: Change Sales monthly
Business
Description: The difference between cumulative sales as of this month [cumulative sales for the number of
business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales
as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal
month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as
the date of refresh in the previous fiscal year]
Field Name:
Change Sales monthly-%age
Business Description: The %age change in monthly sales(The difference
between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is
refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale
for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year])
with respect to cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the
previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of
refresh in the previous fiscal year
Field Name:
Change Profit Monthly
Business Description: The difference between Cumulative Actual profit as of
this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of
the current fiscal month] and cumulative actual profit as of this month last year [cumulative profit for the number
of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in
the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Profit Monthly-%age
Business Description: the % age change in monthly Actual Profit(The
difference between Cumulative profit as of this month [cumulative profit for the number of business days between the date
the report is refreshed and the first day of the current fiscal month] and cumulative profit as of this month last year [cumulative
profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and
the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect
to cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal
day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week)
as the date of refresh in the previous fiscal year]
Field Name:
Change Sales Yearly
Business Description: The difference between cumulative sales as of this year [cumulative
sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year]
and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous
fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in
the previous fiscal year]
Field Name:
Change Sales Yearly-%age
Business Description: The %age change in yearly sales(The difference
between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is
refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number
of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the
same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for
the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal
day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year
Field Name:
Change Profit Yearly
Business Description: The difference between Cumulative Actual Profit as of
this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of
the current fiscal year] and cumulative Actual Profit for last year [cumulative profit for the number of business
days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal
month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Profit Yearly-%age
Business Description: the % age change in yearly Actual
profits(The difference between Cumulative actual profit as of this year [cumulative actual profit for
the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative
actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal
day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the
date of refresh in the previous fiscal year]) with respect to cumulative actual profit for last year [cumulative
actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same
fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]
Field Name:
Change Daily Average (Monthly)
Business Description: Difference between cumulative sales as of this
month last year [cumulative sale for the number of business days between the 1st fiscal day of the same
fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week)
as the date of refresh in the previous fiscal year] per
the number of
business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same
fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same
fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] and cumulative sales
as of this month [cumulative sales for the number of business days between the date the report is refreshed and the
first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month
in the current fiscal year to the date of refresh.
Field Name:
Change Daily Average (Yearly)
Business Description: Difference between cumulative sales as of last
year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal
year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous
fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of
report refresh last year [the same business day for the same fiscal month and week for previous fiscal year] and cumulative
sales as of this year [cumulative sales for the number of business days between the date the report is refreshed
and the first day of the current fiscal year] per number of business days beginning from the first business day of the current
fiscal year to the date of report refresh.
Break/Sort:
Date of the report
Totaling Options:
sum sales, cost, Profit. We need to take an average of %.
In the other tabs of the report all the other fields besides the first
one shall remain the same. We shall be listing only the first field name for the description of the remaining tabs.
1.3 Prompts
Sales Analysis Report
Prompts
are the filter options. For each of the tabs 1 to 4 they need to be specified by the client.
Tab #1: Monthly customer sales
analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.
Tab
#2: Monthly web sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given
a static report.
Tab #3: Monthly customer sales analysis (Regional Manager’s view): The filter criteria are as
following:
Prompt #:
1
Tab #:
3
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
2
Tab #:
3
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Tab #4: Monthly web sales analysis (Regional Manager’s
View):
Prompt #:
3
Tab #:
4
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
4
Tab #:
4
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Daily Sales Report:
We
shall be selecting only the following as the filtering criteria.
Prompt #:
1
Tab #:
1
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
2
Tab #:
2
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
3
Tab #:
2
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt #:
4
Tab #:
3
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
5
Tab #:
3
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt #:
6
Tab #:
4
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
7
Tab #:
4
Prompt Message:
‘Please select the region’
Prompt Conditions: =
Prompt
#:
8
Tab #:
5
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
9
Tab #:
5
Prompt Message:
‘Please select the Region’
Prompt Conditions: =
Prompt #:
10
Tab #:
6
Prompt Message:
‘Please select the date’
Prompt Conditions: =
Prompt #:
11
Tab #:
6
Prompt Message:
‘Please select the Region’
Prompt Conditions: =
2 Technical Details
2.1 Data Provider
Details
2.1.1
Result Objects
Sales Analysis Report
The following are the result objects for the ‘Sales Analysis Report’.
Sales: measure
Cost: measure
Gross Profit: measure
Net profit: measure
Month: dimension
Customer: dimension
Sales area: dimension
Sales person
(shall be used as a segment): dimension
Sales channel: dimension
Item class: dimension
Product class: dimension
Product Line: dimension
Geographic code: dimension.
Sort
details: the sorting needs to take place by the value of a measure object. Hence we cannot specify the conditions for the
same in the query.
Daily Sales Report: The following are the objects as per our anticipation as of now.
Region: dimension
Shipping branch:
dimension
Original demand branch: dimension [both original
demand branch and shipping branch form part of the supply chain dimension. Should we add additional filtering criteria for
the same?]
Sales order method (web based, channel): dimension
Fiscal calendar: dimension
Sales Area: dimension
Customer: dimension
Days: Measure
Sum of Sales: Measure
Sum of Cost: Measure object
Sort details: N/A
Query Name: N/A.
The client has clarified that it does not require additional grouping options such as geographic and
product dimensions for this report.
2.1.2
Condition Objects
Method 4: use of correlated
sub queries
Method 5: use of aggregate awareness within the universe
The option chosen by the technical team shall depend
on the following factors:
- The data load for the queries
- The slice and dice capabilities expected for each view by the client
- Whether the reports shall run on an ad hoc basis or they
shall run on a pre scheduled basis.
SQL
Sales analysis report
The SQL will involve inner join between dimension and
Fact with the filter conditions for the dimensions.
We are presenting the approximate SQL for each of the tabs in consideration. However in case aggregates
are produced then the sql shall change.
Tab
#1:
Select
sum(Sales), sum(cost), sum(profit),
month, customer, sales
area, sales person, sales channel, product groups, geographic code
from [specify the join conditions between the dimensions and the fact. /*please
see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the (fiscal month-13) of
the date of refresh
and
sales channel= specify sales channel
group by month, customer, sales area, sales channel, geographic code, item class, product class, product
line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- The sorting criteria specified by the client involves
the sum of sales. This is a measure attribute. We shall have to specify the same in the report.
Tab #2:
Select
sum(Sales), sum(cost), sum(profit),
month, customer, sales area, sales person, sales channel, product groups, geographic code
from [specify the join conditions between
the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of
the (fiscal month-13) of the date of refresh
and sales channel= specify web channel
group by month, customer, sales area, sales channel, geographic code, item class,
product class, product line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- The sorting criteria specified by the client involves
the sum of sales. This is a measure attribute. We shall have to specify the same in the report.
Tab #3:
Select
sum(Sales), sum(cost), sum(profit), count(distinct(customers))
month, customer, sales area, sales
person, sales channel, product groups, geographic code
from [specify the join conditions between the dimensions and the fact. /*please see the note at
the bottom of the page*/]
where
specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh
and sales channel= specify
web channel
group by
month, customer, sales area, sales channel, product groups, geographic code, item class, product class, product line
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- The drill down/drill through
shall be as per the dimensions specified in the group by option namely: date, customer, sales area, geographic code,
sales channels, item class,
product class and product line.
- The client expects no additional ‘group by’ options with any of the
other dimensions.
- The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We
shall have to specify the same in the report.
Daily Sales Report
Tab #1:
Select region,
sum(Sales),
sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between
the prompt date and 1st day of the previous fiscal year
group by region, date, month, year
Additional details
about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill
through expected through any of the dimensions. This is a static view.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #2:
Select shipping demand branch, region
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and
the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the
previous fiscal year
and
prompt for region
group
by shipping demand branch, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to
add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client
does not expect to drill down by the region parameter.
- The client expects no additional ‘group by’ options with any
of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #3:
Select original demand branch, region,
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and
the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the
previous fiscal year
and
prompt for region
group
by original demand branch, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to add ‘region’ as a segment in the reports.
That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
- The client expects no
additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have
been given by the client.
Tab #4:
Select order method, region,
sum(Sales), sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please
see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st day of the previous fiscal year
and prompt for region
group by order method, date,
month, year, region
Additional details about this tab[all
the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through
any of the dimensions. This is a static view.
- The
client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region.
However the client does not expect to drill down by the region parameter.
- The client expects no additional ‘group by’
options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria have been given by the client.
Tab #5:
Select customer type, region,
sum(Sales), sum(cost), date, month,
year
from [specify the
join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between the prompt date and 1st
day of the previous fiscal year
and prompt for region
group by customer type, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static view.
- The client expects to add ‘region’ as a segment in the reports.
That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
- The client expects no
additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
- No sorting criteria
have been given by the client.
Tab
#6:
Select sales area,
region,
sum(Sales),
sum(cost), date, month, year
from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]
where specify the date range between
the prompt date and 1st day of the previous fiscal year
and prompt for region
group by sales area, date, month, year, region
Additional details about this tab[all the points have been finalized in discussion
with the client]:
- There is no drill down/drill through expected through any of the dimensions. This is a static
view.
- The client expects to add ‘region’ as a segment
in the reports. that is why we have had to group by region. However the client does not expect to drill down by the region
parameter.
- The client expects no additional ‘group by’ options with any of the other dimensions. No slice and
dice capabilities are expected.
- No sorting criteria have been given by the client.
/* details about the join conditions
between dimensions and facts*/
The way
in which shall design the universe shall entail an inner join between dimensions and fact. A sample of the same(from one of
the author’s previous projects) is attached below:
From (("ETMC"."dbo"."fact_ledger2" "fact_ledger2"
INNER JOIN "ETMC"."dbo"."dim_account"
"dim_account" ON
"fact_ledger2"."acct_dim_id"="dim_account"."acct_dim_id")
INNER JOIN "ETMC"."dbo"."dim_company"
"dim_company" ON
"fact_ledger2"."comp_dim_id"="dim_company"."comp_dim_id")
INNER JOIN "ETMC"."dbo"."fiscal_calender"
"fiscal_calender" ON
"fact_ledger2"."CAL_id"="fiscal_calender"."cal_id"
inner join "ETMC"."dbo"."dim_dept"
"dim_dept" on
"fact_ledger2"."dept_dim_id"="dim_dept"."dept_dim_id"
2.2
Document Variables
1. %age freight sale previous month= Return Rate previous month/sales(previous month)
2. Average order size=sum(sales)/count(clean order)
3. Profit-this date= (sales-this date)-(cost-this date)
4. Percent-this date=(profit-this
date)/(Sales-this date)
5. Profit-this date(last year)= [sales-this date(last year)]-[cost-this date(last year)]
6. Percent-this date(last year)= Profit-this date(last year)/ sales-this date(last year)
7. Percent-cumulative
this month=Profit-cumulative this month/Sales-cumulative this month
8. Percent- cumulative
this month (last year)=Profit- cumulative this month (last year)/Sales-cumulative this month (last year)
9. Daily
Average (this year)-Yearly=Sales-cumulative this year/Days-year
10. Daily Average this year (monthly)=Sales-cumulative
this month/Days-month
11. Percent- cumulative last year=(Profit- cumulative last year)/(Sales-cumulative last year)
12. Daily
Average (last year)-Yearly=Sales-cumulative last year/Days-year (last)
13. Daily Average last year (monthly)= Sales-cumulative
this month (last year)/Days-month (last year)
14. Change Sales daily= (Sales-this date) - (Sales-this date (last year))
15. Change Profit daily=
(Profit-this date)-(Profit-this date (last year))
16. Change Sales daily-%age= Change Sales daily/(Sales-this date (last year))
17. Change Profit daily-%age=
Change Profit daily/( Profit-this date (last year))
18. Change Sales Yearly= (Sales-cumulative this year)-(Sales-cumulative last year)
19. Change Profit Yearly=
(Profit-cumulative this year)-(Profit-cumulative last year)
20. Change Sales monthly= (Sales-cumulative this
month)-( Sales-cumulative this month(last year))
21. Change Profit monthly=( Profit-cumulative this month)-( Profit-cumulative this month(last year))
22. Change
Profit Monthly-%age= Change Profit monthly/ Profit-cumulative this month (last year)
23. Change Sales Monthly-%age=
Change Sales monthly/( Sales-cumulative this month(last year))
24. Change Profit Yearly-%age= Change Profit yearly/
(Profit-cumulative last year)
25. Change Sales Yearly-%age=Change Sales Yearly/(Sales-cumulative last year)
26. Percent- cumulative
this year= (Profit- cumulative this year)/(Sales-cumulative this year)
27. Change Daily Average (Yearly) =
(Daily Average (last year)-Yearly)-( Daily Average (this year)-Yearly)
28. Change Daily Average
(Monthly) = (Daily Average (last year)-Monthly) - (Daily Average (this year)-Monthly)
2.3
Report Layout
Specifications
need to be given by the client.
3
Efficiency Analysis
/* this will have to be worked out with the client*/
The efficiency analysis objects below are pulled from different areas
that will help determine how efficient the report is in processing and in presentation. (Example
- Will the report complete if its YTD and will it file out to Excel successfully)
BOR # =
The BusObj Report Number issued by IT when the report has been Implemented. If the report is new then type “NEW”.
Periocity =
How often the report is scheduled to run. (Monday, First of Month, etc,.)
Time Code =
The time period code that indicates the time period that was use while testing the report. (Daily, WK, MTD, QTR, YTD, etc,.)
File Size (KB) WO/Data =
The size of the rep file without any data in KB
File Size
(MB) WO/Data = The size of the rep file without any data in MB (take
the KB size and divide it by 100000)
File Size (KB) W/Data
= The size of the rep file with test data in KB
File Size (MB) W/Data =
The size of the rep file with test data in MB (take the KB size and divide it by 100000)
Comments =
DP Name =
Name of Data Provider (if variable created from Data Provider, if not,
will be blank?)
Run
Time =
Time it took for the DP to complete for the time period that was tested
Number of Rows =
Number of Rows processed/retrieved
Tab Number =
Number assigned to the report tab
Tab Name =
Name of the report tab
Number
of Pages = The number
of pages the on the report tab.
| BOR # |
Periocity
| Time Code
| File Size
(KB) WO/Data | File Size (MB) WO/Data |
File Size (KB) W/Data
| File Size
(MB) W/Data | Comments |
| |
| | | | | | |
| DP Name | Run Time | Number of Rows |
| | |
|
| Tab
Number | Tab Name | Number of Pages |
|
| | |
4
Run Time Options
Documents can be run on the Web, on the Client, or by the
Scheduler. Ad-Hoc reports are executed on the Web using Web Intelligence (WEBI) or using Business Objects on the Client.
The following shall be the mode of running the reports:
- Daily Sales Report: Scheduled
- Sales Analysis Report(Sales Officer’s
view): scheduled
- Sales Analysis Report(Regional Manager’s view): executed on the
web using WEBI. Open for slicing and dicing.
With this we
have specified the run time environment for the reports.
4.1
Scheduler Options
| Report Name |
Periocity
| Mon
| Tue
| Wed
| Thr
| Fri
| Sat
| Sun
| Other Frequency
| Run Time
| Run Time Parameters | Intranet Navigation |
BO Category
| File
Type
|
| Sales Analysis Report | Monthly | Y | | | | |
| | N/A | Not yet decided
| Please see the list of prompts | Details not
available | N/A | Xls |
| Daily Sales Report | weekly | Y |
| | | | |
| N/A | Not yet decided | Please see the list of prompts | Details not available |
N/A | pdf |
5
Examples
Insert the object file containing the layout of the report.
This may be an Adobe .pdf, Excel worksheet .xls, Business Object Report .rep, or a text .txt file. Double clicking the
icon will open the report layout file.
(From the Toolbar: Insert / Object / Create from File / Browse for file / OK.)
6
Change log
Enter a summary of the changes that have taken place. If new document, enter “New” in Detail of Change.
| Date | Name
| Detail
of Change |
| | | |
8.
Adhoc Reporting Requirements
The
following ad hoc reporting requirements shall be fulfilled by the data mart.
In
this section of the document we shall be taking a closer look at the ad hoc requirements that were gathered form the
sales officers. Based on the model that has been prepared and the gathered report requirements we shall seek to document
whether the particular ad hoc reporting requirement shall be met or not. We shall not be looking into the exact nature of
the sql that shall be used to generate the reports. However in whatever case possible we shall elaborate on how the required
information shall be furnished.
| Report requirement
| Possible(P)/Not Possible(NP) with the data mart |
Measures |
Possible
dimensions | Any other description |
| Analysis on sales ranking | P | sum(Sales)
| Region, sales area | Ranking to be carried out in reports |
| Sales figures by product group | P | Sum(sales)
| Product group[Product_Line_Dim] | |
| Sales figures by product warehouse | P | Sum(sales) | | |
| Sales figures
by mover code | NP | Sum(sales) | We need to add
the mover code attribute in Item dimension |
|
| Customer history change-Sales by price group | NP
| | Price group is not captured in the data mart. However the list price is captured as a dimension attribute[ITEM_IMC_PRICE
in Item dimension] |
|
| Customer history change-Sales by class code | P
| Sum(sales) | Date, Item_Class_Desc(Item_class_Dim) |
|
| Customer history change-Sales by markup |
NP |
| | In the fact Item_sales_IMC_Price and Item_sales_Selling _Price are measure objects. We shall not be able to group
by them |
| Customer history change-Sales by sales price | NP
| | | In the fact Item_sales_IMC_Price
is a measure object. |
| Pricing analysis over period of time | P | Average(Sale_IMC_Price) | Date, product, product group |
|
| Product profile of a customer | P
| Sales, pricing factor | Product group, months |
|
| Sales analysis by customer category | P
| Sales | Customer category(TBL_Customer_type_dim) |
|
| Count of customers changed in 3, 6 months | P
| Count(Customer_ID) | Date, Month, Year |
|
| Frequency of buying parts | P
| Count(Item_ID) | date, month, year |
|
