CODEWITHSUNDEEP
visual-studioreporting-services
onefiscus
onefiscus

Reputation: 149

Change color based on a difference of value from previous value to current value SSRS

I am trying to compare the current row value to the previous row value but only within the same group.

I have Group1 (Plant), ChildGroup1 (DeviceTag), Details Group, I have tried this code within the Fill property but it does not work, it just seems to change the color of the first row every time per group regardless of the value of the previous row.

=IIf(Fields!DeviceTag.Value <> Previous(Fields!DeviceTag.Value), "Yellow", "White")

So my data set looks like this:

Plant       DeviceTag       Description    Location

  A            Tag1            ABCD          West
               Tag1            WXYZ          West      DeviceTag Group 1
_____________________________________________
  A            Tag2            EFGH          East
               Tag2            IJKL          East      DeviceTag Group 2
               Tag2            IJKL          West

In both these DeviceTag Groups, the description changed so I would like to change the color of ABCD to yellow and EFGH to yellow but not WXYZ because it is not in the same group as EFGH. Also the Second row that says East should be Yellow since it is different than the previous West location.

In Crystal Reports you would do:

if {#ChangeCounter}=1 then nocolor else
if currentfieldvalue <> previous({DataSet.Field}) then cryellow else nocolor

Where the formula #ChangeCounter is just 1

Clear as mud??

Upvotes: 0

Views: 2170

Answers (2)

thomas
thomas

Reputation: 2642

I always find this kind of thing easier to do in the dataset query (assuming you can control the query in the dataset).

Manipulate your final dataset (the one SSRS will receive) to have another column which tells SSRS to color the cell or not. And keep your business logic in the DB query.

You could number each row (within each group) using row_number() and then join the table back into itself on the new row number column.

Something like this perhaps? I am not sure I 100% follow your cell shading logic and I am sure your dataset is larger than what you provided, but you may be able to adapt this to meet your needs.

;with Data as 
(
    select 'A' as Plant, 'Tag1' as DeviceTag, 'ABCD' as Description, 'West' as Location union all
    select 'A' as Plant, 'Tag1' as DeviceTag, 'WXYZ' as Description, 'West' as Location union all
    select 'A' as Plant, 'Tag2' as DeviceTag, 'EFGH' as Description, 'East' as Location union all
    select 'A' as Plant, 'Tag2' as DeviceTag, 'IJKL' as Description, 'East' as Location union all
    select 'A' as Plant, 'Tag2' as DeviceTag, 'IJKL' as Description, 'West' as Location
),

DataWithRowNumbers as
(
select
    *,
    row_number() over (partition by DeviceTag order by Description) as DeviceTagGroupRowNumber
from
    Data    
)

select
    a.*,
    case when a.Description != b.Description then 'Yellow' else 'Transparent' end as CellColor
from
    DataWithRowNumbers a
    left join DataWithRowNumbers b on a.DeviceTag = b.DeviceTag and a.DeviceTagGroupRowNumber = b.DeviceTagGroupRowNumber - 1

Then you can set the background in the cell (in SSRS) to an expression which will just be Fields!CellColor.Value.

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15027

You need to use the Scope parameter of the Previous function, providing the name of your ChildGroup1 (DeviceTag). e.g.

=IIf(Fields!DeviceTag.Value <> Previous(Fields!DeviceTag.Value , "ChildGroup1" ), "Yellow", "White")

Here's the doco:

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-previous-function

Upvotes: 0

Related Questions