Figure 12-1

This chapter will familiarize you with the many reports that MicroBiz provides, and how best to use them to your advantage. 

Reports Overview

When generating most reports you will be presented with the "Preview" window.  Within this preview window you will find various controls to print your report or export the report to another program (see Figure 12-2). This feature allows you to specify the way that you view and/or save the report. It contains the following options:

  • Printer Icon –Choosing this option will send the report directly to the Report Printer, as specified under Management | Custom System Settings | Hardware and Printer Settings | Printers Tab.
    • If the report to be printed is more than one page long, you will have the option to print all pages or a page range of your choosing.
  • Print to File –You can choose to print your report to a Word, PDF, or EXCEL file.
    • The filename is specified in the print dialog window.
    • Your report will be saved in the local downloads folder.
  • Binoculars – Allows you to search within the report for a particular value.
  • Page Advance/Return – This function advances you to the next page or returns you to the previous page.  You can also advance to the last page or the first page.  These functions only work once the report is completely drawn to your screen.
  • Exit – This option closes the preview of the report.


                                                        Figure 12-2

Inventory Reports

The Inventory Reports feature provides you with a number of ways to access information on your inventory. The following is a brief explanation of the options on this window. 

The main actions here are represented by the three buttons in the middle of the screen:

  • Set Filter – This allows you to narrow your search results.
    • You can filter by a range of SKUs, Styles, Departments and Vendors.
    • This applies to any of the reports except "Purchases Journal" and "Inventory Audit."
  • Generate Report – Uses the selected settings to create your Inventory Report.
    • Like many reports in MicroBiz, you can choose to print the report, view it on your computer screen, or save it to a file.
  • Exit – Closes the Inventory Reports window.

 

                                                        Figure 6-13

There are several different reports you can view, and each varies by the data shown and the options you can configure for them. First we'll go over some of the options available for each report:

  • Audit Date Range – Allows you to select the Start/End date to track inventory items.
    • Used for the Inventory Audit report only.
  • Use New Format – Allows you to switch between the newer/older style reports.
    • Used for the Product List by SKU report only.
    • See notes on the SKU report for differences between the new/old formats.
  • Include Serial Numbers – Allows you to include/omit individual serial numbers from the reports.
    • Used for all of the reports except for Matrix, Margin/Markup and Purchases Journal.
  • Include Quantity in Audit List – Used for the Physical Count report only.
  • Sort by Bin/SKU – Used for the Physical Count report only.
  • Inventory List Subtitle – Allows you to enter a line of text that will appear on the Physical Count report.
    • Used for the Physical Count report only.

 

Report

Description

SKU 
Lists all inventory items, sorted by SKU.

When "Use New Format" is enabled:

  • Includes SKU, Description, Vendor, Vendor SKU, Bin, Price, Quantity, Cost, Quantity times Cost.
  • Totals the Cost of Goods and Retail Value for all items currently in stock. Also gives these totals for items with a negative quantity. 

    When "Use New Format" is disabled:
  • Includes SKU, Description, Color, Size, Style, Bin, Vendor, Normal Stock, Quantity Committed, Quantity On Hand, Cost, Price.
  • Totals the Number of Units, Cost of Goods and Retail Value for all items currently in stock. Also gives these totals for items with negative quantity.

Department 
Lists all inventory items, sorted by Department.

  • Includes SKU, Description, Color, Size, Style, Bin, Vendor, Normal Stock, Quantity Committed, Quantity On Hand, Cost, Vendor SKU, Price.
  • Totals the Cost of Goods and Retail Value for each Department.
  • Gives a grand total for the Number of Units, Cost of Goods and Retail Value on hand. Also gives these totals for items with a negative quantity.

Description 
Lists all inventory items, sorted by Description.

  • Includes SKU, Description, Color, Size, Style, Bin, Vendor, Normal Stock, Committed, Quantity, Cost times Quantity, Vendor SKU, Price times Quantity.
  • Totals the Number of Units, Cost of Goods and Retail value for all items in stock. Also gives these totals for items with a negative quantity.

