Exploring Financial Data in QuickBooks with Syncfusion Dashboards

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 Syncfusion dashboards and create a tracking dashboard.

If you are new to Syncfusion 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 syncfusion dashboards for illustrating the quickbooks online data connection support
Homepage view in Syncfusion 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_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.

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 – Overdue
  • Invoice – Unpaid – Due
  • Invoice – Paid
  • Sales Receipt – 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, scroll down to the bottom and select View all 50+ Services.
    services view in homepage of syncfusion dashboards illustrating the quickbooks online data connection support
    Services view in homepage

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

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

    data connections under finance category in syncfusion dashboards
    Data connections under Finance category
  3. Choose the QuickBooks Online connector. Now the Accounts dialog window opens. This 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 syncfusion dashboards
    Accounts window to connect to QuickBooks Online
  4. Choose one from the list and click Continue. If you haven’t yet, click Connect a new account and register your QuickBooks Online account to access its data from the Syncfusion Dashboards application.
    sign in window for quickbooks online account in syncfusion dashboards
    Sign in window for QuickBooks Online account
    information sharing confirmation page for quickbooks online connection from syncfusion dashboards.
    Information sharing confirmation page for QuickBooks Online

    Now, Create Data Source window opens like the following.

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

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

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

    schema view of uri resultset in syncfusion dashboard platform
    Schema view of URI resultset
  4. Now, the data source window opens in the dashboard designer. Drag and drop the table on the left to the table design view. Click Save and Exit.

    design view of a data source created with quickbooks online connection with syncfusion dashboard platform
    Design view of BillData data source
  5. The data sources list window opens. Click Create Data Source and create the other data sources listed previously. 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

    data sources listing in server view in syncfusion 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 syncfusion dashboard platform
    New Dashboard tile in homepage
  2. Select the New Dashboard tile. Now, a blank dashboard is created and opened.

    a blank dashboard design view in syncfusion dashboard platform
    A blank dashboard design view
  3. Select the data source icon on the right. The data source panel expands as shown in the following.

    an empty data sources panel in syncfusion dashboard platform
    An empty DATA SOURCES panel
  4. Select Use Existing to open the existing data source listing in the same panel.

    existing data sources view in syncfusion dashboard platform
    Existing data sources view
  5. 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 syncfusion dashboard platform
    Data sources listing in DATA SOURCES panel
  6. 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. 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. Now, the data source design view page opens like in the following.

    editing view of billdata data source created from quickbooks online with syncfusion dashboard platform
    Editing view of BillData data source
  2. Select the fx icon in the toolbar to open the expression designer.
  3. Enter the name Due Split and an expression like in the following. 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 syncfusion dashboard platform
    Expression editing view of BillData data source
  4. Click Save in the expression designer.
  5. 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. In the data source design view page, select the filter icon in the toolbar to open the Query Filters dialog window.
  3. Select + ADD at the top-right corner. 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 syncfusion dashboard platform
    Query Filters view of InvoiceData data source
  4. In the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Click Apply.
  5. Click OK in the window to save the filter created.
  6. 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. 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. Click Save in the data design page to save the expressions update with the data source.

Editing PurchasedData data source

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

Editing SalesRecData data source

  1. Open the SalesRecData data source design page like we’ve done for the other data sources.
  2. Select the filter icon in the toolbar to open the Query Filters dialog window.
  3. Select + ADD at the top-right corner. From the new row item added below it, choose the DetailType column.
  4. In the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Click Apply.
  5. Click OK in the window to save the filter created.
  6. Click 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.
  1. To start with, select a widget in the dashboard design area and click the gear icon (Settings) in the top-right corner. This opens the properties panel on the right.

    widget properties view in dashboard design window in syncfusion dashboard platform
    Widget Properties View in Dashboard Design Window
  2. Navigate to the ASSIGN DATA tab, select the data source in the combo box on the top, and start configuring the columns in their proper sections as shown in the previous table.

    data configuration view in dashboard design window in syncfusion dashboard platform
    Data Configuration View in Dashboard Design Window
  3. Repeat this process for all the widgets placed in the dashboard.
  4. Save the dashboard. Finally the dashboard is ready.

    quickbooks online accounting dashboard created using syncfusion dashboard platform
    QuickBooks Online Accounting Dashboard

Sharing the dashboard

Now, share this dashboard with your customers. With their accounts configured to use the Syncfusion 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 Syncfusion Dashboard Platform. If you have any questions or require clarification, please let us know in the comments section below. You can also contact us through this contact page. The Syncfusion Dashboard Platform now comes with a 30-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!

Tags:

Share this post:

Related Posts

Leave a comment