Ģ&#174&#59;€ğ&#167&#59;QĻ


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.





Re: Grand Total Needed

Hammer2


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







Re: Grand Total Needed

Greg35

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







Re: Grand Total Needed

Hammer2

Greg,

This should work for you.

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

Ham






Re: Grand Total Needed

GregSQL

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)






Re: Grand Total Needed

Hammer2

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






Re: Grand Total Needed

GregSQL

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




Re: Grand Total Needed

Hammer2

Greg,

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

Ham






Re: Grand Total Needed

Hammer2

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






Re: Grand Total Needed

GregSQL

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




Re: Grand Total Needed

Hammer2

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.






Re: Grand Total Needed

GregSQL

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




Re: Grand Total Needed

GregSQL

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.






Re: Grand Total Needed

Hammer2

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






Re: Grand Total Needed

GregSQL

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.