Style 
Lists all inventory items, sorted by Style.

  • Includes SKU, Description, Color, Size, Department, Bin, Vendor, Normal Stock, Committed, Quantity, Cost, Vendor SKU, Price.
  • Totals the Cost of Goods and Retail Value for each Style.
  • Gives a grand total for the Number of Units, Cost of Goods and Retail Value on hand. Also gives these totals for items with a negative quantity.

Vendor 
Lists all inventory items, sorted by Vendor.

  • Includes SKU, Description, Color, Size, Style, Bin, Department, Normal Stock, Committed, Quantity, Cost, Vendor SKU, Price.
  • Totals the Cost of Goods and Retail Value for each Vendor.
  • Gives a grand total for the Number of Units, Cost of Goods and Retail Value on hand. Also gives these totals for items with a negative quantity.

Matrix 
Lists all Matrix items, sorted by SKU.

  • Includes SKU, Description, Color, Size, Style, Vendor, Price, Cost.
  • For each SKU, lists all Cells (defined as "ROW x COLUMN"), along with the Normal Stock Level, Quantity On Order, Quantity Committed and Quantity On Hand.
  • Totals the Quantity On Hand, Cost and Retail Value for all items in stock.

Margin/Markup 
Lists all inventory items, sorted by SKU.

  • Includes SKU, Description, Cost, Price, Margin, Markup.
  • Reports your Average Margin and Average Markup for all items.

Physical Count 
Lists all inventory items, sorted by SKU.

  • Includes SKU, Description, Bin.
  • Also includes lines for you to write down the quantity on hand for a physical count, along with "Subtitle" and "Initials" fields.

Purchases Journal 
Lists all inventory items ordered by date range, sorted by date.

  • Includes Purchase Order Date, Received Date, Purchase Order Number, Vendor Code/Name, Amount, Tax, Tax Total.
  • Totals the Amounts, Tax and Tax Totals.

Inventory Audit 
Lists all inventory items with activity within a date range, sorted by SKU.

  • Includes SKU, Description, Start Quantity, Purchases, Sales, End Quantity.

 

Product Reports

Product Movement
The Product Movement Report gives you data on each SKU received within a given date range. See Figures 11-x below for a sample report. 



                                                          Figure 12-14

  • All Items/In Stock Options – Choose one of these options for the products in which you want information.
    • All Items Ordered – All items within the date range will be selected.
    • ...Greater Than Normal Level – All items in stock with more than the "Normal Stock" value (found in the Stock tab when editing an inventory item).
    • ...Greater Than Minimum Level – All items in stock with more than the "Reorder Level" value (found in the Stock tab when editing an inventory item).
    • ...Greater Than Average Month – All items in stock with more than the monthly average value (calculated for you).
  • Earliest/Latest P. O. Date – Allows you to set a date range when searching for Purchase Orders.
  • Vendor Code Matches – Allows you to search for a single vendor at a time.
  • Generate Report – Creates the report based on your settings.
  • Exit – Closes the Product Movement Report window.


                                                           Figure 12-15

 

Product Turnover 

This report will show you your inventory levels as they stood for any given date range. 

  • Starting Date – The inventory level for items with this date will be displayed in the "Start" column.
  • Ending Date – The inventory level for items with this date will be calculated based on quantities received, sold and marked down since the "Starting Date" and displayed in the "Ending" column.
  • Include Date Range in Filter – If searching by Filter (department, style, etc.), you can elect to search by date range as well by turning this option on.


                               Figure 12-16

  • Sort By Description – By default, the report will be sorted by SKU. If this option is enabled, the report will be sorted by item descriptions instead.
  • Unit/Dollar Report – Changes the units used in the quantity columns. 
    • If "Unit" is selected, the numbers displayed will represent the number of items, or units.
    • If "Dollar" is selected, the numbers displayed will represent the retail value.
  • Set/Clear Filter – Allows you to create (or clear) filters by which to search.
  • Generate Report – Creates the report based on your settings.
  • Exit – Closes the Product Turnover Report window.

   

                                                          Figure 12-17

 

