report creator training workshopcontent.leanlogistics.com/bi/docs/report creator workshop.pdf ·...

www.LEANLOGISTICS.com

Report CreatorTraining Workshop


Agenda

8:30-9:00 Welcome, Introductions and Overview of the Day9:00-10:30 Base Report Development Process and Datamart

Overview10:30-10:45 Break10:45-11:30 Creating and Using User-Defined Fields11:30-12:00 User Exercises Part 112:00-1:00 Lunch1:00-1:30 Report Formatting1:30-2:15 General Tips and Techniques2:15-2:45 User Exercises Part 22:45-3:00 Using ReportCaster to Automate Reports2:45-3:00 Break3:00-4:15 Lean-Assisted Report Development Time4:15-4:30 Wrap up


INTRODUCTION


Participant Introduction

• Name• Company• Role• Reporting Background• Your Goals for this Session


Objective

• Build upon the base self-training that you’ve already completed

• Better prepare you for your rollout/transition from the legacy ad-hoc tool (if necessary)

• Allow you to start using the full capabilities of WebFOCUS/InfoAssist to create more complex reports


Initiative Overview

Business Reporting is a LeanLogistics strategic initiative, designed to provide enhanced reporting and analysis capabilities within our suite of products and services.

It is a combination of software tools and data organization that creates value for both LeanLogistics and our customers.

Visual ReportingOnline

Scorecarding

Data

Visualization

Report

Scheduling

Key Deliverables:

User-Defined

Report Portal


Future State


WebFOCUS Reporting Spectrum

• InfoAssist – Business Analyst Ad-Hoc Tool– User-defined Reports, using single data source– Editable examples included

• Lean Standard Reports– Report set that is available to all TMS users – more

complex

• Report Catalog– Best Practices MTS reports


Ad-Hoc Comparison

Legacy Ad-Hoc

Pros: • Simple Wizard interface• Legacy V1 Datamarts available• No immediate report conversion needed• Report creation available to any user

Cons: • No new Datamarts or Datamart

enhancements will be published to this environment

• No Scheduled reports, User Portal, Charts/Graphs, Advanced Report Features available

• Legacy ad-hoc will be deprecated in a future release, and all customers will need to migrate to the new tool

WebFOCUS Ad-Hoc

Pros: • All new datamarts and datamart enhancements

will be available • Report scheduling, User Portal, Charts/Graphs,

Advanced reporting features (e.g. user defined fields, cross-tabs, etc.)

• Pre-defined sample report library with usable/editable templates

Cons: • More sophisticated interface – higher learning

curve requires report creator skillset/mindset and additional time investment to master

• No V1 Datamarts available• Need to convert existing ad-hoc reports• Report creation limited to subset of existing users

with appropriate skillset (all users can run/schedule/manage dashboard)


BASE DEVELOPMENT PROCESS &DATAMART REVIEW


Report Portal Configuration - Users

Set User Type


Report Portal Configuration - Menu

Enable Menu Path for Job Profile


Report Viewers vs. Report Creators

Report Viewers

• Access to Portal• Run Reports• Run Reports Deferred• Schedule Reports• Manage Dashboard

Report Creators

• Same as Report Viewers, plus:– Create Reports via

InfoAssist– Manage Report Folder

Structure– Access to Lean Sample

Reports


Report Development Overview

• Report Design/Spec– What is the business question that this report

answers?– Which datamart contains the appropriate level of

information?– What kinds of calculations are needed?– How often will this report be run?


Transactional Data Relationship

General ConfigurationInformation

Load SupportingInformation

Order

Shipment

Shipment Leg

Load

Order

Shipment

Shipment Leg

Shipment

Shipment Leg

Load

Order

Shipment

Shipment Leg

Load

Shipment Leg

Load

StopsBase Rate/

AccessorialsNotes

One Order = One Load

Load

One Order = Multiple Shipments/Multiple Loads

One Order = Single Shipment/Multiple Loads (Pooling/X-Dock)

Order Shipment Cost Analysis DM

Shipment IndividualMove Costing DM

Load Performance DM

Stop Performance DM

Load Charges DM

Load Note DM

Tendering

TenderDetails DM

Locations

WebSettle

Company Locations DM

WebSettle Details DM

Carriers

Carrier Info DM, Carrier Contacts DM


