Manual Crosstab Unable to put data correctly

[dannab]

Hello All, Im using CR 8.5, connecting via ODBC to a symposium database.
Im currently pulling data for Monday only. I need the report to display the service factor (a custom formula)
as follows:
1/17/05 1/24/05 1/31/05 2/7/05
00:00 70 35 25 90
00:15 95 65 75 80
00:30 95 76 82 54

Im pulling Monday records using record selection formula:
DayOfWeek ({iSkillsetStat.Timestamp}) = 2
my report is grouped on {iSkillsetStat.Time} (00:00,00:15,etc)
To print the correct day of month for the report, I am using formula dow (Minimum({iSkillsetStat.Timestamp})) + 7 seven days. There are a total of 11 formulas to calculate the date.

I cannot figure out how to get the correct service factor to
print for the time/dow.

I have tried this formula, changing the dow_0 as necessary for each date-- but it gives me the same data for every date.
-----------service factor formula-------------------
if {iSkillsetStat.Timestamp} = {@dow_0}
then
Local NumberVar SvcLvl;
Local NumberVar Level1 :=
(Sum ({iSkillsetStat.CallsAnswered}, {iSkillsetStat.Timestamp}) +
Sum ({iSkillsetStat.SkillsetAbandoned}, {iSkillsetStat.Timestamp}))
-
(Sum ({iSkillsetStat.CallsAnsweredAfterThreshold}, {iSkillsetStat.Timestamp}) +
Sum ({iSkillsetStat.SkillsetAbandonedAftThreshold}, {iSkillsetStat.Timestamp}));

Local NumberVar Level3 :=
Sum ({iSkillsetStat.CallsAnswered}, {iSkillsetStat.Timestamp}) +
Sum ({iSkillsetStat.SkillsetAbandoned}, {iSkillsetStat.Timestamp});

if (Level3 = 0) then
SvcLvl := 0
else
SvcLvl := (Level1 * 100.0) / Level3

**Please Help!!
(FYI, I tried to use crystal crosstab, but it only allows me to add summarized fields, which will not report the right data)

dannab

 

[dannab]

Sorry the service factor formula actually contains {iSkillsetStat.Time}, not {iSkillsetStat.Timestamp}

dannab

 

[lbass]

You don't really need variables here. Try creating a series of formulas like:

//{@Level1}:
if {iSkillsetStat.Timestamp} = {@dow_0}
then
{iSkillsetStat.CallsAnswered}+
{iSkillsetStat.SkillsetAbandoned}-
({iSkillsetStat.CallsAnsweredAfterThreshold}+{iSkillsetStat.SkillsetAbandonedAftThreshold})

//{@Level3}:
if {iSkillsetStat.Timestamp} = {@dow_0}
then
{iSkillsetStat.CallsAnswered} +
{iSkillsetStat.SkillsetAbandoned}

//{@svcfactor}:
if sum({@Level3},{iSkillsetStat.Time}) <> 0 then
(sum({@Level1},{iSkillsetStat.Time}) * 100) / sum({@Level3},{iSkillsetStat.Time})

Note that the group field in the formulas should be based on the time field, NOT on the datetime stamp.

-LB

 

[dannab]

{@svcfactor} is returning error
the summary / running total field could not be created.

If I simply print {@Level1} it returns 0.
Even though there are values for the items in {@Level1} for the date/time

dannab

 

[lbass]

I just realized that {@dow_0} is probably the problem. Instead of using the minimum function, try using the following clauses in {@level1} and {@level3}:

//{@thisweek}:
if {table.date} = currentdate-dayofweek(currentdate)+2 then//etc.

//{@lastweek}:
if {table.date} = dateadd("d",-7, currentdate-dayofweek(currentdate)+2) then//etc.

//{@twoweeksago}:
if {table.date} = dateadd("d",-14, currentdate-dayofweek(currentdate)+2) then//etc.

-LB

 

[dannab]

Thanks, great info.
I had to add a timestamp group & put the {@svcfactor} formula in it, worked beautifully, except this problem.

For {@thisweek}, all intervals 00:00-23:25 accumulated properly.
When I moved on to last week, the only data that accumulated was for the first interval 00:00, the remaining intervals are reporting zero.

Any ideas?

dannab

 

[lbass]

Did you create separate Level1 and Level3 formulas for each date and then a separate {@svcfactor} for each? I.e., there should be three formulas per column.

Also the group should be on {iSkillsetStat.Time}, not on your datetime field.

-LB

 

[dannab]

I deleted the datetime group (I had actually had both time & datetime).

There are 3 seperate formulas for last week they are:
//{@lastweek_level1}
if {iSkillsetStat.Timestamp} = dateadd("d",-7,currentdate-dayofweek(currentdate)+2)
then
{iSkillsetStat.CallsAnswered}+
{iSkillsetStat.SkillsetAbandoned}-
({iSkillsetStat.CallsAnsweredAfterThreshold}+{iSkillsetStat.SkillsetAbandonedAftThreshold})

//{@lastweek_level3}
if {iSkillsetStat.Timestamp} = dateadd("d",-7,currentdate-dayofweek(currentdate)+2)
then
{iSkillsetStat.CallsAnswered}+
{iSkillsetStat.SkillsetAbandoned}


//{@lastweek_svcfactor}
if sum({@lastweek_level3},{iSkillsetStat.Time}) <> 0 then
(sum({@lastweek_level1}, {iSkillsetStat.Time}) * 100) / sum ({@lastweek_level3}, {iSkillsetStat.Time})



dannab

 

[lbass]

I think you need to wrap the timestamp fields in each formula in date(), as in:

//{@lastweek_level1}
if date({iSkillsetStat.Timestamp}) = dateadd("d",-7,currentdate-dayofweek(currentdate)+2) then//etc.

I should have noticed that sooner. Sorry.

-LB

 

[dannab]

You are my Hero!!
Thank you so much!
Have a question though, how long have u been doing this, did you study for it?

dannab

 

[lbass]

About four or five years, learning mostly on my own, from the George Peck book, and from this site especially.

-LB

 

[dannab]

lbass,
How do I go about getting a summary for each date to appear on the report?

Ive tried using the same formulas,removing ,{iSkillsetStat.Time}, but I get the svc factor for the first day in all of them.



dannab

 

[lbass]

This should work (a separate formula for each column):

//{@totallastweek_svcfactor}:
if sum({@lastweek_level3}) <> 0 then
(sum({@lastweek_level1}) * 100) / sum ({@lastweek_level3})

This must be placed in the report footer.

-LB

 

[dannab]

the summary / running total field could not be created.

dannab

 

[lbass]

Where are you creating this formula? There is no reason for an error message like that, especially since the same formula is working (with the group condition) when you are using the group level formula.

-LB

 

[dannab]

I dont know what I did yesterday.
Tried again this am, works fine.
I ordered the george peck book.
Cant wait to read it.

Thanks so much for all of your help.

dannab