QBAR

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.”

This window shows up.

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.

 

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.

 

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).

 

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])
2. The expression tab looks like this.
   
   
3. Type the below statement in the Label.
   =$(vTxtMonthYear(0))
   
   
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.”
   2. To paste; right click on the white area and click “Paste.”
5. Here is the expression tab with four same expressions and their labels.
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:
   
   

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.
   

Click “Next>” three times.

On the Numbers tab:

Select “Money” for all four expressions.

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.

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.

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.
   
5. Go to the Presentation tab and make all settings look like as shown in the image below.
   
6. Click Ok.

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

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].
   
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.
   
4. Click Ok.

Now the report is grouped into three dimensions.

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.

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.

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.

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

 

 Close this window too.

You are now on this screen.

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

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.

You should see this window shown below.

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

Click Next>.

The dimension tab will open up.

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

The “Used Dimensions” box is showing all 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.

 

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.

 

Type ‘Amount’ in the Label field.

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)

 

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

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

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

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

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.

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

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.

Click Ok, and your chart should look like this.

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.