How to customize a list box in QuickBooks Enterprise Advanced Reporting

customized list box in QuickBooks Advanced Reporting

This month’s blog is about creating a customized list box in QuickBooks Advanced Reporting. Many times, we develop the report for a specific type of entity like inventory item or customer who has an outstanding balance or just income accounts.

In such cases, we need complementary filters to go along with the reports. It does not make sense to have an item filter with all types of items when the report is only for inventory items.

I am sure you would agree.

So, let’s learn about building a customized list box.

We will show you a few examples, and it can be extended to any extent. It all depends on your needs or your imagination.

Let’s start:

Click anywhere but on an object to open the right-click menu and select “List Box.”

List Box Menu in QuickBooks Advanced Reporting

This window shows up.

New ListBox Dialog in QuickBooks Advanced Reporting

Drop the Field dropdown and scroll till the end.

Choose <Expression>

The expression window will show up.

Here we can type the expression to customize the list box.

To show only the inventory items:

if([Item.Item Type]=’InventoryItem’, [Item.Full Name])

To show inventory and assembly items:

=if(match([Item.Item Type],’InventoryItem’, ‘InventoryAssemblyItem’), [Item.Full Name])

These are simple ones and you can even find them in the library section of QuickBooks Advanced Reporting.

Let’s try little more interesting ones.

Suppose you want to show Items of one type in a comma separated string when a particular item type is selected in another list box.

 

 

To do this, first, create a simple list box for “[Item.Item Type].

Now let’s build the more exciting item list box.

Go to the “List Box” menu and choose the expression from the drop-down menu.

Type this expression:

=if (GetSelectedCount([Item.Item Type]) = 1,
 aggr(concat(DISTINCT [Item.Full Name], ‘,’), [Item.Item Type]), [Item.Full Name])

 And voila, you have your nice list box ready.

This trick can be used in many scenarios.

Let’s see one more.

Suppose we want to show only those items for a customer which has been sold over a certain amount to that customer.

Create a List box for [Transactions.Customer Full Name]

And now create the interesting list box.

Go to the “List Box” menu and choose the expression from the drop-down menu.

And Type the following in the expression.

=if(GetSelectedCount([Transactions.Customer Full Name]) = 1,
if(aggr(sum({<[Transactions.Account Type]={‘Income’}>}
[Transactions.Amount With Sign]), [Transactions.Customer Full Name],
[Transactions.Item Full Name]) > 600,[Transactions.Item Full Name] ), [Transactions.Item Full Name])

 This expression shows all items if there is no selection or more than one selection in the Customer list box. Otherwise, it will display only those items which have been bought by this customer for more than $600.

 Watch the video to see it in action.

 

Monthly Sales Report by Customer, Class & Item in QuickBooks Advanced Reporting

The monthly sales report is probably the most common and vital sales report for any business. It gives an opportunity to monitor the progress of sales goals on a consistent basis.

There are numerous templates for the monthly sales report. Each model serves a different purpose. The QuickBooks, regular sales report, provides a certain kind of template which allows to group the sales across 2 entities (One on the row and other on the column) and time periods as sub-columns.

There are indeed limitations to the customizations for the standard reports. One weakness is not to allow to add more than two entities to group the sales.

This article shows the step-by-step process to build a monthly sales report with the flexibility of adding more than 2 entities to group the sales.

So, let’s begin:

Follow these first few steps as stated here.

Select the pivot table after choosing the “Chart” from the right-click menu.

 

Choose Pivot Table
Choose Pivot Table

Click “Next>.”

On the dimension tab:

  1. Choose “[Transactions.Customer Full Name]” from the “Available Fields/Groups” list (indicated by blue arrow). Once you select it, then it shows up in the “Used Dimensions” (indicated by red arrow).
  2. Check the “Suppress When Value IS Null” (indicated by green arrow).
  3. Give a label to the dimension (indicated by orange arrow).

 

Dimension tab in QuickBooks Advanced Reporting
Dimension tab

Click “Next>.”

