a taste of what’s cooking at us foods business analytics...

A Taste of What’s Cooking at US Foods

Business AnalyticsRunning Reports – June 2013Version 1

1FOOD. FOOD PEOPLE. EASY.

Navigation - Welcome PageNavigate to reports by clicking on the report group or dashboard


The body of the Welcome Page includes announcements and important updates about the reporting system.

Navigation - Welcome Page


All current training documents will also be posted here for you to view or download.

Navigation - Welcome Page


Filters – Report filters allow the user to limit or filter the data returned. For users with more than one customer number attached to their user ID, they can use the filters to limit the report to a specific customer or group of customers. If no filters are selected, all of the data will be returned.

Sticky Filters – Report filters that “stick” when you move from one report to another report within a specific Dashboard report group.

New terminology


New terminology

Default Reports – The report that is automatically generated when a Dashboard is clicked.

One Click Reporting – The ability to run reports with one click. One click reporting assumes that the report will run for all customer numbers attached to the user ID and for the default date range. A list of these default values are defined later in the presentation.

Breadcrumbs – A list of parameters at the bottom of each report that details the filters and time frame used to run the report.Breadcrumbs are included when reports are printed.


RUNNING reports


Navigate to Business Analytics

1. Sign on to USFood.com and click on the Reporting tab.

2. Click on the Business Analytics hyperlink.

2

1

http://www.usfood.com/jsp/USFHeader.jsp






http://reporting.usfood.com/usfdcr/filesfolders/MerlinLogon.aspx


Select the Dashboard

3. Click on the desired report group from the Dashboard.

3


Running Reports

4. Once the Dashboard is selected, the system will begin to pull the data for the selected Dashboard’s default report. In this example the Purchase Detail Descending Dollar report is the default report.

5. Since there are no filters selected, the report will return data for all customer numbers attached to the user ID.

6. The date range is automatically filled in depending upon the report. For Product Usage reporting, the default date is the Last Closed Week.

7. You’ll know the report is running when you see the clock with the hour hand spinning.

4

65

7


Use the Filters to select data Filters allow the users to select or limit the data that is brought back

based on:A. Date rangeB. Customer AttributesC. Product Attributes

If no filters are selected all of the data will be brought back for all of the customer numbers attached to the user ID.

A B

C


Use Column Selectors to customize the report Once reports are run, use column selectors to add additional reporting

data fields:A. Time Attributes – Turn on Calendar Year Month Name for all dataB. Customer Attributes – Turn on Multi Unit, Division, or Customer

Name (Number)C. Product Attributes – Class, Category, Group, Brand Type,

Proprietary Product, Catch Weight Indicator, or Product Description.

A B C


Dashboard Report

Compliance(only available if contract product flagging is turned on in USFood.com)

Contract Product Utilization – SummaryContract Product Utilization – Detail SortableContract Product Utilization – Detail Subtotal

Data Download Invoice Data Download Report

Invoice History Invoice History

Monthly Trend Purchase Summary (default summary by Customer)Purchase DetailContract Product SummaryContract Product Detail

Monthly Trend YOY Product DetailProduct Summary (default summary by Customer)

Product Usage Purchase Detail Descending DollarPurchase Detail Subtotal (default by Class)Purchase Summary (default summary by Customer)

Available Reports


Report Details


Data Download• Gives the user access to all the data elements for all purchases

made for the last two years plus the current year.• This is a great report for those who need line item detail without

any subtotals• Use this report to:

–Track invoice level information about products purchased on a specific date or by a specific account

–Track rebates for customers as it contains all elements required by manufacturers (customer, invoice, date, cases, etc.)

• To select the most recent information, enter the desired date range using the Calendar Date fields. Make sure that “Time Frame” and “Start and End Month”values are cleared andshow as “No Selection”.


Data Download Report

Invoice level data with all data elements


Invoice History and Detail

• The Invoice History Report provides the Invoice information for a requested date range.

• The summary page lists all invoices, credits and debits. • Clicking the invoice number brings up the line item detail.


Invoice History and Detail

Clicking on Transaction Type drills to Invoice

Detail


Descending Dollar Purchase Detail

• Quickly pulls descending dollar report for the date range and customers selected.

• The report can be easily resorted by cases or total weight shipped.• Use the class, category or group filters to run more specific

descending dollar reports such as beef, dry grocery, etc.


Purchase Detail Descending Dollar


Purchase Detail Subtotal

