SSS Highlighting Row if more than 1 occurrence

I have a weekly report with multiple products due for manufacture on the same day. viz. - 

02/09/15 Product A
02/09/15 Product B
03/09/15 Product A
04/09/15 Product C
04/09/15 Product X

I need to highlight any product that appears more than once in the report. e.g. Product A

What is the best way to achieve this ?

September 2nd, 2015 2:46am

Hi Superdata,

The BackgroundColor property for the detail row

=Switch(Count(Fields!ProductName.Value, "WeekGroup") = 1, "Red"
    , Count(Fields!ProductName.Value, "WeekGroup") > 2, "Green")

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 3:02am

You need to add a count field to dataset using query below

COUNT(1) OVER (PARTITION BY ProductName) AS Cnt

and then use it in expression for background color as

IIF(val(Fields!Cnt.Value) > 1 ,"Blue","White")

If you can change dataset you need to add a parent grouping on the report based on ProductName field and use expression as below

IIF(Count(Fields!SomeField.Value,"GroupName") > 1 ,"Blue","White")

September 2nd, 2015 8:13am

Hi Milan

This doesn't work as every row is now highlighted Green.  I just want to highlight the duplicates e.g. product A

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 3:35am

Do you have a row group based on the Product column ?
September 3rd, 2015 3:51am

Hi Superdata,

You can refer to Visakh's solution, we have tested this scenario in our local environment, please refer to the following steps:

  1. Add "COUNT(1) OVER (PARTITION BY ProductName) AS Cnt" to you dataset query.
  2. Create a table and drag the field into the table.
  3. Click the field and press F4, use the following expession for BackgroundColor.
    =IIf(Fields!Cnt.Value>1,"Red","")

Thre result is like below:

If you have any other question, please feel free to ask.

Regards,
Shrek Li

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 9:00am

Hi Shrek

This works fine.  I only needed to add COUNT(1) OVER (PARTITION BY ProductName) AS Cnt to my Dataset, refresh the dataset, and then set the ProductName field (not the row) background colour to =IIF(val(Fields!Cnt.Value) > 1 ,"Light Pink","Transparent").  



September 3rd, 2015 10:29pm

Hi Superdata, 

Thanks for your post. 

I am glad to hear that your requirement has been achieved. 

Regards,
Shrek Li

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 10:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics