Hi,

As part of our QQube implementation, we are required to generate Balance sheet (and other) reports, akin to the Quickbooks report output, and not just for the 33 rollup months, but for any requested time period. We were able to generate a report on an accrual basis, but were unable to identify a field, value, or date table to use for cash, can you please point us to relevant documentation, or (even better) an example if possible, Below are our queries, which extract the GL to our own intermidiate table, followed by the function that  creates the Baalance sheet (accrual based)

Any help or suggestion you can provide is appreciated.

Thanks,

Tal

Our accrual data extract and balance sheet display process goes like this:

1. Create an extract from vf_GLDetail in a view called GetGlDetail.

USE QQ_Import

IF OBJECT_ID('dbo.GetGLDetail') IS NOT NULL
DROP VIEW dbo.GetGLDetail
GO

Create View DBO.GetGLDetail AS

SELECT
CO.[Company Name],
GLD.[GLTxn Document Master Linking Number],
GLD.[GLTxn Document Reference Number],
GLD.[GLTxn Document Current Posting State],
GLD.[GLTxn Document Entry Type],
GLD.[GLTxn Document Master Linking Txn Type],
GLD.[GLTxn Document Type],
GLD.[GLTxn Line Amount],
GLD.[GLTxn Line Amount Type],
GLD.[GLTxn Line Billable Status],
GLD.[GLTxn Line Budget Amount],
GLD.[GLTxn Line Credit Amount],
GLD.[GLTxn Line Debit Amount],
GLD.[GLTxn Line Forecast Amount],
GLD.[GLTxn Line Item Description],
GLD.[GLTxn Line Open Amount],
GLD.[GLTxn Line Paid Amount],
GLD.[GLTxn Line Paid/Open Status],
GLD.[GLTxn Line Quantity],
GLD.[GLTxn Line Rate],
GLD.[GLTxn Line Rate Percent],
From_ACCT.[Account Type] [From Account Type],
From_ACCT.[Account Posting Type] [From Account Posting Type],
From_ACCT.[Account Special Type] [From Account Special Type],
From_ACCT.[Account NAME Grouping Mechanism - 1] [From Account NAME Grouping Mechanism - 1],
From_ACCT.[Account NAME Grouping Mechanism - 2] [From Account NAME Grouping Mechanism - 2],
From_ACCT.[Account NAME Grouping Mechanism - 3] [From Account NAME Grouping Mechanism - 3],
From_ACCT.[Account NAME Grouping Mechanism - 4] [From Account NAME Grouping Mechanism - 4],
From_ACCT.[Account NAME Grouping Mechanism - 5] [From Account NAME Grouping Mechanism - 5],
From_ACCT.[Account BS Grouping - A] [From Account BS Grouping - A],
From_ACCT.[Account BS Grouping - B] [From Account BS Grouping - B],
From_ACCT.[Account BS Grouping - C] [From Account BS Grouping - C],
From_ACCT.[Account BS Grouping - D] [From Account BS Grouping - D],
From_Acct_Cash.[Cash Source Account Active] [From Cash Source Account Active],
From_Acct_Cash.[Cash Source Account Cash Flow Classification] [From Cash Source Account Cash Flow Classification],
From_Acct_Cash.[Cash Source Account Category] [From Cash Source Account Category],
To_ACCT.[Account Type] [To Account Type],
To_ACCT.[Account Posting Type] [To Account Posting Type],
To_ACCT.[Account Special Type] [To Account Special Type],
To_ACCT.[Account NAME Grouping Mechanism - 1] [To Account NAME Grouping Mechanism - 1],
To_ACCT.[Account NAME Grouping Mechanism - 2] [To Account NAME Grouping Mechanism - 2],
To_ACCT.[Account NAME Grouping Mechanism - 3] [To Account NAME Grouping Mechanism - 3],
To_ACCT.[Account NAME Grouping Mechanism - 4] [To Account NAME Grouping Mechanism - 4],
To_ACCT.[Account NAME Grouping Mechanism - 5] [To Account NAME Grouping Mechanism - 5],
To_ACCT.[Account BS Grouping - A] [To Account BS Grouping - A],
To_ACCT.[Account BS Grouping - B] [To Account BS Grouping - B],
To_ACCT.[Account BS Grouping - C] [To Account BS Grouping - C],
To_ACCT.[Account BS Grouping - D] [To Account BS Grouping - D],
To_ACCT_Cash.[Cash Source Account Active] [To Cash Source Account Active],
To_ACCT_Cash.[Cash Source Account Cash Flow Classification] [To Cash Source Account Cash Flow Classification],
To_ACCT_Cash.[Cash Source Account Category] [To Cash Source Account Category],
Classes.[Class Grouping Mechanism - 1],
Classes.[Class Grouping Mechanism - 2],
Classes.[Class Grouping Mechanism - 3],
Classes.[Class Grouping Mechanism - 4],
Classes.[Class Grouping Mechanism - 5],
Customer.[Customer First Name],
Customer.[Customer Last Name],
Customer.[Customer Name],
Customer.[Customer Billing Address State],
Vendor.[Vendor First Name],
Vendor.[Vendor Last Name],
Vendor.[Vendor Name],
Vendor.[Vendor Address State],
Employee.[Employee First Name],
Employee.[Employee Last Name],
Employee.[Employee Name],
Employee.[Employee Address State],
OtherName.[OtherName First Name],
OtherName.[OtherName Last Name],
OtherName.[OtherName Name],
OtherName.[OtherName Address State],
Job.[Job Grouping Mechanism - 1],
Job.[Job Grouping Mechanism - 2],
Job.[Job Grouping Mechanism - 3],
Job.[Job Grouping Mechanism - 4],
Job.[Job Grouping Mechanism - 5],
Item.[Item Grouping Mechanism - 1],
Item.[Item Grouping Mechanism - 2],
Item.[Item Grouping Mechanism - 3],
Item.[Item Grouping Mechanism - 4],
Item.[Item Grouping Mechanism - 5],
SourceName.[Source Name First Name],
SourceName.[Source Name Last Name],
SourceName.[Source Name Name],
PaymentMethod.[Payment Method Active],
PaymentMethod.[Payment Method Name],
PaymentMethod.[Payment Method Type],
PayrollItem.[Payroll Item Active],
PayrollItem.[Payroll Item Category],
PayrollItem.[Payroll Item Expense Account Name],
PayrollItem.[Payroll Item Expense Account Type],
PayrollItem.[Payroll Item Liability Account Name],
PayrollItem.[Payroll Item Liability Account Type],
PayrollItem.[Payroll Item Name],
PayrollItem.[Payroll Item Type],
TxnDate.[CalYr Txn Date],
TxnDate.[CalYr Txn Date Day is Weekend Day],
TxnDate.[CalYr Txn Date Day Number],
TxnDate.[CalYr Txn Date Day Number in Quarter],
TxnDate.[CalYr Txn Date Day Number in Week],
TxnDate.[CalYr Txn Date Day Number in Year],
TxnDate.[CalYr Txn Date Day of Week],
TxnDate.[CalYr Txn Date IS Last Day of Month],
TxnDate.[CalYr Txn Date IS Last Day of Quarter],
TxnDate.[CalYr Txn Date IS Last Day of Year],
TxnDate.[CalYr Txn Date Month Name],
TxnDate.[CalYr Txn Date Month Number],
TxnDate.[CalYr Txn Date Month Begin Date],
TxnDate.[CalYr Txn Date Month End Date],
TxnDate.[CalYr Txn Date Year Number],
TxnDate.[CalYr Txn Date Quarter Begin Date],
TxnDate.[CalYr Txn Date Quarter End Date],
TxnDate.[CalYr Txn Date Quarter Number]

