Using Formulas in Crosstabs

[DLTaylor]

Hi

I am using Crystal Reports 7.

I am reporting on Helpdesk call information. I have got a field which shows the priority of a call and another which shows the fix time for a call (in minutes). I want to use a crosstab to display the average fix time for each priority. I have got a formula which shows the average fix time in days/hours/minutes. When I try to create the crosstab, the formula field is not in the list of fields available.

If the formula field was available for selection, the cross tab would be really simple!

I am trying to achieve something like below:

Average fix time

Priority 1 0 days, 2 hours, 4 minutes
Priority 2 2 days, 4 hours, 12 minutes
Priority 3 5 days, 3 hours, 4 minutes

Please can anyone help!

Thanks.

 

[synapsevampire]

The field likely isn't a number, hence it can't be used in an aggregate.

At any rate, try posting your formula as opposed to describing what it is...

-k

 

[DLTaylor]

The formula I am using is this:

numberVar dur := Average ({ResponseMN});
numberVar days;
numberVar hrs;
numberVar min;
stringVar result;

days := Truncate(dur/480);
hrs := Truncate(Remainder(dur,480)/60);
min := Remainder(Remainder(Remainder(dur,480),60),60);


result := totext(days, "0&quot + " day(s), " + totext(hrs, "0&quot + " hour(s), " + totext(min,"0&quot + " minute(s)";

 

[lbass]

Your example does not indicate the need for a crosstab, since you have no column field in your crosstab, just a summary that varies by the group field {table.priority}. If you do, in fact, have a column field, e.g., date grouped by month, or something like that, please let us know. If that is the case, you could accomplish your display by creating a manual crosstab.

As it is, it looks like you need to group on {table.priority}, and then adjust your formula to:

numberVar dur := Average ({table.ResponseMN},{table.priority});
numberVar days;
numberVar hrs;
numberVar min;
stringVar result;

days := Truncate(dur/480);
hrs := Truncate(Remainder(dur,480)/60);
min := Remainder(Remainder(Remainder(dur,480),60),60);

result := totext(days, "0&quot + " day(s), " + totext(hrs, "0&quot + " hour(s), " + totext(min,"0&quot + " minute(s)";

You could display this in the group header or footer. The only change I made was to define this as an average within the group.

-LB