Remove duplicate records in Report

[dineshchoudhary]

Hi All,

To calculate the license count, I have created the report on sys_user_has_role whose roles are ITIL or approver_user. But in the report if same user is a member of 4 groups with ITIL roles, 4 records are shown in report. How can I remove duplicates so that there should be only one record if he/she has either an ITIL role or approver_user role irrespective of how many groups he/she is a member.

Eg: If user A has approver_user role which is not inherited from any group and ITIL role inherited from group B and ITIL role which is not inherited from any group, in sys_user_has_role table there would be 3 records for this user. I want to display only one record for this.

Regards

Dinesh

[Ashutosh Munot1]

Hi Dinesh,

We have to write a script include or a global business rule and call it into the report condition filter.

Let me know if you want script for the same.

Thanks,

Ashutosh Munot

Please Hit Correct, Helpful or like,if you are satisfied with this response.

[dineshchoudhary]

Hi Ashutosh,

Could you please provide the scipt.

Regards

Dinesh

[Ashutosh Munot1]

Hi Dinesh,

See below script and Mark answer as correct or helpful.

Global BR:

function roles()
{
var answer = [];
var i = 0;
var count = new GlideAggregate('sys_user_has_role');
count.addEncodedQuery('role=0dd198126f21110033dc841dba3ee494^ORrole=debab85bff02110053ccffffffffffb6');
count.addAggregate('COUNT', 'user');
count.groupBy('user');
count.addHaving('COUNT','>',1);
count.query();

while (count.next()) {
var sn = count.user.sys_id;
//var snCount = count.getAggregate('COUNT', 'name');
//if (snCount > 1) {
answer[i++] = String(sn);

// }
}

return answer;

}

Thanks,

Ashutosh Munot

Please Hit Correct, Helpful or like,if you are satisfied with this response.

[Cristian Stefa1]

Hello,

Create a pivot report

And Aggregation> Count distinct, in below example is count distinct by user

Please Hit Correct, Helpful or like,if you are satisfied with this response.