Need a report

[Soni Rahul]

Hi ServiceNow Team,
I have a requirement to create a report on sysapproval_approver table for only RITM's(sc_req_item) records where I can be able to see the request variable information as well, like if we have one request for "Request for ServiceNow group" and if there is variable" Target group name" inside it then my report should look like this.

State               Approval for           Target group name
Requested         RITM00001          Network

To achieve this requirement, I have created the database view to add this 2 tables (sysapproval_approver, sc_req_item) with this query "ap_sysapproval=sc_sys_id" where ap is the sysapproval_approver prefix and sc is the sc_req_item prefix, after creating this database view I am not able to see the variable information which we can see in sc_req_item table while creating the report.

Please help how I can create the report.

Thanks.

[Chaitanya ILCR]

Hi @Soni Rahul ,

yes, you add a where class that

sys_user_group_sys_id=sc_item_option_value && sc_item_option_item_option_new == 'sysid of the group referenced variable'

Replace sys_user_group and sc_item_option table names with variable prefixes(of tables in the database views)

to go with the first option I have shared (create a new variable to store the name of the group) for this to work for existing records a fix script might be required to populate new variable value of existing records

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[Ehab Pilloor]

Hi @Soni Rahul,

Reference values will be stored as sys ids. You would need to dotwalk to Record -> Name for it show as Name. See if you can dotwalk as mentioned.

Regards,

Ehab Pilloor

[Soni Rahul]

Hi Ehab,

can you provide me one little example with screenshot, if possible, I didn't get "dotwalk to Record -> Name" .
Thanks

[Ehab Pilloor]

Hi @Soni Rahul,

As @Chaitanya ILCR mentioned, create a variable and there is an Autopopulate section in variable record. There, put dependent question as variable storing group record. And to autopopulate, use dotwalk in that field to locate Name field in Group record.

Regards,

Ehab Pilloor 

[Ehab Pilloor]

Hi @Soni Rahul,

Variables are stored in sc_item_option table, you would need to add it to Database view too. 

Add them to database views: 

sc_item_option_mtom

sc_item_option

Regards,

Ehab Pilloor