How to get a list of cost center names without having users

[raj149]

Hello experts ,

my requirement is 

I want to create a report  that A list of cost centers without having users.

Here cost center(cmn_cost_center) is different table and user(sys_user) is different table.

how to achieve this by using client script..?

Thanks in advance.

Best regards,

Raj 

[chrisperry · Accepted solution]

Hi there,

You won't be able to do it with client script, you'll need a server script of some sort (business rule, script include, etc.).

The below script will get you a list of cost centers that do not have any users:

var resultArr = [];
var costCenterGr = new GlideRecord('cmn_cost_center');
costCenterGr.query();
while (costCenterGr.next()) {
    var userGr = new GlideRecord('sys_user');
    userGr.addEncodedQuery(gs.getMessage('cost_center={0}', costCenterGr.getUniqueValue()));
    userGr.setLimit(1);
    userGr.query()
    if (!userGr.hasNext()) {
        resultArr.push(costCenterGr.getUniqueValue());
    }
}
gs.info(gs.getMessage('Cost centers with no users: {0}', resultArr.join()));

[chrisperry]

Alternatively, if you want to create a report like you mentioned in the first sentence of your post, you can create a report on the cmn_cost_center table with a related list condition of "Noselected table records are related to a record on User --> Cost Center":

[raj149]

Hello @chrisperry 

Thanks for your help.

This script is working fine ,tested in background script but How to call this in report.

Best regards,

Raj

[raj149]

Hello @chrisperry  and @Bert_c1 

Thanks for your help . Report is working correctly.

There is another requirement could you please help me here.

I want to create a report by using below requirement

User  active=true and user has role "approver_user" or User is part of ABC group(sys_user_grmember) and

User is a member of  level 1 approver (referenced to user table) or level 2 approver (referenced to user table) in a cost centre(cmn_cost_center)

please help me here to get users sys_ids by using script include.

If script is worked then i can call this script include in report

Thank in advance 

Best regards,

Raj