Skip to main content
November 27, 2023
Question

Advanced Reporting revenue report Expression

  • November 27, 2023
  • 1 reply
  • 0 views

I am building a QB Advanced Reporting report in QB Enterprise in which I want to calculate some transaction-related amounts for tax-reporting purposes.  My firm is a manufacturer and distributor that sells many inventory items. 

 

I need the report to sum the amounts on all invoice lines during a period that meet two conditions, where each invoice line is a quantity of a product sold multiplied by its unit price in that transaction.  The two conditions are custom field values.  So, Custom Field 1 value might be either “Y” or “N” and Custom Field 2 value might also be “Y” or “N” and where those custom field values are individually set for each product we sell.  The unit price will vary by product and by invoice.

 

In plain English, the calculation might be, for example:

 

Sum all invoice lines (i.e. units sold * unit price for that item in that transaction) invoiced during a specified time period where the Custom Field 1 value for the product in that invoice line = Y and where the Custom Field 2 value for the product in that invoice line = N.  (For purposes of this example, the custom field values are chosen arbitrarily.)

 

Can you tell me the field names (other than the custom field names) to use and the syntax to use to do the necessary calculation?  I think the Expression should look something like this:

=sum ({< [Transactions.Txn Type] = {‘Invoice’} or [Transactions.Txn Type] = {‘Credit Memo’}, custom field 1 = {‘Y’}, custom field 2 ={‘N’}>} ($vExprSales{ [Transactions.Quantity with Sign] * Invoiced Unit Price})

 

Not sure.

 

As a note, a QB report in >Reports > Sales named “Sales by Items Detail” seems to use the appropriate fields for units invoiced and transaction unit price.  I have done some searching and have been unable to find the field names.

 

Many thanks for any help you can provide.

1 reply

November 28, 2023

Hello there, JeffOw.

 

I understand the importance of making a QuickBooks Advanced Report in QuickBooks Enterprise for tax-related calculations.

 

We ensure the availability of report creation information for you. To customize it, consider joining our QuickBooks Solution Provider (QSP) for assistance with data creation, field names, syntax, and associated fees for customization.

 

Put a checkmark beside Customized Reporting under the Specialized Services drop-down arrow. I've added a screenshot below for visual reference.

 

For more information, you can check this guide:

 

 

Additionally, you can visit these links to know where your business stands in terms of sales and expenses:

 

 

Feel free to visit the Community for follow-up questions about Advanced Reporting in QuickBooks. I'm here to help. Take care.