• This usage report allows the user to subtotal the report by:– Multi Unit Code– Division– Customer Name– Product Hierarchy– Brand Type– Proprietary Product– Catch Weight Indicator

• Optional time, customer and product attributes can be turned on or off


Select attribute to subtotal report by.

Purchase Detail Subtotal


Purchase Summary

• The Purchase Summary report allows the user to select the data element they wish to summarize by:

– Multi Unit Code– Division– Customer Name– Product Hierarchy– Brand Type– Proprietary Product– Catch Weight Indicator



Purchase Summary

Select attribute to summarize report by.


Purchase Detail Trend

• The Purchase Detail Trend report allows the user to select the data element they wish to see line item detail by:




Purchase Detail Trend Select data element to trend

Data elements are broken out by month


Purchase Summary Trend

• The Purchase Summary Trend report allows the user to select the data element they wish to summarize by:




Purchase Summary Trend Select data element to summarize by

Data elements are broken out by month


Contract Compliance

• Available only if contract product flagging is turned on in USFood.com.

• Compliance reports show the purchases that were made by product class or line item detail and how much of the purchases were made on and off contract.

• These reports are helpful in identifying customers, product class and products where contract participation needs to be reviewed.


Clicking on Division drills to product detail

Contract Product Utilization - Summary


Column selectors allow you to add additional attributes to the displayed report.

Contract Product Utilization - Detail



Grouped by Product Class and subtotaled by Contracted and Non-Contracted Purchases

The report subtotals by Class and

Contracted / Not Contracted

The report subtotals by Class and

Contracted / Not Contracted



Grouped by Product Class and subtotaled by Contracted and Non-Contracted Purchases

Click on Subtotal Attributes to change the field to subtotal

on


Contract Product Utilization Trend – Summary and Detail

Trend reports break the data out by month allowing you to easily

analyze data

Select the data set by clicking on the drop down


Dashboard Graph

Charts and Graphs

Quarterly TrendsMonth – Year over YearProduct Class Summary – Point in TimeContracted Product by Class – SummaryTop 25 Contracted and Non-contracted by dollar and case

Available Charts and Graphs


Total purchase dollars, cases and Private Label by quarter with average case price

Total Purchase Dollars

Total Cases

Total Private Label Purchase Dollars


Total purchase dollars, cases and Private Label by month – year over year with average case price

Total Cases




Total purchase dollars, cases and Private Label by class for selected point in time

Total Cases




Click on class

column… …and drill to product level

detail.


Report NavigationRight-Clicking on the data in a report now gives you more navigation and exploration options

Different and New icons appear when you put your mouse pointer over different parts of your report

•The new navigation features are:• On-Demand column sorting• Moving columns within a report• Keeping/Removing columns• Hiding columns• Including/Excluding columns• Showing/Hiding subtotals and grand totals• Creating custom groups of columns• Creating Calculations


Navigation – Checking the Default Sorting

Look for the caret following the column header to determine the default or original sort order for each report.

In this example, the Category column is sorted in ascending order by default, because the caret is dark, when you open the report.

41


While each report has a default sort sequence, the report can be resorted by:

1. Right clicking on the column header2. Selecting “Sort Column”3. Then selecting the desired sorting method:

a) Sort Ascendingb) Sort Descendingc) Add Ascending Sortd) Add Descending Sort

Navigation – On-Demand Sorting

42

12

6

To remove ALL custom sorting on the report, and return back to the default sort:

4. Right clicking on the column header5. Select “Sort Column”6. Select “Clear All Sorts in View”

12


The sorting methods are described as follows:

• Sort Ascending — Sorts the values in the column by ascending order, alphabetically A through Z, numbers sort lowest to highest, and dates sort earliest to latest.

• Sort Descending — Sorts the values in the column by descending order.

• Add Ascending Sort — Specifies that an ascending sort for this column is added as another sort for the analysis. In the Selected Columns pane, when you select this option, a Sort button with a number such as 2 or 3 is displayed to the left of the column name in the Selected Columns pane.

• Add Descending Sort — Specifies that a descending sort for this column is added as another sort for the analysis. In the Selected Columns pane, when you select this option, a Sort button with a number such as 2 or 3 is displayed to the left of the column name in the Selected Columns pane.

• Clear All Sorts in View — Removes all sort specifications that you have made.

Navigation – On-Demand Sorting Terminology

43


Navigation – Moving Columns

44

1. Hover the mouse cursor over the column header and watch for the cursor to change.

