How to Pull a Customized Transaction Extract from QuickBooks Desktop for Cube Integration

This article walks you through generating a complete transaction extract from QuickBooks Desktop (QBD) with the columns you need — account number, account name, and any additional dimensions you track such as department (Class), vendor, customer, item, or custom fields. You'll also learn how to memorize the report so you can re-run it with one click, and optionally schedule it to email automatically.

Before You Start: Enable Required Settings

Some QBD features need to be turned on before their data can appear as columns in reports. Review the settings below and enable the ones that apply to the dimensions you want in your extract. If you've already enabled the ones you need, skip to the next section.

Enable Account Numbers

1. Go to Edit → Preferences → Accounting
2. Click the Company Preferences tab
3. Check Use account numbers
4. Click OK

Once enabled, the Account column in reports will display as "1000 · Cash" (number and name combined).

Enable Class Tracking (for Departments, Locations, or Other Segments)

QuickBooks Desktop uses "Class" as its built-in segment field. Companies use it for departments, locations, business units, programs, or any other dimension they want to track across transactions.

1. Go to Edit → Preferences → Accounting
2. Click the Company Preferences tab
3. Check Use class tracking for transactions
4. Click OK

Note: Classes will only appear on transactions entered after class tracking was enabled. Historical transactions won't have a class assigned unless they were tagged at entry.

Other Dimensions That Don't Require Setup

Several common dimensions are available by default and don't need to be enabled:

• Vendor / Customer — automatically captured via the Name field on transactions
• Item — available on any transaction that uses items (invoices, bills, sales receipts, etc.)
• Memo — free-text field on every transaction

Custom Fields

If you use custom fields on transactions or names (customers, vendors, employees) to track additional dimensions, those can also be added as report columns. Custom fields must be created in advance via the Lists menu or directly on a customer/vendor/item record.

Step 1: Open the Custom Transaction Detail Report

1. From the top menu, go to Reports → Custom Reports → Transaction Detail
2. Set your date range at the top of the report window
3. The customization window should open automatically. If it doesn't, click Customize Report

Step 2: Choose Your Columns

On the Display tab, scroll through the Columns list and check the boxes for the dimensions you want in your extract.

Recommended Core Columns

These columns are useful in almost any transaction extract:

• Date
• Type (transaction type — invoice, bill, check, etc.)
• Num (transaction or reference number)
• Name (customer, vendor, or employee on the transaction)
• Account (shows account number and account name together)
• Debit / Credit (or Amount, depending on your preference)
• Memo

Common Optional Dimensions

Add any of the following based on what you track:

Custom Fields

Scroll to the bottom of the Columns list. Any custom fields you've created on transactions or names will appear there and can be checked like any other column.

Tip: Uncheck What You Don't Need

The default report includes columns you may not want. Uncheck anything irrelevant to keep the export clean and easier to work with in Excel.

Step 3: Export to Excel

1. At the top of the report window, click Excel → Create New Worksheet
2. Choose Create new worksheet in a new workbook
3. Click Export

Tip: If you need account number and account name in separate columns, use Excel's Text-to-Columns feature on the Account column with "·" as the delimiter.

Step 4: Memorize the Report for Future Use

Once your report is customized exactly how you want it:

1. With the report open, click Memorize at the top of the report window (or press Ctrl + M)
2. Enter a clear, descriptive name that reflects what's in the report — for example, "Transaction Detail - Acct & Class" or "Vendor Expense Detail by Department"
3. Check Save in Memorized Report Group to organize it under an existing group (like "Accountant") or create a new group (like "Monthly Extracts")
4. If multiple team members use the file, check Share this report template with others
5. Click OK

Setting Up a Relative Date Range

Before memorizing, set the date range to a relative period rather than specific dates so the report auto-updates each time you open it:

1. On the Display tab, click the Dates dropdown
2. Choose a relative option like Last Month, This Month-to-date, or Last Fiscal Quarter
3. Then memorize the report

Step 5: Re-Running a Memorized Report

To run your memorized report later:

1. Go to Reports → Memorized Reports → [your group] → [your report name]
2. The report opens with all your column choices and settings intact
3. Adjust the date range if needed and re-export to Excel

Editing a Memorized Report

To update an existing memorized report:

1. Open the memorized report
2. Make your changes
3. Click Memorize (or press Ctrl + M)
4. When prompted, choose Replace to update in place, or New to save as a separate report

Optional: Schedule the Report to Email Automatically

You can have QuickBooks Desktop email this report on a recurring basis.

1. Go to Reports → Scheduled Reports → Schedule Setup
2. Follow the prompts to select your memorized report, set the schedule, and add recipients

Requirements:

• Outlook must be integrated with QuickBooks Desktop
• You must be in single-user mode when setting up the schedule
• The company file must be open at the scheduled time for the email to send

Troubleshooting

The Class column option isn't showing. Class Tracking isn't enabled. Go to Edit → Preferences → Accounting → Company Preferences and check Use class tracking for transactions.

Account numbers aren't appearing in the Account column. Account numbers aren't enabled globally. Go to Edit → Preferences → Accounting → Company Preferences and check Use account numbers.

Historical transactions don't have a Class (or other dimension) assigned. Dimensions like Class only apply to transactions entered after the feature was turned on. You'll need to manually edit older transactions to add the value, or accept that they'll show as blank in that column.

I see "Name" but I want vendor and customer in separate columns. QBD stores both vendors and customers in a single Name field. To separate them, export to Excel and use a VLOOKUP or XLOOKUP against your vendor and customer lists to add a "Name Type" column identifying each entry.

My custom field isn't appearing as a column option. The custom field may not be enabled for use on transactions. Go to Lists → Customer & Vendor Profile Lists (or open a customer/vendor/item record), edit the custom field definition, and confirm it's set to appear on the relevant transaction types.

The Item column is blank on many transactions. The Item column only populates for item-based transactions (invoices, sales receipts, bills with items, etc.). Journal entries, checks coded directly to accounts, and other non-item transactions will show blank.

The memorized report isn't visible to other users. The report wasn't shared. Open the report, click Memorize, check Share this report template with others, and choose Replace when prompted.

If you have any questions or run into issues, please contact our support team.