FROM vf_GLDETAIL GLD

--Company
LEFT JOIN vd_Company CO
ON CO.LinkForCompanyID = GLD.LinkToCompanyID
--Account Links
LEFT JOIN vd_AccountSummary Sum_ACCT
ON (GLD.LinkToCompanyID = Sum_ACCT.LinkForCompanyID AND
GLD.LinkToSummaryAccountID = Sum_ACCT.LinkForSummaryAccountID)

LEFT JOIN vd_Account From_ACCT
ON (GLD.LinkToCompanyID = From_ACCT.LinkForCompanyID AND
Sum_ACCT.LinkForSummaryAccountID = From_ACCT.LinkForAccountID)

LEFT JOIN vd_AccountCashSource From_ACCT_Cash
ON (GLD.LinkToCompanyID = From_ACCT_Cash.LinkForCompanyID AND
GLD.LinkToSummaryAccountID = From_ACCT_Cash.LinkForCashSourceAccountID)

LEFT JOIN vd_Account To_ACCT
ON (GLD.LinkToCompanyID = To_ACCT.LinkForCompanyID AND
GLD.LinkToAccountID = To_ACCT.LinkForAccountID)

LEFT JOIN vd_AccountCashSource To_ACCT_Cash
ON (GLD.LinkToCompanyID = To_ACCT_Cash.LinkForCompanyID AND
GLD.LinkToAccountID = To_ACCT_Cash.LinkForCashSourceAccountID)

