Simple indicator comparing last two sums of a Matrix

Question

I have the following matrix displaying datas of the last 3 days:

date     05/07/2015   06/07/20145   07/07/215
number   151          42            84

The field number in each column is calculated using this:

=Sum(Fields!number.Value)

What I want to do is a simple indicator (an Arrow going up or down) just by comparing the last two values of the matrix.

ie in this case: 84 > 42, I would have an Arrow going up.

If tomorrow I have this:

date     06/07/2015   07/07/20145   08/07/215
number   42           84            35

35 < 84, I would have an Arrow going down.

I am not sure wich value should I put in the Value property of the Indicator to get the last value and compare it to the second to last value...

Thanks for the help.

Answer 1

I'm not sure that you can reference specific indices from a column group.

If you can change the SQL and add a row number that always gives the same row numbers to the values you want to compare then you can do the following.

In this example I built a simple table, then grouped it by colGroup (which would be your dates) and added a row number based on the colGroup. NOTE: The ORDER clause on the ROW_NUMBER function is set to DESC so that no matter how many days you show, the report can always compare RowNum 1 with RowNum 2

First, create a report with a single matrix and a single dataset.

Query for Dataset:

CREATE TABLE  #t (colGroup char(1), number int)

INSERT INTO #t
VALUES 
('A', 12),('A', 13),('A', 14)
, ('B', 15),('B', 16),('B', 17)
, ('C', 18),('C', 19),('C', 20)

SELECT 
    *
    , ROW_NUMBER() OVER(ORDER BY ColGroup DESC) as RowNum
    FROM
        (
        SELECT 
            colGroup, SUM(number) as TotalNumber
          FROM #t
          GROUP BY #t.colGroup
        ) x

Now create a report containing a single matrix. Drag the colGroup field to the Columns area Drag the TotalNumber field to the Data area

Now we'll add two columns, one to show the actual difference between the last two values (for illustration only) and another to show an indicator displaying >, = or <

Right-Click the column header of the column containing [colGroup] and do "Insert column" then "Outside Group - Right"

Repeat to create two columns

In the first of the new columns set the expression to this:

=
SUM(IIF(Fields!RowNum.Value=1, Fields!TotalNumber.Value, 0))-
SUM(IIF(Fields!RowNum.Value=2, Fields!TotalNumber.Value, 0))

In the second, set the expression to this:

=
IIF(
    SUM(IIF(Fields!RowNum.Value=1, Fields!TotalNumber.Value, 0))-
    SUM(IIF(Fields!RowNum.Value=2, Fields!TotalNumber.Value, 0))>0 ,
    ">" ,
    IIF(
        SUM(IIF(Fields!RowNum.Value=1, Fields!TotalNumber.Value, 0))-
        SUM(IIF(Fields!RowNum.Value=2, Fields!TotalNumber.Value, 0))=0 ,
        "=" ,
    "<")
)

Run the report and you'll get the output you want (I hope!)

I would also suggest creating a function so you don't have to repeat al SUM(IIF.. code over and over but that's down to you.

Hope this helps.