reporting-servicesssrs-tablixdivide

SSRS How to Divide row results by outside group row summary


I feel like this should be easy but I can't figure it out.

enter image description here

[Result View]

enter image description here

This is logically what I think should work:

=(ReportItems("CustCounts2").value/ReportItems("RowTotal2").value)

But the RowTotal2 is an outside group and won't render.


Solution

  • It looks like you are using a matrix, so this is based on what I think your data looks like..

    I used this to generate some sample data

    I created a dataset DataSet1 with the following as the dataset's query

    DECLARE @t table(office varchar(10), EstimateStatus varchar(10), Counts int)
    
    INSERT INTO @t VALUES
    ('Office A', 'Loss', 92),
    ('Office A', 'Win', 971),
    ('Office B', 'Loss', 50),
    ('Office B', 'Win', 500)
    
    SELECT * FROM @t
    

    I then added a matrix, added office as the row group and EstimateStatus as the column group and finally counts as the data. I then added totals for both row and column groups.

    Finally I removed the last column which was generated (as this was a total percentage column, that you did not have in your design).

    I renamed the rowgroup for clarity to GrpOffice as you can see

    The design looks like this

    enter image description here

    In the percentage column at the office level I used the following expression

    =SUM(Fields!Counts.Value)/SUM(Fields!Counts.Value, "GrpOffice")
    

    This reads get the sum of the Counts for this row (even if there is only 1) and divide it by the sum of the counts this the whole group "GrpOffice". Note this name must be in quotes and is case sensitive.

    For the total row I used a similar expression..

    =Sum(Fields!Counts.Value) / Sum(Fields!Counts.Value, "DataSet1")
    

    This reads, get the sum of counts in the current scope (total column group in this case) and divide by the sum of the counts for the entire dataset called "DataSet1"

    With a format of p2 on those cells to tidy it up, we get the following output.

    enter image description here