Datamart Review

• What is a Datamart?– Transactional vs.

Analysis– Tables vs. Views

• Refresh Process/Schedule

• Enhancing Datamarts• Datamart Limitations

Trans

DW

T1

T2

T3

T4

V1 V2 V3

Reporting Engine

T5

Nightly Population


Available Datamarts

• Load Performance• Stop Performance• Order/Shipment Cost

Analysis• Shipment Individual

Move Costing• Load Charges• Tender Details

• Spotmarket (All)• Spotmarket Last (Most

Recent)• Load Notes• WebSettle• Carrier Information• Carrier Contacts• Company Location


Datamart Detail

Datamart Data Refresh Schedule Structure General Description Exclusions/Notes

Carrier Contacts Real-Time * One record per ContactContact information for associated carriers.

Carrier Info Real-Time * One record per CarrierCarrier company information for associated carriers.

Company Locations Real-Time * One record per LocationShipper company location details.

Load Performance Nightly Refresh * One record per Load

This data set can be used to report on activities that are at summary level of the load(TL/LTL/IM only).

Key aspects of this data set are:

* Lane level information, where lane is defined as the first origin and final destination of the load

* The origin and destination information consist of location information such as name, city, state, zip country, but is not related directly to

the "TMS Lane" setup used in RG/Rating/...

* Carrier of record and the common freight payable charges broken out by base charge, stop off charge and automatic fuel charges

A sample business use of this data set is: Overall cost analysis by Mode, Carrier or Lane.

Load Charges Nightly Refresh * One record per ChargeCharge/Accessorial data for loads.

Load Note Nightly Refresh * One record per Load NoteLoad note data (including service failure information).

Order and Shipment Cost

AnalysisNightly Refresh * One record per Order/Shipment Provides information to facilitate analysis of Profit/Loss on Freight Billable and/or Payable for a shipment or order.

* Excludes unassigned

Order/Shipments (can only report

on Orders/Shipments ASSIGNED to

a load)

Shipment Individual Move

CostingNightly Refresh

* One record for each leg (move) of a

shipment

Provides cost information about the individual moves within a shipment.


* Includes the freight payable allocated to the individual leg.

* Includes (where enabled) freight billable allocated to the individual leg.

* If Estimated Payable Rules and Pre-rating are active at time of shipment creation, provides estimated (non-consolidated) payable rate

for this leg.

A sample business use of this data set is to analyze the cost of moving individual shipment legs.

* Excludes unassigned Shipment

Legs (can only report on legs

ASSIGNED to a load)

Stop Performance Nightly Refresh * One record for each Stop on a load

This data set would be used to identify date related information on each stop of a load(TL/LTL/IM only).


* The row contains the summary level information of the shipments assigned to that stop

* This is stop information that was communicated to the carrier of record.

A sample business use of this data set is: Carrier performance at stops, including for the plan, appointment, arrival and departure dates and

times.

Tender Details Nightly Refresh * One record for each Tender for a load

Information about tendering activity for a load (multiple tender records for each load).

A sample business use of this data set is: Carrier tender acceptance, routing guide effectiveness.


Reporting Portal

http://content.leanlogistics.com/bi/docs


InfoAssist Interface Review

• Menus/Ribbon Bar• Data Pane• Filter Pane• Query Pane• Report Canvas• Report Tabs


Report Development Overview -Components

Sort Fields• BY• ACROSS

Data Fields• SUM• PRINT

Filters/Parameters• Simple Filters• Advanced Filters• Static Parameters• Dynamic Parameters

Output Format• HTML• PDF• Excel

Basic Aggregation• Count, Count Distinct,

Sum, Average, Minimum, Maximum

User-Defined Fields• Defines• Computes


Filtering/Parameters

Given the current design of the data, we recommend using only simple, or static parameters to ensure reasonable report times

Parameter Type Function

Simple Parameter User enters values (case/format-sensitive)

Static Parameter Available values are entered and stored with the report

Dynamic Parameter Available values are queried from the database at time of report execution


Date Formats

Usable Date/Time formatsDate OnlyMM/DD/YYYYMM-DD-YYYY

Date and TimeMM/DD/YYYY HHMM (simple filter)MM/DD/YYYY HH:MM

By default, times will default to 00:00 of the given day, so range filters should include time as well.


