Cosstab Help Needed

[djeddie]

Hey All,

Firstly im using Crystal 9.

Now, i have some formulas that i have created to convert seconds to time. I have had to use the string method because the sum of the times are greater than 23 therefore i am unable to use the ctime function.

The problem im facing is having these time formulas displayed in a crosstab for a trend report that im trying to create.

I am trying to create a running total field to perhaps add to the crosstab but i dont know how to incorporate the string conversion to convert the number of seconds into hh:mm:ss.

Any help will be greatly appreciated.

Eddie S

 

[lbass]

After doing the calculations in seconds, you can format the results to the string format "hh:mm:ss" by right clicking on the summary->format field->common->display string->x+2 and entering your conversion formula there. In the conversion formula, you would use "currentfieldvalue" instead of your field.

-LB

 

[djeddie]

Your a genious!!!

It worked.............thanks heaps!!

One more question though.

The field that i needed help on was agent login per day and the cross-tab is located in Group 3. Heres the group Layout:

G1 - SITE (Sydney, Perth, NZ)
G2 - Team (Team A, Team B)
G3 - Agent (Tim, Bob, Sarah)

How can i get the average Login time for the agent's team to show up as another row in the crostab which is located in the agent group?

Eddie S

 

[lbass]

Please explain what your row, column, and summary fields are in the crosstab, and also show a sample of what the crosstab would look like after adding the average per team. I'm unclear on whether this is one number or a total that varies per column or whether you want this in each cell for some reason.

-LB

 

[djeddie]

Okay,

Here are the details of the crosstab i's like (located in the Agent Group) - Group 3

Column headings = date (dd/mm/yyyy)
Row 1 = Login Time Agent
Row 2 = Average Login time per agent in group
Row 3 = Average Login time per agent in site

The Cross tab should look like this

01/04/2006 02/04/2006 03/04/2006
Bob Smith 07:45:25 08:01:12 07:57:45
Team A 07:21:33 07:12:12 08:45:57
Sydney 07:11:54 08:12:47 08:01:55

Is this possible?

 

[lbass]

A crosstab isn't really designed to do this kind of comparison per row. If you don't have too many columns, it would make more sense to create a manual crosstab where you insert the three groups, and then you create a series of formulas:

//{@twodaysago}:
if {table.date} = currentdate-2 then {@secondsformula}

//{@yesterday:
if {table.date} = currentdate-1 then {@secondsformula}

//{@today}:
if {table.date} = currentdate-1 then {@secondsformula}

Then insert summaries on these at the agent group level.

Then create additional formulas:

//{@twodaysago-aveteam}:
sum({@twodaysago},{table.team})/count({@twodaysago},{table.team})

//{@yesterday-aveteam}:
sum({@yesterday},{table.team})/count({@twodaysago},{table.team})

Repeat for other days and then repeat for the city group, using the city field as the group condition. Place all formulas in the Group #3 group header or footer, and then suppress all other sections.

-LB

 

[djeddie]

kewl.

ill try that later

thanks for your assistance