Formula to convert specific letter to desired result

[PBTX]

I am trying to convert thousands of union codes to reflect their labor class. The second letter of each code dictates what their labor code is.

if the code is CL0532 I need it to return Laborer. Can someone please help?

Thanks

 

[lbass]

How many labor classes are there? Do you have a lookup table that shows the one-character code and description?

-LB

 

[PBTX]

We have six specific codes I need to convert. If I am understanding you correctly, I am using the PR_Time_Card_History_MC.Union_Code as my table. The second position is the key to their labor code. If it is L it is Laborer, if it is D it is driver, if it is O it is operator.

Thank you, for your help.

 

[lbass]

If you only have six codes to convert, then just create a formula like this in the field explorer->formula->new:

if isnull({PR_Time_Card_History_MC.Union_Code}) then
"" else //or "No Code"
(
select mid({PR_Time_Card_History_MC.Union_Code},2,1)
case "D" : "Driver"
case "O" : "Operator"
case "L" : "Laborer" //etc.
)

-LB

 

[PBTX]

Thank you, I had the second part with the 2 and the cases, but did not have the one or the first line.

 

[PBTX]

Well, my report looks great except when I added the race id string it increased my running total for hours.

I am using the following for my total hours - {#RTotal0} + {#RTotal1} I have tried multiple formula's to get it to only count the race id row once to no avail.

Any help would be greatly appreciated.

Thanks,

 

[lbass]

Please explain the exact set up of each running total.

Also identify the fields you are grouping on in the report.

Is the race ID being used for some other purpose or is it relevant to these totals?

-LB

 

[PBTX]

No the race id is purely to show the ethnicity for minority reporting purposes.

Group footer #3 pr_employment-utilization_mc.eeo_class_code-A
employeecode1: pr_employment_utilization_mc.employee
employeename1: pr_employment_utilization_mc.employee
SSN1: @ssn
Hours1hours
Sex1: @ Sex
raceId1: pr_employment_utilization_mc.raceid
unioncode1: @union code
hours2: pr_time_card_history_mc.hours
workclass1work_class


prior to adding Race my total hours were 6.00 for one person. After adding race my total for the same person is 2688.00

 

[lbass]

Please identify all fields you are grouping on, and explain the set up of each running total.

-LB

 

[PBTX]

I am new at this and need a little guidance as to where I would locate the grouping. If I go to grouping there is nothing.

 

[lbass]

Go to report->group expert and list the fields shown there for each group.

For the running totals, go into the field explorer->running total->select the running total->edit (to see the setup).

-LB

 

[PBTX]

Groups
PR_Employment_utilization_MC.job_Number-A
PR_Employment_utilization_mc.employee_code_A
PR_Employment_utilization_MC_EEO_class_code-A

Running totals: sum

RTotal0 PR_Employement _Utilization _MC.Male_hours_list1
RTotal1 PR_Employement_Utilization_MC.Female_hours_list1

on change of group = PR_employment_utilization_MC_EEO_class_Code-A

 

[lbass]

So are the groups in order #1 = Job No,#2=Empl_code,#3=eeo class?

For the rts, what is the set up in the evaluation section? Is the on change of group for the evaluation section or the reset section?

In what report section are the rts currently placed?

-LB

 

[PBTX]

Nothing in evaluation section
rts = reset

groups are in order

The RTS are in group#3

 

[lbass]

Instead of using running totals, create a formula like the following:

{PR_Employement _Utilization _MC.Male_hours_list1}+
{PR_Employement_Utilization_MC.Female_hours_list1}

Place this in the detail section and then right click on it and insert a sum at the group and/or at the grand total level.

-LB

 

[PBTX]

Thank you, the hours are back to what they should be.