Assembled/Unassembled

Report listing items that may require assembly, used in conjunction with the Bike Shop module.
 

Inventory Valuation 

Gives cost of goods details for a single SKU.  



                                         Figure 12-18

                                                  Figure 12-19 

Sales Reports

There are many different sales reports available to from Reports | Sales Reports; each with their own methods of gathering and grouping the data. The various reports include:


                                Figure 12-5

  • Employee
  • Department
  • Style
  • Sales Summary
  • Vendor
  • Item
  • Profit Report
  • Sales Journal

The order in which the report samples are presented below has been changed to optimize the image sizes for this guide.

Sales Report Samples

The next section will go over the information available in each report, and give you image samples. Wherever possible, the Detail reports (as opposed to Summary) were used.

Employee Report

  • Lists all sales within the date range specified, grouped by Clerk ID.
  • Includes the following fields: 
    • SKU, Description, Revenue, Cost, Quantity, Clerk ID.
    • Displays subtotal by Clerk ID, and grand total for each of the Revenue, Cost and Quantity fields.


                                                              Figure 12-6 


Department Sales Report

  • Lists all sales within the date range specified, grouped by department.
  • Allows you to filter results by a range of department codes.
  • Includes the following fields: 
    • SKU, Description, Quantity, Revenue, Percent of Revenue, Cost, Margin, Markup, Gross Profit, Department.
    • Displays subtotals by department, and grand total for each of the numeric fields.


                                                               Figure 12-7 

Style Sales Report

  • Lists all sales within the date range specified, grouped by style.
  • Allows you to filter results by a range of style codes.
  • Includes the following fields: 
    • SKU, Description, Quantity, Revenue, Percent of Revenue, Cost, Margin, Markup, Gross Profit.
    • Displays subtotal by style, and grand total for each of the numeric fields.


                                                                      Figure 12-8 

Sales Summary Report

  • Lists sales totals within the date range specified, grouped by date.
  • Includes the following fields: 
    • Date, Site, Sales, Tax, Tax2, Total, Cost of Goods Sold, Office Payouts, Other Payouts, Number of Receipts, Gross Profits, Gross Profit Percentage.
    • Also includes grand totals for these figures, along with invoice counts and invoice averages.


                                                                              Figure 12-9

Item Sales Report

  • Lists sales, grouped by item.
  • Can be generated for a single SKU by date range or for all SKUs by department.
  • Can also be used to create mailing labels by items purchased within a date range.
  • Single SKU report includes the following fields: 
    • Site, Date, Invoice Number, Customer, Description, Price, Quantity, Discount, Totals.
    • Also lists the averages for each of the numeric fields.


                                                                    Figure 12-10


Profit Report

  • Lists all sales by SKU, grouped by month.
  • Allows you to sort by SKU or description.
  • Allows you to group by department, if desired.
  • Includes the following fields: 
    • SKU, Description, Units Sold by Month, Total Units Sold, Sales, Profit, Percent of Margin.
    • Also displays grand totals for Quantity, Sales, Profit, Percent of Margin.


                                                               Figure 12-11 

Sales Journal Report

  • Lists all sales within the date range specified, grouped by invoice number.
  • Includes the following fields: 
    • Date, Invoice Number, Customer, Amount, Tax, Total, How Paid.
    • Also displays grand totals for Amount, Tax, Total.



                                                             Figure 12-12

Vendor Sales Report

  • Lists all sales within the date range specified, grouped by vendor.
  • Allows you to filter results by a range of vendor codes.
  • Includes the following fields: 
    • SKU, Description, Quantity, Revenue, Percent of Revenue, Cost, Margin, Markup, Gross Profit, Vendor.
    • Displays subtotal by vendor, and grand total for each of the numeric fields.

                                                                           Figure 12-13

Cost of Goods Display

Gives you a concise display of the retail value and cost of your current inventory on hand.

                                    Figure 12-20 

