How to use the sum the value of 2 totals in different table (Reporting Services)?

Question

Hi there

In report design, I have 2 tables (Current and Proposed) the structure like this:

Current

Parameter | Value | Rate | Total

Value ...

Proposed

Parameter | Value | Rate | Total

Value ...

Each bottom of the table (Table Footer), I have something called: "Total: " which is a sum of Total field. I called these textboxes are txtbxCurrent and txtbxProposed and the format is in currency already.

This thing is running well.

But now I need to get a total of these txtbxCurrent and txtbxProposed. How do I do this? Can I take the value of this or not?

BTW .. I am using Ms SQL Server 2005 (ReportViewer - client)

Also here my SINGLE dataset looks like:

RecID | ReportView | Type | Parameter | Value | Rate | Total
1, 'Detail', CURRENT, 'Param1', 100, 0.1, 10
1, 'Detail', CURRENT, 'Param2', 200, 0.2, 10
1, 'Detail', PROPOSED, 'Param1', 100, 0.2, 20
1, 'Detail', PROPOSED, 'Param2', 200, 0.2, 20

The only I can think of is that I could append for another ReportView such as 'Ttl' but I am just wondering I could do from the current dataset.

Thanks

Answer 1

Could you create a new table that only has the totals for the third table?

ex:

Table 1 Table 2 Table 3 (only has total of Table 1 + Table 2)

You might be able to use parameters to store the totals to add as well, but a 3rd table would be pretty easy to create.

Answer 2

Why you don't use sum function? you can use from sum function for each dataset and add these values in a new textbox.

EDIT :

if txtbxCurrent = Sum(Fields!Total.Value, "CurrentDS") and txtbxProposed = Sum(Fields!Total.Value, "ProposedDS")

then you can use this :

Sum(Fields!Total.Value, "CurrentDS") + Sum(Fields!Total.Value, "ProposedDS")

Answer 3

If you're using a single dataset for both tables, how are you restricting each of them to current and proposed values respectively?

If you have a table-level filter on each table, the simplest approach might be to add a third table with no table-level filter, with a table footer row only, containing just

Sum(Fields!Total.Value)

Alternatively, it might be more efficient to rewrite the report to use a single table, grouped by ReportView, with footers containing sums of total at both group footer and table footer level.