Differences

This shows you the differences between two versions of the page.

--- pos:report-wizard [2025/04/06 18:29] – removed - external edit (Unknown date) 127.0.0.1
+++ pos:report-wizard [2025/04/15 23:23] (current) – [Report Wizard] mrjim
@@ Line 1: / Line 1: @@
+====== Report Wizard ======
+\The Report Wizard is where most of the reports are found, Start> Reports> Report Wizard. This tool generates Excel spreadsheets with many different types of data. Because they are in a spreadsheet they can be manipulated  as with any spreadsheet. \\  \There are a few things to remember in using these reports. While you can run the reports from any computer, they will not display unless you have a program that opens spreadsheets installed on that computer. We installed  Open Office [a free program] to open these report spreadsheets on the Print Computer. It’s free and can be installed it on any computer to the printers. Also, these reports are sent to the computer directly as a file. Although the spreadsheets automatically open, THEY MUST SAVED TO THE COMPUTER before they can be modified. Then they can be opened and printed from the spreadsheet program. They don’t just print out like the Daily Closeout or Driver Checkout.\\  \All the reports compile the data from all the stores you have access to at that moment. Most of them have the data  split by store too. If you only want the data from one of your stores have someone sign into POS that only has  access to that one store. It will only pull the data for the stores that are set in [[pos:new-friends#User Store Access|User Store Access]]. \\  \To run any report, click on the radio button next to the name of the report. \\  \Enter the parameters for that report in the settings area and click Generate Report.\\  \A file is generated and downloaded to that computer. If a long period of time or lots of stores for the report has been selected it can take a bit of time. \\  \Once the file is downloaded a pop-up window opens giving the choice to open or save the file. Open these with Open Office Calc or Excel or other spreadsheet program. \\  \If too much data has been requested, it will just time out and no report is generated. Refresh the browser and try a smaller dataset. \\  \The best way to learn about these reports is to run them in different ways to see the results.
+===== Coupon Redemption Report =====
+\This report will help to evaluate how to spend advertising dollars to create the most income possible. Use this report to compare how one offer does compared to another. Also compare how one medium works versus another. \\  \In the settings section select the Start Date: and End Date: as desired. \\  \For the Coupon Redemption Report there is an option to select only one or a few coupons by entering the codes in the Code: form field. Enter a single code like XXX or many codes by separating them with a comma like: 2TO5, WPQ, WPR, WPS. Leave this filed blank to get all the coupons in the report for the period.\\  \There is an option report to choose whether just the Delivery redemptions or just the Pick-Up redemptions or Both. One of these choices is required or the Generate Report button is greyed out and not clickable.\\  \This report has the most redeemed coupon listed first on the list and then the rest are listed in descending order.
+  *   The first column is “Name” which is the name of the special. Each special can have many different codes attached to it.
+  *   “Code” is the actual code used.
+  *   “Price” is the price of the offer. A zero is shown for percent off Specials.
+  *   “Redemptions” is the number of times that Code was applied to orders.
+  *   “Orders” is the number of orders where the Code was used. Some Codes can be used more than once per order.
+  *   “Total$” is the total value of the orders that used that Code. It would include all the items on the order including other Codes used or menu items.
+  *   “Special$” is the sales for that Code only. It does not include other items on the order.
+  * "Discounts$" is the total of the discounts applied to the orders with that code applied.
+  *   “Vendor” the distributor of the Code.
+  *   “Description" is the description of the offer.
+===== Coupon Redemption Report by Store =====
+\If you have access to more than one store you may want to look at the redemption by store. \\ \This is virtually the same report above with a breakdown by store and the addition of the “Store” and “Expires” column.
+  * “Store” is the franchise ID for the store.
+  * “Expires” is the date the Code expires.
+\The first sheet lists all the Codes used. They are grouped by location.\\ \Then there is another sheet for every store you have access to.
+===== Box Count Report =====
+\This reports how many boxes that should have been used. This report is excellent for making sure that every pizza made is put through JOES. Compare the results of this report to the actual number of boxes used. If more boxes were used than the report states, there were some boxes sold that were not entered into JOES. Not all bundles of boxes come into the building with 50 boxes or 100 boxes in them. There can be more or less. \\ \On this report selecting "POS orders only?" limits the report to phone orders. Use "Online orders only?" to limit the report to online sales. \\ \The report has the highest number used as the top row. \\ \When selecting "Group by size name?" you get a report that has 2 columns.
+  * SIZE_NAME is the box size name that was sold.
+  * ITEM_COUNT is how many boxes were sold.
+\Running the report without checking the "Group by size name? produces a 3-column report. Use this version to see what the best-selling pizzas/ items are. It is a longer report because it lists all the items sold.
+  * ITEM_NAME is the name of the item sold.
+  * SIZE_NAME is the box size name that was sold.
+  * ITEM_COUNT is the number of boxes that you sold through JOES for that item only.
+===== New Customers Report =====
+\This report shows how many new people are coming into the store. \\ \You can select a max order count to eliminate customers from the list. For example, in September you could run a report for how many people were new customers in January but only have one order. They came in once but have not been back. You could contact them to see what problem there may be with your operation. \\ \The date range is date of their first order.
+  * ID is a unique identifier for that customer record.
+  * STORE_LIST is the franchise id of the stores the customer went to.
+  * FIRST is the customer’s first name.
+  * LAST is the customer’s last name.
+  * EMAIL is the customer’s email, if entered.
+  * REGISTERED is the date that the customer was first entered into the system either on his own through Online or by one of our staff in the store.
+  * ORDERS is the total number of orders for that customer and includes that first one.
+  * FIRST ORDER this is first date the customer placed an order. Often it’s the same as the entered date but there are customers who sign up for Online or start to place an order and then don’t order a pizza that day.
+  * LAST ORDER is the date last time that customer placed an order.
+  * PHONE_LIST is the list of phone numbers for that customer. Often there is only one phone number.
+  * ORDERED AGAIN is if the placed another order, after their first order, on a different day.
+  * ADDRESS_LIST is the list of customer addresses.
+===== Ancillary Statistics =====
+\This reports the numbers of ancillaries ordered. These are most of the ancillary categories.
+<WRAP group>
+<WRAP quarter column>
+  * Cheese Amount
+  * Cheese Type
+  * Chicken or Beef
+  * Chips
+  * Chips Extra
+  * Crust Type
+  * Dipping Sauce
+</WRAP>
+<WRAP quarter column>
+  * Drink Flavor
+  * Extra Dipping Sauce
+  * Extra Dressing
+  * Extras
+  * Flavor
+  * Greens
+</WRAP>
+<WRAP quarter column>
+  * Icing
+  * Salad Dressing
+  * Condiment
+  * Sandwich Dressing
+  * Sandwich Dressings
+  * Sandwich Extras
+</WRAP>
+<WRAP quarter column>
+  * Sandwich Fixings
+  * Sauce Type
+  * Seasoned Crust
+  * Side Salad
+  * Sticky Finger Extras
+  * Wing Flavor
+</WRAP>
+</WRAP>
+\Enter the date range you wish to view then generate the report. \\ \The first sheet lists the total ancillaries ordered for all accessible stores. It has them listed by ancillary category in alphabetical order.
+  * “Category” is the name of the ancillary category.
+  * “Name” is the name of the ancillary.
+  * “Used” is the count of the number that were used.
+  * “Revenue” is the sales total for those ancillaries that have prices.
+\The second sheet lists them separated by store. With the added column FRANCHISE. \\  \Then there is a separate sheet for each accessible.
+===== Topping Statistics =====
+\This reports the number of toppings ordered. These are the topping Categories.
+\* Cheese * Firsts * Meat * Meat - Sub * Other * Vegetables *
+\Enter the date range you wish to view then generate the report. \\\\  \The first sheet lists the total toppings ordered for all accessible stores. It has them listed by topping category in alphabetical order.
+  * “Category” is the name of the topping category.
+  * “Name” is the name of the topping.
+  * “Used” is the count of the number that were used.
+  * “Revenue” is the sales total for those toppings that have prices.
+\The second sheet lists them separated by store. With the added column FRANCHISE_ID.\\ \Then there is a separate sheet for each accessible.
+===== On Account Invoices =====
+\Many franchisees allow some customers to pay on Accounts for later payment. They pay the order on account on the day of the order and then weekly or monthly they send the invoice statements from this report. Any time an order is paid on account the owner is sent an email with an invoice for that order. This happens every day at about 3:00 AM. That way the owner knows when any order is paid on account. These individual account invoices can also be sent to the customers. \\ \The date range includes all orders paid on account during that period. \\ \There is a tool in OIC that allows you to keep track of payments for the account invoices. [[pos:payoutorder#Account Payments]]\\ \Sample:
+===== Payout History =====
+\Enter the date range to display payouts during the period. Payouts do not need to be assigned to a team member. They are only assigned to the drivers when the driver pays for it out of his bag.
+  * PAYOUT_CODE is the accounting code assigned to that expense category.
+  * PAYOUT_DESCRPTION is the description assigned to that payout category.
+  * PAYOUT_TYPE – whether it is an income or expense Payout.
+  * PAYOUT_DATE – the date of the Payout.
+  * PAYOUT_AMOOUNT – the amount of the Payout.
+  * PAYOUT_NOTES – any notes you need to make about the Payout.
+  * PAYOUT_STATUS – whether the Payout is finalized. Payouts are finalized when you run the Daily Closeout Report.
+  * USER_EMPLOYEE_ID – if the Payout was assigned to a driver so that it is included in the Driver Checkout Report, it lists the Employee User ID.
+  * USER_FIRST_NAME – the first name of the driver that the Payout was assigned to.
+  * USER_LAST_NAME – the last name of the driver that the Payout was assigned to.
+  * FRANCHISE_ID – the store number.
+===== Payroll Report [Spreadsheet] =====
+\This is a Payroll Report for people who pay on a bi-weekly basis. This Spreadsheet can be run for several weeks. All the overtime is calculated correctly based on Sunday – Saturday weeks. \\ \There is another payroll report, [[pos:timecards-schedules#Payroll Report [PDF]|Payroll Report [PDF]]], in Start>Users>Timecards. In that area, an owner can get a PDF version of a payroll report. There are 2 different ways to get a report email & create PDF. It lumps all hours for the period that is selected together. For this reason, you must run only one week at a time when using the report in POS Start>Users>Timecards so that overtime pay is correctly calculated.
+===== Payroll Hours Report =====
+\This report merely lists the total hours for each person without the shift details. The overtime is calculated for any 2-week period.
+===== Paycor Payroll Report =====
+\This report is only for stores using Paycor payroll processing service. When downloaded, verified for accuracy, and then saved as a .csv file it can be uploaded directly to Paycor in the Paycor portal.\\ \One user uses the Payroll Hours Report first and uses that to verify the hours. Once the data is correct in any report it will be correct in all reports.
+===== Driver Expense Report =====
+\This is a report for the reimbursement and tip money for each shift. The Not Cash column where it is marked as “true” indicates that the driver was paid by a bank transfer rather than currency from the cash drawer. \\ \The first sheet lists all the drivers for all stores you have access to and then there is a page for each of the stores.
+===== Delivery Times Report =====
+\This report is limited to the data from the store that POS is signed in on. That's the location listed in the POS taskbar. It lists all the deliveries for all drivers and the checkout and check-in times for that store only. It gives you statistics for out-the-door times.\\ \It is flawed in that when a driver takes multiple deliveries on the same run, the time is incorrectly recorded the same for each of the deliveries.
+===== Delivery Run Times Report =====
+\This report only uses the data from the store that the POS is signed into. This report is designed for those stores that want to pay their drivers 2 different pay rates. A rate for in the car on the road and another different rate for when the drivers are in the store. \\ \It is a log of the minutes that the driver was on the road. \\ \One must be sure that drivers check in and out for deliveries correctly to use this tool effectively.
+===== Summoned Orders report =====
+\This is a list of all the orders where 3<sup>rd</sup> party drivers were summoned to take the deliveries for us. It uses an arbitrary cost of $7 per delivery. When a close order is sent by Nash through Uber Direct it is only $6.10 per delivery. Some deliveries are more than $12. One must use your delivery service provider’s portal to see the actual expense for each delivery.
+===== Price Overrides =====
+\This is the list of the orders where the price was changed after the order was entered by the user applying a manger discount.
+  * ORDER NUMBER
+  * DISCOUNT is the decrease in price of the order.
+  * DELIVERY DISCOUNT is the discount allied to the delivery fee.
+  * OPERATOR is the person who signed into the POS.
+  * APPROVED BY is the person who applied the manager discount.
+  * ORDER DATE
+  * FRANCHISE NUMBER
+  * ORDER TOTAL is the new order total.
+  * SPECIAL NAME is the name of the Special that requires a manager override.
+  * CODE USED Is the code applied that needed a manager discount.
+===== Update Item Prices =====
+\These are the current menu prices for a store. It is a tool to send us new menu prices when changing more than a few prices. \\ \Each menu item size is priced indecently. \\ \There is a column for newPrices with zeros. And another column of zeros called newToppings. When changing prices, enter the price for that item size. Leave a price at $0.00 if it is not to be changed. Change the zeros in the newTopping column to the new topping prices when you wish to change topping prices. If you do not wish to change the price leave the 0. \\ \Sending the new prices with this sheet allows all the changes to be made at once.
+===== Update Ancillary Prices =====
+\These are your current prices for those ancillaries that you charge for. It is a tool for you to send us your new ancillary prices when you want to change more than a few ancillary prices. \\ \There is a column for NEWPRICE with zeros. When you want to change prices, you change a zero to the price you want for that ancillary. \\ \Sending the new prices with this sheet allows all the changes to be made at once.
+===== Sales Report =====
+\A list of every order entered into JOES. It includes all POS, TOOS, and Online orders. This includes canceled orders. \\ \The columns are labeled well. The Driver Out column is the number of minutes the from when the driver checked out on the delivery. A negative number indicates the driver checked out before the order due time. \\ \There is a summary sheet for all stores you have access to and then individual sheets for each store. \\ \This report uses every order and care should be taken to not use too long a time period.
+===== Sales Report without Canceled =====
+\This is the same report as the Sales Report. It does not include canceled orders.
+===== Sales Summary Old (Accountant) =====
+\This report is the old version that shows all cash transactions and all sales data. I left it here and available because the new report was not created until December of 2024. You may prefer this older version to look at last year's data.
+===== Sales Summary by Zip Code Report =====
+\Many states have a city sales tax and a state sales tax. If you deliver into different cities the Sales tax report filed with the state will need the sales specified by city. \\ \The pickup sales, which includes TOOS are taxed in the store’s city location. The top row for that store is the pickup sales for that location. \\ \Deliveries can be sent into other cities, and someday maybe even different states. \\ \This report lists the taxes collected for deliveries delivered into each zip code. It is important to note that the Tax column is the true amount collected for sales taxes and may not be the exact percentages as posted. This is due to rounding the sales taxes on every order and there are some tax-exempt sales. Only pay the state the tax amounts listed. This is true of all sales tax amounts shown in any JOES report. \\ \There are some places where the Del Fee is not taxable.
+===== Sales Summary 2025 (Accountant) =====
+\This report includes all cash transactions and all sales data. If one enters all needed data into POS one merely sends this report and his bank ledger to his bookkeeper. No need to send Closeout Reports. \\  \Sometimes a number shows as ##### instead of the actual number. That is merely because the column is too narrow to display the full number. To widen the column, go to the top row of letters and grab and move the right edge to the right or double click the right edge of the column. \\  \I color coded some columns for fun.
+  * **Store - **The franchise ID of the store.
+  * **Date**
+  * **Net Sales - **The total value of all items and fees sold.
+  * <fc #ff00ff>**Tax - **The total value of the actual sales taxes that were collected on each order. It is not identical to the monthly net sales multiplied by the tax rate. The tax collected on each order is stored. Rounding causes the tax to vary slightly on each order.</fc>
+  * **Non-Taxable -** The total value of the items and fees sold to tax-exempt customers.
+  * **Total Revenue -** Total value of all items and fees including sales taxes collected.
+  * <fc #008000>**Cash-Income -** The amount of currency that was put into the drawer for cash purchases.</fc>
+  * <fc #008000>**Cash Drawer -** The amount of money left in the Drawer at the end of the night.</fc>
+  * <fc #008000>**Deviation - **That days change in value of the drawer.</fc>
+  * <fc #008000>**$ from Drivers -** [Only for those stores transferring gas and tip money to the drivers Not for stores that pay drivers gas and tips in cash from the drawer.] The cash tips that drivers turned in and added to the drawer. This money is transferred to the driver. It is the drivers’ tips, and not income to the store.</fc>
+  * <fc #008000>**Cash** **Refill -** A non-taxable event. It is the amount of money that was added to the drawer to bring it to the level desired.</fc>
+  * <fc #008000>**Cash-In -** The total of the Payouts / income. When you sell something for cash that is not a POS item, you enter a Payout / income and put the cash from the sale of that item into the Drawer.</fc>
+  * <fc #008000>**Paid-Out - **The cash that was taken from the drawer [or driver’s bag] to purchase any needed items for the store. This includes the driver reimbursements paid in cash to the drivers.</fc>
+  * <fc #008000>**Insider Tips - **Tips that pick-up customers using cards added to their payments. The total value of those credit card tips is taken from the drawer and given to the insiders. There are stores where tips are correctly added to the insider paychecks and not paid out daily in cash.</fc>
+  * <fc #008000>**Driver Tips –** The tips that customers using cards added to their payments for deliveries. The tips in this column are the ones given in cash to the driver at checkout.</fc>
+  * <fc #008000>**Dep – Cash -** The currency that was sent to the bank for deposit.</fc>
+  * <fc #008000>**Over/ Short -** The cash amount that was over or short for that day.</fc>
+  * **Tips Total - **The total of all tips. It includes Driver Tips whether paid in cash or transferred, Insider Tips, and Counter Tips from customers adding cash to the tip jar at those stores that deposit Counter Tips.
+  * **Tips Summoned -** Tips from customer payments that are for third-party drivers that were summoned to deliver orders that we took through the phones or Online.
+  * **Gas Sent -** For stores that transfer money to the drivers, this is the gas money that was transferred to the drivers’ accounts.
+  * **Tips Sent -** For stores that transfer money to drivers, the total of tips transferred to drivers. This includes the credit card and cash tips that were given to drivers by customers.
+  * **Total Sent -** For stores that transfer money to the drivers, the total amount that was transferred to drivers.
+  * <fs Large><fc #efcc00>**Dep – Check -** The total of the orders that were accepted with a Check payment method. Most stores no longer have a check payment method. Checks accepted are merely included with and treated as cash. </fc></fs>
+  * <fs Large><fc #efcc00>**Dep – Visa -** The total of the payments made with a VISA card. </fc></fs>
+  * <fs Large><fc #efcc00>**Dep – MC -** The total of the payments made with a Master Card. </fc></fs>
+  * <fs Large><fc #efcc00>**Dep – Amex -** The total of the payments made with an American Express card. </fc></fs>
+  * <fs Large><fc #efcc00>**Dep – Disc -** The total of the payments made with a Discover card. </fc></fs>
+  * <fs Large><fc #efcc00>**Refund CC –** The total of any refunds processed on that day. </fc></fs>
+  * <fs Large><fc #efcc00>**Dep – Credit -** The bank deposit from the credit card company and will always match the bank deposit to the penny for every day. </fc></fs>
+  * <fc #6495ed>**Pmt – Acct -** The total payments for the orders with on Account payments that will be paid later.</fc>
+  * <fc #6495ed>**Dep – Acct - **The total deposits you manually entered with POS Spend/ Receive tool for customers paying for their Account orders.</fc>
+  * <fc #4682b4>**Pmt – TOOS -** The total accepted as payments for the day’s TOOS orders that will be partially paid later. The payments for TOOS orders are reduced by commissions, ads, and customer’s that get credits from the TOOS.</fc>
+  * <fc #4682b4>**Dep – TOOS -** The total TOOS deposits that were manually entered on that day.</fc>
+  * **[COMMISSION –** Bottom of column AF & AG. The easiest way to calculate TOOS expenses is to enter the TOOS bank deposits into the POS Spend/ Receive tool. COMMISSON in this summary is the total recorded for TOOS payments minus the entered deposits. The difference between what is recorded as a payment and the deposit is the TOOS expense. Recording the deposits as they come in is an acceptable workaround and easier than reconciling the deposits to the orders that were taken in the prior week. DoorDash charges a different commission rate for some customers. Some stores pay for ads applied to each order. Customers are given partial credits for reported errors. All those things are an expense.**]**
+  * **Dep – Bank -** The total of the Cash Deposit and the Check Deposit [if there is a Check Deposit].
+  * **Dep – Total-** The total of all deposits.
+  * **Pickup - **The total of the pick-up sales.
+  * **Delivery - **The total of the delivery sales.
+  * **Online -** The total of the Online sales.
+  * **Orders -** Total order count.
+  * **Deliveries - **Delivery order count.
+  * **O Orders -** Online order count.
+  * **Summoned Count -** Outsourced/summoned order count.
+===== Sales Summary by Zip Code Report =====
+\Many states have a city sales tax and a state sales tax. If you deliver into different cities the Sales tax report filed with the state will need the sales specified by city. \\ \The pickup sales, which includes TOOS are taxed in the store’s city location. The top row for that store is the pickup sales for that location. \\ \Deliveries can be sent into other cities, and someday maybe even different states. \\ \This report lists the taxes collected for deliveries delivered into each zip code. It is important to note that the Tax column is the true amount collected for sales taxes and may not be the exact percentages as posted. This is due to rounding the sales taxes on every order and there are some tax-exempt sales. Only pay the state the tax amounts listed. This is true of all sales tax amounts shown in any JOES report. \\ \There are some places where the Del Fee is not taxable.
+===== Items Sold Report =====
+\This report lists all the Item Sizes sold. \\ \The first sheet is a summary of all the item sizes sold at the stores you have access to. The second sheet shows all the item sizes sold with a store column so you can easily compare store performance. Then there is a sheet for each store.\\ \"Checking the TOOS only?" Checkbox prior to running the report will report only those items sold through our 3<sup>rd</sup> party delivery service partners.
+===== Pizzas Sold Report =====
+\Very similar to the Items Sold. It is limited to pizzas only.
+===== Beverage Sold Report =====
+\Enter the date range you wish to view then generate the report. \\ \The first sheet lists the total count of the beverage flavors ordered for all accessible stores. It has them listed by size and flavor in alphabetical order.
+  * “Size” is the drink size.
+  * “Flavor” is the drink flavor.
+  * “Used” is the count of the number that were ordered.
+  * “Sales” is the sales total for that beverage flavor.
+\The second sheet lists them separated by store. With the added column FRANCHISE. \\  \Then there is a separate sheet for each accessible.
+===== Doughball Report =====
+\Enter the date range you wish to view then generate the report. \\ \The first sheet Dough Balls All Stores lists the total doughball counts that should have been used based on what was ordered at all accessible stores.
+  * "Size" is the doughball size.
+  * "Counts" is the number of doughballs that were ordered.
+\The second sheet Dough Balls by Store list the doughballs ordered with the added column Store. \\ \Then there is a sheet for each store.
+===== Mozzarella Usage Guess Report =====
+\This is a hypothetical cheese usage report. There is an arbitrary cheese amount assigned to each item size that uses mozzarella cheese. Those amounts are totaled and will be remarkably close to what you should have used. If you find your team is using more than this report total you can go over with them the importance of weighing the cheese and every topping on every pizza every time.
+===== Thin Crust Report =====
+\Enter the date range you wish to view then generate the report. \\ \The first sheet Thin Crusts All Stores lists the total thin crust counts that should have been used based on what was ordered at all accessible stores.
+  * "Size" is the thin crust size.
+  * "Counts" is the number of thin crusts that were ordered.
+\The second sheet Thin Crusts by Store list the Thin Crusts ordered with the added column Store. \\ \Then there is a sheet for each store.
+[[:start|Back to start]]