Slow/Fast Moving Products


                                                           Figure 12-21

This report will assist you with finding out which products sold the most or least within a given date range, based on many customizable settings.

  • Report Type - Defines the Fast Moving or Slow Moving Report
  • Show the fastest/slowest –Defines the number of items reported on.
    • Choose "% of" and enter a percentage
    • Choose "Number of" and enter the exact number of items you are looking for.
  • Products based on – Allows you to search by quantity or revenue based on sales.
  • Between – Defines the date range for which you will be searching.
  • Include products...not sold – Allows you to include unsold items in your report.
  • Include returns – If disabled, returns will not be considered when calculating sales figures for this report.
  • Filters – Allows you to set filters to restrict search results.
  • Generate Report – Click here to generate the report based on your settings.
  • Cancel – Click here to close the Slow/Fast Moving Products Report window.



                                                        Figure 12-22a 

Sales Graphs

MicroBiz provides you with several graphical reports under this menu.

                                                        Figure 12-23

  • Sales by Department – Compares sales by department for a given date range.
  • Quarter-to-Date Sales – Creates bar graph displaying sales/margins by department.
  • Sales/Profits by Employee – Compares sales/profits by employee for a given date range.
  • Sales/Margins by Month – Graphs sales and margins by month for a given date range.
  • Last 12 Months Sales – Displays sales and margins by month for the last 12 months.
  • Sales/Profits by Source – Creates a two-form report in spreadsheet form displaying sales and profit by source (how heard).
  • New Sales Graphs – Opens the graphical report viewer, which can be customized to quickly view and customize several different sales graphs.

Customer Reports

Customer List

Under the Reports menu you can open the Customer List report form, where you can see all or some of your customers at a glance, and print out lists whenever you want.


                                                             Figure 8-13

Customer Lists may be printed in order of Last Name, Company Name, ZIP/Postal Code, How Heard Code or Class/Area (e.g. Tax by Area location). Filter criteria may be entered for any these fields. Report information includes Name, Company, City, State, Phone #, Last Activity, Discount, Check Approval.

Mailing Labels

This function allows you to print or create Mailing Labels for your customers.

Best Customers

This report covers the customers that have purchased the most in Total Purchases or YTD Purchases or MTD Purchases, your choice.

Worst Customers 

This report covers the customer that have a Total Purchases or YTD or MTD purchases less than a set value.

Sales/Marketing Source


                                 Figure 8-11

MicroBiz can store "How Heard Codes" so you can keep track of how your customers are finding your business.

You can run a "Sales Source Report" (accessible under Reports, Sales Source) to to find out which How Heard Code is recorded for each customer in a couple of ways, depending on your personal preference:

  1. Sales Source at Time of Sale – You can set up MicroBiz to ask for the How Heard Code before completing each sale.
    • To use this method effectively, you should enable "Enter Shipping Information at F9" under Management, Customize, Print Settings.
  2. Customer's Original Source – You can enter each customer's How Heard Code with the rest of their information at the Add/Edit Customer screen.
    • You can do both of these, as either report is always available to you.

The How Heard Maintenance window has the following options:

  • Code – The short name MicroBiz uses to identify the How Heard category.
  • Description – The full description of the How Heard category.
  • Order By – All How Heard categories are sorted by their Code, but you can enter a search term in the box to the right to jump to a specific one.
  • Help – Displays the context-specific help window (always accessible by pressing F1 on your keyboard).
  • Edit – Allows you to make changes to the currently selected How Heard Code.
  • Add – Allows you to create a new How Heard Code.
  • Delete – Deletes the currently selected How Heard Code.
  • Close – Closes the How Heard Codes window.

Tax Reports

There are two different tax reports available under the Reports | Tax Reports menu.

Tax Exempt

  • This report displays all tax exempt transactions for a given date range. 
    • The "Cust. Tax ID(s)" are stored for each customer record under the Account tab (see Chapter 8 for more details on customer records).


                                                                           Figure 12-3