LEFT JOIN vd_Class Classes
ON (GLD.LinkToCompanyID = Classes.LinkForCompanyID AND
Classes.LinkForClassID = GLD.LinkToClassID)

LEFT JOIN vd_Customer Customer
ON (GLD.LinkToCompanyID = Customer.LinkForCompanyID AND
Customer.LinkForCustomerID = GLD.LinkToCustomerID)

LEFT JOIN vd_Vendor Vendor
ON (GLD.LinkToCompanyID = Vendor.LinkForCompanyID AND
Vendor.LinkForVendorID = GLD.LinkToVendorID)

LEFT JOIN vd_Employee Employee
ON (GLD.LinkToCompanyID = Employee.LinkForCompanyID AND
Employee.LinkForEmployeeID = GLD.LinkToEmployeeID)

LEFT JOIN vd_OtherName OtherName
ON (GLD.LinkToCompanyID = OtherName.LinkForCompanyID AND
OtherName.LinkForOtherNameID = GLD.LinkToOtherNameID)

LEFT JOIN vd_Job Job
ON (GLD.LinkToCompanyID = Job.LinkForCompanyID AND
Job.LinkForJobID = GLD.LinkToJobID)

LEFT JOIN vd_Item Item
ON (GLD.LinkToCompanyID = Item.LinkForCompanyID AND
Item.LinkForItemID = GLD.LinkToItemID)

LEFT JOIN vd_SourceName SourceName
ON (GLD.LinkToCompanyID = SourceName.LinkForCompanyID AND
SourceName.LinkForSourceNameID = GLD.LinkToSourceNameID)

LEFT JOIN vd_PaymentMethod PaymentMethod
ON (GLD.LinkToCompanyID = PaymentMethod.LinkForCompanyID AND
PaymentMethod.LinkForPaymentMethodID = GLD.LinkToPaymentMethodID)

LEFT JOIN vd_PayrollItem PayrollItem
ON (GLD.LinkToCompanyID = PayrollItem.LinkForCompanyID AND
PayrollItem.LinkForPayrollItemID = GLD.LinkToPayrollItemID)

LEFT JOIN vd_CalYrTxnDate TxnDate
ON TxnDate.LinkForTxnDateID = GLD.LinkToTxnDateID

GO


2. Define a table (GLDetailExtract) structually matching the view output:

IF OBJECT_ID('dbo.GLDetailExtract') IS NOT NULL
   DROP TABLE dbo.GLDetailExtract
GO

