Bold BI

QuickBooks Dashboard Example for Exploring Financial Data with Bold BI

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, register for a free trial on our website. At the end of the registration process, you will reach the following page view.

homepage view in Bold BI dashboards for illustrating the quickbooks online data connection support
Homepage view in Bold BI Dashboards

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>

 

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

Table 1: Parameters used in the QuickBooks Online Accounting URI

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.
    services view in homepage of Bold BI dashboards illustrating the quickbooks online data connection support
    Services view in homepage

    Consequently, the supported data connectors get listed like in the following.

    data connections listing in Bold BI dashboards for illustrating the quickbooks online data connection support
    Data Connections listing in Bold BI Dashboards
  2. Furthermore, in the Categories drop-down list at the top, choose the category Finance.

    data connections under finance category in Bold BI dashboards
    Data connections under Finance category
  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.

    accounts window to connect to quickbooks online from Bold BI dashboards
    Accounts window to connect to QuickBooks Online
  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.
    sign in window for quickbooks online account in Bold BI dashboards
    Sign in window for QuickBooks Online account
    information sharing confirmation page for quickbooks online connection from Bold BI dashboards.
    Information sharing confirmation page for QuickBooks Online

    As a result, Create Data Source window opens like the following.

    new data source window of quickbooks online connection from Bold BI dashboard platform
    New Data source window of QuickBooks Online

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

Datasource NameUsed for
BillDataCalculating accounts payable (A/P) to vendors for the purchases made.
CustomerDataCalculating accounts receivable (A/R) from customers for the goods sold.
InvoiceDataCalculating income obtained through invoices, income overdue and income expected.
PurchasedDataCalculating expenses.
SalesRecDataCalculating 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

    search query for bill data from quickbooks online to showcase in dashboard created using Bold BI dashboard platform
    Search query for Bill data from QuickBooks Online
  3. Therefore, the Choose Table(s) window opens. Furthermore, select the required columns and click Connect.

    schema view of uri resultset in Bold BI dashboard platform
    Schema view of URI resultset
  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.

    design view of a data source created with quickbooks online connection with Bold BI dashboard platform
    Design view of BillData data source
  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 sourceURL
    InvoiceDatahttps://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20invoice
    PurchasedDatahttps://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20purchase
    SalesRecDatahttps://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20salesreceipt
    CustomerDatahttps://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20customer

    data sources listing in server view in Bold BI dashboard platform
    Data sources listing in server view

Creating a dashboard

  1. Click the Home icon on the left to navigate to the homepage.

    new dashboard tile in homepage in Bold BI dashboard platform
    New Dashboard tile in homepage
  2. Select the New Dashboard tile. As a result, a blank dashboard is created and opened.

    a blank dashboard design view in Bold BI dashboard platform
    A blank dashboard design view
  3. Select the data source icon on the right. As a result, the data source panel expands as shown in the following.

    an empty data sources panel in Bold BI dashboard platform
    An empty data sources panel
  4. Furthermore, select Use Existing to open the existing data source listing in the same panel.

    existing data sources view in Bold BI dashboard platform
    Existing data sources view
  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.

    data sources listing in data sources panel in Bold BI dashboard platform
    Data sources listing in data sources panel
  6. Similarly, in the dashboard design view, drag these listed widgets from the toolbox to showcase required metrics.
    MetricWidget Type
    Invoice – UnpaidCard
    Overdue AmountCard
    Due AmountCard
    Invoice – PaidCard
    Sales Receipt – PaidCard
    ExpensesCard
    A/R Balance (by days past due)Column Chart
    A/P Balance (by days past due)Column Chart
    Customer Balance OverviewGrid
    Vendor Balance OverviewGrid
  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.

    editing view of billdata data source created from quickbooks online with Bold BI dashboard platform
    Editing view of BillData data source
  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′))))

    expression editing view of billdata data source created from quickbooks online connection using Bold BI dashboard platform
    Expression editing view of BillData data source
  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.

    query filters view of invoicedata data source created from quickbooks online using Bold BI dashboard platform
    Query Filters view of InvoiceData data source
  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.
    NameExpressionReason
    Paid[TotalAmt]-[Balance]To showcase the amount paid by customers from invoices.
    Overdue AmountIF([DueDate]<TODAY(),[Balance],0)To showcase in the card the overdue amount from invoices as part of the income.
    Due AmountIF([DueDate]>=TODAY(),[Balance],0)To showcase in the card the due amount from invoices as part of the income.
    Due SplitIF(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.

WidgetData 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.
  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.

    widget properties view in dashboard design window in Bold BI dashboard platform
    Widget Properties View in Dashboard Design Window
  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.

    data configuration view in dashboard design window in Bold BI dashboard platform
    Data Configuration View in Dashboard Design Window
  3. In addition, repeat this process for all the widgets placed in the dashboard.
  4. Hence, save the dashboard. Finally the dashboard is ready.

    quickbooks online accounting dashboard created using Bold BI dashboard platform
    QuickBooks Online Accounting Dashboard

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. For any questions or clarifications, please post us through the comments section available below. You can also contact us by submitting your queries here or if you already have an account, please login to submit your support query. Bold BI Dashboards now comes with a 15-day free trial with no credit card information required.  Give it a try on your own and let us know what you think! We are happy to assist you. Design with a smile!

Start your free 15-day trial today

Leave a Reply