Database view

[BKash]

I have a requirement to create a Multi table report for an engagement where the columns are from Test Plan and control table. I have no experience in how to create database view for reporting purpose. Kindly assist, thanks!

[Community Alums]

Hi @BKash 

To create a Database View, you will need to follow these steps:

1. Navigate to the Database Views:

   • In the ServiceNow application, go to System Definition > Database Views.

2. Create a New Database View:

   • Click on New to create a new record.
   • Set the Name of the Database View (for example, "Test Plan and Control Data View").
   • Set the Table field to a temporary placeholder (e.g., Test Plan), as the actual database view will combine fields from different tables.

3. Define the Tables to Join:

   • After saving the Database View record, you need to define the tables you want to join.

   • Scroll down to the Tables related list and click New to add the tables.

   • Add Test Plan Table:

     • Add the Test Plan table to the database view (you will need to join this with another table, e.g., Control).

   • Add Control Table:

     • Add the Control table. This is the second table you will join with the Test Plan table.

4. Join the Tables:

   • In the Database View Tables section, specify the Join Conditions to relate records from the Test Plan table to the Control table. This is typically done by defining a common field between the tables (e.g., Test Plan ID and Control ID or some other key).

     • For example:
       • Test Plan Table: Test Plan ID
       • Control Table: Test Plan ID (this would be the common field for the join)

   • Select the appropriate Join Type (usually INNER JOIN if you only want records where there is a match in both tables).

5. Define the Columns to Select:

   • In the Database View Columns section, add the fields (columns) you want to include in your report. For example:
     • From the Test Plan table, you might want fields like Test Plan Name, Test Plan Status, etc.
     • From the Control table, you might want fields like Control Name, Control Status, etc.

6. Save the Database View.

Once the Database View is set up, you can use it as the source for your Report.

Follow below steps to create a report:

1. Navigate to Reports: Go to Reports > Create New.

2. Select the Report Type:

3. Select the Source:In the Source field, select the Database View you created (it will appear as a table option).

4. Add Columns:

5. Filter and Group Data:If necessary, add filters to narrow down the data (e.g., show records where Test Plan Status = Active). You can also use grouping to organize the report by certain fields (e.g., group by Test Plan Name).

6. Run the Report:

7. Save the Report:

[wdeich]

I do not see a "Database View Tables section"

[Dr Atul G- LNG]

Hi @wdeich 

https://www.servicenow.com/docs/bundle/yokohama-platform-administration/page/use/reporting/concept/c...

https://youtu.be/QdiTio73Mnc

[Ankur Bawiskar]

@BKash 

database view is used to join 2 or more tables if some field is common between them

what did you start with and where are you stuck?

If my response helped please mark it correct and close the thread so that it benefits future readers.