2. Click on the column header, and drag the column to the desired new location on the report

a) Acceptable locations to move a column to are indicated by a solid blue bar on the

3. Release the mouse button

Almost all columns can be moved to a different location within the report. To move a column:

Original View of Report

1

2

Final View of Report


Navigation – Moving Columns into Sections

45

A column can be moved into a “Section”.

Each value in the column will become a separate section of data on the report. In this example, there are 2 Divisions in the data Denver and Western PA – Altoona. When the “Division” column is moved to a “Section”, two sections appear, one for each Division.

Section

Section


Navigation – Moving a Column to create a Prompt

46

A column can be moved into a “Table Prompt”.

This column will now be a prompt on the report. The values in the prompt are the values that were in the column on the report. In this example, there are 2 Divisions in the data. When the “Division” column is moved to a “Table Prompt”, the prompt appears with the 2 Divisions as values. You can now filter the report on this prompt, as would be done with all other report prompts.


Navigation – Keeping / Removing Columns

47

A powerful new feature allows you to customize your report by keeping or removing data, based on your own definition. The following slides demonstrate this feature.

Keep or Remove are new options, when you right-click on a column name or the data.

To Keep Customers with Net Wght Shipped over $4000:

1. Right click on the “Customer Name (Number)” column header

2. Hover the mouse over “Keep Only”. Since it has a carat after it, another menu will expand.

3. Select “Members where Net Wght Shipped is greater than” then select “A Value…” (this is the criteria by which you want to keep the Customers).

4. Enter “4000” into the value field. Click “OK”5. The page will refresh, and you will see only

the Customers with a Net Wght Shipped over $4000.

1

3

5


Navigation – Keeping / Removing Columns (cont’d)

48

6. To Clear the customized view of the data, click on the “Clear” button at the top of the screen.

** Note – Clicking “Clear” will also remove all customized report filters **

To Remove data, use the same steps, only choose “Remove” (instead of “Keep Only”)

For example: If you have just one specific item that you want to Keep or Remove from the report. Here are the steps:

Remove the “Scottsbluff” Customer:

1. Right click on the customer’s name.2. Select “Remove” 3. The page will refresh, and the

“Scottsbluff” customer is no longer on the report.

6

2

3


Navigation – Hiding a Column

49

Hiding a column simply removes it from the view of the report on the screen. It does not remove the column from the data behind the scenes.

Hiding a column can be useful if you want to customize a report with many columns, to a smaller set of columns that are specific to your needs

1. Right clicking on the column to hide. Select “Hide”.2. The page will refresh, and the column is no longer visible.3. To Clear the customized view of the data, and expose the hidden column, click on the

“Clear” button at the top of the screen.

3

** Hidden columns will NOT appear when you download the data to Excel **** Hidden columns WILL appear when you download the data to CSV **


Navigation – Including / Excluding Columns

50

Placeholder for:

Including / Excluding Columns


2. Hover the mouse over “Include”….


Navigation – Showing / Hiding subtotals and grand totals

51

Placeholder for:

Showing / Hiding subtotals and grand totals




Navigation – Creating custom groups of columns

52

Placeholder for:

Creating custom groups of columns




Navigation – Creating Calculations

53

Placeholder for:

Creating Calculations




Navigation - Report Breadcrumbs

At the bottom of the report, is an area called the bread crumbs. The bread crumbs displays the filters that generated the report.

In this example, there were no filters and the date range was January 2011 to February 2011.

When you print the report, the breadcrumbs will also print so you’ll know what selections generated the report.

54


Refreshing, Printing and Downloading the Report

Also at the bottom of the report are hyperlinks that allow you to….

55

Download the report to Excel, PowerPoint or as a pure data file (CSV)

Print the report in a PDF or HTML format

Refresh the report after changing the filers.


Paging through long reports

First page Previous page Next page Maximum Rows per page (5000)

Reports that return more than 50 lines, such as the Contract Product Utilization Report, will display page navigation buttons just above the breadcrumb area.

To retrieve more data, click on the “Maximum Rows” icon. The report page will now show up to 5000 rows.If the report has more than 5000 rows, click on “Next Page” to see those rows.

To print all pages in a report, make sure to first click on the “Maximum Rows” button.


Report Filters


Report Filters

• Filters allow the users to limit the data that is brought back based on Dates, Customer Names, Customer Numbers, Divisions, Product Numbers, Product Names, customer names, customer numbers or divisions

• Users who want to change the default date range, can do so through the “Time Frame”, “Month” or “Date” filters.