Configuring the IA Interface

• Saved Preferences• View Options

– Design Mode– Data Panel Format– Query Panel Options– Output Window


Available Example Reports

Report Name Datamart Description Notes

01. SAMPLE_Tender Acceptance Tender Details Shows acceptance percentage, tenders, accepted tenders,expired tenders, rejected tenders (carrier) and rejected tenders (shipper)

- Acceptance percentage is defined as: number of winning tenders / total loads- Carriers under 80% Acceptance highlighted in red

02. SAMPLE_Carrier Performance Load Performance and Costing Shows on-time percentage for Pick (Appt. vs. Actual),Drop (Appt. vs. Actual), Drop (Plan vs. Actual) and Load Close (Plan vs. Load Close Date)

- Pick, Drop and RDD On-time %: Carrier is considered late if the Pick Actual date/timeis greater than 30 minutes from the Pick Appointment date/time- Load Close On-time %: Carrier is considered late if the Load Close date/time is greater than 72 hours from the Last Drop Plan date/time- Only includes Completed loads- Only includes loads where the Drop Appointment and Actual are not missing

03. SAMPLE_Core Metrics by Carrier Load Performance and Costing Shows load count, average weight, average volume,average cost (payable) by Carrier

- Only includes Active and Completed loads- Tender Date in previous month

04a. SAMPLE_Load Count by Mode (Pie Chart)

Load Performance and Costing YTD load count by mode - YTD by Tender Date

04b. SAMPLE_Load Count by Mode (Line Chart)

Load Performance and Costing YTD load count by mode - YTD by Tender Date

05. SAMPLE_Core Metrics by Location Load Performance and Costing Shows weight and volume by mode for the top ten locationsby number of loads

- Only includes Completed loads- Loads with a first pick calculated date greater than 01/01/2010


Available Example Reports, Cont’d

Report Name Datamart Description Notes

06. SAMPLE_Top Carriers by Volume Load Performance and Costing Shows weight and total distance for the top ten Carriersby number of loads

- Only includes Completed loads- Loads with a first pick calculated date greater than 01/01/2010

07. SAMPLE_Header and Footer Variables

N/A Shows how to use Headers and Footers to include values from different types of fields

- N/A

08. SAMPLE_Carrier Insurance Carrier Info Shows the status and days until expiration for each Carrier's Liability, Cargo and Worker's Comp Insurance

- Only includes Carriers where one type of insurance has an expiration date within the next 90 days

09. SAMPLE_Load Count by Contact across Status

Load Performance and Costing Shows the count of loads across load status for all load contacts

- Loads with a first pick calculated date greater than or equal to last month's start date

10. SAMPLE_Carrier Metrics across Month and Year Document

Load Performance and Costing Shows load count; various payable and unit of measurement calculations (including averages, Cost Per Mile and Cost Per Pound iterations); on-time percentage for Pick (Appt. vs. Actual),Drop (Appt. vs. Actual), Drop (Plan vs. Actual) and Load Close (Plan vs. Load Close Date); average number of stops; across month and year

- Only includes Completed loads- Only includes loads where the Drop Appointment and Actual are not missing - Filter is based on First Pick Calculated Year (2008 - 2011)

11. SAMPLE_Carrier Contact Listing Carrier Contacts Shows all contacts per carrier - N/A

12. SAMPLE_Load Note Summary Load Notes Shows a count of loads by Sender Company, Sender and Load Note Type

- N/A


CREATING AND USING USER-DEFINED FIELDS


Types of User-Defined Fields

• Define– Applied to each row of data as it is retrieved from

the database

• Compute– Applied to the aggregate of data after the data

has been retrieved


Simple Define Field Examples

• Date Defines– Extract Date Component

Year: YY, Month Name: Mt, Quarter: Q

• Alpha Defines– String Concatenation

• Numeric Defines– Calculations


COMPUTE ExamplesPurpose Data Type/Format Returned Definition

Calculate an on-time percentage, based upon the aggregated value of IsLate.

D12.2% (Decimal of length 12, with 2 places after the decimal. Append a % at the end of the number)

100 – ( SUM.IsLate / CNT.LPC_SHIPPER_LOAD_NBR ) * 100

