CODEWITHSUNDEEP
reporting-servicesdynamic-columnsssrs-expression
Sandeep T
Sandeep T

Reputation: 441

SSRS Dynamic Columns grand total required to be copied and use the same in another calculation

I am working on a matrix report where we need to calculate the Year to date variance between the last year and current year for each category group. Now I have generated the Grand total for the current year and previous year for each category. Now I need to use these grand total value in another calculation. How can I achieve this? Any help would be appreciated.

Eg: For Year 2014 for the category A Grand Total is 1000, and grand total for the same in 2015 is 1200

Here I need the values 1000 and 1200 for further calculation. Is there a method or expression to achieve the same?

There are n number of categories for an year, so each category would be having a Grand Total for each year.

sample data

          Search           Website  
Month     2014      2015   2014   2015
January   376       306    489    649
February  295       167    439    534
March     245       206    425    609
April     425       241    465    597
May       313       269    524    845
June      346       285    497    686
July      372       243    509    636
August    410       349    523    753
September 409       296    442    642
October   288       267    475    446
November  169       315 
December  101       347 
GTotal    3,749     2,629  5,450  6,397

% vs 2014           -30%          17%
% YTD vs 2014       -26%          38%

Here the grand total for the year 2014 is =3749 and the same for 2015=2629. And the formula to calculate the variance value is : (2629-3749)/3749

Upvotes: 3

Views: 1319

Answers (1)

Jonnus
Jonnus

Reputation: 3028

Ooh, that was a tricky one, but it is do-able. I assume you are doing this in a Matrix, and the dataset looks something like this

Month     Year  Category    Value
--------- ----- ----------- -----
January   2014  Search      376
January   2015  Search      306
February  2014  Search      295
February  2015  Search      167
March     2014  Search      245
March     2015  Search      206
January   2015  Website     489

It’s fairly simple then to get the layout you require to display the values per month per year. Each 'Month' is its own Row Group. Each 'Year' is its own column group, and the Years are themselves contained in a Parent Group on 'Type'. The sum of values is calculated using the expression

=Sum(Fields!Val.Value)

Adding a new row off the bottom of the matrix allows the same expression to be used to calculate the total for the group.

Now as a default, as the columns are grouped by year, only the values for that year are in scope. As we want to compare across years we need to preserve this total to be assessed by the next column. We can do this using the code behind the report.

Right click the Report Body, select Report Properties, then choose Code, and enter the following

Dim CurrentSubtractor AS Integer
Dim CurrentDivisor AS Integer

Public Function GetPrevValueForSubtraction(NewSubtractor AS Integer) AS Integer
    Dim ReturnValue AS Integer = NewSubtractor 

    if CurrentSubtractor = 0 then
        CurrentSubtractor = NewSubtractor 
    else
        ReturnValue = CurrentSubtractor 
        CurrentSubtractor = 0
    end if

    return ReturnValue
end function

Public Function GetPrevValueForDivisor(NewDivisor AS Integer) AS Integer
    Dim ReturnValue AS Integer = NewDivisor 

    if CurrentDivisor = 0 then
        CurrentDivisor = NewDivisor 
    else
        ReturnValue = CurrentDivisor 
        CurrentDivisor = 0
    end if

    return ReturnValue
end function

Ignore the fact there is the same function twice for the moment – we’ll come back to that later. The code effectively preserves either the new value passed into it as current, or resets the current value to zero. As the report renders it processes each cell one at a time, and runs the code for each cell in turn, so effectively if you just had the result of this code in the cell it would return

0, value, 0, value, 0 value…

Finally to bring this all together we need to ensure that the calculation we will use is as follows

For every alternative column
    Take the Sum of this column
    Minus the previous column value
    And divide the result by the previous column value

Note how we need to use the previous column value twice. If we used the same code snippet for both, it would evaluate it twice for each cell, resetting the value to zero for one of the times we needed to run it. Hence the requirement for two functions above

Set the code for your percentage vs 2014 to cell to 

=iif(Fields!Year.Value = MAX(Fields!Year.Value, "DataSet10"),
    (sum(Fields!Val.Value) - 
        Code.GetPrevValueForSubtraction(sum(Fields!Val.Value))) / 
        Code.GetPrevValueForDivisor(sum(Fields!Val.Value)),
    "")

This will result in a matrix laid out like this

enter image description here

And when run, it will look like this

enter image description here

Hopefully this is what you require. If not, or if you require further help please let me know and I shall try to assist further.

Upvotes: 1

Related Questions