Published on July 12, 2023.

A little over 3 years ago, I wrote about a NetSuite project that I had been working on that involved developing a custom financial portal. The project was for a client of mine that is an asset management firm. The portal provides summarized financial information to the firm's management team as well as their private investors, and essentially provides visibility to key summarized financial data.

That portal has evolved over the years, as my client has asked for additional reports, charts, and so on. Earlier this year, my client asked that I provide access to the firm's fiscal year budget.

In this post, I'll share a few queries that I developed to provide the portal's users with access to the budget information.

Get the IDs and Names of Fiscal Year Accounting Periods

Before we dig into the budget-related tables, let's first take a look at how we can get access to fiscal years. To query for budgets, we'll need to know what fiscal year to query for, so this is a logical place to start.

There are two easy ways to access the fiscal years.

First, you can query the AccountingPeriod table, and filter for the records that represet a fiscal year.

SELECT
	ID, 
	PeriodName AS YearName
FROM 
	AccountingPeriod 
WHERE 
	( IsYear = 'T' )

Another way to get the fiscal years - and ensure that you're only listing the years where a budget was actually setup - is to query against the Budgets table itself.

SELECT DISTINCT
	Budgets.Year AS ID,
	BUILTIN.DF( Budgets.Year ) AS YearName
FROM
	Budgets
ORDER BY
	YearName

It's the ID of the year that you're going to need in the queries that follow. In the examples shown below, I'm using ID 122, which in my test instance represents 2022.

Get Budgets for a Fiscal Year

Annual budgets are stored in a table named Budgets. Each record includes the total budgeted amount for a given year and general ledger account. It is also possible to add more granular budgets, such as the budget for a given year, GL account, as well as subsidiary, department, or any combination.

In the query below, I'm requesting the budgets for all expense accounts for fiscal year 2022.

SELECT 
	Budgets.ID AS Budget,
	BUILTIN.DF( Budgets.Year) AS BudgetYear,
	Budgets.Subsidiary AS Subsidiary,
	BUILTIN.DF( Budgets.Subsidiary ) AS SubsidiaryName,
	Budgets.Department AS Department,
	BUILTIN.DF( Budgets.Department ) AS DepartmentName,
	Budgets.Category AS Category,
	BUILTIN.DF( Budgets.Category ) AS CategoryName,
	Budgets.Account AS Account,
	BUILTIN.DF( Budgets.Account ) AS AccountName,
	Account.AcctType AS AccountType,
	Budgets.Total
FROM 
	Budgets
	INNER JOIN Account ON
		( Account.ID = Budgets.Account )
WHERE
	( Budgets.Year = 122 )
	AND ( Account.AcctType = 'Expense' )
ORDER BY
	BudgetYear,
	SubsidiaryName,
	DepartmentName,
	CategoryName,
	AccountName

You can add additional filters to the WHERE clause to narrow down the budgets that are returned. For example, you could query for a specific general ledger account, a department, a subsidiary, and so on. You have a lot of options here.

Get Budgets for A Fiscal Year With Details by Period

In this version of the query, I'm joining to the BudgetsMachine (somtimes referred to as "Budget Line" or "Budget by Period") table. BudgetsMachine is used to store period-specific budget information. For example, a record in the BudgetsMachine table might represent the budget for a GL account, and specifically for January of the fiscal year.

SELECT 
	Budgets.ID AS Budget,
	BUILTIN.DF( Budgets.Year) AS BudgetYear,
	BudgetsMachine.Period,
	BUILTIN.DF( BudgetsMachine.Period ) AS PeriodName,
	Budgets.Subsidiary AS Subsidiary,
	BUILTIN.DF( Budgets.Subsidiary ) AS SubsidiaryName,
	Budgets.Department AS Department,
	BUILTIN.DF( Budgets.Department ) AS DepartmentName,
	Budgets.Category AS Category,
	BUILTIN.DF( Budgets.Category ) AS CategoryName,
	Budgets.Account AS Account,
	BUILTIN.DF( Budgets.Account ) AS AccountName,
	Account.AcctType AS AccountType,
	BudgetsMachine.Amount
FROM 
	Budgets
	INNER JOIN Account ON
		( Account.ID = Budgets.Account )
	INNER JOIN BudgetsMachine ON
		( BudgetsMachine.Budget = Budgets.ID )
WHERE
	( Budgets.Year = 122 )
	AND ( Account.AcctType = 'Expense' )
ORDER BY
	BudgetYear,
	Period,
	SubsidiaryName,
	DepartmentName,
	CategoryName,
	AccountName

Again, you can modify this query so that it returns the detailed budget information for a specific general ledger account, a department, a subsidiary, and so on.

Permissions Needed

In order to run queries on the Budgets and BudgetsMachine tables, you'll need the "Transactions - Set Up Budgets" permission. For information about the permissions needed to query other tables, refer to SuiteQL Tables & Permissions Reference.

Wrapping Up

In a future post, I'll show how you can extend these queries to get the "actual" values, so that you can provide budgeted vs. actual reports.

If you'd like to learn more about NetSuite's support for budgets, check out Budgets in NetSuite and NetSuite Planning and Budgeting.