Creating custom reports in QuickBooks Desktop

Before getting into custom reports, is important to understand the difference between “Source” and Target” Intuit has a great article here: https://community.intuit.com/articles/1556317-source-and-targets-use-in-reports

This article is based on an article posted in the Intuit Community, but recently archived under: https://community.intuit.com/articles/1020949-create-customized-reports-in-quickbooks-desktop (must have an intuit community login to see)

Customer and sales reports

Taxable customers

The report is a list of all customers that have a taxable sales tax code.

  1. From the Reports menu, select Customers and Receivables, then click Customer Phone List.
  2. Click Customize Report.
  3. On the Display tab, uncheck to remove Main Phone column.
  4. Scroll down the column list then check to add Sales Tax Code.
  5. Click the Filters tab.
  6. From the Filters list, select Sales Tax Code and from the drop-down, select All Taxable Codes or Tax depending on your need. Note: You may want to try one then the other to see which one gives you what you need.
  7. Click OK. You will see a report showing you all customers who are taxable.
  8. Click Memorize to save the report for future reference.

 

Sales Receipts that show check number for the payment

This report shows the check number specified by the user when they enter a sales receipt.

  1. From the Reports menu, select Customers & Receivables then click Transaction List by Customer.
  2. Click Customize Report.
  3. On the Display tab, check to add P.O. # and Pay Method columns.
  4. Click the Filters tab.
  5. From the Filters list, select Transaction Type.
  6. From the Transaction Type drop-down, select Multiple Transactionsthen add Sales Receipts and Payments.
  7. Click OK.

 

Report that shows sales by state

This report shows sales by state. This is particularly helpful if you want to see sales location trends or if you need to correct sales tax for a specific state.

  1. From the Reports menu, select Sales then click Sales by Customer Detail.
  2. Click Customize Report.
  3. On the Display tab:
    1. From the Columns list, select Name State.
    2. Click the Total by drop-down and choose Total Only.
    3. Click the Sort by drop-down and choose Name State.
  4. Click OK.

 

 

Daily sales report totaled by payment method

Follow the steps below to create a report of payments totaled by payment methods:

  1. From the Lists menu, select Customer & Vendor Profile Lists then click Payment Method List.
  2. In the Payment Method List window, click the Reports drop-down and click QuickReport.
  3. Click Customize Report.
  4. Set the appropriate date range.
  5. Go to the Filters tab and from the Filters list, choose Payment Method.
  6. From the Payment Method drop-down, select Multiple Payment Methods then click the payment method you want to show on the report.
  7. Click Ok.

 

 

Sales Order report grouped by customer with Item Name displayed for open items only

The default Sales Order by Customer Report does not include Item detail and displays the total amount of the Sales Order rather than just what is open. The Sales Order by Item Detail Report cannot be grouped by customer. To get this information, you need to customize the existing Open Purchase Orders by Job report.

  1. From the Reports menu, select Purchases then click Open Purchase Order by Job.
  2. Click Customize Report.
  3. On the Display tab, choose the following columns: Type, Date, Num, Source Name, Item, Item Description, Qty, Open Balance and Amount.
  4. Go to the Filters tab, then choose the following filters:
    1. Transaction Type = Sales Order
    2. Paid Status = Open
    3. Received = Either
  5. Click the Header/Footer tab and change the Report Title to Open Sales Order Detail by Customer.
  6. Click OK.
  7. Click Memorize to save this report for future use.

 

 

Customer payments and the invoices and deposits to which they are linked

This report is based on the Check Detail report. It shows customer payments you have received and the invoices and deposits to which the payments are linked. When formatted properly, it can function as a Customer Remittance report showing each Invoice and each Customer Payment.

  1. From the Reports menu, choose Banking then click Check Detail.
  2. Click CustomizeReport.
  3. On the Display tab, choose the appropriate date range.
  4. From the Filters tab, remove all selected filters under the Current Filter Choices.
  5. Select the filter for Transaction Type then choose Payment.
  6. Select the filter for Detail Level and choose All Except Summary.
    Note: If the Detail Level filter is selected as Summary Only, it will only show linked deposits. You can filter this report for specific customers by choosing the Name filter and narrowing down to a single name or type of name.
  7. Select OK.

 

 

Payments linked to open invoices

