Grand Total Needed

I have two subreports (Revenue and Expense) that each have subtotals.

This works just fine, but I need a Grand Total that would sum the two subtotals.

How would I go about doing this?

So it looks something like this:

Revenue Report (matrix)

Subtotal

Expense Report (matrix)

Subtotal

<~~~~ Need Grand Total here.

Greg,

It would be difficult across two data regions, myself, I create Calculated GrandTotal field in my dataset and then use that field for display.

Ham

|||

Ok, so say DataSet1 has Total1 and DataSet2 has Total2.

How would I sum the two and display the result at the end of the second report?

|||

Greg,

This should work for you.

Sum(Fields!Counter.Value, "DataSet1")+ Sum(Fields!Counter2.Value, "DataSet2")

Ham

|||

I'm not quite sure how to implement that since my revenue and expense report have a column for each month in the current year.

Maybe I should draw it out a little better:

Revenue Report(matrix)

Jan Feb Mar .........

Account 1 $100 $200

Account 2 $50 $300

Subtotal $150 $500

Expense Report (matrix)

Jan Feb Mar .........

Account 1 $100 $100

Account 2 $0 $300

Subtotal $100 $400

Grand Total $50 $100

I need the Grand Total for each month (subtract Expense subtotal from Revenue subtotal)

|||

Greg,

You will need to use subtraction instead of addition. I was able to get resume for my calculated fields with 2 matrix reports.

Sum(Fields!Income1.Value, "DataSet1") - Sum(Fields!Income1.Value, "DataSet1")

Calculated fields value :=Fields!Income.Value and Income

Calculated fields value :=Fields!Income.Value and Income2

Ham

|||Where would I need to place that code so that it will give the grand total for each month?|||

Greg,

I added a textbox to the bottom of my 2 matrix to make my Grand total align correctly.

Ham

|||

Greg,

Is there a reason why you need 2 matrices? Could you have used 1 Matrix and then selected Expense type field to distingish what totals were being calculated. It much easlier to calculated within a Data Region and to calculated across data regions.

Just thought I would ask.

Ham

|||I used two matrices because the SQL statement to combine Revenue with Expense would be too complex.|||

Could you use a UNION ALL statement with the 2 dataset you are now using. You then could group by expenses, expense type

That would get you the subtotal expense type

and the Grand Total expenses.

|||I'll try it and let you know. Thank you for all of the help by the way!|||

I see that other people in the forums are using UNION ALL. But when I put UNION ALL between the two SQL queries I have, "ALL" is not recognised as a keyword. I get a SQL error near UNION.

I tried the same in SQL 2005 itself and received the same error.

select BLAH,BLAH,BLAH
FROM BLAH,BLAH

WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH
ORDER BY BLAH

UNION ALL

select BLAH,BLAH,BLAH

FROM BLAH,BLAH
WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH

I get a SQL error: incorrect syntax near the keyword 'UNION'. Both of these queries work fine individually.

|||

Greg,

Your order and group by use by like to following:

select BLAH,BLAH,BLAH
FROM BLAH,BLAH

WHERE BLAH AND BLAH AND BLAH

UNION ALL
select BLAH,BLAH,BLAH

FROM BLAH,BLAH
WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH
ORDER BY BLAH

|||

GregSQL wrote:

Revenue Report

Jan Feb Mar .........

Account 1 $100 $200

Account 2 $50 $300

Subtotal $150 $500

Expense Report

Jan Feb Mar .........

Account 1 $100 $100

Account 2 $0 $300

Subtotal $100 $400

Grand Total $50 $100

Ok I have the Grand Total by combining the datasets for Revenue and Expense into one dataset.

Now I'm not sure how to add the subtotals back. There should be a subtotal for Revenue and one for Expense.