function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
zen_njzen_nj 

how to have report run only for the last week day (as oppose to yesterday())

I have a report that I want to run daily that shows work done the previous working day.  So in the report I would have in the criteria something like:

Date/Time Closed equals Yesterday

And this worked fine when I am running the report on Tuesday thru Friday. But if I am running the report on Monday, it would only show cases where Date/Time Closed equals to Yesterday (where yesterday is in fact Sunday, and not friday).

I created a custom formula field with data type of date and called it  PreviousWeekDay:

CASE (MOD( Datevalue (now()) - DATE(1900, 1,7),7),
2,today()-1,
3,today()-1,
4,today()-1,
5,today()-1,
6,today()-1,
0,today()-2,
1,today()-3,
today()-1
)

And this seems to work fine in that when I look at the field output from a case page layout, it's showing the proper date value.
So if I was looking at cases at a case today (today being monday Sept 29, 2008), it would give us the value of PreviousWeekDay this value: 9/26/2008

But now when I change my report criteria so that instead of:  Date/Time Closed equals Yesterday
It now says:  Date/Time Closed equals PreviousWeekDay

the report would not work and it would flash red error messages saying

Error: Invalid date (Valid date format 9/29/2008 or 9/29/2008 1:57 PM)

What gives here?  Is there some more conversion I need to do from the custom formula date field?
Marc PannenbergMarc Pannenberg
Hi Zen,

the problem is that you are trying to use a field name as a filter criteria. There are certain reserved word in salesforce.com that help you specify a date or time frame, such as: TODAY, THIS WEEK, YESTERDAY.

This is unfortunately not the same as using a custom field itself - it would be great, though!

How about this:

Change your formula from "PreviousWorkingDay" to "NextWorkingDay". I.e. it displays the day when the record needs to be reported upon. Then you can use the report criteria to say:

NextWorkingDay Equals TODAY

That should work.

Marc
zen_njzen_nj
thx Mark for your posting.
Yeah I found out that was the issue, that in reports, you can't use a field name's value as a filter.

Not sure your suggestion would work though since what I wanted to do is really compare say the Date/Time Closed field is equal to whatever value was in my other field (PreviousWorkingDay, or NextWorkingDay), as well as a couple of other custom date/time fields that I created to track certain stages in the case.

So it's really more like:
show all cases where either the Date/Time Closed field = previous weekday
                                              or   Date/Time CaseStatusSetToCompleted field = previous weekday
                                              or   Date/Time CaseStatusSetToBeginBilling field = previous weekday

So not sure how I can incorporate your suggestion into this.

What I wind up doing is creating 2 separate reports - one to be run daily from Tuesday to Friday, and another to be run only on Monday (which would essentially be showing cases processed previous weekday, which would be friday).

So in 1st report, the criteria would be:

(Date/Time Closed equals YESTERDAY) or (Date/Time CaseStatusSetToCompleted equals YESTERDAY) or (Date/Time CaseStatusSetToBeginBilling  equals YESTERDAY)


And in the 2nd report, the criteria would be:

((Date/Time Closed equals LAST 3 DAYS) and (Date/Time Closed not equal TODAY))
or
((Date/Time CaseStatusSetToCompleted equals LAST 3 DAYS) and (Date/Time CaseStatusSetToCompleted not equal TODAY ))
or
((Date/Time CaseStatusSetToBeginBilling  equals LAST 3 DAYS) and (Date/Time CaseStatusSetToBeginBilling   not equal TODAY))

And the 2nd report will only be run on a monday, and so would capture all cases that were processed in the last 3 days, except for TODAY (so that would really cover Friday and also Sat/Sun as well, in case work was done during the weekend).
rpr2rpr2
Would scheduling the report to run every Saturday and be emailed to users be a workaround for you?  That capability has been added in Winter '09 release.
zen_njzen_nj
I am scheduling the report to run (via dashboard scheduling) except I had to use two different dashboard (which uses two different reports). So I have dashboard scheduled to run weekly from tues to fri using the regular report (which is based on criteria that the DateTimexxx = Yesterday). Then I have another dashboard scheduled to run every monday which use the modified report (with criteria that DateTimexxx = last 3 days and not equal TODAY). The reason for that instead of just having regular dashboard schedule running from tues to sat is that the user would be clicking on the dashboard to see the report during work days. So no one would actually be looking at the dashboard scheduled on saturday until they are in the office monday - and by then when they click on that, it would no longer contain the actual results of friday data (when ran on saturday) but would instead be trying to show actual results of sunday data .