- 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:44 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:46 PM
Yes, you're using LEFT JOIN, thus the issue. Do you see under the Left Join column it says "true"? If you set it to "false", your issue will go away.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 12:52 PM
Hi John,
But that would essential remove all the 'Problem' without a problem task. Isn't it? I need all the open problems irrespective of whether a task is associated with it or not.
Regards,
Darshak
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 01:08 PM
That's correct. So per your requirement, using a LEFT JOIN is correct, and the issue you're posing here's the byproduct of that design.
Then another solution you can try is to apply a filter to your report (not the view) saying
[Task Number] [is not empty] (or use whatever field from the task that always has a value)
This will remove all problems without tasks in your report, so you won't see those misleading counts any more.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-30-2016 01:12 PM
Hi John,
Thank you so much for your time. I'll let the report be as is without the filter you've suggested as I intend to capture the open problems as well in the report.
Regards,
Darshak