SQL Tables & Views

Junction BI™ builds tables, master tables, and views in the Repository, all in SQL format.

An SQL table is a “column and row” type structure to hold data, similar to an Excel spreadsheet.

A view is a set of instructions to SQL that retrieves data from one or more tables, for presentation to a destination, such as Excel.

Views are built by Junction BI™ to combine multiple tables in a presentation format.  For example, invoice summary rows are combined with invoice detail items and inventory items in the view vInvoiceItems.

The master table mGeneralLedger combines different transaction types, accumulated by account or class.  For example, multiple transaction types are combined to create a P&L view or a Balance Sheet view.  Queries from these views can be either totaled in SQL or exported to external reporting software.

Primary Views

The following primary views are based on the master table mGeneralLedger:

View vPLByAccount provides transactions used for P&L queries. Summaries for desired periods are obtained by summing the values in this view and grouping by account. See the file SamplePandLQuery.txt for examples.

View vPLByClass provides transactions for P&L queries by class.

View vBalanceSheet provides transactions for balance sheet queries. See the file SampleBalanceSheetQueries.txt for examples.

View vCashFlow0 provides a cash-flow analysis and forecast with a 0-day delay. See also the vCashFlow15 view for a 15 day delay on AR, and the vCashFlow1515 view for a 15 day delay on both AR and AP. The file SampleCashFlowQueries.txt provides examples.

Disclaimer

The publisher of Junction BI™, Junction BI, LLC, advises that if you are not familiar with the QuickBooks data schema, you should deal only with views supplied with Junction BI™. If you create your own views or modify the supplied views, we cannot guarantee their accuracy.

About Table and View Names

Table and view names differ between Zeus BI and Excel. The tables built by Junction BI™ correspond to the source QuickBooks tables, but some special tables are also added:

  • Junction Master Tables, which have the prefix “dbo.m”.
  • Junction Control Tables, which have the prefix “dbo.z”.

The prefix “dbo.” is not displayed in Excel.

In Excel, all view names begin with "v". Internal-use tables begin with "z". These are tables built and maintained by Junction BI™, and are not directly used by users.

Note that by default these views show all the data, and are not limited by time period.

To limit the data displayed to a given time period, such as a year, month or day, locate the column in the view containing an appropriate date (often the txndate column), and filter it: Uncheck the “All” box, and check the box for the desired time period.

SQL Views

View vBalanceSheet - Transaction level data for balance sheet queries. See the file SampleBalanceSheetQueries.txt for examples.

View vBillItems - Detailed invoices, some of which may have been paid, and some of which may be outstanding.

View vBillPaymentCheckItems - Check payments for billed items.

View vBillPaymentCreditCardItems - Credit card payments for items, purchased or paid.

View vCashFlow0 - Provides a detailed cash-flow analysis and forecast with a 0-day delay. The file SampleCashFlowQueries.txt provides examples.

View vCashFlow15 - Similar to vCashFlow0, but provides a view for a 15 day delay on AR.

View vCashFlow1515 - Similar to vCashFlow0, but provides a view for a 15 day delay on AR and a 15 day delay on AP.

View vCheckItems - Checks posted, with detail.

View vClasses - The list of classes as defined in the Company File.

View vCreditCardChargeItems - Credit card charge posted, summarized by charge.

View vCreditCardCreditItems - Credit card credits posted, summarized by charge.

View vCreditMemoItems - Credit memos posted, with header and detail.

View vCustomers - The list of customers defined in the Company File.

View vDepositItems - Deposits posted, with header and detail.

View vInventoryAdjustmentItems - Inventory adjustments posted, with header and detail.

View vInvoiceItems - Invoices posted, with header and detail.

View vItemReceiptItems - Items received into inventory posted, showing both expense and items received.

View vItems - The list of inventory items defined in the Company File, including the following items types: service, inventory, non-inventory, fixed assets, and others.

View vJournalItems - Journal entries posted, with header and detail.

View vPLByAccount  - Transactions used for P&L queries. Summaries for desired periods are obtained by summing the values in this view and grouping by account. See the file SamplePandLQuery.txt for examples.

View vPLByClass - Transactions for P&L queries by class.

View vReceivedPaymentItems  - Received payments posted, with header and detail.

View vVendorCreditItems - Credits earned or taken against vendor invoices, posted, with header and detail.