database view - get rid of duplicates

[Pastupe]

Hello

My database view is returning duplicates that I dont want. For example In metric table it finds one incident 2 times and it is then visualised in report which is confusing.

Report look for incidents where I was assigned to.

If I was assigned to more then once, report show all those results - and this is my problem - even if I was assigned to 10 times, I should be in report only once

incident - metric_instance

Is there any change to customize my view ? I currently dont know what to do. Should I somehow improve whereclause ? Do you know how ?

Please help, thank you

/Petr

[telmo]

Hi Petr,

I believe you won't be able to do it at the database view level.

You will have to do it on your report.

In this last report, why don't you "Group by" Incident Number and in the aggregation select "Count distinct" ?

In the report of type list, "Group by" also by Incident number.

Telmo

[Pastupe]

Hello and thank you for quick answer

I already tried but it doesnt make requested output

Target is to have either pie chart or bar chart   with one bar, that will show number of incidents that are grouped per metric where definition is   Assigned to Duration

see image below, it would be nice to have Assigned to Duration field available for aggregation, but it doesnt appear there, that would solve my issue

why this field doesnt appear in aggregation ?

/Petr

[telmo]

Hi Petr,

But "Assigned to Duration" is not a field. It's a metric definition name. You cannot use it in the aggregation.

I'm still a bit confused on what you want to achieve:

1st example you gave:

2nd example:

Is this what you want?

Telmo

[Pastupe]

Hello,

let me take it from start again.

I need information from metric_instance table and also from incident table, therefore I created database view mentioned before

I need from :

Incident table:                 sys_created_on - to be able set date range in the report, I want monitor incidents created last month

metric_instance :       value - I want monitor user name that is stored in field VALUE

Report should be either PIE or BAR

1. Imagine, incident was created and assigned to you = 1 new row in metric_instance was created
2. You reasigned incident to your colleague = 2nd row in metric_instance was created
3. Your colleague assigned incident back to you = 3rd row in   metric_instance was created
4. Pie chart now display me 2 incidents from you, but in reality it is 1 single   incident where you was assigned to 2 times

I need   in report see only unique information lets say from incident table where inc sys id is stored only once,   therefore report should display information that you was handling 1 incident only

How to do it ?

is this more clear ?

thank you

/Petr