Formula in a Query

[jalenben1]

I am trying to write a formula in Crystal Reports. I am trying to include the names of technicians in my report. There user names are used in the database but I want to see there real names. For example I have:

If {Huawei_Dashboard.Owner} = "N1ANANDER" then "Antonio Anderson";

How to continue with the formula and add other techs names;

N1EDPEREZ - Eddie Perez
N1NDIAZ - Nicole Diaz
N1MLEWIS - Maurice Lewis

 

[briangriffin]

The actual names don't exist anywhere in the database? This formula will do what you want, but must be modified anytime a new employee is added.

select {Huawei_Dashboard.Owner}

case "N1ANANDER": "Antonio Anderson"
case "N1EDPEREZ": "Eddie Perez"
case "N1NDIAZ": "Nicole Diaz"
case "N1MLEWIS": "Maurice Lewis"
default: "New Employee"

 

[jalenben1]

Thanks Brian Griffin -

That worked perfectly. I was playing around with this formula and ogt the following:

If {Huawei_Dashboard.Owner} = "N1ANANDER" then "Antonio Anderson”

Else if

{Huawei_Dashboard.Owner} =’”N1EDPEREZ” then “Eddie Perez”

Else if

{Huawei_Dashboard.Owner} =” N1NDIAZ” then “Nicole Diaz”

Else if

{Huawei_Dashboard.Owner} =” N1MLEWIS” then “Maurice Lewis”

The actual names does not exists in the database. I have another question.


I have a list of times for the MTTR (Mean Time to Repair) for each tech. How do I get a average of the total of MTTR times for each tech.

 

[briangriffin]

Insert a group on the name formula you just created. Do an Insert--> Summary, then select the MTTR field and choose "Average". It will put the summarized field in the group footer; you may want to drag it to the group header instead.

Suppress or hide the details section if you don't want to display all the records.

Also, I'm assuming that you're joining the HUAWEI_Dashboard to the table containing the MTTR's, using the Owner field to link them.

 

[jalenben1]

The field name for the MTTR is Huawei_Dashboard.MTTR Response Time.

 

[jalenben1]

I am not seeing where to choose the word AVERAGE.

 

[briangriffin]

What is the datatype of the MTTR field? Show a couple of examples, please.

 

[jalenben1]

The data type is as for example, 13:33
0:54
it is a formatted field.

 

[briangriffin]

Rather than using the MTTR field, are there also fields that list the start and end times used to calculate the MTTR? That would make it easier.

If not, we need to know the specific datatype of the MTTR field in the database. In the field explorer right click on it and select Browse Data - at the top will be the datatype - date, string, etc.