General Ledger Detail Data Model

Modified on Wed, 29 Jul at 3:02 AM

Model Summary

The QQube General Ledger Detail Data Model provides line-level detail for all posting transactions in QuickBooks.

Model Type	General Ledger transaction detail
Transaction Coverage	Posting transactions only
Available Fields	More than 1,500
Reporting Calendars	Calendar year, fiscal year, and 52/53 tax year
Primary Use	Analyze account activity, linked posting transactions, actual results, budgets, forecasts, and foreign-currency values

Best Used For

Creating traditional General Ledger detail reports.
Reviewing Balance Sheet and Profit and Loss transaction detail.
Comparing actual, budget, and forecast activity.
Reviewing related posting transactions together.
Analyzing cash activity and daily control-account balances.
Reviewing home and foreign-currency activity.

Key Capabilities

Includes all posting transaction details.
Uses a Master Linking Number and type to group related posting transactions.
Provides enhanced descriptions for applied transactions.
Places actual, budget, and forecast values side by side.
Includes debit, credit, paid, open, home-currency, and foreign-currency values.
Supports calendar, fiscal, and 52/53 tax-year reporting.

General Ledger-Specific Logic

Linked Posting Transactions

Use the Master Linking Number and type to review related posting transactions together.

The original transaction can appear with its related applied transactions directly beneath it. This method applies only to posting transactions.

See General Ledger Linked Transactions.

Enhanced Descriptions

Applied transactions include additional descriptive detail.

For example, a Receive Payment summary line can identify the invoice number and date to which it was applied. A Bill Payment Check can identify the related bill number and date.

Budget and Forecast Dates

Budget and forecast dates use the first day of the applicable period.

For example, January and February values use January 1 and February 1 as their transaction dates.

Quantity, Rate, and Amount Fields

The model contains 16 primary General Ledger fields. Native fields provide transaction quantities, rates, actual amounts, paid amounts, and open amounts. QQube field extensions add budget and forecast amounts.

Native Fields (10)

Quantity Fields	Rate Fields	Amount Fields
Line Quantity	Line Rate Line Rate Percent Line Exchange Rate	Line Amount Line Amount (Foreign) Line Debit Amount Line Credit Amount Line Paid Amount Line Open Amount

QQube Field Extensions (6)

Amount Fields
Line Budget Amount Line Budget Debit Amount Line Budget Credit Amount Line Forecast Amount Line Forecast Debit Amount Line Forecast Credit Amount

Power BI and Excel Power Pivot Measures

The prepared General Ledger Detail examples include 131 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Measures Without a Display Folder	3
.Base Measures	16
.Base Measures For Hierarchies	16
Calendar Pattern Measures	24
Current Measures	18
Previous Year Measures	36
Today Measures	9
Yesterday Measures	9

Power BI includes three company-filter support measures.

These measures restrict Account, Job, and Source Name slicers to values associated with the selected company. Excel Power Pivot handles company filtering automatically.

Included Dimensions

Calendars

52/53 Tax Year	Billed Date	Due Date
Fiscal Year	Fully Paid Date	Service Date
Transaction Date	Job End Date	Job Projected End Date
Job Start Date	More Info on Calendars

Document

Document Attributes

Line Attributes

Lists

Account	Account (Summary)	Class
Company	Currency	Customer
Customer Rep	Employee	Item
Job	Job Rep	Other Name
Payment Method	Payroll Item	Sales Tax Item
Site	Source Name	Template
Vendor

Linked Information

General Ledger Linked Transactions

Technical Details

Model Structure	One prepared FACT table with the required dimensions and relationships already included
Transaction Scope	Posting transactions only
Primary Model Fields	16 fields: 10 Native Fields and 6 QQube Field Extensions
Linked Transaction Method	Master Linking Number and type for related posting transactions
DAX Availability	131 visible measures for Power BI and Excel Power Pivot

Expected Result

Use the prepared General Ledger Detail model to analyze posting activity, linked transactions, account balances, actual results, budgets, forecasts, and foreign-currency values.

The FACT table, dimensions, relationships, Native Fields, QQube Field Extensions, and DAX measures are already included.