QuickBooks Dashboard Example for Exploring Financial Data with Bold BI

QuickBooks is accounting software offering tools for small businesses, accountants and individuals. QuickBooks doesn’t require its users to be accounting experts, thereby keeping everything simplified and easily accessible. You can track income, expenses, invoiced and accepted payments, sales, sales tax, inventory, and bills with metrics from your QuickBooks account. This blog article will walk through you how to integrate QuickBooks Online data with Bold BI dashboards and create a tracking dashboard.

If you are new to Bold BI Dashboards, sign up for a free 30-day trial on our website. At the end of the registration process, you will reach the following page view.

Before getting into the data fetch process, ensure you have a QuickBooks Online account with your company or client details to analyze.

Getting QuickBooks Online data

The QuickBooks Online Accounting API is a RESTful API through which QuickBooks companies can be accessed.

Its format looks like the following.

https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=<:select_query>

Table 1: Parameters used in the QuickBooks Online Accounting URI

:company_id	Represents a unique value assigned for each company. This will be shown at top of the quick reference guide accessible through the keyboard shortcut.
:select_query	Represents the query request through Intuit Developer SQL-like query language.

Defining required metrics for dashboard

To track income, expenses, customer receivables and vendor payables, the following metrics will be useful to showcase in the dashboard:

• Invoice – Unpaid
• Invoice – Unpaid – Due
• Sales Receipt – Paid
• Invoice – Unpaid – Overdue
• Invoice – Paid
• Accounts Receivables (A/R) Balance with due days split
• Accounts Payables (A/P) Balance with due days split
• A/R Balance by customer
• A/P Balance by vendor

In the previous list, Invoice and Sales Receipt need to be considered different modes of income.

Connecting to QuickBooks Online

1. As you are already on the homepage, click the Create Data Source button under the Data Sources icon as highlighted below. 
2. Furthermore, in the Categories drop-down list at the top, choose the category Finance.
3. Likewise, choose the QuickBooks Online connector. As a result, the Accounts dialog window opens. Consequently, window shows a list of the QuickBooks accounts in the drop-down that are already configured by you.
4. Furthermore, choose one from the list and click Continue. Certainly, if you haven’t yet, click Connect a new account and register your QuickBooks Online account to access its data from the Bold BI Dashboards application. As a result, Create Data Source window opens like the following. ]

Likewise, for tracking metrics, the following lists the data sources that need to be created and what for.

Datasource Name	Used for
BillData	Calculating accounts payable (A/P) to vendors for the purchases made.
CustomerData	Calculating accounts receivable (A/R) from customers for the goods sold.
InvoiceData	Calculating income obtained through invoices, income overdue and income expected.
PurchasedData	Calculating expenses.
SalesRecData	Calculating income obtained through sales receipts.

Creating a QuickBooks Online data source

1. Name the data source BillData.
2. In the URL section, fill the following API endpoint URL (replace the company ID in the placeholder) and click Preview & Connect.

   https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20bill

   
3. Therefore, the Choose Table(s) window opens. Furthermore, select the required columns and click Connect.
4. Consequently, the data source window opens in the dashboard designer. Also, drag and drop the table on the left to the table design view. Finally, click Save and Exit on the top right corner menu.
5. As a result, the data sources list window opens. Furthermore, click Create Data Source and create the other data sources listed previously. Also, the names and URLs for those data sources are listed in the following table.

   Data source	URL
   InvoiceData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20invoice
   PurchasedData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20purchase
   SalesRecData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20salesreceipt
   CustomerData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20customer

   

Creating a dashboard

1. Click the Home icon on the left to navigate to the homepage.
2. Select the New Dashboard tile. As a result, a blank dashboard is created and opened.
3. Select the data source icon on the right. As a result, the data source panel expands as shown in the following.
4. Furthermore, select Use Existing to open the existing data source listing in the same panel.
5. Also, choose the data sources we created in the beginning one at a time and select ADD. Finally, all five data sources get added to the dashboard and listed in the DATA SOURCES panel like in the following. 
6. Similarly, in the dashboard design view, drag these listed widgets from the toolbox to showcase required metrics.

   Metric	Widget Type
   Invoice – Unpaid	Card
   Overdue Amount	Card
   Due Amount	Card
   Invoice – Paid	Card
   Sales Receipt – Paid	Card
   Expenses	Card
   A/R Balance (by days past due)	Column Chart
   A/P Balance (by days past due)	Column Chart
   Customer Balance Overview	Grid
   Vendor Balance Overview	Grid

7. Finally, select Publish in the Save drop-down menu at the top-left to save and publish the dashboard to the server.

Editing a data source

Some of the data sources need to be edited to shape the data before showcasing it in widgets.

Editing BillData data source

1. To start with, click the edit icon near the data source BillData listed in the DATA SOURCES panel. As a result, the data source design view page opens like in the following.
2. Furthermore, select the fx icon in the toolbar to open the expression designer.
3. Also, enter the name Due Split and an expression like in the following. Consequently, this expression column is required to show the A/P to vendors split by different ranges of due days to prioritize your focus.

   IF(DAYDIFF(TODAY(),[DueDate])>=’0′,’0 or less’,IF(DAYDIFF([DueDate],TODAY()) < 31,’1 ~ 30′,IF(DAYDIFF([DueDate],TODAY()) < 61,’31 ~ 60′,IF(DAYDIFF([DueDate],TODAY()) < 91,’61 ~ 90′,’Over 90′))))

   
