Database View

[harry24]

What is database view?Practical demo of how to create Database view and how to use it.

[Jaspal Singh]

Yes, for some scenario but the scenario above was to get all tickets where

1. Opened by : ABC (for incidents)

2. Requested for: ABC (for RITM)

as Opened by may be different from Requested for  RITM

Also, dot-walk will work from child to parent so you can dot-walk from RITM to Task but how will incident be included for reporting.

[Anish Reghu]

Hi, Can you please suggest how do we configure this?

Also a left join example would be helpful.

Cheers,

Anish

[Ronak3]

Hi Jaspal,

I have a tracker table where every unique tracker record contains two related lists where we can add or remove the codes that are related to this tracker.

I should be able to run a report where I can pull the list of codes that are under each tracker.

What I have done:

Created two separate m2m definitions to relate a tracker to Codes A table and tracker to Codes B table in order to add the codes in each tracker record.

Create a database view and added these two m2m tables and added a where clause and run a report. Problem is I am getting duplicate records. I guess this is because a tracker can contain 4 codes from Codes A table and 7 codes from Codes B table.

So if I have,

a tracker number as 113 and codes from A table Q1,B2,C8 and codes from B tables as R12,V22

then I should be able to see on the list as below,

Tracker num	Codes A	Codes B
113	Q1	R12
113	B2	V22
113	C8

[Megha Padale]

Hi Harry,

A database view defines table joins for reporting purposes. For example, a database view can join the Incident table to the Metric Definition and Metric Instance tables. This view can be used to report on incident metrics and may include fields from any of these three tables.

A number of useful database views are installed with the Database View plugin and the Database Views for Service Management plugin. These database views cover most metric reporting needs and greatly reduce the need to define new ones.

Rotated and extended tables cannot be included in a database view. Also, an existing database view cannot be a component of another database view.

Perform the following tasks to create a new database view:

• Task 1: Create a new database view record.
• Task 2: Add tables to the database view using the View Tables related list on the Database View form.
• Task 3: Restrict the fields that are returned by the join by using the View Fields related list on the View Tables form.
• Task 4: Edit the number of rows returned, if necessary.
• Task 5: Test the database view you just created.

For more information: https://old.wiki/index.php/Database_Views#Task_1:_Create_a_Database_View

If my answer helped you in any way, mark answer as helpful and correct.

Thanks and regards,

Megha

[MD AQUIB KHAN · Accepted solution]

Hi Harry,

A database view defines table joins for reporting purposes.

For example, a database view can join the Incident table to the Metric Definition and Metric Instance tables. This view can be used to report on incident metrics and may include fields from any of these three tables.

A number of useful database views are installed with the Database View plugin and the Database Views for Service Management plugin. These database views cover most metric reporting needs and greatly reduce the need to define new ones.

Note: In general, as the number of tables that are included in the view and the number of records that those tables contain increases, the accumulated impact on performance grows. In addition, to optimize the performance of the database view ensure that the ‘where’ clauses that are defined in the database view are based on indexed fields.

For Example:

Database view Name:Incident SLA

you can create database view and the two tables (incident, task_sla) in the related tabs (View Tables). Once you add them. You can use the same database view in the report as table.Once done you can run the report.

Please find the screenshot below:

Report:

Using the database view in Table: