Create your first Velixo report for Sage Intacct

Created by Damien Zwillinger, Modified on Tue, 5 Nov, 2024 at 11:42 AM by Harry Lewis

Applies to:

Velixo NX
Sage Intacct

Pre-requisites:

Table of contents:

Overview
Add a connection
Your first formula
Get the balance of an account
Ranges and wildcards - Combine multiple accounts and locations
Filter balances by dimensions
A complete example

Overview

The first step after you open Excel is to connect your spreadsheet to your ERP. The add-in adds a new ribbon tab to Excel, titled Velixo NX:

Add a connection

Click the Connection Manager button on the Velixo NX ribbon to open Connections tab in the side panel. Then click Add.

To set up connection for Sage Intacct Report, select Sage Intacct from Type drop-down menu. Enter a unique Name for the connection (you will use this name later as you build formulas). When using username & password, Company ID, User ID and Password are also required parameters.

As you may have guessed by looking at the Connection Manager, it is possible to connect to multiple companies and multiple ERP websites from a single workbook. This allows you to easily consolidate data coming from multiple sources. Learn more in Sage Intacct integration guide

Remember credentials: If you check this box to save your credentials for the connection, it's important to realize that it is not actually saved within the spreadsheet. Rather, it is saved to your Velixo user profile, which resides in your local Microsoft office installation and is encrypted at rest.

Your first formula

Let's begin with something very simple — retrieving the name of an account. In the A1 cell, we will put an account code, in this case 60100.

In the B1 cell, we will use the SI.ACCOUNTNAME function. As you start typing, notice that the add-in provides you with information about the function and its parameters:

The first parameter for the SI.ACCOUNTNAME function is the name of the connection as it was defined in the Connection Manager - "Demo”.

The second parameter that is required by this function is the account code. In our example, the account code is in cell A1. Click on the cell and Excel automatically adds a reference to that cell in the formula.

The complete formula should look like this: =SI.ACCOUNTNAME("Demo",A1)

Press the Enter key.

Next, try to change the account code in the A1 cell, and then press enter to see an immediate update to the value of the account name cell.

All Velixo functions work in the same way.

Get the balance of an account

Let’s continue to build our worksheet and do something more useful. We’re going to get the closing balance of the Cash account (account code 10100), using the SI.CLOSINGBALANCE function.

Complete list of functions: There are many more Sage Intacct functions included in Velixo, and we invite you to explore them all when you complete this tutorial.

The SI.CLOSINGBALANCE function expects a few different parameters:

Connection - The name of the connection. In this example, it's “Demo”.
AccountGroup - In this example, leave this empty since we are specifying the exact GL account code to use. You can specify either an account group name or an account code, but not both at the same time.
AccountCode - The account for which we want the balance. The value here could be written as “10100” directly in the formula. However, the account number is already in cell A1 so let's use that as a cell reference.
StartDate - Start date of the period for the calculation. For this parameter, enter “2020-04-01” directly in the formula. While entering a date value right to the formula you are expected to follow the YYYY-MM-DD format. Don’t forget to surround the date with double-quotes.
EndDate - End date of the period for the calculation. Enter "2020-04-30". Further, in your reports you can also use a cell reference to a date.
LocationId - Enter "200" to specify a location ID, which becomes required when you are working with multi-entity company. Location ID corresponds to entity id.
ReportingBook - Enter "ACCRUAL" so you will see results based on that book's transactions.
Dimensions - In this example, we will avoid focusing on filtering by predefined dimension, so this will remain empty.
UserDefinedDimensions - In this example, we will avoid focusing on custom filtering also, so this will remain empty.

Press Enter to complete the formula. The closing balance will calculate automatically.

Since this the first time you’re calculating a value for this connection, reporting book and financial year, Velixo will connect to your ERP to retrieve the necessary data.

Ranges and wildcards - Combine multiple accounts and locations

In the previous example, you saw how to get the balance of a single account. Now, what if you want to get the total for multiple accounts? Yes, you can use the + operator to add the values, but this could result in a messy formula and sluggish calculations. There's a better way — with ranges and wildcards.

Here are some examples of what’s possible:

Range — “10010:12100” will return all the accounts between 10010 and 20100
Wildcard — “100?0” will return any account that starts with a 100 and ends with 0 (examples would be 10010 or 10070).
Excluding or subtracting specific accounts from a range — "10000:10100;-10020" would subtract the balance of account 10020 from the total.
A mix of single accounts, ranges and wildcards can be combined together by using the “;” character — One example would be “1?000;61100”.

Filter balances by dimensions

For complex reports you might want to filter balances additionally. For doing that you can use either predefined or custom dimensions.

In our next example we are going to filter balances by predefined dimensions — Customer and Department.

This is essentially the output of the SI.DIMENSIONS function, which is the preferred method for filtering by dimensions, and you may also want to directly reference cells of your workbook.

The formula would then look like this:

A complete example

By building on the formulas, tools, tips and tricks you've learned here, you should be able to build a full Trial Balance, balance sheet, or P&L using the Velixo add-in.

Example workbooks: allow us to suggest that you download and explore a sample workbook and connect it to your own ERP.

Here’s a view of the Profit and Loss example:

Download our Wiser Services Demo Sample.xlsx example file:

Download this sample file

Download

💡 Having trouble downloading the file?
Simply right-click the link and select Save link as