Creating a report using Database views

[Mike_G5]

Hello, I would like to create a report for our Service Desk.  They would like a report that pulls all activity from the Task table as well as the Interaction table so that they may track any trends.  I have created a database view between the task table and interaction table.  

I am having trouble with the Where clause.  What link between the two tables can I use so that the report will display all information from the task table as well as the interaction table?

Thank you

[Sebas Di Loreto · Accepted solution]

@Mike_G5 
I have been in your shoes but you are facing this wrong for two reasons:

• The DB view will only show you information from the two tables WHEN there is a "connection" (the where clause) between the two of them. For example, an incident was opened from an interaction AND even in this case, that relationship is not represented in any interaction nor task field. That information resides on the interaction_related_record table.
• Creating a DB view that combines huge tables like task and interaction could get out of hand and create performance issues.

Again, the interaction_related_record table will only show TASK (or knowledge) that has been related to interactions.  I would separate the reports that the SD needs, attacking each table on its own.

IF you do want a single report then consider using multiple datasets: one for the TASK table and another one for the interaction one. Read it here.

[mattystern]

Hi Mike,

I think you may want to look into adding data sets into your reports with this button here. This way, you can compare two datasets. EX From INC and SCTask here:

Otherwise, I would think you would need to first define a metric you were interested in on the metrics definition table and then join on that metric with a database view. I do not think there is a ID link between the interaction and the task tables.