How to Report on QuickBooks Data for Journal Entry + Journal Entry Lines or Bill + Bill Lines

When working with QuickBooks Online data, certain financial objects like Bills or Journal Entries are split into two levels:

• A summary-level object (e.g., Bill, Journal Entry)

• A corresponding line-level object (e.g., Bill Lines, Journal Entry Lines)

If you only use one of these, you’ll either miss key breakdowns or lose context for filtering and grouping. Merging ensures you get complete and accurate financial reporting.

To build accurate reports—especially for use cases like P&L by Subcategory—you need to combine these objects using a Merged Dataset.

Create Datasets for Bill, Journal Entry, and their respective Line objects

1. Navigate to the Datasets page and create new Datasets from Quickbooks.

   1. Create a Bill Dataset and another Bill Lines Dataset.

   2. Create a Journal Entry and another Journal Entry Lines Dataset.
      

      View

      Columns

      Bills

      Id, other columns like DueDate, CurrencyRef_Code, etc.

      Bill Lines

      BillId (Bill ID), other columns like Account_UnitPrice, Account_Quantity, etc.

      Journal Entries

      Id, other columns like CreatedAt, CurrencyRef_Name, ExchangeRate, PrivateNote, etc.

      Journal Entry Lines

      Journal Entry ID, other columns like AccountRef_Name, AccountRef_Value, Amount, etc.

How to merge Bill or Journal Entry with their Line objects

1. Go to Data Manager > Merged Datasets and click Merge Datasets.

2. Select the two entities to merge:

   • Bill + Bill Lines

   • Journal Entry + Journal Entry Lines

3. Set the Join Operator to Left Outer..

4. Set the Join Conditions to Id <> JournalEntryID (or BillId of using Bill Lines)

5. On the next screen, you will be prompted to select what columns to include in the merged dataset. We recommend selecting all columns.

Build your Custom Metrics

Now that your data is merged:

1. Use Metric Builder to build Custom Metrics like Amount by AccountRef_name or Subcategory Spend by Month.

2. Apply filters using fields from the summary object for greater context (e.g., filter by Vendor, Date, Transaction Type).

3. Group line-level data for deeper financial insights.

This setup ensures you're pulling all relevant line items and gives you the flexibility to build accurate P&L reporting, category tracking, and vendor-level breakdowns.

For more, visit: How to merge Datasets.