- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:09 PM
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
Solved! Go to Solution.
- Labels:
-
Problem Management
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 01:14 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:23 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:46 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 01:11 PM
Yes, that's because the Problem Task [problem_task] table is based on a JOIN, not a LEFT JOIN.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:38 PM
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 ![]() | ![]() |
Winner of November 2016 Members' Choice Award