CREATE TABLE dbo.GLDetailExtract (
[Company Name] varchar(59),
[GLTxn Document Master Linking Number] int,
[GLTxn Document Reference Number] varchar(40),
[GLTxn Document Current Posting State] varchar(11),
[GLTxn Document Entry Type] varchar(14),
[GLTxn Document Master Linking Txn Type] varchar(25),
[GLTxn Document Type] varchar(24),
[GLTxn Line Amount] decimal(15,2),
[GLTxn Line Amount Type] varchar(22),
[GLTxn Line Billable Status] varchar(255),
[GLTxn Line Budget Amount] numeric(15,2),
[GLTxn Line Credit Amount] numeric(32,5),
[GLTxn Line Debit Amount] numeric(32,5),
[GLTxn Line Forecast Amount] decimal(15,2),
[GLTxn Line Item Description] varchar(4096),
[GLTxn Line Open Amount] numeric(32,9),
[GLTxn Line Paid Amount] numeric(32,10),
[GLTxn Line Paid/Open Status] varchar(5),
[GLTxn Line Quantity] numeric(32,5),
[GLTxn Line Rate] numeric(32,5),
[GLTxn Line Rate Percent] numeric(26,9),
[From Account Type] varchar(23),
[From Account Posting Type] varchar(12),
[From Account Special Type] varchar(30),
[From Account NAME Grouping Mechanism - 1] varchar(31),
[From Account NAME Grouping Mechanism - 2] varchar(31),
[From Account NAME Grouping Mechanism - 3] varchar(31),
[From Account NAME Grouping Mechanism - 4] varchar(31),
[From Account NAME Grouping Mechanism - 5] varchar(31),
[From Account BS Grouping - A] varchar(50),
[From Account BS Grouping - B] varchar(50),
[From Account BS Grouping - C] varchar(50),
[From Account BS Grouping - D] varchar(50),
[From Cash Source Account Active] varchar(12),
[From Cash Source Account Cash Flow Classification] varchar(14),
[From Cash Source Account Category] varchar(15),
[To Account Type] varchar(23),
[To Account Posting Type] varchar(12),
[To Account Special Type] varchar(30),
[To Account NAME Grouping Mechanism - 1] varchar(31),
[To Account NAME Grouping Mechanism - 2] varchar(31),
[To Account NAME Grouping Mechanism - 3] varchar(31),
[To Account NAME Grouping Mechanism - 4] varchar(31),
[To Account NAME Grouping Mechanism - 5] varchar(31),
[To Account BS Grouping - A] varchar(50),
[To Account BS Grouping - B] varchar(50),
[To Account BS Grouping - C] varchar(50),
[To Account BS Grouping - D] varchar(50),
[To Cash Source Account Active] varchar(12),
[To Cash Source Account Cash Flow Classification] varchar(14),
[To Cash Source Account Category] varchar(15),
[Class Grouping Mechanism - 1] varchar(31),
[Class Grouping Mechanism - 2] varchar(31),
[Class Grouping Mechanism - 3] varchar(31),
[Class Grouping Mechanism - 4] varchar(31),
[Class Grouping Mechanism - 5] varchar(31),
[Customer First Name] varchar(25),
[Customer Last Name] varchar(25),
[Customer Name] varchar(41),
[Customer Billing Address State] varchar(21),
[Vendor First Name] varchar(25),
[Vendor Last Name] varchar(25),
[Vendor Name] varchar(41),
[Vendor Address State] varchar(21),
[Employee First Name] varchar(25),
[Employee Last Name] varchar(25),
[Employee Name] varchar(41),
[Employee Address State] varchar(21),
[OtherName First Name] varchar(25),
[OtherName Last Name] varchar(25),
[OtherName Name] varchar(41),
[OtherName Address State] varchar(21),
[Job Grouping Mechanism - 1] varchar(41),
[Job Grouping Mechanism - 2] varchar(41),
[Job Grouping Mechanism - 3] varchar(41),
[Job Grouping Mechanism - 4] varchar(41),
[Job Grouping Mechanism - 5] varchar(41),
[Item Grouping Mechanism - 1] varchar(31),
[Item Grouping Mechanism - 2] varchar(31),
[Item Grouping Mechanism - 3] varchar(31),
[Item Grouping Mechanism - 4] varchar(31),
[Item Grouping Mechanism - 5] varchar(31),
[Source Name First Name] varchar(25),
[Source Name Last Name] varchar(25),
[Source Name Name] varchar(41),
[Payment Method Active] varchar(19),
[Payment Method Name] varchar(41),
[Payment Method Type] varchar(19),
[Payroll Item Active] varchar(17),
[Payroll Item Category] varchar(17),
[Payroll Item Expense Account Name] varchar(31),
[Payroll Item Expense Account Type] varchar(21),
[Payroll Item Liability Account Name] varchar(31),
[Payroll Item Liability Account Type] varchar(22),
[Payroll Item Name] varchar(31),
[Payroll Item Type] varchar(20),
[CalYr Txn Date] date,
[CalYr Txn Date Day is Weekend Day] smallint,
[CalYr Txn Date Day Number] int,
[CalYr Txn Date Day Number in Quarter] int,
[CalYr Txn Date Day Number in Week] int,
[CalYr Txn Date Day Number in Year] int,
[CalYr Txn Date Day of Week] varchar(10),
[CalYr Txn Date IS Last Day of Month] smallint,
[CalYr Txn Date IS Last Day of Quarter] smallint,
[CalYr Txn Date IS Last Day of Year] smallint,
[CalYr Txn Date Month Name] varchar(10),
[CalYr Txn Date Month Number] int,
[CalYr Txn Date Month Begin Date] date,
[CalYr Txn Date Month End Date] date,
[CalYr Txn Date Year Number] int,
[CalYr Txn Date Quarter Begin Date] date,
[CalYr Txn Date Quarter End Date] date,
[CalYr Txn Date Quarter Number] int

)

