Add the sum of an expression at the bottom of a Colum in Microsoft report builder

Question

I have been at this for a few days off and on and I cannot figure it out. I Have a row that does some calculation based on student population. IF the school is not named "Bad school" (not the real name) then i do the sum of the SRB value devided by 4 multiplied by 2 added to the sum of the non srb value divided by 6 multiplied by 2. I get srb and non srb values from a case in my sql.

I need the sum of that row but I cannot get anything to work. I would think it would just be (sum[Table]) but that throws errors. Is ther ean easy way to do this? What I have

I just need the total of the rows What I need I can't think of how to get the total with the bad school added in as .5 I have looked at some other answers to siilar questions but mine seems to have extra layers that complicate it

Answer 1

I'm adding this answer based on the assumptions I commented above so if this does not help, please provide all the details I asked for in the comments.

I started by creating a simple dataset using the following as my dataset query.

declare @t table (school varchar(20), srb decimal (10,2), nonsrb decimal(10,2))

    INSERT INTO @t values 
    ('A', 1, 2), ('A', 2, 2), ('A', 3, 1),
    ('B', 11, 12), ('B', 12, 12), ('B', 13, 11),
    ('C', 21, 22), ('C', 22, 22), ('C', 23, 21)

    SELECT * FROM @t

I then created a new report with a simple table, added the 3 column and then added a row group called grpSchool that groups by 'school'.

I added an expression to the group rows which is essentially the same as yours. The expression was this.

=iif(
    Fields!school.Value = "B", 0.5
    , (
        (SUM(Fields!srb.Value)/4*.2)
        +(SUM(Fields!nonsrb.Value)/6*.2)
      )
     )

I then added a total row and just summed the srb and nonsrb columns, these are not required for this answer so can be ignored.

I then added the following expression to the total row.

=SUM(
    iif(
        MAX(Fields!school.Value = "B", "grpSchool")
        , 0.5
        , (
            (Sum(Fields!srb.Value, "grpSchool") / 4 * 0.2) 
            + (Sum(Fields!nonsrb.Value, "grpSchool") / 6 * 0.2)
            )
        )
    )

Basically this evaluates you original expression over the whole dataset and sums the results. However, as the we need to evaluate based on each school, I have added a context to the sums so Sum(Fields!srb.Value, "grpSchool") means the sum of srb within the context supplied, in the case the context is "grpSchool" which is the name of our row group.
Finally, we need to do the same this with 'school' but obviously can't sum this so we can just use MAX() as this will be the same for each row within each row group. We could have used MIN() to as this will always return the same value (either A, B or C depending on the group).

Now we simply sum the results of values returned.

For reference, the report design looks like this...

and the final output looks like this..