CT Records Report

From UG

(Difference between revisions)
Jump to: navigation, search
(→User Defined Conditions)
(→SOW 2 Option to report per ASN portal)
 
(34 intermediate revisions not shown)
Line 1: Line 1:
[[Category: CT Records Per Office]]
[[Category: CT Records Per Office]]
 +
 +
 +
== Info ==
 +
* parent mantis - http://ct.jaguarfreight.com/mantis/view.php?id=3126
 +
 +
'''Scope:''' 
 +
 +
This article covers "CT Records" report.
 +
 +
== Business Needs and Requirements ==
 +
 +
See [[#Business Needs]] and next.
 +
 +
== SOW 0 ==
 +
See [[#Technical Specification]] and next.
 +
 +
 +
<br><hr><br>
 +
 +
== Business Needs ==
 +
 +
President of the Company needs report that would help him to identify how many shipments has been created by specific office during a given time frame.
 +
 +
== Business Requirements ==
 +
 +
=== Interview ===
 +
 +
* The single purpose of this report is "to find out how many shipments selected office(s) created during selected time frame". Correct?
 +
 +
::''Yes.''
 +
 +
* Should this report be available in Scheduler or just be run “manually” or both?
 +
 +
::''Both.''
 +
 +
* Should output be in xls or html or both?
 +
 +
::''Spreadsheet only.''
 +
 +
 +
* Result should be:
 +
 +
a) Just a number of shipments (simplest, fastest) OR
 +
 +
b) A list of shipments with some fields reported in a table, correct? What fields to show (shipper, consignee, etc)? What is a group by requirement?. What is a Sort by requirement?
 +
 +
::''Number of CTs but also CT number with hyperlink. Fill in page with one CT per cell left to right then top to bottom. Not too wide so it is easy to print. One tab per office. Group by MOT (as on In Transit).''
 +
 +
 +
* As far as report filters this is what I have:
 +
** a)    from <date> to <date>
 +
** b)    for office(s) <list of offices (multiple could be selected)>
 +
** c)    any additional filters required?
 +
 +
::'' a) and b), that is all ''
 +
 +
 +
* New Shipments Per Office Report – is this a good name or you have a better name?
 +
 +
::''Call it "CT Records Per Office".''
 +
 +
 +
* Location in the system
 +
 +
::''Acc > Reports''
 +
 +
 +
* CT number with hyperlink – This will be a hyperlink to Internal App> View Shipment, correct? Or Internal App> Edit Shipment?
 +
 +
::''Yes, link to Internal App>View Shipment.''
 +
 +
 +
* CT# from left to right, top to bottom, are they sorted by earlier CT# to latest CT#, correct?
 +
 +
::''I prefer left to right, with the most recent CT# being top left, and the oldest being bottom right.''
 +
 +
 +
* Date criteria – Created on Date(shipments), correct?
 +
 +
::''The date the CT# is first created.''
 +
 +
 +
* Group per MOT – you mentioned that it would be the same as In-Transit report.
 +
 +
a)Undefined MOT result is not a part of In-transit report, would you like to include “Undefined MOT” for the result of this report?
 +
 +
::''It will consist of  Ocean- FCL, Ocean - LCL , AIR, Truck,and Undefined MOT
 +
 +
 +
* Schedule report – How the result will be generated per schedule? 
 +
 +
::''Every quarter''
 +
::''The report should only capture records created in the “date range” for example:''
 +
''a)Jan 1st  – March 31st''
 +
''b)April 1st – June 30th''
 +
''There needs to be a start date and an end date.''
 +
