How to build Open Sales Order Report in QuickBooks Enterprise Advanced Reporting

Open Sales Report QuickBooks Advanced Reporting

Did you ever need:

1.       To see the combined view of “Open Sales Order report by Customer” and “Open Sales Order report by Item.”?

2.       To group the open sales order either by a sales rep or any custom fields or year or month?

The QuickBooks standard open sales order reports cannot do these for you out of the box.

But, here is good news for the Enterprise users. Intuit has provided a fantastic feature for enterprise users. The feature is Advanced Reporting. And, this blog will explain the step by step process to build Open Sales Report using this feature.

You can find the QuickBooks Advanced Reporting under the Reports menu.

Advanced Reporting Menu
Advanced Reporting Menu

If you have not used this feature before, then QuickBooks will download it and install it, when you click on it.  After it has installed Advanced Reporting, then it will start extracting data from QuickBooks and build the reporting data model. Once, it has made the reporting data model; then it is ready.

So let’s start.

Open Advanced Reporting, if it the first time then wait until it is ready if it is not the first time then you can choose to update data or skip it.

Update or Skip

Whether you decide to skip or update, the QuickBooks Advanced Reporting will open a Welcome window before you can start working with it.


Welcome Center
Welcome Center

 Close this window too.

You are now on this screen.

Start From Scratch
Start From Scratch

Click on the “Current Selection” in the left-hand Library section and your window should like this now.

Current Selection
Current Selection

The “Current Selection” will help to clear out the applied filters.

Next, do the right click on the white area and click “Chart” as shown in the image below.

Right-Click Chart Menu
Right-Click Chart Menu

You should see this window shown below.

Choose the pivot table (shown as an enlarged icon on the image).

Select Pivot Table
Select Pivot Table

Click Next>.

The dimension tab will open up.

Dimension Tab
Dimension Tab

We will choose three dimensions from the left-hand “Available Fields/Groups” for this report.

The “Used Dimensions” box is showing all selected dimensions.

Selected Dimensions
Selected Dimensions

Check “Suppress When Value is Null” for all dimensions.

Also, give a name to all selected dimensions in the “Label” field.

Click the “Next>” button.

The expression window will show up.

The first expression is to show the sales order amount.

Expression For Sales Amount
Expression For Sales Amount


Here is the text that you can copy:

sum({<[Transactions.Txn Type]={‘Sales Order’}, [Transactions.Header Or Line]={‘Line’}, Transactions.Invoiced={0}>}Transactions.Amount)

Press Ok, and you should see this window.

Expression Window
Expression Window


Type ‘Amount’ in the Label field.

Expression with Label
Expression with Label

Then click on the “Add” button to add the expression for the Quantity.

A new expression window will show up.

Now type the following expression in there.

sum({<[Transactions.Txn Type]={‘Sales Order’}, [Transactions.Header Or Line]={‘Line’}, Transactions.Invoiced={0}>}Transactions.Quantity)


Expression For Sales Quantity
Expression For Sales Quantity

Press Ok and give a label “Quantity” to this expression.

Quantity Label
Quantity Label

Click “Next>” five times and you will be on the “Numbers” tab.

Select Money for Amount

Select “Money” for Amount and “Fixed to” with 2 Decimals for the Quantity expression as shown below.

Select Fixed for Quantity
Select Fixed for Quantity

Click “Finish,” and you should have the following chart.

Report is ready
Report is ready

Right now report is in collapsed form.

Now, either click on each “+” sign to expand the individual lines or you can right click and select “Expand all.” as shown in the image below.

Expand All
Expand All

Once you have expanded both Customer and Ref#, then your chart should look like this.

Expanded Report
Expanded Report

The subtotals are missing so let’s add them.

Do right click on the chart and select “Properties” and go to the “Presentation” Tab.

