Webinar - Your Most-Requested Reports: Step-by-Step

Additional Notes

• Jason Swan led a session on building reports in Order Time, demonstrating the creation of an item ledger report for tracking inventory, an item sales pivot report for forecasting, and an item vendor report for bulk cost updates. The session covered key concepts such as table order, join verification, column configuration, and the use of calculated fields, with Michael DeFelice facilitating a Q&A session where various report-building scenarios were addressed by Jason Swan and Ian Benoliel, including reports for "where used" assemblies, reorder points, sales of kits, and raw material needs estimation with input from viewers.
• Jason Notes
  • Item Ledger - This report is a report every one should have in their file. Anytime you have questions on why a certain quantity or value is appearing this is the report we as a team use.
  • Item Sales Pivot - This allows you to see the qty sold (or dollar amount) in a set time period. In our example we will look at by month. You are also able to add the previous year data to help gather data for the purpose of forecasting.
  • Item Vendor - This is a report that is not brought up that much but it is one that I have helped customers which almost weekly. Here we are able to grab the Item Vendor details with the Unique ID to make updating these records much easier.
• Gemini Notes

• Introduction to Reporting Jason Swan welcomed attendees to a session focused on building commonly requested reports in Order Time. The session aimed to explain the report building process, particularly the order of operations when adding tables. At the end of the session, a Q&A was planned to discuss the overall structure of reporting.

• Item Ledger Report Overview Jason Swan introduced the item ledger report as essential for all customers to track inventory quantity and value. This report is the first point of reference for the support team when investigating quantity or value discrepancies. Jason Swan recommended saving this report filtered by individual items for easy access.

• Building the Item Ledger Report - Grouping and Type To build the item ledger report, Jason Swan navigated to Reports and selected to build a new one from scratch. They chose the "movement with bends" grouping, which provides a predefined list of relevant tables for tracking inventory movement, including transfers and adjustments. Jason Swan emphasized setting the report type to "ledger" before adding any tables.

• Adding Tables to the Item Ledger Report Jason Swan explained the importance of adding tables in a specific order, starting from the top, due to their priority and interdependencies. They added the "docs" table for transaction details, "bins, lot and serial number" for inventory movement specifics, "items" for item names, and tables for customer/vendor, location name, and the "bend" table for precise warehouse locations. Jason Swan highlighted that each added table joins with another, and the join between location and document should be changed to join with the bin and lot serial number for accurate transfer tracking.

• Configuring Columns in the Item Ledger Report In the Columns tab, Jason Swan added relevant fields, processing them one table at a time and checking the menu options at the bottom for ledger-specific settings. They added "date" and set its ledger column to "date" for chronological ordering, "transaction number," "posting date," and "transaction type," leaving their ledger columns as "data". For the "bins and lotware serial number" table, Jason Swan added "movement quantity" and "value amount," setting both to "running total" to track inventory and value changes over transactions.

• Setting Up Groupings and Filters in the Item Ledger Report Jason Swan added "name" from the "items" table and set its ledger column to "grouping" to organize information by item. They then added "customer or vendor name" as a regular data field and "location" as a second grouping, followed by "name" from the "bin" table as a third grouping. To avoid excessive data, Jason Swan recommended filtering the report by a specific item using the "item ID" from the "bins and lot or serial number" table, which allows for typing and Order Time's auto-suggest feature.

• Viewing and Customizing the Item Ledger Report Jason Swan advised viewing the report before saving to allow for easy corrections. Upon viewing the initial report, they noted that the repeated "name" fields for location and bin were confusing. Jason Swan then demonstrated how to customize the report by changing the captions of these columns to "item," "location," and "Ben" for clarity. The viewed report showed the item name, location, bin, date, transaction details, quantity changes, and value adjustments.

• Saving and Using the Item Ledger Report Jason Swan showed how to save the report using the "save as" button, recommending the use of all caps for custom report names to make them stand out. They explained that filters can be added or modified after saving to view specific date ranges or different items without needing to save a new report. The saved report can be found in the Inventory Movement with Bends section under All Reports.

• Item Sales Pivot Report for Forecasting Jason Swan moved on to the item sales pivot report, useful for analyzing historical sales data for forecasting. For this report, they started a new report and, because it focuses on actual sales, selected the "ship docs" grouping. Before adding tables, Jason Swan changed the report type to "pivot".

• Building the Item Sales Pivot Report Jason Swan added the "ship doc" table for dates and the "ship doc and customer return line items" table for quantity information. They also added the "items" table for item names, ensuring the join was correctly set between the item name and the ship doc line items. In the Columns tab, Jason Swan added the "date" field, setting the date interval to "monthly" to create pivot columns for each month. They added "item name" as a row and "quantity" from the line item information, setting it to "sum" to get total quantities.

• Filtering and Viewing the Sales Pivot Report To make the pivot report easier to read, Jason Swan filtered it for a smaller set of items using "item name" and for the current year using the "date" filter. They corrected a mistake by setting the "quantity" field as the "data" to be displayed in the pivot table. The viewed report showed the selected items and their sales quantities for each month of the current year. Jason Swan then added a filter on the "ship doc" table for "posting date" set to "this year" to only show closed transactions.

