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

Here are the screenshots of both database view and the report.


Database View.jpgReport.jpg



Regards,


Darshak


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.


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


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.


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