On the Expression tab:

  1. Type the expression as shown in the image or copy it from here:
    sum({<$(vExprSales), $(vExprPreviousNMonths(0))>} [Transactions.Amount With Sign])
    First Expression in QuickBooks Advanced Reporting
  2. The expression tab looks like this.

    Expression Tab in QuickBooks Advanced Reporting
  3. Type the below statement in the Label.
    =$(vTxtMonthYear(0))

    Expression Tab WIth Label in QuickBooks Advanced Reporting
  4. Copy and paste the same expressions as many as a number of months needed on the report.

    1. To copy; right-click on the expressions and click “Copy.”Copy Expression in QuickBooks Advanced Reporting
    2. To paste; right click on the white area and click “Paste.”Paste Expression in QuickBooks Advanced Reporting
  5. Here is the expression tab with four same expressions and their labels.Four Expressions in QuickBooks Advanced Reporting
  6.       Let’s change these expressions to represent different months.
    1. The month of the  max transaction date of the company file:
      1. Expression:
        sum({<$(vExprSales),$(vExprPreviousNMonths(0))>}[Transactions.Amount With Sign])
      2. Label:
        =$(vTxtMonthYear(0)
    2. Last month:
      1. Expression:
        sum({<$(vExprSales), $(vExprPreviousNMonths(1))>} [Transactions.Amount With Sign])
      2. Label:
        =$(vTxtMonthYear(1))
    3. Second last  month:
      1. Expression:
        sum({<$(vExprSales), $(vExprPreviousNMonths(2))>} [Transactions.Amount With Sign])
      2. Label: =$(vTxtMonthYear(2))
    4. Third last month:
      1. Expression:
        sum({<$(vExprSales), $(vExprPreviousNMonths(3))>} [Transactions.Amount With Sign])
      2. Label:
        =$(vTxtMonthYear(3))
  7. This is how the expression tab looks like:
    All Expressions in QuickBooks Adavnced Reporting

Click “Next>” two times.

On the Presentation tab:

  1. Select the Customer in the “Dimensions and Expressions” list.
  2. Check “Show Partial Sums.” This will show the subtotals.
    Presentation Tab in QuickBooks Advanced Reporting

Click “Next>” three times.

On the Numbers tab:

Select “Money” for all four expressions.

Numbers Tab in QuickBooks Advanced Reporting

Now click on “Finish.”

 The first cut of the report is ready. It shows the data for the last 4 months from the max transactions date of the company file. Since it is a sample file, so the max transactions date is Dec 2022.

First Cut of Monthly sales report in QuickBooks Advanced Reporting

This report is built on the sample file, so the months will differ than your report.

Some color settings will also differ depending on your settings.

On scrolling to the end, there should be subtotals.

Show Subtotals in QuickBooks Advanced Reporting

Now when the first cut is ready, let’s add Item as one more dimension to this report.

Add Item as a dimension:

  1. Right click on the chart and choose properties.
  2. Go to the Dimensions tab.
  3. Choose [Transactions.Item Full Name] from the right-hand lists and click on the “Add” button to add to the “Used Dimensions” list.
  4. Make other settings look like same as shown in the image below.
    Add Item dimension in QuickBooks Advanced Reporting
  5. Go to the Presentation tab and make all settings look like as shown in the image below.
    Presentation Tab in QuickBooks Advanced Reporting
  6. Click Ok.

Now the report is grouping the sales amount by customer and item both.

Second Cut on Monthly Sales Report in QuickBooks Advanced Reporting

Let’s add one more dimension [Transactions.Class Name] to the report.

Just follow the previous steps:

  1. Right click on the chart and click properties and go to the Dimensions tab to choose [Transactions.Class Name].
    Add Class Dimension in Monthly sales Report in QuickBooks Advanced Reporting
  2. This dimension is moved to the second place by clicking on the “Promote” button.
    The “Suppress When Value is Null” is not checked because a class can be null in the sample report on some transaction line items.
  3. Go to the Presentation tab and check the “Show Partial Sum” For this new dimension too.
    Class Presentation tab in QuickBooks Advanced Reporting
  4. Click Ok.

Now the report is grouped into three dimensions.

Third Cut with Class Dimension Monthly sales Report in QuickBooks Advanced Reporting

The same process can be followed to add as many dimensions as needed by your business.

You can even watch the video to learn these steps.

 

Let us know if you got any questions and comments.

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.