4. Furthermore, click Save in the expression designer.
5. Finally, click Save in the data design page to save the expression update with the data source.

Editing InvoiceData data source

1. Similarly, click the edit icon near the data source InvoiceData listed in the DATA SOURCES panel.
2. Likewise, in the data source design view page, select the filter icon in the toolbar to open the Query Filters dialog window.
3. Furthermore, select + ADD at the top-right corner. Also, from the new row item added below it, choose the DetailType (QueryResponse_Invoice_Line) column.
4. Also, in the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Click Apply.
5. Hence, click OK in the window to save the filter created.
6. In addition, select the fx icon in the toolbar, to open the expression designer.
7. Create four expression columns listed in the following table with their respective reasons. Hence, click Save for each.

   Name	Expression	Reason
   Paid	[TotalAmt]-[Balance]	To showcase the amount paid by customers from invoices.
   Overdue Amount	IF([DueDate]<TODAY(),[Balance],0)	To showcase in the card the overdue amount from invoices as part of the income.
   Due Amount	IF([DueDate]>=TODAY(),[Balance],0)	To showcase in the card the due amount from invoices as part of the income.
   Due Split	IF(DAYDIFF(TODAY(),[DueDate])>=’0′,’0 or less’,IF(DAYDIFF([DueDate],TODAY()) < 31,’1 ~ 30′,IF(DAYDIFF([DueDate],TODAY()) < 61,’31 ~ 60′,IF(DAYDIFF([DueDate],TODAY()) < 91,’61 ~ 90′,’Over 90′))))	To showcase the A/R from customers split by different ranges of due days to prioritize your focus.

8. Finally, click Save in the data design page to save the expressions update with the data source.

Editing PurchasedData data source

1. Further, open the PurchasedData data source design page like we’ve done for the other data sources.
2. Likewise, click the filter icon in the toolbar to open the Query Filters dialog window.
3. Similarly, select + ADD at the top-right corner. Certainly, from the new row item added below it, choose the DetailType column.
4. After that, in the third drop-down list showing values, check only the AccountBasedExpenseLineDetail value and uncheck all others. Hence, click Apply.
5. Furthermore, click OK in the window to save the filter created.
6. Finally, click Save in the data design page to save the filter update with the data source.

Editing SalesRecData data source

1. Further, open the SalesRecData data source design page like we’ve done for the other data sources.
2. Likewise, click the filter icon in the toolbar to open the Query Filters dialog window.
3. Similarly, select + ADD at the top-right corner. Certainly, from the new row item added below it, choose the DetailType column.
4. After that, in the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Hence, click Apply.
5. Furthermore, click OK in the window to save the filter created.
6. Finally, select Save in the data design page, to save the filter update with the data source.

Configuring widgets in a dashboard

Configure the widgets in the dashboard with corresponding data sources as described in the following table.

Widget	Data Configuration
Invoice – Unpaid	Data source: InvoiceData Actual Value: Balance Aggregation Type: Sum
Overdue Amount	Data source: InvoiceData Actual Value: Overdue Amount Aggregation Type: Sum
Due Amount	Data source: InvoiceData Actual Value: Due Amount Aggregation Type: Sum
Invoice – Paid	Data source: InvoiceData Actual Value: Paid Aggregation Type: Sum
Sales Receipt – Paid	Data source: SalesRecData Actual Value: TotalAmt Aggregation Type: Sum
Expenses	Data source: PurchasedData Actual Value: TotalAmt Aggregation Type: Sum
A/R Balance (by days past due)	Data source: InvoiceData Value(s): Balance (Sum) Column(s): Due Split
A/P Balance (by days past due)	Data source: BillData Value(s): Balance (Sum) Column(s): Due Split
Customer Balance Overview	Data source: CustomerData Column: DisplayName Balance (Sum) => with filter criteria set to greater than 0.
Vendor Balance Overview	Data source: BillData Column: name (QueryResponse_Bill_VendorRef) Balance (Max) => with filter criteria set to greater than 0.

<!-- wp:table --><!-- /wp:table -->

1. To start with, select a widget in the dashboard design area and click the gear icon (Settings) in the top-right corner. As a result, this opens the properties panel on the right.
2. Further, navigate to the ASSIGN DATA tab, select the data source in the combo box on the top. Also, start configuring the columns in their proper sections as shown in the previous table.
3. In addition, repeat this process for all the widgets placed in the dashboard.
4. Hence, save the dashboard. Finally the dashboard is ready.

Sharing the dashboard

Now, share this dashboard with your customers. With their accounts configured to use the Bold BI Dashboard platform, they can start exploring their financial data like this in order to make better decisions. We hope this article helps you build a tracking dashboard with QuickBooks Online data using the Bold BI Dashboard Platform. If you have any questions on this blog, please feel free to post them in the following comment section. Get started with Bold BI by signing up for a free 30-day trial and create more interactive business intelligence dashboards. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.