Conditional Format On Time In Crosstab v10

[mabis]

First, thank you for any help you can offer. Running on CR v10 and SQL 2000. My cross tab offers the results of sales phone calls in my rows and my columns are broken down by half hour. For example:

9:30 10:00 10:30
completed 15 5 3
attempted 2 4 6
message 1 3 2

My users have certain 2 hour blocks during the day where they are required to only make calls. I want to conditionally highlight the column based on certain times per day.

My formula for the column by half hour is:

NumberVar MinExtract := Minute({history.completeddate});
TimeVar AMDMins;
DateTimeVar GPResult;

If MinExtract < 30 then AMDMins := Time(Hour({history.completeddate}),00,00) else
If MinExtract > 30 then AMDMins := Time(Hour({history.completeddate}),30,00);

AMDMins;

This works just fine. history.completeddate referenced above is datetime field in sql. An example of the field results from sql is 8/16/1999 8:43:55 AM

So, ultimately I hope to format the column based on (for example) this criteria.

history.username='smith, jim' and history.completeddate is between 9:30am and 11:30am

Again, thank you for any feedback you can offer.

mabis

 

[lbass]

Is the crosstab placed in a user name group or are you using user name as row #1, although you didn't show it in your example?

-LB

 

[mabis]

Thank you for your question and the help. The cross tab is placed in a username group "history.username

 

[lbass]

Select and inner cell->format field->border->background->x+2 and enter:

if {history.username} = "smith, jim" and
gridrowcolumnvalue("@AMDMins") in time(9,30,0) to time(11,30,0) then cryellow else crNoColor

...where {@AMDMins} is the name of your formula. Note that "" replace the {} in the gridrowcolumnvalue function.

-LB

 

[mabis]

I understand the formula required here. But I am struggling to modify to use it not only with time but also with day per week.

In other wods, for "smith, jim" I may want to highlight
time(9,30,0) to time(11,30,0) on Monday but
time(12,30,0) to time(15,30,0) on Tuesday

lbass, thank you for your initial response. I look forward to further feedback.

 

[mabis]

GOT IT. Sorry for the false alarm. Looks like this (unless there is an easier way!).

if {history.username} = "harvey, traci" and
gridrowcolumnvalue("@halfhour") in time(13,00,0) to time(15,30,0) and
dayofweek({HISTORY.COMPLETEDDATE})=2
then cryellow else
if {history.username} = "harvey, traci" and
gridrowcolumnvalue("@halfhour") in time(9,00,0) to time(11,00,0) and
dayofweek({HISTORY.COMPLETEDDATE})=3
then cryellow else
if {history.username} = "harvey, traci" and
gridrowcolumnvalue("@halfhour") in time(13,00,0) to time(15,30,0) and
dayofweek({HISTORY.COMPLETEDDATE})=4
then cryellow else
if {history.username} = "harvey, traci" and
gridrowcolumnvalue("@halfhour") in time(9,00,0) to time(11,00,0) and
dayofweek({HISTORY.COMPLETEDDATE})=5
then cryellow else
if {history.username} = "harvey, traci" and
gridrowcolumnvalue("@halfhour") in time(13,00,0) to time(15,30,0) and
dayofweek({HISTORY.COMPLETEDDATE})=6
then cryellow else crNoColor

 

[lbass]

You could have simplified it:

if {history.username} = "harvey, traci" and
(
(
gridrowcolumnvalue("@halfhour") in time(13,0,0) to time(15,30,0) and
dayofweek({HISTORY.COMPLETEDDATE}) in [2,4,6]
) or
(
gridrowcolumnvalue("@halfhour") in time(9,0,0) to time(11,0,0) and
dayofweek({HISTORY.COMPLETEDDATE}) in [3,5]
)
)
then cryellow else crNoColor

-LB