Formulas using same table information 1

[LAMLIS]

Hi

I have a table called PAYIHIST.

In one column I would like to report the sick incidents for each employee so I created a formula:

if {PAYIHIST.ACCUMCD}= 'sinc' then
{PAYIHIST.AMOUNT}

In the next column I would like to write another formula that pulls that employees worked hours in their history

I thought I could write another formula:

if {PAYIHIST.ACCUMCD}= 'regu' then {PAYIHIST.AMOUNT}

but all I am getting is the hours for 'sinc' not 'regu'

LL

 

[lbass]

You are essentially creating a manual crosstab. If you place these formulas in the detail section, results will appear in different lines. You should insert summaries on them at the employee group level.

Or you could insert a crosstab in the report header or footer that uses employee as the row, and accumcd as the column, and amount as the summary field.

-LB

 

[LAMLIS]

I should clarify

{PAYIHIST.ACCUMCD} has many different paycodes and depending on the paycode {PAYIHIST.AMOUNT} may either be a count or hours.
Here is a brief discription of what is in the tables

For example {PAYIHIST.ACCUMCD} {PAYIHIST.AMOUNT}

Employee 1 SINC 1
REGU 67.5
SKPD 7.5

In my Crystal report, I would like group the data this way
Employee Name Sick Incidents Worked Hours
Employee 1 1 75

Thanks, LL

 

[lbass]

Create a formula like this:

if {PAYIHIST.ACCUMCD} = 'sinc' then
"Sick Incidents" else
"Worked Hours"

Use this as your column field in the crosstab, use employee as the row, and use sum of {PAYIHIST.AMOUNT} as your summary field.

-LB

 

[LAMLIS]

Thank you that is working but how do I exclude {PAYIHIST.ACCUMCD} codes that I do not want.

Thanks LL

 

[lbass]

Can you remove them in your selection formula or do you need them for other purposes in the report?

-LB

 

[LAMLIS]

I will need those accumalator codes elsewhere. Here is how the report should look when I am done

The first sort needs to be by department then employee

Here is the layout of my columns

Department Name/ Employee Name/ Sick Incidents/ Worked Hours/ Sick Hours/ YTD Sick Hours

{PAYIHIST.ACCUMCD] = SINC for the sick incidents but the count is in {PAYIHIST.AMOUNT}

For the Worked Hours in {PAYIHIST.ACCUMCD] there are about 20 codes that I need to pick up their {PAYIHIST.AMOUNT} for

For the Sick Hours in {PAYIHIST.ACCUMCD] there are about 4 codes that I need to pick up their {PAYIHIST.AMOUNT} for.

For the YTD sick hours it will be the same codes but YTD values instead of the period.

Thanks, LL

 

[lbass]

Does the report only show one period with a YTD column or does it show all periods YTD?

-LB

 

[LAMLIS]

With the exception of the YTD column all other columns will have data that corresponds to the same period. I was thinking I would add a parameter to the employees {SHIFTDATE} for the period I'm looking at.

Thanks, LL

 

[lbass]

Is the YTD column a calculated one or does it already contain summary information? In other words, do you need to bring ytd records into the report in order to get this figure?

-LB

 

[LAMLIS]

No I do not need to bring YTD figures into the report, I can just filter on the accumulator codes for sick time but have the period YTD rather than just for the period of the rest of the report.

I hope this makes sense

Thanks, LL