• When running the Product Usage by Manufacturer report, the filter function can be used to select or filter on specific Manufacturers

• Because the filter function is so powerful, it might seem complex at first

• After using it a few times, it’s functionality will become second nature


1. Click on the down arrow button to open up the filter.

2. A menu will appear displaying many of the values available for that prompt.

3. To search for values or more easily select a large number of values, click on the “Search…” text.A. The resulting box enables

searching for values, selection of multiple values at a time.

B. The prompt will only display 256 values at one time. In order to view additional values, click the “More…” button.

1

Report FiltersReport Filters

2

3

A

B


4. To move an account into the selection box, double click on it.

5. It will move to the “Selected” box on the right OR…

6. After highlighting the selection, click on the “Move” arrow to move it to the “Selected” box.

Report Filters, cont’dReport Filters


7. Clicking on the “Move All” arrow will move all accounts listed in the left box to the “Selected” box on the right.

Report Filters, cont’dReport Filters


Report Filters, cont’d

8. To remove an account from the selection box, double click on it. It will return to the box on the left OR…

9. After highlighting the selection, click on the “Remove” arrow to move it back to the selection box.

10. Clicking on the “Remove All” arrow will move all accounts listed in the selection box back to the box on the left.



Values can be edited or pasted into the selection box from a spreadsheet or file.

11. To manually edit or enter values, click on the “Edit” pencil icon. The “Edit” box opens.

12. Edit the values, type in new values, OR copy and paste a list of values into the “Edit” box.

13. Click “OK” when finished.

11

12

11



14. When all selections are complete, click on “OK” to save the selections and return to the main screen.

14



15

15. To generate the report with the new filters, click on “Go”.

16. You’ll know the report is running when you see the clock with the hour hand spinning.

16


Filtered Report

At the bottom of the report, you’ll see the filters that generated the report.

In this example, the report is filtered by 13 customers and the new date range filter is “Last Closed 6 Months”.

When you print the report, the breadcrumbs will also print so you’ll know what selections generated the report.

66


Clearing Filters

To clear selected filters, simply click on “Clear”.

All filters will be removed, and the Date prompt will be reset to the default value.

67


Sticky Filters


Sticky FiltersOnce filters are selected and a report is run using them, the filters

will “stick” when moving between reports within the report group (dashboard).

Report Groups (Dashboards)•Compliance

•Contract Product Utilization – Summary•Contract Product Utilization – Detail Sortable•Contract Product Utilization – Detail Subtotal

•Data Download•Detail Data Download

•Invoice History

•Monthly Trend•Purchase Summary•Purchase Detail•Contract Product Summary•Contract Product Detail

•Monthly Trend YoY•Product Detail•Product Summary

•Product Usage•Purchase Detail Descending Dollar•Purchase Detail Subtotal•Purchase Summary

•Product Usage Trend


Saving Customizations (Filters)


Saving Customizations (Filters)

To save filters that have been used to run a report:1. Select the filters and run the report2. After the report is generated, click on the Page Options icon:3. Click “Save Current Customization…”4. Give the saved customization a meaningful name5. Click ‘OK’ to save

Once filters are selected and a report is run using them, the filters can be saved and named.

4

5

2

3


Applying and Clearing Saved Customizations (Filters)

23

1

To access your saved filters and apply them to a report:

1.Click on the “Page Options” icon: 2.Click “Apply Saved Customization”3.Click on your saved customization to open it4.The report will regenerate with the saved selections

Click “Clear My Customization”

to clear the saved filters

from the report and reset all filters to the

defaults


Modifying Saved Customizations (Filters)

2

3

To modify the filters in your saved customization, follow the same steps to create a new customization, and save with the same name as the old customization:

1.Select the *new* filters to be applied to the report, and run the report2.After the report is generated, click on the Page Options icon.3.Click “Save Current Customization…”4.Give the saved customization the same name – or give it a new name.5.Click ‘OK’ to save


Editing Properties of Saved Customizations (Filters)

1

2

Click here to type a new name

Click this icon to delete

To rename or permanently delete your saved customizations:1.Click on the “Page Options” icon.2.Click “Edit Saved Customizations…”3.Select the customization to rename or delete it

FOOD. FOOD PEOPLE. EASY.

Business Analytics – Enhancement Detail

Questions??•Call the Service Desk for support on the enhancements at 888-648-2580

75

a taste of what’s cooking at us foods business analytics...

Documents