Report Builder "What?" with multiple criteria

gkbrenne

I am trying to create a report to find all tasks that are not checked as done or does not have a status of Complete.  See the attached screen shot -- what am I doing wrong?

I have 88 items in the list; there are 5 where Done is checked; there are 7 that are marked 'complete.  That means when I run the report, I should have 88-5-7 = 76

When I use the "OR" joiner within "What?" - I show all 88 records; if I change it to AND, I have 60 records.

Anyone have any ideas?

Mike Wilday

Basically, one of either criterion is counting the task in the report. Because Done is not checked on the lines with complete, and complete is not listed on the lines with Done checked, you will always get those results. 

On a design level, it seems kind of redundant to have a Done column and a status column. Couldn't you do away with the Done column and just use the status, then filter away the items marked as complete?  

Charles DESCOTES

Since you don't mind redundancy, you could create a third column, wrapping up columns 'Done' and 'Status'

Let's call the new column 'Report' → in this column properties, make it 'Symbols' and select the first one, red flag ('0' will uncheck, '1' will flag red).

Copy paste the following formula on every cell of the 'Report' column (example of row #3)

=IF(Done3 = 1, 0, IF(Status3 = "Complete", 0, 1))

Then adjust your report with 'What?' selecting the new 'Report' column when it is flagged red.

...

I believe the reason for the initial mishappen is due to the negative conditions. I'd like to understand it better if someone in the community has clues ...

gkbrenne

Thank you for the reply:  my problem is trying to be all things to all people: we have some that want just a quick check box for items, and some prefer the status column.

 

I'd love it if when someone checks the Done box to have the Status box go to complete - but I guess that's either another question or a wish list item.

gkbrenne

I was hoping someone would have an answer -- I did try writing this with positives (IE Status is In Progress or is Not Started or is On Hold  OR Done is not checked), but that didn't work either.

I'll use your work-around of adding another column for the short-term until we get a consensus of how things should work.

Mike Wilday

Charles' solution of an additional column makes sense and would work. Once you create it, you could hide it on the original sheet, and on the report. but continue to filter your report based on that additional column. That would allow you to filter based on either column. Great suggestion Charles.