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.
