FinJinni provides its built-in reporting using Excel. If you want to access the FinJinni database directly using other applications, such as Power BI, Tableau and Crystal reports, here is some basic information:

Use a SQL Server database connection. The “FinJinni Pro Queries Manual” has two Reference chapters (here and here)  that describe the tables and columns in the database in detail. While you can access the data tables directly, FinJinni provides a few views for simplicity and clarity.

If you want to create financial views of the Profit & Loss or Balance Sheet, start with the vGeneralLedger view. You can filter by the Account_Category field or you can use one of the views already set up with a filter: vPLByAccount or vBalanceSheet. These three views also filter to Accrual basis. Add “Cash” to the end of the view name to use the Cash basis filter instead.

If you have multiple QuickBooks companies, the Company_ID field identifies which company each transaction applies to. Filter on this for single-company reporting or ignore it for all-company reporting.

These views already join in the most useful account and class information, such as Account_Description, Account_Type, Class_Name, and so on. To obtain information on other related entities, merge with one of  these views:

vItem on Item_ID = vItem.ID
vVendor on Vendor_ID = vVendor.ID
vLocation on Location_ID = vLocation.ID
vEmployee on Employee_ID = vEmployee.ID
vCustomer on Customer_ID = vCustomer.ID (see note below)
vCustJob on Job_ID = vCustJob.ID

For customers, the vGeneralLedger view separates the top-most customer from sub-customers, which can also be jobs or projects in QuickBooks. The CustJob_ID field references the original customer or sub-customer on the transaction. The Customer_ID field always references the top-level customer. The Job_ID field references the job or project when a sub-customer is used; this field is null when a sub-customer is not used. The only difference between the vCustomer and vCustJob views are the column names which start with “Customer” or “Job” respectively.

There are additional views with plural names, e.g. vCustomers, that are similar to the above but don’t have the name prefix.

Be aware of how FinJinni handles “Roll Over” at the end of each year. FinJinni creates Journal Entry transactions with a date 12/31 and time 23:59:59 to handle two cases:

1. All balances in P&L accounts are subtracted from the P&L and added to Retained Earnings. Because of the time 23:59, a P&L ranging from 1/1 to 12/31 only will not include these and show the proper P&L balance for the entire year. This is due to the fact that time is not present in the QuickBooks transaction date and defaults to 00:00 for all transactions in a day.

2. All balances in Balance Sheet accounts are subtracted and re-added as of 1/1. This allows for balances to be created using a start date of 1/1 for each year. That is, adding together all transactions from 1/1 to any date in a year will give you the balance as of that date.

These transactions have a “IsRollOver” column set to “1” so that you can easily filter them if needed, e.g. if you are looking at balance sheet activity only and don’t want to include the annual starting balance.