The count shows non zero value in reports on a table created using 'Database view'

Rama Chandra D
Kilo Guru

Hello everyone,

I have created a 'Database view' to join Problem and associated Problem task table to run a report that displays all the open problems along with their tasks if any. The Database view runs well so does the report. However, when I group the report using 'Problem Number' it shows the count against each problem with no tasks as '1' and when expanded it shoes 'empty' in all the fields. I was expecting that count would be zero if there are no tasks associated with the problem.

Is there any way, I can avoid the misleading count of '1' when there are no tasks?

Regards,

Darshak

1 ACCEPTED SOLUTION

Yep. To be clear, going back to your question



Is there any way, I can avoid the misleading count of '1' when there are no tasks?



the answer is NO - the "1" is by design, since when you use a LEFT JOIN, there's always 1 record for Problem even if there's no tasks.


View solution in original post

11 REPLIES 11

dvp
Mega Sage
Mega Sage

I think you don't even need a database view for what you are trying to achieve



Here is the sample one on change request



find_real_file.png



For your case create a report on problem task and group by problem field


Hi dvp,



Thanks for the reply. Let me try and get back to you on this.



Edit : I've tried as per your suggestion but the report doesn't contain Problem without any task as the report is run on 'Problem Task' table.


Regards,


Darshak


Yes, that's because the Problem Task [problem_task] table is based on a JOIN, not a LEFT JOIN.


drjohnchun
Tera Guru

I like the solution dvp is suggesting. But if you'd like fix your view, my feeling is you may be using a LEFT JOIN instead of JOIN. If you simply change the JOIN type, then you won't see any problem records with no tasks. If you can share a screenshot of your view definition, I can pinpoint if that's the case.



Hope this helps.



Please feel free to connect, follow, mark helpful / answer, like, endorse.


John Chun, PhD PMP see John's LinkedIn profile

visit snowaid


ServiceNow Advocate

Winner of November 2016 Members' Choice Award