Reports - Creating a Database view for reference field

pavi12
Tera Contributor

yesterday

Hi,I am looking for some guidance.

Requirement:
Create a report for Approved and Closed/Completed Requested Items (RITMs/Requests) that also displays catalog variable data.

The report needs to include the following fields:

  • Customer Name

  • Account Number (Catalog Variable – Reference)

  • Chargeable Service Type (Catalog Variable)

  • Cost (Catalog Variable)

  • Approver (User who approved the request)

  • Case which requires charge (Reference to Case)

What I’ve done so far:

  • I created a Database View to fetch the Case-related fields.

  • Added the required tables to the database view.

  • I’m able to access normal fields in Reports.

pavi12_1-1767882617293.png

 

Issue:
When creating the report, I’m unable to see the catalog variables (Cost and Chargeable Service Type) as selectable columns, even after using the database view.

Question:

  • Am I missing any configuration or table mapping to expose catalog variables in reports?

  • Is there a recommended approach to report on catalog variables along with reference fields like Case and Account?

Any guidance or best practices would be greatly appreciated.

Thanks,
Pavithra

0 Helpfuls
  • 154 Views
2 REPLIES 2

Itallo Brandão
Mega Guru

yesterday

Hi Pavithra,

 

Great effort on the Database View, but that is actually what is causing your variables to disappear!

 

The Technical Issue: In your Database View, you joined sn_customerservice_case on optn.value = case.sys_id.

  • This acts as a filter. The view will only return the specific variable row that contains the Case SysID.

  • The other variables (Cost, Chargeable Service Type) do not contain a Case SysID in their value, so those rows do not match the join condition and are excluded from the result set (or appear as null depending on Left Join settings).

The Recommended Solution (No Database View required): You can achieve 90% of this requirement using standard ServiceNow Reporting functionality, which has a built-in feature to display variables as columns side-by-side.

 

Steps to configure the Report:

  1. Data Source: Point your report to the Requested Item [sc_req_item] table (or sysapproval_approver if you want to list by Approver).

  2. Add Columns: Click on Choose Columns.

  3. The Trick: Scroll to the very bottom of the Available list.

  4. Select Variables (+) (Click the plus icon, do not just highlight it).

  5. A new window will open. Select the specific Catalog Item associated with these requests.

  6. The middle box will populate with your variables: Cost, Chargeable Service Type, Account Number, etc.

  7. Move them to the Selected box.

Result: This will display one row per RITM with the variables shown as clean columns.

 

Regarding the "Case" and "Account" references:

  • Since "Case" and "Account" are Reference Variables, the report will display their Display Value (e.g., the Case Number and Account Name).

  • Note: If you need to pull specific fields from the Case (like Case Priority) that are not the display value, standard reporting on variables has limitations. However, for just displaying the Case Number and Cost, the method above is the best practice.

 

Hope this simplifies your reporting!

 

If this response helps you solve the issue, please mark it as Accepted Solution.

This helps the community grow and assists others in finding valid answers faster.

 

Best regards, Brandão.

pavi12
Tera Contributor
In response to Itallo Brandão

yesterday

Hi @Itallo Brandão ,

Thanks a lot for your response.

 

I initially tried to create the report as suggested in your previous comment. However, I’m facing an issue with reference fields.

 

For example, Customer Name is a reference field (customer_account). I attempted to dot-walk from this field to fetch the required value, but I’m unable to do so.

 

Could you please advise on the correct way to handle this reference field in the report?

Thanks in advance for your support.

0 Helpfuls