GO


3. Insert the results from GetGlDetail into the GLDetailExtract table.

INSERT INTO dbo.GLDetailExtract
SELECT * FROM dbo.GetGLDetail


4. Function selects from GetGLDetail and returns a BalanceSheet:
USE QQ_Import;


IF OBJECT_ID('dashboard.BalanceSheetStandard') IS NOT NULL
DROP FUNCTION dashboard.BalanceSheetStandard
GO

CREATE FUNCTION [dashboard].[BalanceSheetStandard]
(
@company AS varchar(59),
@ASOfDate DATE
)
RETURNS TABLE
AS
RETURN
(

SELECT
GLE.[Company Name],
GLE.[To Account BS Grouping - A],
GLE.[To Account BS Grouping - B],
GLE.[To Account BS Grouping - C],
GLE.[To Account BS Grouping - D],
GLE.[To Account Type],
GLE.[To Account NAME Grouping Mechanism - 1],
GLE.[To Account NAME Grouping Mechanism - 2],
SUM(GLE.[GLTxn Line Amount]) Amount

FROM GLDetailExtract GLE

WHERE
GLE.[Company Name] = @company
AND
GLE.[To Account NAME Grouping Mechanism - 1] IS NOT NULL
AND
GLE.[CalYr Txn Date] <= @ASOfDate
AND
GLE.[To Cash Source Account Category] = 'Balance Sheet'

GROUP BY GLE.[Company Name],
GLE.[To Account Type],
GLE.[To Account NAME Grouping Mechanism - 1],
GLE.[To Account NAME Grouping Mechanism - 2],
GLE.[To Account BS Grouping - A],
GLE.[To Account BS Grouping - B],
GLE.[To Account BS Grouping - C],
GLE.[To Account BS Grouping - D]

HAVING SUM(GLE.[GLTxn Line Amount]) <> 0

)

GO

We have many moving parts in this request, so I am going to make a list of items that pertain to what I see in both you request and your SQL.

1. There is no cash basis in QQube detail subjects.  Having the linked transactions will not give you the ability to reverse engineer that, because there are many times in QB where a cash basis amount is not the result of a linked transaction.  Second there are algorithms that QB uses to determine where the 'extra penny' goes - say out of a dollar one item gets .34 andf the other two get .33
2. We use summary financials for deriving PandL, Balance Sheet, Balance Sheet by Class, Cash Activity, Trial Balance Activity, Trial Balance by Class.
3. Deriving a Balance Sheet from the General Ledger Detail would require rolling up PandL into Retained Earnings.
4. Balance Sheet by Class can only be accurately obtained by the financial summaries.  This is dictated by how QuickBooks works:  QuickBooks 'calculates' the Balance Sheet by Class at run time because it does not store all of the balance sheet splits underneath the hood; it could exponentially increase the size of the QuickBooks database, to the detriment of performance, corruption issues.  We pull the Balance Sheet by Class directly as QuickBooks outputs it.
5. The CashSource Account Dimension is used for reconciling bank accounts, per the traditional beginning balance, cash activity, ending balance activity.  QuickBooks does not provide a report for this.  QuickBooks also operates using a split account that is one removed from the original transaction itself e.g. receive payment goes to undeposted funds, then undeposited funds go to the bank account.  The CashSource Account dimension for the General Ledger Detail, can show the 'final destination' of a payment on an invoice.  It is not designed for Cash Basis Accounting.
6. If you want Cash Basis for Financial Summary, then turn on the Cash Basis Preference for Reports in QuickBooks, and then QQube will retrieve the Cash Basis PandL and Balance Sheets
7. Generally there is no need to use expensive Left Outer Joins when creating manual SQL for QQube queries.  QQube is a true data warehouse, where inner joins will not filter out what only exists in the right hand table.
8. Linked Transactions require a distinct summary as a subquery approach as there can be more than one link per transaction.  If you don't the original transaction willl get repeated as many times as there are linked transactions.  QuickBooks has NO links at the line level - only transaction level.  QQube follows this procedure.