Flagging repeat rows and adding color

[que1983]

In a report studio query I have as an example the field
Loan Number 
1111
1112
1113
1113
1114

If loan number is repeated on the next row (in this case 1113)  I want to flag both by highlighting the background with a color yellow
Can that be done in cognos report studio in properties

[BigChris]

I'd probably try experimenting with a running-count calculation, coupled with a conditional format

[hespora]

Exactly what BigChris said:

Code Select Expand


running-count(
  [any measure you have available]
  for [Loan Number]
)


Then, format on [running-count] > 1. Do note however that this will flag different rows depending on how you have sorted your output.

[Lynn]

The difficulty with running-count is that you can only flag the second or subsequent repeated values, not the first. The first one will have a running-count of 1 so you can't tell if it is a distinct value or just the first one of two or three or more.

Since que1983 indicated that both repeated values need to be highlighted, it might be worth experimenting with the count() function. The below expression will give the count of loan numbers on the layout (1 for each row) and then total them with loan number as the scope. For the example given, this should return a value of 1 for first, second, and fifth rows while third and fourth rows would return 2.

Conditionally style for values > 1 to highlight all the duplicated rows.

Code Select Expand


total ( count ( [Loan Number]  ) for [Loan Number] )


All this assumes we are talking about a relational model.

[BigChris]

Lynn's right, I'm wrong...'nuff said.

[Lynn]

Lynn's right, I'm wrong...'nuff said.

Nothing wrong with providing multiple approaches to consider! Chances are that que1983 and other interested parties who happen across the thread will learn a couple of new things in any case.

[Lynn]

que1983 wrote in a personal message:

Conditionally style for values > 1 to highlight all the duplicated rows.


Code: [Select]

total ( count ( [Loan Number]  ) for [Loan Number] )

I used your code in a data item called count1  When I run it I get large numeric counts (not a 1 or 2) which you mentioned in your reply.  I get something like
Loan Number        count1
1111                     5999
1112                     5200
1113                     6444
1113                     6444
1114                     5688
Isnt your code supposed to assign a 1 if not repeated or a 2 if repeated.  I tried experimenting with aggregation however I get the same results

Maybe others can assist further as my time is limited today. Always better to post to the thread rather than personal messages because not everyone is free all the time and certainly others benefit by keeping conversations in the forum.

I mocked up an example using the GO Sales (query) package but your source and granularity might be different. Perhaps count distinct might be the ticket. You didn't confirm if your source is relational or dimensional - none of what I posted is applicable for dimensional.

If you solve the problem try to take a moment and post back so that others in the future with a similar problem might benefit.