SSRS summing values from multiple data sets and sub reports

Question

I have a two data sets and a sub report in my SSRS master report.

I need to create a third - Grand total table or matrix that returns a field from each of the data sets and sub report.

It would look like this (to start)

To start I built a table and added a columns to test this with the following values. (This is in the Grand Total Row) each element alone is in the respective row.

=ReportItems!tps_actual4.Value + ReportItems!Textbox1230.Value

tps_actual4 comes from one dataset. Textbox1230 comes from a different dataset.

the first error that comes up reads:

"The value for the DataSetName property is missing"

if i change my code to something that reads like this

=First(ReportItems!tps_actual4.Value, "Master_Data_Set") + First(ReportItems!Textbox1230.Value, "Secondary_Data")

I get another error that reads:

The value expression for the textrun ''.. uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. 

There is no one data set we are referencing here. i'm not sure how to resolve this.

The second question is in the case of Textbox1230 it is returned 6 times as there are six lines of data. How do i specifiy in my logic a case statement when to add Textbox1230

lastly I'm not even sure how to reference the sub report as we need to access two of the fields from the sub report as well.

thank you.

Grand Total         column1 column2  
Dataset1 Values        a      x  
Dataset2 Values        b      y       … and so on
Subreport Values       c      x  
Grand Total Sums    a+b+c   x+y+z    

the above is an excel sampling hopefully it will help with the understanding -- the columns will continue the first row of data comes from one data set. The second from another etc and the Grand total adds the values together for a grand total.

Answer 1

The only thing you can do is to catch all the totals from different tablixes and summarize these values in a separate tablix. But you cant fetch values from a subreport, this works just with datasets, which are in the actual report. If you want to sum the subreport values as well I recommend to use @junketsu´s solution.

Lets say you have 2 Datasets in your report, each of them is the source of another tablix. In each tablix you need to add a total now

'Tablix 1 total
=Sum(Fields!TestValue.Value, "Dataset1")  'This expression is in Textbox1

'Tablix 2 total
=Sum(Fields!TestValue2.Value, "Dataset2")  'This expression is in Textbox2

Now add a third tablix. Write in the first textbox the folloing expression:

=ReportItems!Textbox1.Value  

In the second textbox:

=ReportItems!Textbox2.Value 

And in the third textbox:

=ReportItems!Textbox1.Value + ReportItems!Textbox2.Value

The result for the thrid tablix will look like this:

Dataset1 Total Value     11111
Dataset2 Total Value     22222
Overall Total            33333