I need help with a report: How to create a report that pulls requests that have a deadline of 1 day or less.

[TimVa]

We have a couple of fields that indicate the deadline depending on the request type/queue topic. "Final Files /needed" and "Preferred Posting Date". How can I create a report that shows requests with these fields being 1 day or less? Seems simple but I am having a tough time figuring it out. I would want to pull this report regularly to show leadership.

I guess it would need to show requests where the submission date and the deadline field are 1 day or less. Would that involve a calculated field? I have not grasped those yet!

[RubyAr]

Hi Tim,

You should be able to by filtering the custom field and choosing 'Between' date "$$TODAY-1d" to "$$TODAY"

Hope that works for you.

[TimVa]

Thanks Ruby,

Looks like this method would need to be a daily report, if we are looking for requests with a deadline of today or tomorrow, would that be 'Between" $$TODAY" to "$$TODAY+1d"?

If I used "TODAY-1d" to "TODAY" that would show the prior days requests that had the deadlines of yesterday and today?

[RubyAr]

Ahh yes, you're correct. +1d

[TimVa]

Thanks Ruby I think this is great for a regular daily report, I am actually going to use the advance options to color the row. However, I would also like to add this to maybe a weekly report. The "$$TODAY" wildcard would not work when the report is pulled at a later date. Wondering how to show the difference between the entry date and due date, and if it is 1 day or less set a rule or make that the report parameter.

[RubyAr]

Hi Tim, we created a text mode column that will calculate from Entry Date to the Custom field date:

Example:

displayname=Requested Turnaround Time

textmode=true

valueexpression=WEEKDAYDIFF({entryDate},{DE:Delivery date required})

valueformat=

[RubyAr]

NOTE: 'Delivery date required' is our custom field name.

[AnthonyIm]

Something I've done in the past. I had a group that required 2 day notice and so wanted to report on requests that gave less than that.

I created a "Compliance Date" which is a calculated field (set to date and time) that has the formula like ADDWEEKDAYS(Entry Date,1) - since you said you want less than one day.

Then I have a second calculated field called In Compliance? and that formula is IF(Compliance Date>Planned Completion Date,"No","Yes") Although you can replace Planned Completion Date for a Deadline date if you have that in the custom form.

Worked for my group and hope it works for yours :)

[TimVa]

Thank you Anthony,

I think I am getting the hang of your solution. Do you know if there is a way to do an "IF, OR" calculation formula?

For example we have one request form that has multiple options. Is it possible to do IF(Compliance Date>Planned Completion Date,"No","Yes"), OR IF(Compliance Date>Final FIles Needed,"No","Yes")

To account for both possibilities?

[AnthonyIm]

Hi Tim - totally possible. You just put the second condition inside the first IF statement. If it is OR you put it in False (which is Yes for you) and if it is AND you put it in True (which is No for you).

So in your case...

IF(Compliance Date>Planned Completion Date,"No",IF(Compliance Date>Final FIles Needed,"No","Yes"))

So if the Compliance Date is greater than Planned Completion Date, it will display no. If it is less than, then it will look to see if the Compliance Date is greater than Final Files Needed field and put Yes or No.

Hope that helps

[TimVa]

Hi Anthony,

Hoping you may be able to help me out with this. I started using fields like you have, except for mine I used "Rush?" and switched places with the "yes" and "no". So it looks like this:

Compliance Date:

ADDWEEKDAYS(Entry Date,2)

Rush?

IF(Compliance Date>Preferred Posting Date, "Yes","No")

And I set a rule in the report we watch that any request that has "yes" for Rush to highlight in Red. This seems to work most of the time but I get random requests where it will say Rush when it isn't or say something is not a rush when it is. I just got a request in that has a Preferred Posting Date of 7/12, the custom form field shows the compliance date would be 7/2, but the Rush field is saying YES. ANy idea what could be causing this?

[AnthonyIm]

That is weird. I would check do a Recalculate Expression on the project to see if maybe it was a server issue when the request was created. If that still doesn't fix it, I'd contact Support. Something doesn't right with this.

[Doug_Den_Hoed__AtAppStore]

Anthony, I like (and liked) your answer; very nice!

Tim, because Anthony's "In Compliance?" yes/no data is persisted as Custom Data, you can also Filter, View, Group, Sort, and (personal favorite) Chart by it.

Taking it Too Far (admittedly, my signature move), you could similarly create a numeric calculated field called "Lead Time In Days", with a formula of ROUND(DATEDIFF(Entry Date, Preferred Posting Date),0), then Filter, View, Group, Sort, Chart based on those deltas, to the nearest day...perhaps by the Person / Department / etc. who's making the request to then reveal (and/or grade, and/or educate, and/or penalize) accordingly.

Regards,

Doug