3Web Reports Gen1 to Gen2 Conversion Guidelines
Web Reports Gen1 to Gen2 Conversion Guidelines
- Web Reports Gen1 provides categories which are more like views. One or more tables with predefined joins and columns from these tables may be provided as a view.
- Web Reports Gen2 provides categories which more directly expose the tables themselves with all the relevant columns exposed. There are no predefined joins in these categories. This provides a better performance advantage as it avoids predefined joins. This also provides better flexibility so you can join with categories that are relevant for the report.
- This also means that Gen2 categories cannot be directly mapped to Gen1 categories and Gen1 reports cannot be automatically converted to Gen2 and have to be handled manually
- A table has been provided later in this document listing all Gen1 categories and what Gen2 categories they are made up of
- The following guidelines will help in the conversion of Gen 1 reports to Gen2:
- First, assess what the report is about and how many users are using the reports. Sometimes a lot of reports can get built with different names and the same content displayed in a different manner. It is important to come up with a consolidated list of unique reports which are frequently used.
- Identify the relevant categories used in Web Report Gen1 and make sure to identify the unique set of categories that need to be used in Web Reports Gen2
- Identify the join conditions required for Web Report Gen2 between the categories in Web Reports Gen2.
- Identify the equivalent filter conditions and sorting category/column in Web Reports Gen2.
- Identify the grouping category/column user at the Group/Report Header/Footer level in case there are aggregations being done. Identify the equivalent category/column in Web Report Gen2
- In the main content of the report, identify the categories/columns that are used in Web Reports Gen1 and the equivalent in Web Reports Gen2.
- Before formatting the report in Web Report Gen2, run the report to make sure the data obtained matches what is seen in Web Report Gen1. Make sure to run it for a larger data set and also for multiple pages if required.
- Once step-4 is consistent and correct, then do the formatting part of the report in Web Reports Gen2 to match the formatting in Web Report Gen1.
Gen1 and Gen2 Categories
This section contains Web Reports Gen1 Categories and the equivalent Web Reports Gen2 categories. It is important to note the following:
- While converting the reports, DO NOT take the categories directly from Web Reports Gen1 and convert to equivalent categories in Web Reports Gen2 from the following table.
- The following table is provided as a reference to illustrate that Web Report Gen1 categories were more abstracted and encapsulated at a higher level, whereas Web Report Gen2 categories are not.
- Look at the mapping below and identify the Web Report Gen1 categories from the left and identify the Web Report Gen2 categories from the right (unique set) and then identify the join condition required to join them appropriately as per the report requirements. Also choose the bare minimum number of categories that are required for the report.
- In some cases, you may need to create multiple aliases of the same table in order to join with foreign keys for different logical entities.
For example: Allocation category has from_inventory_id and to_inventory_id as foreign keys but are pointing to the inventory table but they two represent two different logical entities i.e. ( from Inventory - is inbound inventory) and ( to inventory - is outbound inventory).
- In many cases, all the joins may not be required if you are not using data from the joined tables, so be aware of this, as this could provide better performance.
- Important: Web Reports Gen2 does create some of the obvious joins automatically. But you must go check the joins created and see if there are any other additional joins to be added or any redundant joins to be removed. Also avoid all duplicate joins.
- Also, anytime there is a change in a category or any options in a category (for example, one suppresses duplicates using the check box) then Web Reports will try to regenerate the joins again. It may seem frustrating, but this behavior is expected as some settings have changed and the application is recreating joins to suit those settings. It is important to double check again to see if joins have adjusted again correctly. To avoid regeneration of joins, it is good practice to decide upfront what categories and columns are required to minimize this.
Web Report Gen1 Category Equivalent Web Report Gen2 Categories and Related Joins Allocation - allocation
- allocation_type (inner)
- allocation_status (inner)
- allocation_run_hdr (left outer)
- uom (use alias to create two uom categories if you have to see alloc_uom and cartonize_uom) (left outer)
- lpn_type (left outer)
- task_creation (left outer)
AllocationFromContainer - container
- container_status ( inner)
- facility ( inner)
- company ( inner)
- lpn_type ( inner)
- pallet ( left outer)
- pallet_status ( left outer)
- audit_status ( left outer)
AllocationFromContainerLocation - location
- company ( for dedicated company ) (at location level) (inner)
- company ( destination company on location) ( left outer)
- Facility ( inner)
- Item_assignment_type ( inner)
- location_type ( inner)
- inventory_lock ( left outer)
- location_size_type ( left outer)
- mhe_system ( left outer)
- mhe_type ( left outer)
- item ( for item dedicated in a location) ( left outer)
AllocationFromContainerShipment - ib_shipment
- company (inner)
- facility (inner)
- ib_shipment_status (inner)
- ib_shipment_type (left outer)
- inventory_lock (left outer)
AllocationFromInventory - inventory
- inventory_attribute ( left outer)
- facility ( inner)
- inventory_status ( inner)
- batch_number ( left outer)
AllocationFromInventoryLocation - location
- Company ( for dedicated company at location level) (inner)
- Facility ( inner)
- Item_assignment_type ( inner)
- location_type ( inner)
- inventory_lock ( left outer)
- location_size_type ( left outer)
- mhe_system ( left outer)
- mhe_type ( left outer)
- item ( for item dedicated at location) ( left outer)
- task_zone ( left outer)
- replenishment_zone ( left outer)
AllocationMheSystem - mhe_system
- mhe_type ( inner )
- facility ( inner)
AllocationToContainer - container
- facility ( inner)
- company ( inner)
- lpn_type ( left outer)
- pallet ( left outer)
- pallet_status ( left outer)
- audit_status ( left outer)
AllocationToContainerLocation - location
- company (for dedicated company at location level) (left outer)
- facility (inner)
- Item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item (for item dedicated at location level) (left outer)
AllocationToInventory - Inventory
- inventory_attribute (left outer)
- facility (inner)
- inventory_status (inner)
- batch_number (left outer)
AllocationToInventoryLocation - location
- company ( for dedicated company at location level) (left outer)
- facility (inner)
- Item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for item dedicated at location level) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
Appointment - appointment
- dock_type (inner)
- facility (inner)
- appointment_status (inner)
- value_type (matching value type) (left outer)
- dock (left outer)
Batch Number - batch_number
- facility (inner)
- company (inner)
- inventory_lock (left outer)
BillingItem - billing_item
- company (inner)
- history_activity (left outer)
- column_name (activity group) (left outer)
Carrier - carrier
- company (inner)
- carrier_status (inner)
- std_carrier (left outer)
- carrier_type (left outer)
CarrierFacility - carrier_facility
- facility (inner)
- carrier_integration_type (left outer)
CarrierLpn - carrier_lpn
- container (inner)
- facility (inner)
- company (inner)
CCAdjustment Header Detail - cc_adjustment_hdr
- cc_adjustment_dtl (inner)
- inventory_attribute (left outer)
- cc_adjustment_status (inner)
- company (inner)
- facility (inner)
- cc_adjustment_warning (left outer)
- cc_warning (left outer)
Company - Company
Container - Container
- container_status (inner)
- facility (inner)
- company (inner)
- lpn_type (left outer)
- pallet (left outer)
- pallet_status (left outer)
- audit_status (left outer)
ContainerCurrLocation - Location
- Facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company (dedicated company on location) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( on dedicated item at location) (left outer)
ContainerLocks - container_lock_xref
- inventory_lock (inner)
- Container (inner)
- Inventory (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- item (inner join with inventory)
- facility ( inner)
- company ( inner)
- putaway_type (left outer with item)
ContainerPrevLocation - Location
- Facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company (dedicated company on location) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( on dedicated item at location) (left outer)
ContainerRcvdLoad - load
- company (inner)
- facility (inner)
- load_status (inner)
- carrier (left outer)
- carrier_type (left outer)
- trailer (left outer)
ContainerRcvdShipment - ib_shipment
- company (inner)
- facility (inner)
- ib_shipment_type (left outer)
- inventory_lock (left outer)
Dock - dock
- facility (inner)
- dock_type (inner)
- dock_status (inner)
- location (inner)
IBShipmentPrepackItemResidual - ib_shipment_item_residual
- facility (inner)
- company (inner)
IBShipmentSerialNbr - ib_shipment_srl_nbr
- company (inner)
- facility (inner)
InboundShipmentItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
InboundShipmentLoad - load
- company (inner)
- facility (inner)
- load_status (inner)
- carrier (left outer)
- carrier_type (left outer)
- trailer (left outer)
InboundShipmentVendorPerf - ib_shipment_vendor_perf
- vendor_perf_code (inner)
- ib_shipment (inner)
- company ( for vendor) (inner)
- uom (left outer)
Inventory - inventory
- facility (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- batch_number (left outer)
- allocation (left outer)
InventoryItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
InventoryItemLocation - inventory
- facility (inner)
- item (inner)
- putaway_type
- inventory_attribute (left outer)
- item_characterstics (inner)
- putaway_type (left outer)
- item_metrics (inner)
- item_struct (inner)
- item_hierarchy_defn (this will be one alias per hierarchy level) (left outer)
- item_group_defn (left outer)
- inventory_status (inner)
- batch_number (left outer)
- allocation (left outer)
- company (for item ,dedicated company on location and destination company on location) (left outer)
- location (full join)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item (for dedicated item on location) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
- property_prompt_mode (left outer)
InventoryLocation - location
- company ( more aliases may be required) (left outer)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for dedicated item on location) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
Invoice - invoice
- invoice_dtl (inner)
- facility (inner)
- company (inner)
- invoice_status (inner)
- history_activity (left outer)
Item - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( multiple aliases may be required) ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
ItemFacilityMaterialHazardType - item_facility_material_hazard_type
- item_facility (inner)
- item (inner)
- facility (inner)
- material_hazard_type (inner)
ItemImage - item_image
- item (left outer)
ItemMaterialHazardType - item_material_hazard_type
- item (inner)
- material_hazard_type (inner)
ItemPrepack - item_pre_pack
- item (inner)
Location - location
- company ( for dedicated_company and destination company) (left outer)
- facility (inner)
- location_type (inner)
- item_assignment_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- item ( for item dedication) (left outer)
- replenishment_zone (left outer)
- task_zone (left outer)
Order - order_dtl
- order_hdr (inner)
- inventory_attribute (left outer)
- company ( for destination company and company on Order itself) (inner)
- order_status (inner)
- order_type (inner)
- facility ( for destination facility, shipto facility and facility on the Order itself) ( inner for facility and for destionation and shipto it is left outer)
- lpn_type (left outer)
- ship_via (left outer)
- batch_number (left outer)
OrderCompany - Company
OrderDestinationCompany - Company
OrderFacility - Facility
OrderInstructionsDetail - order_instructions
- order_dtl (left outer)
- order_hdr (inner)
- order_instruction_type (inner)
- facility (inner)
- company (inner)
OrderInstructionsHeader - order_instructions
- order_hdr (left outer)
- order_instruction_type (inner)
- facility (inner)
- company (inner)
OrderItem - Item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
OrdersOnLoad - allocation
- order_dtl (inner)
- order_hdr (inner)
- order_status (inner)
- inventory ( from and to require different aliases and must be left outer joins)
- container (left outer)
- container_status (inner)
- ob_stop_dtl (left outer)
- ob_Stop (left outer)
- load (left outer)
- load_status (left outer)
- item (inner)
- inventory_attribute ( for to inventory) (left outer)
- batch_number ( for both from and to inventory) (left outer
- facility (inner)
OrdersOnLoadExtended - allocation
- order_dtl (inner)
- order_hdr (inner)
- order_status (inner)
- inventory ( from and to) (left outer)
- container (left outer)
- container_status (left outer)
- ob_stop_dtl (left outer)
- ob_Stop (left outer)
- load (left outer)
- load_status (left outer)
- item (inner)
- inventory_attribute ( for to inventory) (left outer)
- batch_number (left outer)
- facility (inner)
- order_type (inner)
- ship_via (left outer)
- facility ( shipto) (left outer)
- srl_nbr_inventory (on to_inventory) (left outer)
- serial_nbr (left outer)
- item_characterstics (inner)
OrderStageLocation - location
- company ( for dedicated company) (left outer)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company ( for destination company) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for item dedication ) (left outer)
OutboundLoad - load
- company
- facility
- carrier
- carrier_type
- trailer
OutboundLoadCompany - company
OutboundLoadFacility - facility
OutboundStop - ob_stop_dtl
- ob_stop_hdr
- stop_status
- container
- facility
Pallet - pallet
- company
- facility
PalletHistory - pallet_history
- facility
- company
Parcel - parcel_manifest
- facility
- parcel_manifest_status
- parcel_shipment
ParcelLpn - parcel_shipment_dtl
- parcel_shipment
- parcel_manifest
- carrier_lpn
PurchaseOrder - purchase_order_dtl
- purchase_order_hdr
- facility
- company
- inventory_lock
- purchase_order_type
PurchaseOrderItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
PutAwayPriority - putaway_priority
- facility (inner)
- location_type (inner)
- putaway_method
- putaway_search_mode
- location_size_type
- putaway_type
QCVerificationHistory - verification_result_dtl
- verification_result_hdr
- qc_status
- facility
- company
ReplenishmentZone - replenishment_zone
- facility
Route - route_dtl
- route_hdr
- facility
- company
SerialNbrRepository - serial_nbr
- Item
- company
- facility
SerialNbrInvetnory - serial_nbr_inventory
- serial_nbr
- inventory
ShipVia - ship_via
- company ( inner)
- std_carrier_service ( left Outer)
ShipViaDetail - ship_via_dtl
- ship_via (inner)
- std_carrier_accessorial (inner)
SimpleInventory - inventory
- facility (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- batch_number (left Outer Join)
Task - Task
- facility (inner)
- task_status (inner)
- task_type (inner)
TaskCurrLocation - location
- company ( for dedication company) ( inner join)
- facility ( inner)
- item_assignment_tpye ( inner)
- location_type (inner)
- company ( destination company) ( left outer join)
- inventory_lock ( left outer join)
- location_size_type ( left outer join)
- mhe_type ( left outer join)
- item ( for item dedication) ( left outer join)
TaskNextLocation - location
- company ( for dedication company) ( inner join)
- facility ( inner)
- item_assignment_tpye ( inner)
- location_type (inner)
- company (destination company) (left outer join)
- inventory_lock (left outer join)
- location_size_type (left outer join)
- mhe_type (left outer join)
- item (for item dedication) (left outer join)
Trailer - tailer
- location (left Outer join)
WMSActivityDtl - wms_activity_dtl
- wms_activity (inner)
- wms_activity_code (inner)
- facility (inner)
- company (inner)
WMSACtivityHdr - wms_activity
- wms_activity_status (inner)
- facility (inner)
- company (inner)
WorkOrder - work_order_hdr
- work_order_status (inner)
- work_order_type (inner)
- facility (inner)
- company (inner)
- work_order_kit (inner)
- item ( for work Order Kit) (inner)
- item ( for work Order Component)
- batch_number ( for work Order )( left outer)
- inventory_attribute ( for work Order Component) ( left Outer)
- inventory_attribute ( for work Order Kit) ( left Outer)
- work_order_component (inner)
- uom ( inner)
- location (for work order kit) ( left Outer)
WorkOrderComponentItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
Example Gen1 to Gen2 Report
Now, keeping the above points in mind, let’s take an example report from Web Report Gen1 and convert it to Web Report Gen2:
Let’s say we generate a summary of Inventory in the warehouse by item. Then we show the breakup based on where the inventory is located and show the appropriate status. We’ll also show an aggregation of the total inventory quantity by inventory status.
Let’s call this report Inventory Report (Gen1)
Categories
We’ll choose the following categories:
SimpleInventory (This category contains the basic inventory columns required)
Along with it we need to print the item information so let’s choose category
Inventoryitem
Also we need to print the container information if inventory is present in a container and the location where the container is, so let’s choose the following categories:
Container
ContainerCurrLocation
Now the inventory we choose can be present in either a container or directly in a location which WMS allows only on active locations. So let’s choose the category.
InventoryLocation
With these categories we should be able to get all the inventory in the warehouse and show the inventory information like SKU, quantity, its location etc. We can also show some aggregations by SKU.
Sorts
The following are the sorts that we are choosing:
- Item Alternate Code ( first by Item)
- Inventory Location Type which is typically Active location
- Container Location Type which can be any of the other locations or Container may not be located at all
- Container Type which helps distinguish between inbound and outbound containers.
Filters
Next let’s add a filter condition:
- Filter for specific Company and facility codes
- Exclude inventory that are in cancelled status
- Choose all inventory that are in a container and exclude all containers that are in ‘Delivered’, ‘Consumed’, ‘Cancelled’ and ‘Shipped’ status
- Choose all inventory that is in active locations
Layout the report with all required columns in the details section. Add two more group footer sections which do the following:
- Group footer 1 – aggregates the inventory current quantity based on item and the location type where it is located. For this the group footer condition chosen is:
- Concatenate({InventoryLocation.Type},{ContainerCurrLocation.Type},{Container.Type},{InventoryItem.Alternate Item Codes})
- Group footer 2- aggregates the inventory quantity by item
- The two group footers are color formatted to make the breakup on the report easily visible.
Inventory Report (gen1)
When you execute the report, the output shows up as follows:
In this case, the joins are automatically created by the application. But the joins are inbuilt and a little bit more expensive in terms of performance as they bring out a lot of data.
Convert Report to Gen2
Now let’s convert the above report to WebReport Gen2.
If we pick up the Web Report Gen1 Categories and map them to Web Report Gen2, the following is what we will get:
Note about Importing Gen1 WebReports into Gen2
Note: It is possible to download a Gen1 webreport to get a .wr report definition file downloaded and then to attempt uploading it into Gen2. However, you will not get a working report via this process. Many reports will simply not import and will fail with a "500 Error". A few simple reports might import without such an error; however, the categories and filters will be empty and will not execute. At best, you might get the field layouts. Even in this scenario, you will have to change all the fields to use the appropriate Gen2 categories and fields. So, in short, this mechanism may at best give you a basic starting point for just the field layout, and in most cases will not work at all.
| Gen1 Categories | Gen1 Categories Mapped to Gen2 |
|---|---|
| SimpleInventory |
|
| Inventoryitem
|
|
| Container |
|
| ContainerCurrLocation |
|
| InventoryLocation |
|
Create Report Categories
From the above table, let’s quickly consolidate and create a unique set of categories that are the bare minimum required for our report:
- inventory
- Facility
- company
- item
- container
- location (for inventory which is present in active) -alias it as inventory_location
- location (container’s location)
- lnventory_status
- container_status
- location_type for active location let us call it inventory_location_type
- location_type for container’s location
The report should be much faster because we are avoiding joining other categories as we are not going to use any of the columns. If we have to put in more columns, then the need to join with other categories would be necessary.
For sorting and filtering we will retain the same as in Web Report Gen1 except change the category names to suit the Web Report Gen2 category and column names.
We will also retain the same report layout columns and formatting. Similarly, keep the same two group footers and the group footer conditions except change the category and column names appropriately.
Report Sorts
Report Filters
Joins
Now in this case, the system will also create some joins by default but not all of them so let’s create all the joins required. We need to do the following inner joins:
- Inventory with item (the reason being inventory will always have an item)
- Item with company (item always belongs to a company)
- Inventory with facility (Inventory is always in a given facility)
- Inventory with inventory_status (inventory always has a status)
- Container with container_status (container always has a status)
The following left outer joins:
- Inventory with inventory_location ( as inventory may or may not be in an active location)
- Inventory with container (as inventory may or may not be in a container)
- Container with location (container may or may not be located)
- Inventory_location with inventory_location_type ( all inventory locations may not be active so its location type also may not be required if it is not in active)
- Location with location_type (this is for container’s location. All container’s may not be located and hence may not have a location type)
Now if you execute this report, the following is the output which is the very same as the one we created with Web Reports Gen1 and it is much faster in execution as well.