Totals at bottom

[mbrassfi]

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.

[RobSil]

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?

[mbrassfi]

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

[RobSil]

What is the structure on your queries and objects?  Is it 1 list, 2 etc?

R.

[mbrassfi]

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.

[RobSil]

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.

[mbrassfi]

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.

[MFGF]

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.