Need to highlight duplicate items within a report

[DrSmyth]

Hi, I've got a report which has several linked and several non linked items, i need to highlight which ones are and which ones aren't linked.

Basically there is a field called a <MirrorWillCod> if this field has a linked item within the report then it will appear twice, if it only appears once then it is unlinked.

I need to give each some kind of marker so people using the report can see at a glance if an item is linked or not.

I'm a bit short of ideas but i have tried count<MirrorWillCod) and then where count = 2, but this didn't work as all the counts equaled 1..

Please help!!!

 

[Subhashab]

are u having two DP and u are linking them. or what exactly you mean by linking.

 

[DrSmyth]

OK, it's a single data provider and the linked fields are in a single table.

Basically the <MirrorWillCod> is linked to something called a <WillCod> and if two <WillCod>'s are linked they have matching <MirrorWillCod>... eg table would look like this

Will Cod MirrorWillCod
1 a
2 a
3 b
4 c

In this case 1 and 2 are linked because a appears twice, but 3 and 4 are not linked because b and c occur individually

 

[Subhashab]

why don't you break on the mirrorwillcod and apply a count on willcod.
this format will be more intutive to the user.

 

[DrSmyth]

Hmm, that's a good idea, I'll give it a razz and get back to you..

Cheers

 

[pressjanssen]

I think this could help you :
Create a table with your two columns WillCod and Mirror.
Then break on the mirrorWillCod, as said by Subha, delete the unwanted lines and delete finally the mirrorWillCod column then filters or alerts:
This looks like that :

1 a -> a (filter on a or alert)
2 a a
3 b
4 c

Good luck

Yan
Personal Consulting

 

[DrSmyth]

OK, although this seems to do the trick (In as much as the items i'm looking for are highlighted by a <MirrorWillCod> spanning two <WillCod> in a break) it isn't exactly what the end user is looking for.

They've just let me in on some more info that might be useful though.

Apparently there's another crucial field which they failed to mention <PrimaryWillInd> - this has a value of Y or N. Basically if the <MirrorWillCod> has two <WillCod>'s then one <WillCod> will have an indicator of Y and one will have an indicator of N. But if the <MirrorWillCod> only has a single <WillCod> then it's indicator will be Y....

 

[DrSmyth]

OK, I've made a bit of progress on this, i basically need to create an object in the universe which highlights <willCod> where the count of <MirrorWillCod> is greater than 1...

Simple i thought... Put <WillCod> in the select statement and count(<MirrorWillCod>)>1 in the where clause...

It has to work... But it don't, why aren't things that simple...???

Think i'm gonna get a job in a garden centre....

 

[Subhashab]

Since you have a field which indicates which row is the duplicate. (Y/N field). create alert with condition <indicator> = 'N'. apply this alert to all columns you want to highlight.

 

[DrSmyth]

But this will only highlight one of the pair of matching <MirrorWIllCod>'s, the other will still have an Indicator of 'Y'

 

[Sridharan]

Have an another DP and bring the required fields with duplicates and use some sort of IS NULL to check for the duplicates and then do a Alerting.. Jus a idea that came to my mind.. I think still there are ways to do it in a much simpler way using a normal SQL.. only thing is my mind is going crazy... lemme see if i can throw some more ideas.

Sri

 

[DrSmyth]

Cheers Sri, know how you feel, must be the heat......