Creating a report to show all payments on open Invoices would require a filter to see if the Payment(s) are linked to an open invoice. The only two reports that show selected transactions and other transactions to which they are linked are the Check Detail report and the Deposit Detail report. However, these reports do not total the payments. To get a total of all payments for open invoices export the Deposit Detail report to MS Excel using the Advanced option for Auto Filtering.

  1. From the Reports menu, choose Banking then click Deposit Detail.
  2. Click CustomizeReport.
  3. Select the date range.
  4. Go to the Filters tab, then choose the following filters:
    1. Accounts = All Accounts Receivable
    2. Transaction Type = Invoice
    3. Detail Level = Summary Only
    4. Paid Status = Open
  5. Click Ok.
  6. Click the Export button at the top of the report then select a new Excel workbook.
  7. Click the Advanced tab and select options as you wish..
  8. Click OK then Export.
  9. In the MS Excel Worksheet:
    1. Delete all of the invoices that do not show a linked payment.
    2. Move each of the invoice amounts on cell to the right.
    3. Under the payments, insert a Sum function, that will show you the total of the payments.

 

 

Amounts on Estimates

This report shows the outstanding amounts on estimates.

  1. From the Reports menu, select Jobs, Time & Mileage > Estimates by Job.
  2. Click Customize Report.
  3. On the Display tab, check to add the Open Balance column. Uncheck any unneeded columns.
  4. Select the date range then click Ok.
  5. Click Memorize this report for future use.

 

 

Open invoices as of a selected date

  1. From the Reports menu, select Customers & Receivables then click Open Invoices.
  2. Click Customize Report.
  3. Click the Advanced… button.
  4. In the Open Balance/Aging area, select As of Report Date.
  5. Click OK in the Advanced Options then in the Customize Reportwindow.
  6. Enter the correct date on the report.
  7. Click Memorize to save this report for future use.

 

 

Customer invoices sorted by Invoice Number

This report shows all invoices for all customers sorted by Invoice Number instead of Date, Customer:job name or Item.

  1. From the Reports menu, select Custom Reports then click Transaction Detail.
  2. On the Display tab:
    1. Set the date range.
    2. Click the Sort by drop-down and choose Num.
  3. On the Filters tab, set the following filters:
    1. Transaction type = Invoice
    2. Detail Level = Summary only
  4. Click OK.
  5. Click Memorize to save the report for future use.

 

 

Customer:Job status, date and estimate totals

This report helps job related businesses that may need report of job status, start and end dates, and estimate totals.

  1. From the Reports menu, select List then click Customer Contact List.
  2. Click Customize Report.
  3. On the Display tab, select Job status, Estimate Total, Start Dateand Projected End columns.
  4. Remove any unwanted columns. You may want to add Balance Totals (for invoice balances).
  5. Click Filters, and choose the same filters, as needed: Job Status, Estimate Total, Start Date, Projected End if you need to filter out specific criteria in these areas, such as certain Start Dates, or only Awarded Jobs. You can also filter for one specific Customer or multiple Customers.
  6. Click OK..
  7. (Optional) Go to the Header/Footer tab to rename the report title.
  8. Click Memorize to save this report for future use.

 

 

A sales by rep report based on gross profit

Many companies want to pay commissions to sales reps based on Gross Profit rather than the gross of the sale. This enables companies to maintain a profitability on an item if it is deeply discounted. The Sales by Rep report is based on the item total and not the gross profit. Follow the steps below to create a sales by rep report based on the gross profit:

  1. From the Reports menu, select Custom Reports then click Summary.
  2. On the Display tab, select Rep in the Display Rows by drop down.
  3. Select the appropriate date range.
  4. On the Filters tab, select the Multiple Accounts filter.
  5. Check to select the appropriate Income and Cost of Goods Sold accounts.
  6. Click OK.
  7. Click Memorize to save this report for future use.

 

 

Customers and their associated price levels

QuickBooks do not have the option to display customers and their associated price levels in Reports. However, you can work around this by following these steps.

To see the price level field:

  1. From the Lists menu, select Add/Edit Multiiple List Entries.
  2. In the Add/ Edit Multiple List Entries window, click the List drop down choose Customer.
  3. On the right hand side, click Customize Columns,
  4. In the Available Columns pane, scroll down and select Price Levelthen click Add button in the middle.
  5. With the Move Up button, arrange the columns so the Price Level is next to the Name or Job column.
  6. You can edit the field here but you cannot print or export this report.

