Is there a way to make a total at the bottom of the page that subtracts one subtotal from another?
Right now I just have totals which role up into a grand total that adds everything together.
I did something similar... just had to modify my expression.
So [Data].[AmmountSpent] with aggregation set on the field became an expression with...
[Data].[AmmountSpent] - [Data].[AmmountEarned] etc.
Is this what you mean?
Well basically I have a list with expenses and revenues. The controller wants both to be positive, which means that my grand total at the bottom would need to be Revenues - Expenses. I am using the sum symbol to auto subtotal YTD and budget, but I want it to look like below.
Revenues YTD Budget
Interest 500 1000
Tuition 300 600
Lab fees 100 150
Subtotal Revenues 900 1750
Expenses
Rent 500 1000
Utilities 200 400
Subtotals Expenses 700 1400
Total Operating 200 350
What is the structure on your queries and objects? Is it 1 list, 2 etc?
R.
I have 1 list
3 queries
Query 1
Query 2
Query 1
Query 3<
Query 2
When I aggregate, it only adds, i cant find a way to make the final total to subtract Expeses from Revenues.
So Query 1 is revenues, Query 2 is expenses correct?
I have to run for the day but will follow up with you tomorrow AM on this...
R.
Aha, I see what you are saying. Actually it is for two different years. I have two sets of the columns for 2 different years. Ultimately I am trying to get Totals in the footer. But when I aggregate all I get is a data item in the query that says "Total YTD" or "Total Budget", So I dont know where or how the calculations are being made.
Hi,
I suspect you could do this by adding two calculated items - call them YTD2 and Budget2.
Set the expression for YTD2 to be:
if ([Your item description] in ('Rent','Utilities')) then (0 - [YTD]) else ([YTD])
and the expression for Budget2 to be:
if ([Your item description] in ('Rent','Utilities')) then (0 - [Budget]) else ([Budget])
You could then add calculated expressions to total these in the final footing of the report:
total([YTD2] for report)
and
total([Budget2] for report)
Regards,
MF.