Tax Area

  • This report is used in conjunction with the Tax by Area feature. 
    • This feature is enabled on the Taxes tab under Management | Custom System Settings | Transaction Settings (for each workstation).
    • See Chapter 3 for more details on configuring this feature. 

                                                                                                          Figure 12-4
 

Report Modification

The following information will explain how to modify reports, but this is not supported by MicroBiz Technical Support (end users assume all liability for this). Use extreme caution when modifying reports:

  • Make sure to back up any files before modifying them.
  • Make sure to back up any modified reports so you don't lose them.
  • For information on Customizations, please contact Sales at 800-937-2289.

Any of the reports in the program may be modified from the Command Window found in Management | Custom System Settings | Transaction Settings | Security Tab.  Report and invoice forms are stored as FRX/FRT files. Find the FILENAME.FRX/FRT and modify it from the Command Window by typing "MODI REPO FILENAME" (where FILENAME is the name of the form desired).
To find the FRX/FRT file for a form, choose the report from the Main Menu and click on the generate report button. The filename will be displayed with a FRX/FRT/PRN extension on the screen (i.e. the filename for the Sales by Department Report is DEPTREPT).
So, when modifying the report, copy DEPTREPT.FRX and DEPTREPT.FRT to a safe place and proceed to the Command Window. Type MODI REPO DEPTREPT and the Visual FoxPro Report Designer will open up.

The Visual FoxPro Report Designer
The Visual FoxPro Report Designer works in a similar fashion to most other report designers. There are four main sections, known as bands, in any report or invoice: the Page Header, the Detail, the Page Footer and the Summary.

  • The Header typically contains the information on the report that is not statistically relevant, such as the title of the report and the date.
  • The Detail band of the report contains the raw data that is being reported, such as the sales for an individual SKU or the items in an invoice.
  • The Footer contains any extra information you want to include at the bottom of each page, such as a version number for the report. The Footer also contains the totals for invoice forms.
  • The Summary is the section that contains the totals for reports and is only printed at the end of the report, not on each page.

Fields on the Report Form
Individual data fields on the report are known as expressions. Expressions can be populated
with memory variables or values taken from a table. Double-click on a text box to edit its properties. In the expression field, enter the memory variable name. If you wish to use a value from a table, the syntax for the expression is ALIASNAME.FIELDNAME, where ALIASNAME is the name of the table or DBF file. If only a FIELDNAME is entered, the report will use the currently open table. Most reports use tables that are made available for that report so it is best to try to use the data from these tables rather than bringing in data from another table.
The best way to create a new expression field on a report form is to right-click on an existing field and Copy it to the clipboard. Then Paste it back where you want the new field and change the expression to what you want it to be.
Grouping
Some reports separate data according to a certain parameter. For example, the Sales by Department Report separates the data based on department. In these reports there is a Group Header and a Group Footer. The Header contains the information to indicate how the grouping is done, such as the Department Name in the Sales by Department report, and any other information you want to appear before the detail for that group. The Group Footer commonly contains totals for that group.
Totals
The totals for reports are calculated within the form and are easily understandable. Reports use the Summary section of the form to report the totals.
In the Summary section, enter QTY as the expression and in properties choose "Calculations" and select SUM to take the total of the values in the QTY field. You can also use any of the other operators under calculations, such as Average.
You will notice a drop-down box titled "Reset". This is where the computer makes the calculation. If you do not have any grouping in your report, just choose End of Report and it will calculate the expression at the end of the report. If you have grouping in your report and you want to calculate a total for a group in your Group Footer choose the name of the group as the Reset value.

If you make a mistake and irreparably damage a report, you can get a fresh copy by reinstalling from the CD or by finding the file on the CD. Note that in order to avoid overwriting your modified reports during a reinstall/replacement/upgrade, you should make back up copies and store them safely.

Please note that after you copy files from the CD, they may be marked as "read-only." You should use the Windows Explorer or the "attrib" command at a CMD prompt to remove the "read-only" attribute from the files.