To print or export price level reports:

  1. Create an export IIF file and open it in MS Excel.
    1. Export your Customer List to your desktop.
    2. Browse to your desktop.
    3. Right-click the export file and choose Open with.
    4. Select Excel and the file will open in Excel.
  2. Find the information you need.
    1. In the Excel spreadsheet, scroll down Column B until you see the word NAME in a cell.
    2. The customer names will be listed in the cells below.
    3. Scroll across the spreadsheet in the same row where the NAME cell was found.  At the end of the row you will see PRICE LEVEL.
    4. Listed below are all the Price levels associated with these customers.
  3. Clean up the Spreadsheet.
    1. Delete (or hide) the rows above the row where the NAME and the PRICE LEVEL cells were found.
    2. Delete any other columns in between NAME and PRICE LEVEL that you do not wish to see in your report.
  4. When saving the file.
    1. Excel will give you a message: [File Name] may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?
    2. Select Yes.

 

 

Job Profitability Summary report that includes an Inventory Adjustment assigned to a Customer:Job

This report will include inventory adjustments and accurate job costing.

  1. From the Reports menu, choose Jobs, Time, & Mileage and click Job Profitability Summary report.
  2. Click Modify/Customize Report and select the Filters tab (For QuickBooks for Mac, choose the Filters icon).
  3. Under the Filters tab, select Account.
  4. Select the filter “Accounts and choose “All Accounts” from the drop down menu.
  5. Click OK to save the apply the changes and memorize the reports if necessary.

 


Job Profitability Detail Report For Paid Invoices

This report will show only Paid invoices by the invoice date. NOT the date they were paid.

  1. From the Reports menu, choose Jobs Time and Milage and select Job Profitability Detail.
  2. Choose the desired Customer and click OK.
  3. Click the Customized Report button.
  4. Under the Filters tab, select the filter for Paid Status and choose Closed.
  5. Click OK to generate the customized report.

 

 

Report for Missing Invoices

This report will show the missing Invoices

  1. From the Reports menu, select Banking and choose Missing Checks.
  2. Choose the Accounts Receivable Account that you used in your invoices.
  3. Click the Customize Report button.
  4. Under the Filters tab, select the filter for Transaction Type and choose Invoice.
  5. Click OK to generate the customized report.
Note: In the report, the following message will be displayed where there is a Missing Invoice.
*** Missing Numbers Here ***

Vendor reports

Bill Credits you have used

The report shows a list of bill credits you have applied to bills.

  1. From the Reports menu, select Vendors & Payables then click Vendor Balance Detail.
  2. Click Customize Report.
  3. On the Display tab, check to add the Paidcolumn.
    Note: The Paid column will show Paid if used and Unpaid if not used.
  4. On the Filters tab, select Transaction Type then click Bill Credit.
  5. Click OK.
  6. Click Memorize to save the report.

 

 

Open bills as of a selected date

  1. From the Reports menu, select  Vendors & Payables then click Unpaid Bills Detail..
  2. Click Customize Report.
  3. Click the Advanced… button.
  4. In the Open Balance/Aging area, select As of Report Date.
  5. Click OK in the Advanced Options then in the Customize Reportwindow.
  6. Enter the correct date on the report.
  7. Click Memorize to save the report.

 

 

Open Purchase Order report totaled by vendor

The Open Purchase Order report does not have a Total By option. However, you can create a Transaction Detail report that shows all open purchase orders for each vendor.

  1. From the Reports menu, select  Custom Reports then click Transaction Detail.
  2. Click Customize Report.
  3. On the Filters tab, select the following filters:
    1. Account = Purchase Order
    2. Posting Status = Either
    3. Received = No
  4. On the Display tab, click the Total by drop-down and choose Vendor.
  5. Click OK.

 

 

Report of total vendor payments

The report shows all payments to all vendors.

  1. From the Vendors menu, select Print/E-file 1099s then click 1099 Summary or Detail report.
  2. From the 1099 Options drop-down, choose All Vendors, All Allowed Accounts and Ignore thresholds.
  3. From the Dates drop-down, select All (or set the appropriate date range).

Note: The modified report will now display ALL vendor payments to a vendor. There may be multiple columns to reflect the 1099 mapping (Box 1: Rent, Box 7: Non employee Compensation; Uncategorized , Total, etc.). The Total column will reflect all payments regardless of the 1099 mapping.

 

 