• Enhancements to the Sales Pivot Report Jason Swan demonstrated adding the prior year's data to the pivot report by selecting the "show prior year" checkbox for the "quantity" column. This also allowed showing the quantity change and percentage change. However, they cautioned that adding these options could clutter the report.

• Item Vendor Report for Bulk Cost Updates Jason Swan addressed a common question about updating item costs in bulk based on a vendor price list. They explained that an item vendor report can provide the necessary item and vendor combination details. To build this, Jason Swan started a new report under the "items" grouping, keeping the type as "tabular". They added the "items," "item vendors," and "vendors" tables, emphasizing the importance of checking and adjusting the joins, especially for the "vendor" table, to ensure it joins with "item vendors" to avoid repeating the default vendor.

• Configuring Columns and Viewing the Item Vendor Report In the Columns tab, Jason Swan added "item name," "name" from the "vendors" table, and "ID," "part number," and "cost" from the "item vendors" table. They moved the "ID" to the top for easier export/import processes and changed the captions of the "name" columns to "item" and "vendor" for clarity. Upon viewing, Jason Swan noted that items with multiple vendors would repeat.

• Grouping and Exporting the Item Vendor Report To improve readability, Jason Swan customized the report by making "item name" a section, which grouped all vendors under their respective items. They highlighted the "ID" from the "item vendors" table as the unique identifier for importing updates. Jason Swan explained how to save this report as "item vendors".

• Review of Key Reporting Concepts and Introduction of Calculated Fields Jason Swan summarized the importance of table order, join verification, column menu checks, and using captions and sections when building reports. They then introduced the new "calculated fields" feature, accessible via company preferences under advanced, which allows for creating custom calculations based on report fields. Jason Swan demonstrated how to enable this feature.

• Demonstrating Calculated Fields for Average Inventory Value Jason Swan illustrated the use of calculated fields by trying to calculate the standard cost value and average value in the inventory valuation report. They added "standard cost" to the columns, created a "standard cost value" field by multiplying quantity and standard cost, and attempted to create an "average value" field by dividing value amount by quantity. Despite some beta-related calculation display issues, Jason Swan showed that the calculations were performed correctly in the viewed report. They encouraged users to report any errors encountered with the calculated fields, providing details about the formula and report type.

• Q&A - Database Schema and Report Examples Michael DeFelice opened the Q&A session. In response to a question about a database schema diagram, Jason Swan explained that due to the complexity and numerous IDs, a comprehensive diagram is not feasible. They recommended contacting support for help with specific report needs. Ian Benoliel mentioned that the API documentation contains information about tables and columns.

• Q&A - "Where Used" Report for Assemblies Doug H asked about a report showing where an item is used in assemblies. Jason Swan demonstrated building this "where used" report by starting a new report under the "bills and materials" grouping. They added the "bomb," "steps," "components," and the "item" table twice (once joined to the BOM for the finished good, and a second time as "item-2" joined to components for the component item). Jason Swan configured the report with "name" from "item-2" as a section (the component) and "name" from "item" indented underneath (the finished good), along with the quantity needed. After filtering for a specific component ID, the report showed all finished goods using that component and the required quantity.

• Q&A - Exporting/Importing Report Definitions Doug H inquired about exporting or importing report structures. Jason Swan explained that there isn't a direct export/import function for the report structure itself. Instead, they suggested taking screenshots of the Columns and Filters tabs to share the report's configuration. Doug H confirmed this level of detail was sufficient.

• Q&A - List of Items with Configured Units of Measure Doug H asked for a report listing items and their configured units of measure (purchased as, sold as, used as). Jason Swan attempted to build this report using the "items" and "unit of measure set" tables. They found that while the unit of measure set name and primary unit of measure could be retrieved, the specific "purchased as," "sold as," and "used as" configurations were not available in reporting. Jason Swan suggested checking the item details and unit of measure set profile directly.

• Q&A - Reorder Points Report A question was raised about reorder points. Jason Swan showed how to create a report with reorder points by location, using the "items" table and the "reorder point by location" table, joined with the "location" table. The report included item name, location name, max quantity, reorder amount, and reorder point, filtered for specific items.

• Q&A - Sales Report for Kits A user asked about a sales report showing the number of kits sold, rather than just the components. Ian Benoliel clarified that standard reports typically show either kits or components, not combined. He mentioned the possibility of a custom data source for this specific need. Jason Swan attempted to modify a ship docs report to show kits sold. After some investigation with Ian Benoliel, they found that using the "sales order line item kit" table and filtering for kit-related fields in a summary report type could display the sold kits. Jason Swan also demonstrated switching back to tabular view to see individual kit transactions. They emphasized setting the report type at the beginning of the building process.

Q&A - Raw Material Needs Estimation Carol asked for a report to estimate raw material needs for a given finished good item without creating a fake work order. Jason Swan explored using calculated fields in a bill of materials report to achieve this. They created a "total components" calculated field by multiplying the component quantity per assembly by a user-defined value (representing the desired number of finished goods). Jason Swan also attempted to calculate the total component cost by multiplying the component's standard cost by the same user-defined value. Due to the beta status of calculated fields, there were some initial issues with the formula referencing the correct tables. Jason Swan and Ian Benoliel agreed to investigate further based on a support ticket.