Calculate Cost per Pound D12.2M (Decimal of length 12, with 2 places after the decimal. Append a monetary symbol at the end of the number)

SUM.LPC_PAYABLE_TOTAL/SUM.LPC_WEIGHT

Calculate Cost per Piece D12.2M SUM.LPC_PAYABLE_TOTAL/SUM.LPC_PIECES

Calculate Cost per Hundredweight D12.2M SUM.LPC_PAYABLE_TOTAL/(SUM.LPC_WEIGHT/100)

Calculate Accessorial CPM (no fuel) D12.2M (SUM.LPC_TOTAL_ACCESSORIAL –SUM.LPC_TOTAL_FUEL_SURCHARGE)/SUM.LPC_MILEAGE

Calculate Fuel CPM D12.2M SUM.LPC_TOTAL_FUEL_SURCHARGE/SUM.LPC_MILEAGE


Flagging Records

• Using a DEFINE, you can create a field that returns a value, based upon a condition

• This flag can then be aggregated using a COMPUTE


Customer Examples

• Determine lead time buckets

• Carrier On-Time Performance– By various categories

• Report Card– Metrics by Mode

• Aggregate Shipment Legs to Single Row

• General data extracts

• Comparing DM Dates to Relative Dates– Today, Yesterday, etc– DATEDIF(HDATE(SL_ORDER_CREATE_DATE,'YYMD‘),

DATECVT(&&DTE_TODAY, 'I8YYMD', 'MDYY'), 'D')


Determine Lead Time Buckets

• Use DATEDIF to calculate difference between dates – days, weekdays, weeks, months, years

• Date/Time formats must be converted to Smart Dates via HDATE

• Example:PPD/YYMD = HDATE(SL_PLANNED_PICK_DATE,’YYMD’)OCD/YYMD = HDATE(SL_ORDER_CREATE_DATE,’YYMD’OLT_DAYS = DATEDIF(PPD,OCD,’WD’)


Carrier On-Time Performance

• Define– Identify which individual loads would be considered late– IsLate/I1 = IF HDIFF ( LPC_FIRST_PICK_ACTUAL_DATE ,

LPC_FIRST_PICK_APPT_START_DATE , 'MINUTE' , 'D6' ) GT 30 THEN 1 ELSE 0

• Compute• Once all loads in the data set have been flagged by the Define, then

calculate the percentage of on-time for the set– OTP/D12.2% = 100 – ( SUM.IsLate /

CNT.LPC_SHIPPER_LOAD_NBR ) * 100

• Aggregate to different levels via BY


Aggregating Shipment Legs

• Using the LAST directive in a define/compute to create each virtual field

• Dataset needs to be in the appropriate order – i.e. sorted by shipment_id

LAST_LEG_DROP_ARRIVAL/HMDYYI=IF SL_SHIPMENT_ID EQ LAST SL_SHIPMENT_ID AND SL_LEG_TYPE EQ 'L' THEN SL_LD_DROP_ACTUAL_DATE


USER EXERCISES 1


Hands On Reports - Requirements

• Load Detail Report– Output a list of loads, sorted by load id, that were

tendered in a given timeframe, and filtered by mode

• Lane Summary Report– Summarize the number of loads, average

weight, average miles, average total cost, by mode for a given timeframe.


Creating a Load Detail Report

• Sort Fields:– Load ID

• Data Fields:– Load ID, Origin Name, Dest Name, – Carrier, Weight, Payable Total

• Filters:– Tender Date/Time (start/end)– Mode

• Output Format: Excel


Load Detail - Output


Load Detail - InfoAssist


Creating a Lane Summary Report

• Sort Fields:– By: Lane (Origin City/State to Dest City/State)– By: Carrier Name

• Data Fields:– Load Count– Average Wt– Average Cost

• Filters– First Pick Date/Time(start/end)

• Output Format: Excel


Lane Summary - Output


Lane Summary - InfoAssist


REPORT FORMATTING & OUTPUT


Report Styling

Themes, Styles and Banding

Font Style and Colors

Data Bars


Header/Footer Options

• Types– Report Header/Footer– Page Header/Footer– Sub-Header/Footer

• Content Options– Static Text– Field Values “<FIELDNAME”– Variables “&VARIABLE”


Report Navigation

• Table of Contents• Freeze• Pages on Demand


Report Features

• Accordion• Stacked Measures• Repeating Sort Values


Report Layout Options

• Margins• Orientation• Page Size• Measurement Units

• Page Numbers• Cell Padding/Spacing• Autofit Column


Report Output Formats

• HTML– Typically used for reports within the portal

• PDF• Excel – multiple options

– 2000– 2007– Formulas– Pivot (requires PRINT output, rather than SUM)


TIPS AND TECHNIQUES


Using Relative Date Values

• Global values – available to any report• Useful when report is run repeatedly for a standard time

frame (e.g. last month)• Dates are calculated relative to the day the report is

executed• Used with Advanced Filters• Can mix multiple in the same report – e.g. show all loads

from the start of the current year, through yesterday

Examples: TODAY, TOMORROW, YESTERDAY, LAST_MONTH_START, THIS_YEAR_START, THIS QUARTER_END


Reporting Across Date Ranges

1. Show by Week # (1 – 52):

Calculation: WEEK_NUMBER/A2=HNAME(<Date Field>,’WEEK’,'A2′)

2. Show by Week End Date:

Calculation:WEEK_ENDING/MDYY=DATEMOV(HDATE(<Date Field>,’YYMD’),’EOW’)


Using Drill Downs

1. Create Detail Report– Need to match filters– Include parameters that will be passed from Summary

2. Create Summary Report

3. Insert Links– Identify the detail report– Associate summary report fields with detail report parameters

Note: Drilldowns will not work for emailed reports (due to report security)


Working with Subtotals and Totals

• Simple sum totals (row and column) available via ribbon buttons

• Non sum-based totals must be calculated based upon a compute to have the values flow through to the total line

• All fields will be included in a total line


USER EXERCISES 2


Hands On Reports - Requirements

• RG Non-Compliance Graph– Display bar chart of impact of RG non-

compliance for top 10 carriers (by volume)

• Trending Metric Report– Summarize 5 key statistics by carrier, month

over month


Creating a Trending Metric Report/Report Card

• View 5 key statistics by carrier, month over month:– Number of Loads– Average Weight– Average Mileage– Average Total Cost– Average Fuel Cost– Average Other Accessorials (requires define)– On-Time Performance (requires define and compute)

• Sort: By Carrier Name, Across Month/Year

• Filters:– Pick Date/Time Range– Completed Loads Only

• Formatting:– Stack Measures

• Output: HTML


Trending Metric Report - Output


Trending Metric Report - InfoAssist


Creating a RG Non-Compliance Report

• Defines:– RGCarrier1Rate = FIRST_CARRIER_BASE_RATE + FIRST_CARRIER_ACCESSORIAL_RATE– ComplianceCost=RGCarrier1Rate – TENDER_TOTAL_RATE

• Sort Fields:– By: Tender Carrier

• Data Fields:– ComplianceCost

• Filters– Tender Date/Time (start/end)– Tender Total Rate > 0– Compliance Cost != 0

• Conditional Formats:– Compliance Cost > 0 - Green– Compliance Cost < 0 - Red

• Output Format: PDF


RG Non-Compliance - Output


RG Non-Compliance Report - InfoAssist


USING REPORTCASTER TO AUTOMATE REPORTS


ReportCaster Overview

Schedule• Day • Week• Month• Others



Task• Report Parameters

– Relative Dates– Multi-select

• Output Format– Can vary from what is

defined in the report– Graphs must be

PNG/JPG/GIF format

• Bursting



Distribution• Email

• Distribution List– Defined within the

ReportCaster Tab

• Define Subject/Message

• Can Zip Report Attachments


Bursting Reports

• Report Requirements– Text Report: Burst will occur via the first BY field– Graph Report: Burst will occur via the second BY

field

• Creating a Distribution List– Burst values are case-sensitive– Can use wildcard characters

• [wildcard:]abc*– Default distribution

• [elsesend:]


ASSISTED REPORT DEVELOPMENT


Rollout Assistance

• Rollout assistance is provided for those customers beginning the transition from the legacy ad-hoc tool to InfoAssist.

• For a two week period following this class, you will have access to a rollout resource to assist in answering questions that you may have.

• After that period, you will be transitioned into our standard customer support process.

report creator training workshopcontent.leanlogistics.com/bi/docs/report creator workshop.pdf ·...

Documents