How to access counts to calculate the difference in a SSRS matrix with column and row groups

Question

I’m building a report in Visual Studio 2017 (SSRS) and it uses a stored procedure that returns the following data:

    PRODUCT_ID  TYPE    YEAR    STATUS
    15242       01      1516    ACTIVE
    54541       02      1617    ACTIVE
    64454       01      1516    INACTIVE
    73697       02      1516    INACTIVE
    98878       03      1617    ACTIVE

I needed to get the counts per status, per year, per type, so I started building a matrix with STATUS as first column group and YEAR as its child, then, in the row group I only have TYPE. In the data fields I only have the count, so it looks like this:

            ACTIVE      INACTIVE
            1516  1617  1516  1617  
    01      1     0     1     0     
    02      0     1     1     0 
    03      0     1     0     0

My problem is the following. I want add a DIFF column (example below) that calculates the difference between the two years, but the problem is that since all is done dynamically, I don’t know how to access the text boxes with the counts to calculate the difference. I could build a stored procedure that calculates all those numbers, but it would be a slower solution since the field TYPE will grow over time.

            ACTIVE              INACTIVE
            1516  1617  DIFF    1516  1617  DIFF
    01      1      0    1       1     0     1   
    02      0      1    1       1     0     1
    03      0      1    1       0     0     0

Any help will be appreciated. Thank you guys in advance.

Hannover Fist Hannover Fist · Accepted Answer · 2017-05-22T18:18:12

I don't think you'll be able to make a matrix work the way you want without using a bunch of LookUps that would kill performance.

I would make a regular table and filter the data in the expression to separate the years. You'll have to figure out some logic based on your data to determine which year is the current and which is the last year.

You would use the same grouping for the TYPE as you do now.

Assumeing you have identified the previous can current year:

=SUM(IIF(Fields!YEAR.Value = Parameter!Current_Year.Value, 1, 0)

For the DIFF column, use

=SUM(IIF(Fields!YEAR.Value = Parameter!Current_Year.Value, 1, 0) - SUM(IIF(Fields!YEAR.Value = Parameter!Previous_Year.Value, 1, 0)

You could use a variable or a Field in your query to identify the current year - it doesn't need to be a parameter.

You could still use the matrix for your Statuses (Stati?) for the Active and Inactive.

How to access counts to calculate the difference in a SSRS matrix with column and row groups

2 Answers