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.