Divide two specific values from a matrix

Question

I have a matrix defined in my report which looks similar to this:

SSRS Matrix

I want to add another row that is the Total value from Row 5 divided by the Total value from Row 1

As these rows are produced dynamically how can I do this?

The first column is grouped and the Total column is a SUM. I need to pick out the Total values based on the grouping column and divide the two.

Have you considered using a Group Variable to store the total and then doing your necessary calculations with that? — mr.theTrain, Sep 30 '13 at 15:44
@mr.theTrain How would I do that? I think the simplest option for me is to move the bulk of these calculations to SQL Server and keep the report quite simple. — codingbadger, Sep 30 '13 at 18:22
Not sure if this applies directly to your situation, but a good tutorial on report variables http://www.wiseowl.co.uk/blog/s283/group-variables.htm — mr.theTrain, Oct 02 '13 at 15:58

score 1 · Answer 1 · edited May 23 '17 at 12:07

Are the values of Row 1 and Row 5 constant, or will the row numbers you are calculating on always be 1 and 5?

You could use custom code to store the values in variables and then perform the calculation using those.

Create a function which takes both Col1 value and the calculated value of Col2. It will then assign Row1 to var1 and Row5 to var2. It will then return the value of Col2 for display as the Total value.

Make sense? Let me know if you need some help with the function...

EDIT

SSRS:

    Col 1     |  Col 2 |   Col 2 Expression
    England   |  201   |   =Code.SetOneFive(Count(Fields!Country.Value))
    Ireland   |  451   |   =Code.SetOneFive(Count(Fields!Country.Value))
    Scotland  |  215   |   =Code.SetOneFive(Count(Fields!Country.Value))
    Wales     |  487   |   =Code.SetOneFive(Count(Fields!Country.Value))
    Zenovia   |  2145  |   =Code.SetOneFive(Count(Fields!Country.Value))

Code:

    Public Shared Dim i as Integer = 1
    Public Shared Dim rowOne as Integer  
    Public Shared Dim rowFive as Integer

    Public Function  SetOneFive (byval _OneFive As Integer) as Integer

    If i = 1 then

             rowOne = _OneFive

        Else If i = 5 then

             rowFive = _Onefive

        End If

        i = i + 1

    End Function  

    Public Function GetRowOne () As Integer

          GetRowOne = RowOne 

    End Function

    Public Function GetRowFive () As Integer

          GetRowFive = RowFive 

    End Function

For every iteration of the code, i is increased by 1. This is checked every iteration for a value of 1 or 5.

In your total column you can then use:

    =Code.GetRowFive() / Code.GetRowOne()

Note: I haven't tested this so there could be some typos or syntactical errors, but you get the general idea.

Depending on how you use this you may want to consider not declaring the variables as 'shared':

SSRS code variable resetting on new page

They are not constant. They are dynamic and the row numbers will be based on whatever the value is is column 1. — codingbadger, Dec 18 '13 at 09:13
I'm sorry, I think I was unspecific in my question, I'll edit it to be more precise. — user3056839, Dec 18 '13 at 10:24

Divide two specific values from a matrix

1 Answers1