Query Builder Help - How to fill a column when condition is true

[mariaSutton]

I am creating a report listing task details.

One of the columns in this report is for the "Completed Date" of a task.

I need help implementing a condition for the "Completed Date" column. The condition is:

          If "Work Item.Status" equals "Completed"

               then populate the "Completed Date" with "Wf Assignment. Last Modified",

          else, leave "Completed Date" null/blank.

How can I go about accomplishing this?
Should I create two reports? If so, how do I merge the reports to see both completed and uncompleted tasks?
Is this something that is done under the "Select or Constrain" tab or under the "Criteria" tab? 

TIA!

[aaronjlarson]

Use the "DECODE" database function.

Name the column "Completed Date"

First argument to be "Work Item.Status" (what you're evaluating)

Second argument to be "Completed" (the value you are looking for)

Third argument to be "Wf Assignment.Last Modified" (the value you want to display if the value you're evaluating matches the one you're looking for, i.e. arg2 = arg1.value

Fourth argument to be blank text string (the value to return if the value in arg2 not found as value of arg1

Link to WC Help database function: Database Functions

Hope that helps!

[mariaSutton]

Hi @aaronjlarson,

Thanks for your reply. I am trying out what you described but cannot get a result. Can you let me know if this looks right?

These are the steps I did:

1. Under Select or Contrain tab, selected Database Function
   1. In Add Database Function modul/popup form
      • Display Name = Completed Date
      • Database Function = Decode
      • Operator = equals
      • Value = Constant value
      • 
   2. Under Decode dropdown
      1. Add Reportable Item "Work Item. Status"
      2. Add Constant String "Completed"
      3. Add Reportable Item "Wf Assignment.Last Modified"
      4. Add Constant String "" (blank)
      5. 

[mariaSutton · Accepted solution]

Hi again,

I was able to figure out what was missing. When I would run the report an error was getting thrown about an invalid number.
The result that "Work Item.Status" returns is not a string or a number, its a enumerator. 
I was able to add a Database function of "To Character" then under that "Work Item.Status". 
After doing this the first value under Database "Decode" can be compared to the second arg which is a string value of "COMPLETED".

[aaronjlarson]

Yes - was just testing that and was going to post same thing as well.  I didn't check that before my first response and since it's an enum you must specify in the internal value, case-sensitive, when specifying as string criteria in a database functino.  I see it works too in my example. Not sure what you're going for, but in my simple test I thought to just use the work item's last modified date as the "Completed Date" if the status is COMPLETED - because if the item is complete wouldn't the completion date be the "last modified"?  Maybe not in your case... anyhow glad you got it working.

[HelesicPetr]

Hi @aaronjlarson 

I guess that the issue is if the state is completed, then the Last modified can be still changed in future so it can be irrelevant information when it was really completed 

PetrH

[aaronjlarson]

Like I said - in my case this would work because that condition could never happen.  Depends on your environment/use-case.  If you really wanted to dig into when the assigned user clicks the "Complete" button on the task you would have to pull in the Wf Voting Event Audit table to the query and use the "Created" date from it as the "voting event" is created when the task is completed.

[mariaSutton]

@aaronjlarson, what table would you join the Wf Voting Event Audit to if you were to try to get the Wf Voting Event Audit "Created" date?