• Michael Intravartolo

Tech Tip - Variables



How do I do math with sub-totals? NAV's "Report Viewer" will allow me to generate sub-totals; but what if I want to subtract one sub-total from another to form a new sub-total?

I ran into this problem doing a Sales History-type Report, when I brought in the credit memo lines. I wanted to show a "Net Sales".

As it turns out, it is just about as easy as it could be. Since I had already set up my DataItems to include the "Amount" fields for both the Sales Invoice Lines and the Credit Memo Lines, I simply took advantage of the Expression Builder to both do the Summing-up of each of those Fields, and to do the Subtraction.

Here are the steps:

1. Create a Text Box in the Body Section of your Report Layout in Visual Studio. This will hold your "new" Sub-Total (Net Sales).

2. Right-Click in the Text Box you just created, and choose "Expression".

3. In the Expresssion Builder, choose "Sum" from the "Common Functions - Aggregate" Category List.

3a. In the "Datasets" in the Category List, DOUBLE-click on the "First(Sales_Invoice_Line_Amount.Value")

3b. Delete the word "First"

3c. At the end of the line, type a Minus sign (a "dash").

3d. Repeat Steps 3, 3a and 3b, but in 3a substitute "Sales_CR_Memo_Line_Amount.Value".

4. Click "OK" to close the Expression Builder.

Done! You will now have a "Net Sales" Sub-Total on your Report.

#microsoftdynamicsnav

0 views
Contact Us

Solution Systems, Inc.

3201 Tollview Dr. 

Rolling Meadows, IL 60008

Office: 847-590-3000

Email: info@solsyst.com

Support: 224-345-2020

Support Email: support@solsyst.com

Connect With Us
  • Manufacturing Software | Illinois
  • Manufacturing Software | Illinois
  • Manufacturing Software | Illinois
  • Manufacturing Software | Illinois
  • Manufacturing Software | Chicago
  • Manufacturing Software | Illinois
  • Microsoft Business Podcast
  • Manufacturing Software | Chicago
  • Manufacturing Software | Chicago
  • Manufacturing Software | Chicago
Join our Mailing List

© 2020 Solution Systems, Inc.