CODEWITHSUNDEEP
reporting-servicesssrs-2008
Benjamin Ph
Benjamin Ph

Reputation: 23

SSRS Matrix conditional formatting

I have spent some time but can't seem to be able to accomplish this. I have this very simple matrix report and would like to highlight the Amount column if it is different from previous date. Please see below for my report designer screen and my desired output screen with 04/15/2020 highlighted since the amount is different from 04/14/2020. (sorry I circled it instead highlighting it).

Thank you in advance for any suggestions.

enter image description here

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 913

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21738

You can do this directly in SSRS but its messy and will rarely give perfect results if you are using a dynamic number of columns which is almost always the case.

You end up having to use a bit of VBA code to track the last value but if you use that in a background color expression for example it will mess things up.

There are plenty of questions just like this and most either unanswered or rely on you knowing what the date values are in advance.

NOTE: I have used a windowed function here, I think this is available in SQL 2008 but cannot be certain.

So to start I created some test data

Then I summarize this into a temp table (assuming you need to do summarize by project and date?), its here I use the windowed function to get a row number. We need this in case there are gaps in the dates.

Finally I join the temp table back to itself offsetting by 1 Row (using the row number)

Here's the full code I used in my dataset.

-- create some sample data
DECLARE @t TABLE(dt date, project varchar(10), amount float)
INSERT INTO @t VALUES
('2020-04-01', 'A', 10),('2020-04-01', 'A', 10),('2020-04-01', 'B', 10),('2020-04-01', 'C', 10),('2020-04-01', 'C', 10),
('2020-04-02', 'A', 20),('2020-04-02', 'A', 20),('2020-04-02', 'B', 10),('2020-04-02', 'C', 20),('2020-04-02', 'C', 20),
('2020-04-04', 'A', 25),('2020-04-04', 'A', 15),('2020-04-04', 'B', 10),('2020-04-04', 'C', 25),('2020-04-04', 'C', 25)

-- summarise and add a row number
SELECT project, dt, SUM(amount) as amount , ROW_NUMBER() OVER(PARTITION BY project ORDER BY dt) as RowN
    into #x 
    FROM @t 
    GROUP BY project, dt 

-- join #x to itself offseting by 1 row and calc diff vs previous amount
SELECT 
        cur.*
       , cur.amount - ISNULL(prv.amount, cur.amount) as diff -- if there is no previous amount compare to current amount to difference is zero
    FROM #x cur 
        LEFT JOIN #x prv
            ON cur.project = prv.project
            and cur.RowN = prv.RowN + 1

This gives us the following results...

enter image description here

Now allwe have to do is use this in our matrix and set the BackgroundColor property of the textbox to something like

=IIF(Fields!diff.Value = 0, Nothing, "#ff8c8c")

This gives us this as the final output.

enter image description here

Upvotes: 1

Related Questions