CODEWITHSUNDEEP
reporting-services
Camille
Camille

Reputation: 233

ssrs Summing two fields same dataset, different tablix

I am trying to sum two same fields, from two different Tablix's. Both Tablix's filter for different building names. "Mydata" is the name of my dataset.

=Sum(Fields!TotalFloorArea.Value,"Mydata") +Sum(Fields!TotalFloorArea.Value, "Mydata")

How do I reference the different Tablix?

Many thanks in advance.

Upvotes: 0

Views: 4453

Answers (3)

StevenWhite
StevenWhite

Reputation: 6024

Referring to textboxes can get messy. And there's no direct way to reference sums from other groups or tables. A good option that will work for this is to add calculated fields to your dataset. You can have one for the 1 building value and another for the other 25 buildings. The expression would look something like this:

    =IIf(Fields!BuildingName.Value = "BLDG-A", Fields!TotalFloorArea.Value, Nothing)

That way you can sum those calculated fields from anywhere in the report. You don't need to specify the scope in your Sum function, let it use the current scope for the cell like this:

    =Sum(Fields!BldgA_TotalFloorArea.Value)

Upvotes: 0

Camille
Camille

Reputation: 233

I got this to work now. I added a row Outside the group below then added Totals into Rows. Then for grand total I referenced the textboxes for example Reportitems!Textbox22 + Reportitems!Textbox23.

I'm not sure this makes sense. But now working! Just trying to work out the final formulas.

Upvotes: 0

David Tansey
David Tansey

Reputation: 6013

There is more than one way to accomplish this. One approach is to use an IIf() as part of each of your SUM() calls. Using the IIf() condition(s) you will be repeating the condition(s) used to filter your two Tablix controls.

Try something like this:

=Sum(IIf(Fields!BuildingName.Value = "BLDG-A", 
         Fields!TotalFloorArea.Value, 
         0), "Mydata") +
 Sum(IIf( Fields!BuildingName.Value = "BLDG-B", 
         Fields!TotalFloorArea.Value, 
         0), "Mydata")

I prefer to use Report Variables for this. Define a couple of report variables totalBuildingA and totalBuidlingB and use the expressions above for the individual variable's expressions:

totalBuildingA

=Sum(IIf(Fields!BuildingName.Value = "BLDG-A", 
         Fields!TotalFloorArea.Value, 
         0), "Mydata")

totalBuildingB

=Sum(IIf(Fields!BuildingName.Value = "BLDG-B", 
         Fields!TotalFloorArea.Value, 
         0), "Mydata")

Then you can add the two report variables together to get the equivalent result as the first example, (but with code that is a lot more flexibile for combining the SUMs for two or more other Building Names etc):

=Variables!totalBuildingA.Value + Variables!totalBuildingB.Value

Upvotes: 1

Related Questions