Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Report Builder "What?" with multiple criteria

Options
gkbrenne
gkbrenne
edited 12/09/19 in Archived 2017 Posts

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?

2017-06-02_16-31-10.png

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    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?  

  • 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
    Options

    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
    Options

    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
    Mike Wilday ✭✭✭✭✭✭
    Options

    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. 

This discussion has been closed.