-
8/6/2019 Sales Report Design Specifications Final v7.1
1/33
-
8/6/2019 Sales Report Design Specifications Final v7.1
2/33
Sales Report Design Specifications
Page 2
Table of Contents
FUNCTIONAL OVERVIEW................................................................................................................. 4
REPORTING STANDARDS ................................................................................................................. 5
HIERARCHY .......................................................................................................................................... 5DATE RANGE ........................................................................................................................................ 5STANDARD REPORT FORMATTING REQUIREMENTS ................................................................................. 6
SECURITY ............................................................................................................................................. 7
WORLDWIDE CASE CREDIT REPORTS ............ ............. ............. .............. ............ ............. ............. 9
OVERVIEW ............................................................................................................................................ 9REPORT LAYOUT ................................................................................................................................... 9PROMPTS .............................................................................................................................................. 9
World Wide Case Credit World Report .............................................................................................. 9World Wide Case Credit Region Report ............. ............. ............. ............. ............. ............. ............. 12World Wide Case Credit Region Operating Company Report ............ ............. ............ .............. ........ 13World Wide Case Credit Operating Company Home Company Report ............ .............. ............ ....... 14
World Wide Case Credit Home Company Division Report ............. .............. ............ ............. ........... 15World Wide Case Credit Division Sales Area Report ....................................................................... 16World Wide Case Credit By Distributor ID Report .......................................................................... 17
REPORT TYPE AND SOURCES................................................................................................................ 17REPORT DATA..................................................................................................................................... 17
Source Data Providers: ................................................................................................................... 17Data Elements Prompts For World Wide Case Credits World Report .............. ............. ............. .... 18Data Elements World Wide Case Credits World Report ................................................................ 19Data Elements World Wide Case Credits Region Report ............. .............. ............ ............. ........... 19Data Elements World Wide Case Credits Region Operating Company Report ............ ............. ...... 20Data Elements World Wide Case Credits Operating Company Home Company Report ................. 20Data Elements World Wide Case Credits Home Company Division Report .................. ............. .... 21Data Elements World Wide Case Credits Division Sales Area Report .............. ............. ............. .... 21Data Elements Drill to Distributor information ............ ............. ............. ............. ............. ............. 22
OTHER CONSIDERATIONS .................................................................................................................... 22
GROWTH PERCENT REPORTS ............ ............. ............. ............. ............. ............. ............. ............. 23
OVERVIEW .......................................................................................................................................... 23NAVIGATION ....................................................................................................................................... 23REPORT LAYOUT ................................................................................................................................. 23
Growth % Region............................................................................................................................ 24Growth % Operating Company ....................................................................................................... 25Growth % Home Company .............................................................................................................. 26Growth % Report Division .............................................................................................................. 27Growth % Sales Area ...................................................................................................................... 28
REPORT TYPE AND SOURCES................................................................................................................ 29REPORT DATA..................................................................................................................................... 29Source Data Providers .................................................................................................................... 29
Data Elements Prompts: ............................................................................................................... 29Data Elements Growth % Region ................................................................................................. 29Data Elements Growth % Operating Company .............................................................................. 30Data Elements Growth % Home Company.................................................................................... 31Data Elements Growth % Division ............................................................................................... 32Data Elements Growth % Sales Area ............................................................................................ 33
OTHER CONSIDERATIONS .................................................................................................................... 33
-
8/6/2019 Sales Report Design Specifications Final v7.1
3/33
-
8/6/2019 Sales Report Design Specifications Final v7.1
4/33
Sales Report Design Specifications
Page 4
FUNCTIONAL OVERVIEW
xxxx acquired Cognos in early 2008. At that time, a pilot project was conducted to address thefollowing initial reporting needs:
Establish Cognos Environment
Build Framework Manager Models to support Distributor Area and Purchase AreaReporting. Build custom views to support Regional and Operating company security. Create Case Credit reporting by month with comparisons to the previous year. The
following levels of reports were built:- Level One Reporting (Region)- Level Two Reporting (Operating Company)- Level Three Reporting (Case Credits by Home Company)- Level Four Reporting (Case Credits by Division)- Level Five Reporting (Case Credits by Distributor Area)
As use of these initial reports has taken hold, additional reporting requests were identified,including:
1. Worldwide Case Credit Report2. Distributor Ratio Report3. Growth % Report4. Detail Level Distributor Report5. Case Credit by Product report6. Product Type Reports7. Purchase Area Report
In December 2008 the following reports went into production1. Worldwide Case Credits Reports2. Growth % Reports3. Top Distributors Reports
The purpose of this document is to outline the requirements for additional sales reports to bedeveloped against the new SQL database, existing cubes and corresponding FrameworkManager Models and Packages.
The new reports will be developed for the current management user base. It is anticipated thatthese reports will gradually be rolled out to additional users.
-
8/6/2019 Sales Report Design Specifications Final v7.1
5/33
Sales Report Design Specifications
Page 5
REPORTING STANDARDS
As part of the current initiative, XXXX has decided to standardize all future reports with thefollowing features. During the development of the Phase Two reports a new level, World, wasadded to the report hierarchy. To ensure a consistent flow of the Phase Two reports all reports
start at the World hierarchy which serves as the entry point for all levels of report users.Hierarchy
Future reports will allow selection based on the organizational hierarchy of:
World+ Region
- Operating Company Home Company
Division
Sales Area
An example of this hierarchy is depicted in the table below.
World Africa
Asia
Europe
Latin America
North America
Region + NA
+ Asia
- Europe
OperatingCompany
+ Germany
- FRA
Home Company - FRADivision + Div 1
- Div 2
Sales Area Area 1
Area 2
The prompting of hierarchy will work in conjunction with security to ensure that users only see drilldowns based on what they are authorized to see.
The selected prompt values will be displayed in the header of each report. Subsequent drill downreports will carry forward the selected values in a cascading fashion.
Date Range
Reports will allow selection based on a year and a month. Year and month prompt formats will bestandardized on the reports and display as follows:
Starting Year/Mo: 2008 Jun
Ending Year/Mo: 2008 Jul
The prompts will allow a drop down showing the year and month, allowing the user to select thestart and ending period of the report. Validation will be included in the date prompts to verify thatthe start date precedes the end date.
-
8/6/2019 Sales Report Design Specifications Final v7.1
6/33
Sales Report Design Specifications
Page 6
Standard Report Formatting Requirements
All reports will include the XXXX logo in the upper left corner. In addition, the reports will beformatted with the following font sizes.
Description Font Size Alignment / Other Specifications
Report Titles Tahoma 14 Bold, Underlined
Report Sub Titles Tahoma 12 Bold
Page Headers Tahoma 10 Bold
Page Footers Tahoma 10
Notes Tahoma 8
Report Cell /Columns Tahoma 8
Sub Total Tahoma 8 Decimals(3), Thousands Separator
Grand Total Tahoma 8 Decimals(3), Thousands Separator
Numeric Values Tahoma 8 Right
CurrencyTahoma
8Right ( Prefix $) Decimals(2),
Thousands Separator
Percentage Tahoma 8 Right (Suffix %, Decimals (2)
Characters Tahoma 8 Left
In addition, the default printer settings will be:
Page Orientation Landscape
Paper Letter
-
8/6/2019 Sales Report Design Specifications Final v7.1
7/33
-
8/6/2019 Sales Report Design Specifications Final v7.1
8/33
Sales Report Design Specifications
Page 8
A custom view needs to be created for each group in order to secure the data in a cube.Currently, XXXX has the following combinations of groups:
Option 3: Framework Manager Model Security
An alternative to these approaches involves establishing security within Framework Manager.This approach could eliminate the need to maintain and build SQL tables and Cognos views.
This approach is currently under investigation.
-
8/6/2019 Sales Report Design Specifications Final v7.1
9/33
Sales Report Design Specifications
Page 9
WORLDWIDE CASE CREDIT REPORTS
Overview
These new reports display worldwide case credit information and personal and non-managercase credit totals starting at the World Level. Data in all of the reports is secured to limit data to
the location(s) that are appropriate to the user sign-on. This report also allows the ability to drilldown into progressively more granular data by location. All levels of the World Wide Case Creditsreports allow a drill through to a separate report, World Wide Case Credits Drill Down byDistributor Id Report.
Report Layout
Prompts
The report will allow the user to:1. Place a check mark in the box next to the desired region or select all regions. Ability to
select Regions will be limited by security.2. Specify time frame consisting of a Starting Year and Month and an Ending Year and
Month.3. Specify minimum number of case credits by selecting the radio button next to the
predefined options for number of case credits.4. Prompts will be replicated at the top of the report page in the World and Region Level
Reports thus allowing the report to be re-run with new parameters.
World Wide Case Credit World Report
Overview
The World Wide Case Credits World Report is at the top of the World Wide Case Credits Reportshierarchy and serves as the entry point to all of the sub-level reports. The World Wide CaseCredits World Report is structurally different than the reports below it in the hierarchy in that theCase Credits are not grouped by a location but grouped by Distributor ID. This report will be used
-
8/6/2019 Sales Report Design Specifications Final v7.1
10/33
Sales Report Design Specifications
Page 10
primarily only by top executives that have the ability to view case credits in the context of theworld. The report can however be filtered by region if the select all option is not chosen.
Prompt Page
Navigation
The prompts on this report collect the parameters that will be passed through to the drill downreports. To launch the World Wide Case Credits Region Report click on the blue link in thefollowing the text on the header of the report page. Click to launch the World Wide Case
Credits Region Report for the following Region(s) ->ALL.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Values selected in the prompts and in the report drill downs will be passed to the report header ofthe next report in the hierarchy.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
11/33
Sales Report Design Specifications
Page 11
-
8/6/2019 Sales Report Design Specifications Final v7.1
12/33
Sales Report Design Specifications
Page 12
World Wide Case Credit Region Report
Overview
The World Wide Case Credits Region Report is at the second level of the hierarchy and reflectsthe Case Credit data in descending order grouped by Region
Navigation
To navigate to the next level down in the hierarchy the, World Wide Case Credits RegionOperating Company Level report, the user will click the blue text of the desired Region in theRegion Column of the report.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
13/33
Sales Report Design Specifications
Page 13
World Wide Case Credit Region Operating Company Report
Overview
The World Wide Case Credits Region Operating Company Report is at the third level of thehierarchy and reflects the Case Credit data in descending order grouped by Region and
Operating Company
Navigation
To navigate to the next level down in the hierarchy the, World Wide Case Credits OperatingCompany Home Company Report, the user will click the blue text of the desired OperatingCompany in the Operating Company Column of the report.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
14/33
Sales Report Design Specifications
Page 14
World Wide Case Credit Operating Company Home Company Report
Overview
The World Wide Case Credits Operating Company Home Company Report is at the fourth levelof the hierarchy and reflects the Case Credit data in descending order grouped by Operating
Company and Home Company
Navigation
To navigate to the next level down in the hierarchy the, World Wide Case Credits HomeCompany Division Report, the user will click the blue text of the desired Home Company in theHome Company Column of the report.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
15/33
Sales Report Design Specifications
Page 15
World Wide Case Credit Home Company Division Report
Overview
The World Wide Case Credits Home Company Division Report is at the fifth level of the hierarchyand reflects the Case Credit data in descending order grouped by Home Company and Division
Navigation
To navigate to the next level down in the hierarchy the, World Wide Case Credits Division SalesArea Report, the user will click the blue text of the desired Division in the Division Column of thereport.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
16/33
Sales Report Design Specifications
Page 16
World Wide Case Credit Division Sales Area Report
Overview
The World Wide Case Credits Home Division Sales Area Report is at the sixth and lowest level ofthe hierarchy and reflects the Case Credit data in descending order grouped by Division and
Sales Area
Navigation
Since this is the lowest level of the report the only navigation available to the user is to view theWorld Wide Case Credits by Distributor Id Report.
To launch the World Wide Case Credits by Distributor Id Report Click on the Distributor Id (bluetext in the ID column) and the report for that distributor will launch in a separate window.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
17/33
Sales Report Design Specifications
Page 17
World Wide Case Credit By Distributor ID Report
Overview
This report displays worldwide case credits by Distributor detail. Each Distributor is owned byone Region and Company (Referred to as a Domestic Company). Besides the domestic company
relationship, it is possible for a Distributor to be sponsored in another company. This report isdesigned to show case credit details for a Distributor including all companies in which they havehad activity. The diagram below depicts this scenario. The Domestic Company for Bob is USA.However, it is possible that Bob is sponsored and has case credits in other companies. In theexample below, Bob has case credits in USA, WAL, and KOR.
OWNING HOME COMPANY DISTRIBUTOR
REGION OPERATING COMPANY HOME COMPANY BOB
NA USA USA AUG SALES
NA CAR CAR
EU GB WAL JUN SALES
EU FRA FRA
DEC SALES
AS JAP JAP
AS KOR KOR
Navigation
There is no navigation associated with this report
Report Page
Report Type and Sources
Detail report using SQL server data through a framework model package.
Report Data
Source Data Providers:
Data Provider Name Data Provider Description
SERVICE.COGNOS Incrementally pulled via SQL server from AS/400 I-series system
COGNOSDB.SERVICE Service DB is create via extracts from AS/400
-
8/6/2019 Sales Report Design Specifications Final v7.1
18/33
Sales Report Design Specifications
Page 18
Report Data comes from the following files:
File Name Description
BOMP Case Credit InformationDSMP Distributor Master
Data Elements Prompts For World Wide Case Credits World Report
Name Type Formula Description
Location Select Check Box Prompt Select the Region(s)
Psdate Year and Month Prompt Starting prompt date
Penddate Year and Month Prompt Ending prompt date
Case Credits Radio Button
Selection
Select a minimum value
from listed options:
>250
>500
>750
>1,000
>1,250
>1,500
>2,500
>5,000
>7,500
>10,000
Select the number and the
report will pull amounts
equal to or greater than the
selected number of case
credits.
-
8/6/2019 Sales Report Design Specifications Final v7.1
19/33
Sales Report Design Specifications
Page 19
Data Elements World Wide Case Credits World Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Selected Region from prompt
Starting Yr and Month Dim.Date Selected starting Year andMonth
Ending Yr and Month Dim.Date Selected ending Year andMonth
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in400.
Data Elements World Wide Case Credits Region Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Region parameter from World
level report prompt
Starting Yr and Month Dim.Date Selected starting Year and
MonthEnding Yr and Month Dim.Date Selected ending Year and
Month
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Region CGRGNP. CGRGNN Dimlocation Region
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in
400.
-
8/6/2019 Sales Report Design Specifications Final v7.1
20/33
Sales Report Design Specifications
Page 20
Data Elements World Wide Case Credits Region Operating Company Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Region parameter from World
level report promptStarting Yr and Month Dim.Date Selected starting Year and
Month
Ending Yr and Month Dim.Date Selected ending Year andMonth
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Region CGRGNP. CGRGNN Dimlocation Region
Operating Company CGDSMP. OPCP Dimlocation Operating Company
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in
400.
Data Elements World Wide Case Credits Operating Company Home Company Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Region parameter from World
level report promptOperating Company Selected Operating Company
Starting Yr and Month Dim.Date Selected starting Year and
Month
Ending Yr and Month Dim.Date Selected ending Year andMonth
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Operating Company CGDSMP. OPCP Dimlocation Operating CompanyHome Company Dimloaction Home Company
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in
400.
-
8/6/2019 Sales Report Design Specifications Final v7.1
21/33
Sales Report Design Specifications
Page 21
Data Elements World Wide Case Credits Home Company Division Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Selected Region from prompt
Operating Company Selected Operating CompanyHome Company Selected Home Company
Starting Yr and Month Dim.Date Selected starting Year and
Month
Ending Yr and Month Dim.Date Selected ending Year andMonth
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Home Company Dimloaction Home Company
Division Dimlocation Division
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in
400.
Data Elements World Wide Case Credits Division Sales Area Report
Data Element Name Table FieldTable orView
Formula/Description
Region Hierarchy Dim Selected Region from promptOperating Company Selected Operating Company
Home Company Selected Home Company
Division Selected Division
Starting Yr and Month Dim.Date Selected starting Year and
Month
Ending Yr and Month Dim.Date Selected ending Year andMonth
Case Credits Static Values Selected minimum case credits
from prompt
Distributor Name CGDSMP. CGFNAM
CGDSMP. CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP. CGDID# vw_Distributor Distributor ID
Division Dimlocation Division
Sales Area Dimlocation Sales Area
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP and
BMTCCR
Personal & Non Mgr CGBOMP.PCAS
CGBOMP.NCAS
vw_Activity Add PCAS and NCAS.
Originally, from MSTP table in
400.
-
8/6/2019 Sales Report Design Specifications Final v7.1
22/33
Sales Report Design Specifications
Page 22
Data Elements Drill to Distributor information
Data Element Name Table Field View Formula/DescriptionRegion Selected Region from prompt
Starting Yr and Month Selected starting Year and Month
Ending Yr and Month Selected ending Year and Monthfrom Prompt
Case Credits Selected minimum case creditsfrom prompt
Region CGRGNP.CGRGNN
Dimlocation Region
Operating Company CGDSMP.OPCP,
Dimlocation Operating Company
Distributor Name CGDSMP.CGFNAM
CGDSMP.CGLNAM
vw_Distributor Distributor First and Last Name
Distributor ID CGDSMP.CGDID#
vw_Distributor Distributor ID
Home Company CGBOMP. CISO vw_Distributor Home Company
Case Credits CGBOMP. RTCC vw_Activity Derived from BOMSTP andBMTCCR
Personal & Non Mgr CGBOMP.PCASCGBOMP.NCAS
vw_Activity Add PCAS and NCAS.Originally from MSTP table inAS400.
Other Considerations
Scheduling Requirements
Run on demand.Sorting Requirements
For the main report, the default sort will be by:
Total Case Credits descending, Distributor Name in alphabetical order
For the Distributor drill down, the default sort will be by:
Home Company in alphabetical order
-
8/6/2019 Sales Report Design Specifications Final v7.1
23/33
Sales Report Design Specifications
Page 23
GROWTH PERCENT REPORTS
Overview
This new report will display the percentage increase in the number of case credits from thepervious year to the current year by region for the selected date range. The report will also allow
a user to drill down to any level in the Location Hierarchy (Region, Operating Company, HomeCompany, Division, and Sales Area). The initial audience will be the existing executivemanagement team utilizing the phase one reports. Future plans are to roll out this report down toall areas in the Location Hierarchy.
Navigation
To navigate to the next level down in the hierarchy the, World Wide Case Credits Division SalesArea Report, the user will click the blue text of the desired Region in the Region Column of thereport.
Report Layout
Report Prompts
This report follows the prompt standard described previously.
The report will allow the user to:
1. Place a check mark in the box next to the desired location or select all.2. Specify an optional time frame consisting of start Year and Month and an end Year and
Month.
-
8/6/2019 Sales Report Design Specifications Final v7.1
24/33
Sales Report Design Specifications
Page 24
Growth % Region
Overview
This new report will display the percentage increase in the number of case credits from thepervious year to the current year by region for the selected date range. The report will also allowa user to drill down to any level in the Location Hierarchy (Region, Operating Company, Home
Company, Division, and Sales Area).
Navigation
To navigate to the next level down in the Growth % hierarchy, the user will click the blue text ofthe desired Region in the Region Column of the report.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
25/33
Sales Report Design Specifications
Page 25
Growth % Operating Company
Overview
This new report will display the percentage increase in the number of case credits from thepervious year to the current year by Region and Operating Company for the selected date range.
Navigation
To navigate to the next level down in the Growth % hierarchy, the user will click the blue text ofthe desired Operating Company in the Operating Company Column of the report.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
26/33
Sales Report Design Specifications
Page 26
Growth % Home Company
Overview
This new report will display the percentage increase in the number of case credits from thepervious year to the current year by Region, Operating Company and Home Company for the
selected date range.
Navigation
To navigate to the next level down in the Growth % hierarchy, the user will click the blue text ofthe desired Home Company in the Home Company Column of the report.
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
27/33
-
8/6/2019 Sales Report Design Specifications Final v7.1
28/33
Sales Report Design Specifications
Page 28
Growth % Sales Area
Overview
This new report will display the percentage increase in the number of case credits from thepervious year to the current year by Region, Operating Company, Home Company, Division
Sales Area for the selected date range.
Navigation
There is no navigation associated with this report
Report Page
-
8/6/2019 Sales Report Design Specifications Final v7.1
29/33
Sales Report Design Specifications
Page 29
Report Type and Sources
List report using data from the framework model package DistributorAreaCube created from theDistributorAreaCube.
Report Data
Source Data Providers
Data Provider Name Data Provider Description
SERVICE.COGNOS Incrementally pulled via AS/400 I-series system
Report Data comes from the following file:
File Name Description
BOMP Case Credit Information
Data Elements Prompts:
Name Type Formula Description
Location Select Check Box Prompt Select the Region(s)
Psdate Year and Month Prompt Starting prompt date
Penddate Year and Month Prompt Ending prompt date
Data Elements Growth % Region
Data Element
NameTable. Field Cube Data Format Formula/Description
Region Selected Region(s) from
prompt
Starting Year and
Month
Selected starting Year
and Month from prompt
Ending Year and
Month
Selected ending Year
and Month from prompt
Region CGRGNP.
CGRGNN
Text Region
Current Year
Month
BOMP.CGPYR From Prompt YYYY - Mmm Year and Month
Prior Year Month BOMP.CGPML From Prompt YYYY - Mmm Year and Month
Case Credits CGBOMP.
CGRTCC
###.### Derived from
ORMSTP,OMCCRD
% Difference % Growth
Function
Cognos
% GrowthFunction
##.##% Calculated Field
Rank Ranking Function Cognos
Ranking
Function
Numeric Highest % Difference will
be ranked first to show
the largest increase over
prior year time frame
-
8/6/2019 Sales Report Design Specifications Final v7.1
30/33
Sales Report Design Specifications
Page 30
Data Elements Growth % Operating Company
Data ElementName
Table. Field Cube Data Format Formula/Description
Region Selected Region from
prompt
Starting Year andMonth
Selected starting Yearand Month from prompt
Ending Year and
Month
Selected ending Year
and Month from prompt
Region CGRGNP.
CGRGNN
Text Region
Operating
Company
CGOPCP.
CGOPCY
Text Operating Company
Current Year
Month
YYYY - Mmm Selected Year and
Month from prompt
Prior Year Month YYYY - Mmm Selected Year and
Month from promptCase Credits CGBOMP.
CGRTCC
###.### Derived from
ORMSTP,OMCCRD
% Difference % Growth
Function
Cognos
% Growth
Function
##.##% Calculated Field
Rank Ranking Function Cognos
Ranking
Function
Numeric Highest % Difference will
be ranked first to show
the largest increase over
prior year time frame
-
8/6/2019 Sales Report Design Specifications Final v7.1
31/33
Sales Report Design Specifications
Page 31
Data Elements Growth % Home Company
Data ElementName
Table. Field Table or ViewData
FormatFormula/Description
Region Selected Region from
prompt
Starting Year and
Month
Selected starting Year
and Month from prompt
Ending Year and
Month
Selected ending Year and
Month from prompt
Region Selected Region from
prompt
Operating
Company
CGOPCP. CGOPCY Text Operating Company
Home Company CGCMPP. CGCNTY Text Home Company
Current Year
Month
BOMP.CGPYR From
Prompt
YYYY -
Mmm
Year and Month
Prior Year Month BOMP.CGPML From
Prompt
YYYY -
Mmm
Year and Month
Case Credits CGBOMP. CGRTCC ###.### Derived from
ORMSTP,OMCCRD
% Difference % Growth Function Cognos %
Growth Function
##.##% Calculated Field
Rank Ranking Function CognosRankingFunction
Numeric Highest % Difference willbe ranked first to showthe largest increase overprior year time frame
-
8/6/2019 Sales Report Design Specifications Final v7.1
32/33
Sales Report Design Specifications
Page 32
Data Elements Growth % Division
Data ElementName
Table. Field Cube Data Format Formula/Description
Region Selected Region from
prompt
Starting Year and
Month
Selected starting Year
and Month from prompt
Ending Year and
Month
Selected ending Year
and Month from prompt
Operating
Company
Selected Operating
Company from prompt
Home Company Selected Home
Company from prompt
Region Region
Operating
Company
Operating Company
Home Company CGCMPP.CGCNTY
Text Home Company
Division CGAIAP.
CGASEX
Text Division
Current Year
Month
BOMP.CGPYR From Prompt YYYY - Mmm Year and Month
Prior Year Month BOMP.CGPML From Prompt YYYY - Mmm Year and Month
Case Credits CGBOMP.
CGRTCC
###.### Derived from
ORMSTP,OMCCRD
% Difference % Growth
Function
Cognos
% Growth
Function
##.##% Calculated Field
Rank Ranking Function CognosRanking
Function
Numeric Highest % Difference willbe ranked first to show
the largest increase over
prior year time frame
-
8/6/2019 Sales Report Design Specifications Final v7.1
33/33
Sales Report Design Specifications
Data Elements Growth % Sales Area
Data ElementName
Table. Field Cube Data Format Formula/Description
Region Selected Region from
prompt
Starting Year and
Month
Selected starting Year
and Month from prompt
Ending Year and
Month
Selected ending Year
and Month from prompt
Operating
Company
Selected Operating
Company from prompt
Home Company Selected Home
Company from prompt
Division Selected Division from
Prompt
Region Region
OperatingCompany
Operating Company
Home Company Home Company
Division CGAIAP.
CGASEX
Text Division
Sales Area CGMGRP.
CGAREA
Text Sales Area
Current Year
Month
BOMP.CGPYR From Prompt YYYY - Mmm Year and Month
Prior Year Month BOMP.CGPML From Prompt YYYY - Mmm Year and Month
Case Credits CGBOMP.
CGRTCC
###.### Derived from
ORMSTP,OMCCRD
% Difference % GrowthFunction Cognos% Growth
Function
##.##% Calculated Field
Rank Ranking Function Cognos
Ranking
Function
Numeric Highest % Difference will
be ranked first to show
the largest increase over
prior year time frame
Other Considerations
Scheduling Requirements
Run on Demand.
Sorting Requirements
For the main report and all drill down reports, the sort will be in ascending order by the rank fromhighest percentage growth to lowest percentage growth.