Select “Show Partial Sums” for all three (Customer, Ref#, Item) dimensions.

Presentation tab
Presentation tab

Click Ok, and your chart should look like this.

Report with Subtotals
Report with Subtotals

So, this is your chart Open Sales Order by Customer and Item.

Now If you need you can add as many dimensions as you want.

Just right-click the chart and open properties dialog and go the dimensions tab and choose the required dimensions like Year, Month, Sales Reps or Custom fields, etc.

Please watch this video to see all these steps again.

Expression variable (vExprPreviousNMonths) in QuickBooks Enterprise Advanced Reporting

vExprPreviousNMonths QuickBooks Advanced Reporting

The QuickBooks Advanced Reporting has many handy expression variables. Most of these variables are for dates.

This blog discusses vExprPreviousNMonths.

This expression variable is defined as

[Period ID] = {$(=Max([Period ID]) – $1)},Year=,Quarter=,[Quarter ID]=Month= ,Period= ,Week=,Day= ,[Transaction.Txn Date] =,[Month Year]=,[Quarter Year]=

Where to use it:

This expression variable is very helpful for a monthly sales report.

Here are expressions for the last two months of the current year and for the same months from the previous 2 years.

Expressions for the last two months of the current year:

sum({<$(vExprSales),$(vExprPreviousNMonths(0))>}[Transactions.Amount With Sign])

sum({<$(vExprSales),$(vExprPreviousNMonths(1))>}[Transactions.Amount With Sign])

Expressions for the last two months of the previous year:

sum({<$(vExprSales),$(vExprPreviousNMonths(11))>}[Transactions.Amount With Sign])

sum({<$(vExprSales),$(vExprPreviousNMonths(12))>}[Transactions.Amount With Sign])

Expressions for the last two months of the previous to previous year:

sum({<$(vExprSales),$(vExprPreviousNMonths(22))>}[Transactions.Amount With Sign])

sum({<$(vExprSales),$(vExprPreviousNMonths(23))>}[Transactions.Amount With Sign])


So, what is the variable in all these expressions?

It is these number 0, 1, 11, 12, etc. which is the parameter to the expression variable.

This number can be derived by subtracting 1 from the nth position of the month from the current month.

For example;

If the current month is July 2018 then the number that will be used as the parameter will for Nov 2016 be calculated as follows:

The November 2016 is at the 20th position from the current month. So, the number to use in the expression is 19.

sum({<$(vExprSales),$(vExprPreviousNMonths(19))>}[Transactions.Amount With Sign])

The best thing about this expression variable is that it takes care of change of the year.

Other usages:

You can use this expression to compare the sales between two months.

For instance, the below expression can be used to find the difference of sales amount between the current month and same month last year.

sum({<$(vExprSales),$(vExprPreviousNMonths(0))>}[Transactions.Amount With Sign]) –
sum({<$(vExprSales),$(vExprPreviousNMonths(11))>}[Transactions.Amount With Sign])

The expression for a label:

There is related expression variable to be used in the label.

It is vTxtMonthYear.

You can use the variable in the Label like

=$(vTxtMonthYear(0))  for the current month.

=$(vTxtMonthYear(1))  for the previous month and so on.


Expression variables (vDayRangeFilter) in QuickBooks Enterprise Advanced Reporting

vDayRangeFilter QuickBooks Advanced Reporting

The QuickBooks Advanced Reporting has a collection of convenient expression variables.

This blog discusses about one of them


It is defined as

[Transactions.Txn Date]={“>=$(=Date(Max([Transactions.Txn Date])-$1))<=$(=Date(Max([Transactions.Txn Date])-$2))”},Year = ,Quarter = ,Month = ,Period  = ,Week    = ,Day = ,[Period ID] =

Where to use it:

This expression variable is used to show amount or quantity between two different number of days.

The following expression shows sales amount and quantity between 50th and 60th days.

sum({<$(vExprSales),$(vDayRangeFilter(60,50))>}[Transactions.Amount With Sign])

sum({<$(vExprSales),$(vDayRangeFilter(60,50))>}[Transactions.Quantity With Sign])

Few things to note here:

  • The number of days calculation is always done from the max transaction date of your company file and not from today’s date.
  • The large number always goes as the first parameter to this expression.

The vDayRangeFilter is the mother of all date range-based expression variables because it can be used for any date range like monthly, quarterly, YTD or anything else.

For example,

The following expressions are for last 60 days of the current and for the same period last year respectively:

sum({<$(vExprSales),$(vDayRangeFilter(60,0))>}[Transactions.Amount With Sign])

Just add 365 to parameter values to get the values for the last year:

sum({<$(vExprSales),$(vDayRangeFilter(455,365))>}[Transactions.Amount With Sign])

Let us know if you have any questions.