I'm trying to create a report that shows a manager's beginning budget in the group header, purchases against the budget in detail lines, and then ending budget balance in the group footer. The data is from a query, which includes a calculated cost value of each purchase of Expr2: Sum(Purchases!Qty*Inventory![Unit Price]).
I'm doing great on the detail lines which show each purchase, including their costs. In the group footer, I sum those costs using =Sum([Expr2]). That works. Now how do I subtract that sum from the beginning budget amount?
In the same group footer I tried using =[Budget Amt]-(Sum([Expr2])). I get an error. I tried giving a name to the sum expression of Total, and using the expression =[Budget Amt]-[Total]. Error again.
Maybe I'm going about this the wrong way? Your help would be greatly appreciated.
I'm doing great on the detail lines which show each purchase, including their costs. In the group footer, I sum those costs using =Sum([Expr2]). That works. Now how do I subtract that sum from the beginning budget amount?
In the same group footer I tried using =[Budget Amt]-(Sum([Expr2])). I get an error. I tried giving a name to the sum expression of Total, and using the expression =[Budget Amt]-[Total]. Error again.
Maybe I'm going about this the wrong way? Your help would be greatly appreciated.