Microsoft SSRS - show table group totals outside the tables in a separate TextBox - using Report Items

Question

I have this situation below in Microsoft SSRS 2012:

I am developing a table in which there are 3 groups - City (Innermost Child), State, Country (Parent).

I have totals for two of my groups - Country and State.

Now, our client wants us to show these group totals (State) and grand total (Country) outside the Tablix (Table data region) also but inside the Body of the Report.

I plan to put each State Total inside a separate TextBox outside the Table, but inside the Body of the Report. See below:

Country Total: $ 2,900 (okay)

Texas State Total: $1,400

Arizona State Total: $1,500

For the Country Total, which is fixed inside the Tablix TextBox, I can use Report Items.

But how do I show the State Totals - for Texas and Arizona ?

These values ($ 1400 and $ 1500) obviously cannot be captured using Report Items, since the Sales Amount varies from State to State and there are multiple States.

Is there a way to use Group Variables or something ? Please let me know.

I need to show the below data in a separate TextBox outside the Tablix:

Texas State Total: $1,400

Arizona State Total: $1,500

The report does not use any parameter. This is sort of a dashboard report.

Answer 1

I just had a similar situation. We wanted a separate "total box". I solved this by creating a second table off the same dataset and simply totaled the data a different way. The inner programmer in me wants a more elegant way to do this. But it works.

Answer 2

You can use your right tablix twice. Apply the same grouping. Now you just need one column with the total sum on top of this tablix (first row outise group). And below add following expression (next row inside group):

=Fields!State.Value & " State Total: " & Sum(Fields!SalesAmount.Value, "StateGroupingScope")

If you apply now the background color it will look like your in left picture. And if you want to put these two tablixes side by side put a rectangle in your report and in that rectangle put your two tablixes. Importent here is, you put your rectangle in the report and then drag your tablixes into the rectangle.