''So for a), the scheduler will run on April 1st, and list all CT’s created between Jan 1st and March 31st.''
Line 18: Line 115:
'''From Scheduled Reports'''
'''From Scheduled Reports'''
-
See [[#Figure 1. CT Records Per Office (Schedule Menu) ]]
+
See [[#Figure 1a. CT Records Per Office (Schedule Menu) ]]
See [[#Figure 2. General Filters Tab ]]
See [[#Figure 2. General Filters Tab ]]
Line 25: Line 122:
'''From Acc Reports'''
'''From Acc Reports'''
 +
 +
See [[#Figure 1b. CT Records Per Office (Acc Report Menu)]]
See [[#Figure 4. Filters and Buttons ]]
See [[#Figure 4. Filters and Buttons ]]
Line 44: Line 143:
'''Under Acc Report'''
'''Under Acc Report'''
-
* See [[#Figure 4. for filters and buttons]]
+
* See [[#Figure 4. Filters and Buttons]]
* Date filter is from Date to Date calendar
* Date filter is from Date to Date calendar
* Office filter is "single select and mandatory filter"  
* Office filter is "single select and mandatory filter"  
Line 51: Line 150:
==== Filters and Filters Matching Logic ====
==== Filters and Filters Matching Logic ====
-
* Date: From [[Ct#Created_On_Date]] To [[Ct#Created_On_Date]]
+
* ''Date:''
-
* [[Office#City]]
+
:* Mapping: From [[Ct#Created_On_Date]] To [[Ct#Created_On_Date]]
 +
:* Constrain: Mandatory field
 +
:** Date range MUST be selected
 +
:** From Date MUST be before To Date
 +
:** To Date MUST be after From Date
 +
* ''Office:''
 +
:* Mapping:[[Office#City]]
 +
:* Constrain: Mandatory field
 +
:** Office filter MUST be selected
=== Output ===
=== Output ===
Line 67: Line 174:
** Default on the ''combo box'' selected list
** Default on the ''combo box'' selected list
* Fill in page with one CT per cell left to right then top to bottom
* Fill in page with one CT per cell left to right then top to bottom
-
* Fit page for printing
+
* Fit Portrait page for printing
==== Sort By Definitions ====
==== Sort By Definitions ====
* Sort by CT# from the most recent CT# being on the Top left, and the oldest CT# being on the bottom right
* Sort by CT# from the most recent CT# being on the Top left, and the oldest CT# being on the bottom right
-
* Tab by alphabet order (Office(s))
 
==== Summary ====
==== Summary ====
* Output is a spreadsheet
* Output is a spreadsheet
-
* Spreadsheet tabs: There is one tab PER office with total of CT# records
+
* Spreadsheet tab: There is ONLY one tab PER office with total of CT# records
-
* Spreadsheet each tab: Group by MOT
+
* Spreadsheet tab: Group by MOT with number of CT#
* Spreadsheet consists of Header, Body, and footer
* Spreadsheet consists of Header, Body, and footer
* Header:
* Header:
Line 93: Line 199:
=== Figures ===
=== Figures ===
-
==== Figure 1. CT Records Per Office (Schedule Menu) ====
+
==== Figure 1. CT Records Per Office Menu ====
 +
==== Figure 1a. CT Records Per Office (Schedule Menu) ====
[[File:CTRecordsSchedules.JPG]]
[[File:CTRecordsSchedules.JPG]]
 +
==== Figure 1b. CT Records Per Office (Acc Report Menu) ====
 +
[[File:CTperOfficeAcc.JPG]]
==== Figure 2. General Filters Tab ====
==== Figure 2. General Filters Tab ====
Line 116: Line 225:
[[File:CTrecordsPerOffice.JPG]]
[[File:CTrecordsPerOffice.JPG]]
 +
 +
== Change Requests ==
 +
 +
 +
=== SOW 1 ===
 +
 +
==== Add type option "Per Sales Person" NOT IMPLEMENTED ====
 +
 +
'''[http://ct.jaguarfreight.com/mantis/view.php?id=3599 0003599]: ''' (CT Records Report ) add type option "Per Sales Person"
 +
 +
==== Core Requirements ====
 +
We need ability to create report of shipment list per sales person.
 +
* ''In other words, the report should be able to select and show shipments that have been created by Jaguar Users who are "Sales Person".''
 +
 +
==== Solution ====
 +
 +
* Add option "Per Sales Person" to "Type" filter
 +
* Use "Sales Persons" filter on "General Filters" tab
 +
** Mapping: Sales Person is Jaguar User which is set as "Sales Person" in his own User's Profile in Admin section of CT2.
 +
* Output Sales Persons in XLS the same way as Offices and Client Companies - in header of the report
 +
 +
[[File:addPerSalesPersonType.gif|Figure: "Per Sales Person" type of "CT Records" report.]]
 +
 +
==== Related Functionality ====
 +
* ? update the report in "saved/scheduled reports"
 +
 +
 +
=== SOW 2 Option to report per ASN portal ===
 +
 +
mantis: 4244
 +
 +
'''Requirements:'''
 +
 +
* to report on CT records per '''ASN2''' Portal (all E0s associated with portal combined)
 +
* need to know commodity item count because payments for portal will be based on this number
 +
 +
'''Solution''':
 +
 +
* add option "per Portal" (to the list of report sub-types)
 +
* in output report not only on number of shipments but on the number of commodity lines as well
 +
* layout:
 +
** tabs list - same
 +
** tab contents - different - show these 3 columns:
 +
*** CT#
 +
*** PO
 +
*** SKU

Current revision as of 15:41, 6 August 2013


Contents

[edit] Info

Scope:

This article covers "CT Records" report.

[edit] Business Needs and Requirements

See #Business Needs and next.

[edit] SOW 0

See #Technical Specification and next.




[edit] Business Needs

President of the Company needs report that would help him to identify how many shipments has been created by specific office during a given time frame.

[edit] Business Requirements

[edit] Interview

  • The single purpose of this report is "to find out how many shipments selected office(s) created during selected time frame". Correct?
Yes.
  • Should this report be available in Scheduler or just be run “manually” or both?
Both.
  • Should output be in xls or html or both?
Spreadsheet only.


  • Result should be:

a) Just a number of shipments (simplest, fastest) OR

b) A list of shipments with some fields reported in a table, correct? What fields to show (shipper, consignee, etc)? What is a group by requirement?. What is a Sort by requirement?

Number of CTs but also CT number with hyperlink. Fill in page with one CT per cell left to right then top to bottom. Not too wide so it is easy to print. One tab per office. Group by MOT (as on In Transit).


  • As far as report filters this is what I have:
    • a) from <date> to <date>
    • b) for office(s) <list of offices (multiple could be selected)>
    • c) any additional filters required?
a) and b), that is all


  • New Shipments Per Office Report – is this a good name or you have a better name?
Call it "CT Records Per Office".


  • Location in the system
Acc > Reports


  • CT number with hyperlink – This will be a hyperlink to Internal App> View Shipment, correct? Or Internal App> Edit Shipment?
Yes, link to Internal App>View Shipment.


  • CT# from left to right, top to bottom, are they sorted by earlier CT# to latest CT#, correct?
I prefer left to right, with the most recent CT# being top left, and the oldest being bottom right.


  • Date criteria – Created on Date(shipments), correct?
The date the CT# is first created.


  • Group per MOT – you mentioned that it would be the same as In-Transit report.

a)Undefined MOT result is not a part of In-transit report, would you like to include “Undefined MOT” for the result of this report?

It will consist of Ocean- FCL, Ocean - LCL , AIR, Truck,and Undefined MOT


  • Schedule report – How the result will be generated per schedule?
Every quarter
The report should only capture records created in the “date range” for example: 
a)Jan 1st   – March 31st 
b)April 1st – June 30th
There needs to be a start date and an end date.
So for a), the scheduler will run on April 1st, and list all CT’s created between Jan 1st and March 31st.


[edit] Technical Specification

[edit] Summary

  • This report should appear on Saved/Scheduled Reports section (to run this report you have to schedule it).
    • Report is sent to recipients on schedules via email.
      • Usually user will schedule this report to generate result every quarter for example:
        • Schedule on April 1st --> Result is CT(s)# that are created from Jan 1st to March 31th.
        • Schedule on July 1 st --> Result is CT(s)# that are created from April 1st to June 30th.
  • This report MUST include ALL new shipments records that are created since previous report.
  • This report should appear Acc > Reports > CT Records Per Office.
    • Report is allowed user to select date range for new shipment created per office
  • Report shows date time stamp and how many records are found (number)per mode

[edit] User Interface

From Scheduled Reports

See #Figure 1a. CT Records Per Office (Schedule Menu)

See #Figure 2. General Filters Tab

See #Figure 3. Output Tab

From Acc Reports

See #Figure 1b. CT Records Per Office (Acc Report Menu)

See #Figure 4. Filters and Buttons

[edit] Filters

See #Filters and Filters Matching Logic.

[edit] Hard Coded Conditions

  • This report is used to identify how many shipments (CT's) has been created in the system by specific office during a given time frame since the last time the instance of this report run.
  • Report should use CT#Created On Date date field to identify when shipment is first created.

[edit] User Defined Conditions

Under Save Schedule report

Under Acc Report

  • See #Figure 4. Filters and Buttons
  • Date filter is from Date to Date calendar
  • Office filter is "single select and mandatory filter"
  • Check box in front of each filter. If filter is set, check box MUST be automatically checked vise versa

[edit] Filters and Filters Matching Logic

  • Date:
  • Office:
  • Mapping:Office#City
  • Constrain: Mandatory field
    • Office filter MUST be selected

[edit] Output

  • Create output tab with list of output headings; in this case it is only for schedule report view
  • This output heading consists of ONLY CT#
  • Required default headings on the combo box
  • List of headings #Cell Definitions
  • See #Figure 5. SpreadSheet for output result (xls)

[edit] Cell Definitions

  • CT# with hyperlink to Internal App> CT# View Shipment
    • Mapped to CT#CT Num
    • Default on the combo box selected list
  • Fill in page with one CT per cell left to right then top to bottom
  • Fit Portrait page for printing

[edit] Sort By Definitions

  • Sort by CT# from the most recent CT# being on the Top left, and the oldest CT# being on the bottom right

[edit] Summary

  • Output is a spreadsheet
  • Spreadsheet tab: There is ONLY one tab PER office with total of CT# records
  • Spreadsheet tab: Group by MOT with number of CT#
  • Spreadsheet consists of Header, Body, and footer
  • Header:
    • Report name
    • Date Time Stamp
    • Criteria (list of filter(s) selected)
    • Jaguar Logo
  • Body: (general info)
    • Consists of heading (CT#)and result in each cell
      • Cell: CT# with hyperlink
  • Footer
    • There is no footer

[edit] Special Cases

TBD

[edit] Figures

[edit] Figure 1. CT Records Per Office Menu

[edit] Figure 1a. CT Records Per Office (Schedule Menu)

File:CTRecordsSchedules.JPG

[edit] Figure 1b. CT Records Per Office (Acc Report Menu)

File:CTperOfficeAcc.JPG

[edit] Figure 2. General Filters Tab

File:GeneralTabCTrecords.JPG


[edit] Figure 3. Output Tab

File:OutputCTRecords.JPG


[edit] Figure 4. Filters and Buttons

File:CTrecordsACCMenu.JPG


[edit] Figure 5. SpreadSheet

File:CTrecordsPerOffice.JPG

[edit] Change Requests

[edit] SOW 1

[edit] Add type option "Per Sales Person" NOT IMPLEMENTED

0003599: (CT Records Report ) add type option "Per Sales Person"

[edit] Core Requirements

We need ability to create report of shipment list per sales person.

  • In other words, the report should be able to select and show shipments that have been created by Jaguar Users who are "Sales Person".

[edit] Solution

  • Add option "Per Sales Person" to "Type" filter
  • Use "Sales Persons" filter on "General Filters" tab
    • Mapping: Sales Person is Jaguar User which is set as "Sales Person" in his own User's Profile in Admin section of CT2.
  • Output Sales Persons in XLS the same way as Offices and Client Companies - in header of the report

Figure: "Per Sales Person" type of "CT Records" report.

[edit] Related Functionality

  •  ? update the report in "saved/scheduled reports"


[edit] SOW 2 Option to report per ASN portal

mantis: 4244

Requirements:

  • to report on CT records per ASN2 Portal (all E0s associated with portal combined)
  • need to know commodity item count because payments for portal will be based on this number

Solution:

  • add option "per Portal" (to the list of report sub-types)
  • in output report not only on number of shipments but on the number of commodity lines as well
  • layout:
    • tabs list - same
    • tab contents - different - show these 3 columns:
      • CT#
      • PO
      • SKU
Retrieved from "https://wiki.jaguarfreight.com/CT_Records_Report"
Personal tools