A report that shows how Bill Credits are applied

Although you can show bill credits by going to File > Print Forms > Bill Payment Stubs, the stub does not show to which Bill the Bill Credit is applied. This report shows which bill credits are applied to which bills.

Single bill credit

For a single bill credit, run a Transaction History report.

  1. From the Reports menu, select Vendors & Payablesthen click Vendor Balance Detail.
  2. Scroll to the appropriate vendor.
  3. Highlight the Bill Credit then on your keyboard, press Ctrl + H to open the transaction history.
  4. In the Transaction History – Credit window, click Go To to open the bill or Print to have a printed copy of the report.

Multiple credits

If you need the report for more than one bill credits, modify the Check Detail report to show multiple bill credits.

  1. From the Reports menu, select Report>Banking > Check Detail.
  2. Click Customize Report then go to the Filters tab.
    1. Remove the default filters (Account, Amount and Detail Level).
    2. Add the Date filter then enter the appropriate date range.
    3. Add the Name filter and select the appropriate Vendor name.
    4. Add the Transaction Type filter then select Bill Credit.
  3. Click Ok.
  4. Click Memorize to save the report.

 

 

Vendor expenses by Customer:job

Follow these steps if you need a report that displays detailed vendor expenses totaled by Customer:job

  1. From the Reports menu, select Custom Reports > Transaction Detail.
  2. Click Customize Report then go to the Display tab.
  3. In the Display tab:
    1. Set the appropriate date range.
    2. In the Columns section, click to clear the checkmark next to Name, and then click to add a checkmark next to “Source Name”.
    3. Click the Total By drop-down arrow and select Customer.
  4. Go to the Filters tab and from the Filter List, select Transaction Type.
  5. Select the transaction type associated with the Vendor purchases (i.e. Bill).
  6. Click OK to display the report.

 

 

A report of purchase orders and sales orders by item

Run this report to see purchase orders and sales orders by item.

  1. From the Reports menu, choose Custom Report and select Transaction Detail.
  2. Under the Display tab, choose the following columns:
    • Amount
    • Balance
    • Date
    • Name
    • Num
    • Item
    • Qty
    • Type
  3. On the Total by drop down menu, select Item detail .
  4. Under the Filters tab, select the following filters:
    • Detail Level: All except summary
    • Item: All Sales Item
    • Posting Status: Non-posting
    • Transaction Type: Multiple Transaction Types: Sales Orders and Purchase Order.

Item reports

P.O. Report that includes items and item descriptions

Item and item description are readily available in Open Purchase Orders Detail report available in QuickBooks Premier 2013/QuickBooks Enterprise 13 or later. If you use QuickBooks Pro or QuickBooks for Mac, you can run a Transaction Detail report.

  1. From the Reports menu, select Custom Reports then click Transaction Detail.
  2. Click Customize Report.
  3. On the Display tab, select Item and Item Description columns.
  4. On the Filters tab, select the following filters:
    1. Transaction Type = Purchase Order
    2. Posting Status = Either
    3. Received = No for open Purchase Orders
    4. Received = Yes for closed Purchase Orders.

Note: In QuickBooks for Mac, click the Filters icon then check or uncheck a filter to add/delete it. Click Apply and close the window to save the changes.

 

 

Inventory items used in assemblies to be ordered from Vendors

This report shows inventory items that make up assemblies currently on sales order. It will help you see what inventory items need to be ordered from a vendor (using a purchase order, in most cases).

  1. Create a sales order using the inventory assembly that will be sold to the customer.
  2. Create a pending build for this assembly..
  3. Go to the Inventory Stock Status by Item report.
  4. View the For Assemblies column to see which inventory items are on the pending build.
  5. Now purchase orders can be entered for any inventory parts in the For Assemblies column.
  6. Once the items have been received, and the assemblies are ready to build, go to the Pending Builds report.
  7. Double-click a pending assembly that is ready to be built.
  8. Click Remove Pending Status.
  9. Click Build & Close.
  10. Now an invoice can be created from the sales order for the assembly that was built.

 

 

Items used in Build Assemblies

  1. From the Reports menu, select Custom Reports then click Summary.
  2. On the Display tab, set the date range to the period you need.
  3. Click Customize Report.
  4. On the Display rows by drop-down, select Item detail.
  5. Under Display columns for, select Quantity (leave default to Amount if dollar amount is required).
  6. On the Filters tab:
    1. Remove the Account filter.
    2. Add Transaction Type then select Build Assembly filter.
    3. Add Detail Level then select All except summary filter.
  7. Click Memorize to save the report for future use.

