Convert Seconds to Minutes & Seconds in Cross Tab? 2

[Solojer]

I've got a cross-tab I would like to create which shows average time taken to process a task. The data is stored in the database in seconds. However, I would like the report to show minutes and seconds (easier to read, of course). For other similar reports (non-cross tab), I've created a formula that converts the minutes and seconds, and displays the result in a text field similar to "4 min, 3sec".

Where my problem lies, is when I try to show the data in a cross tab. To get the average time spent, the calculation must be done on the "seconds" field, but I want the resulting cross tab to show the converted value. Is this possible?

 

[IanWaterman]

YOu will need to produce a manual cross tab.

Use a formula to define each column

If (condition = true) the timefield else 0

Group report as required suppress details and the in group footer use your formula to convert the sum of each column into minutes and seconds.

This of course means you must have a defined number of columns for each row.

Ian

 

[lbass]

I think you can use an inserted crosstab and just select the summary->right click->format field->display string->x+2 and convert the seconds to a string there.

-LB

 

[Solojer]

I ran into a bit of problems when I tried what you suggested, LB.

In the "seconds" field on the cross-tab, I entered the following formula for "display string":

local numbervar minutes;
local numbervar seconds;

minutes:=truncate(average({Case.TimeSpent}) /60);
seconds:=remainder((average({Case.TimeSpent})),60);

totext(minutes,0,"")&":"&totext(seconds,0,"")

Now, all the values in the cross tab are the same number.

I think this has to do with the fact that it's taking the average of all the "Case.TimeSpent" values rather than the current field in the cross-tab...

How do I specify in the display string that the evaluation should only be done on the current field?

 

[lbass]

You should just be using:

whileprintingrecords;
numbervar curr := currentfieldvalue;
numbervar mins := truncate(curr/60);
numbervar secs := remainder(curr,60);
totext(mins,0,"")&":"&totext(secs,0,"")

-LB

 

[Solojer]

Thanks! That did it! I wasn't aware of the built in "currentfieldvalue".

Now I only have one more question regarding this report:On the same cross tab, I've got numbers for the current week, and the current fiscal year. How can I suppress the entire row in the cross tab from displaying if the current week value is 0?

 

[lbass]

What are the exact names of your row and column fields? Do you have them set up "on change of" some time interval? Or are you using a formula? Is so, what is the content? When you say the 'current week value', do you mean a row total?

-LB

 

[Solojer]

Here's how the cross tab is sorted:

Rows:
Table.Activity

Columns:
Table.Person_Name

Summarized fields:
Avg of @This_Week
Avg of @This_FY

//@This_Week
if date({Table.Start Date & Time}) in [currentdate-8 to currentdate-1] then {Table.TimeSpent} else 0

//@This_FY
numbervar cmonth:=month(currentdate);

//if April - December, user current year
if cmonth in [4 to 12] then (
numbervar cyear:=year(currentdate)
)
else (
cyear:=year(currentdate)-1;
);


(if date({Table.Start Date & Time})in [(date(01/04/cyear)) to currentdate] then {Table.TimeSpent} else 0)

Because this will be a weekly report, I don't want to show the activity if it wasn't done that week. I only want to compare the time taken on the activity this week with the rest of the fiscal year.

 

[lbass]

Can you clarify whether you want the averages to include the 0 values or not?

-LB

 

[Solojer]

Wow -- I must have been tired not to notice that! Thanks for pointing it out. How would I modify this to exclude zeros?

 

[lbass]

Change your "else 0" to "else tonumber({@null}). To create the {@null} formula, open and save a new formula without entering anything.

If an entire row contains nulls, then using "suppress empty rows" in the customize style tab should work.

-LB

 

[Solojer]

Will the "suppress empty rows" work if @This_Week is blank but @This_FY has a value? I tried it, and wasn't able to get it to work..

 

[lbass]

No, since the row isn't then empty. So you are hoping to suppress the entire row whenever the week is empty, including the fiscal year results, I guess?

-LB

 

[Solojer]

Yes, that's right.

 

[Solojer]

Is this possible?

 

[lbass]

Okay, it took me a while to figure this out. You need to have a group on {table.activity} in your main report for this to work. Create these formulas:

//{@This_Week}:
if date({Table.Start Date & Time}) in
currentdate-8 to currentdate-1 then
{Table.TimeSpent} else
tonumber({@null})

Then create two formulas:

//{@Wk}:
if not isnull(average({@This_Week},{table.activity})) then
{@This_Week}

//{@FY}:
if not isnull(average({@This_Week},{table.activity})) then
{@This_FY}

Use these formulas as summary fields in the crosstab and in the customize style tab, make sure that the "suppress empty rows" is checked.

-LB