You also have the following options:

  • Set the Posting Status filter to Either to include pending builds.
  • Change Display Columns by to Month to display data by month.
  • Set the Item filter to Multiple items and select desired items.

Company and financial reports

Multiple year Profit & Loss comparison report

Follow the steps below to create a Profit & Loss report that displays yearly data separated by column for easy comparison.

  1. From the Reports menu, select Company & Financial then click Profit & Loss Standard.
  2. Click Customize Report.
  3. On the Display tab:
    1. Set the From and To date range to the years you want to display on the report.
    2. Click the Display columns by dropdown and select Year.
  4. Click Ok.

 

 

Foreign currencies on Profit and Loss or Balance Sheet report

Currently,QuickBooks can only create a Balance Sheet and a Profit and Loss report using the home currency. On these reports, transactions using foreign currencies are converted into their corresponding US dollar amounts.The following summary reports, however, allow you to choose to display amounts in your home currency (the default) or in foreign amounts:

  1. A/R Aging
  2. A/P Aging
  3. Customer Balance Summary
  4. Vendor Balance Summary
  5. Sales by Customer Summary
  6. Purchases by Vendor Summary (if purchase orders are turned on)

To change the default currency for one of these summary reports:

  1. Click the Customize Report button.
  2. Go to the Display tab and in the the Display amounts in section:
    1. Choose to display amounts in either the home or transaction (foreign) currency.
    2. Click OK.
  3. Click Memorize to save the report.

Payroll and employee reports

Report of payroll item, rate, and total paid

Follow the steps below to create a detailed custom report that lists each individual payroll item, the rate for each payroll item, and the total amount that has been paid to that payroll item.

  1. From the Reports menu, click Custom Reports > Transaction Detail.
  2. Go to the Display tab.
  3. Under the Columns section, deselect everything except Payroll Item, Qty, Sales Price and Amount.
  4. Go to the Filters tab and choose the following filters:
    1. Account = the appropriate Payroll Expense Account(s)
    2. Transaction Type = Paycheck
  5. Click OK.
  6. Enter the date range at the top of the report (From and To).
  7. Click Refresh.

 

 

Total expenses by employee broken down by month

The following steps will create a detailed custom report that shows expenses by employee totaled by month.

  1. From the Reports menu, click Custom Reports > Summary.
  2. Select the desired date range.
  3. On the Display columns by drop down menu, select Month.
  4. On the Display rows by drop down menu select Employee.
  5. Go to the Filters tab and choose the following filters:
    1. Account = Expense and other expense accounts.
  6. Click OK.
Hector Garcia

Hector Garcia

Use our link to get 30% off for a year, valid through 07/31/2022

BOOK NOW AND SAVE 30%

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *



Popular Courses

Private QuickBooks Training onsite

Customized Training

Accounting & Bookkeeping Services

Video-Based Virtual QuickBooks Course

Share

30% Off QuickBooks

BOOK NOW AND SAVE 30%

Use our link to get 30% off for a year, valid through 07/31/2022

Setup 30-day FREE Trial of QuickBooks Online

We are committed to providing the highest quality Accounting & QuickBooks Education in a live class format or private manner.

Youtube Linkedin Facebook Twitter

We are Advanced Certified QuickBooks ProAdvisors.

QuickBook Training Courses in Miami

Self-Paced Courses

QuickBooks Online Banking

QuickBooks Online for Construction

QuickBooks Desktop Basics

QuickBooks Desktop Advanced

Live QuickBooks Training 

1-on-1 QuickBooks Training Remote

1-on-1 QuickBooks Training Onsite

1-on-1 QuickBooks Training Our Office

Group & Custom Training

Accounting & Tax

Bookkeeping Services

Tax Preparation

Buy QuickBooks

QuickBooks Online Pricing

QuickBooks Desktop Pricing

Free Resources

Our YouTube Channel

QBKaccounting Blog

FaceBook Group

Free Manuals and Guides

Contact Us

Palmetto Park West Office
7791 NW 46th Street
#109
Doral, FL 33166

Quick Bookkeeping and Accounting LLC is a florida-based company that provides Bookkeeping Services, QuickBooks Training, and Accounting Consulting Services.