Formula Editor

[ChrisH1]

Hi

Im trying to run a report in Crystal 8 with the following formula included

{Job} is equal to 123456

but what I want the report to do is display all records for employees that have allocated time to 123456 but display all the jobs that they have booked to as well as. I just cant think of a way to structure this on the report.

Thanks

 

[IanWaterman]

You can group on this formula

If {Job} = 123456 then totext({Job},0,"") else 'Other Jobs'

This assumes {job} is a number. No need to the totext if a string.

Ian

 

[ChrisH1]

Many thanks, Im actually modifying en existing report but an error now appears when adding in the above that too many arguments have been given to this function.


{SYJOBTRN.WORK_STAGE} >= ' ' and
{SYJOBTRN.WORK_STAGE} <= 'zzzzzzzzzzzzzzzz' and
{SYJOBTRN.ACTIVITY_CODE} >= ' ' and
{SYJOBTRN.ACTIVITY_CODE} <= 'zzzzzzzzzzzzzzzz' and
{SYJOBTRN.WEEK_NO} in 0.00 to 999999.00 and
{SYJOBTRN.APPLY_OVERHEAD} = 1.00 and
{PEPERSON.DISCIPLINE} >= ' ' and
{PEPERSON.DISCIPLINE} <= 'zzzzzzzz' and
{PEPERSON.GRADE} >= ' ' and
{PEPERSON.GRADE} <= 'zzzzzzzz' and
(1 = 1 OR
{JCJOBS.JOB_STATUS} = 0) and
(1 = 1 or
{JCJOBS.ACTIVE_FLAG} = ' ') and
(1 = 1 or
{JCJOBS.PRODUCTIVE} = 0) and
(1 = 1 or
{JCJOBS.PARTNER_IN_CHARGE} = ' ') and
(1 = 1 or
{JCJOBS.JOB_RUNNER} = ' ') and
(1 = 1 or
{JCJOBS.JOB_TYPE} = 0) and
(1 = 1 or
{JCJOBS.SL_ACCOUNT_CODE}= ' ') and
{PEPERSON.PERSONNEL_NO} >= ' ' and
{PEPERSON.PERSONNEL_NO} <= 'zzzzzzzzzzzzzzzz' and
{PEPERSON.COMPANY} in ["A", "D"] and
if {SYJOBTRN.JOB} = 'A012621' then totext({SYJOBTRN.JOB},0,"") else 'Other Jobs'

 

[IanWaterman]

What I gave you has to be a formula within the report and then group on that formula.

I assume the above is your select statement?

You can not add the formula there.

Ian

 

[ChrisH1]

Your quite right, not sure what I was doing there.

Still have a problem with this being a standalone formula :
if {SYJOBTRN.JOB} = 'A012621' then totext({SYJOBTRN.JOB},0,"") else 'Other Jobs'

this generates too many argumnets for this function

the {SYJOBTRN.JOB} is a string so I removed the totext addition but an error came up with missing (.

Thanks for the help

 

[IanWaterman]

So you should have

if {SYJOBTRN.JOB} = 'A012621' then {SYJOBTRN.JOB}else 'Other Jobs'

There should not be any brackets.

Ian

 

[ChrisH1]

Im seeing records returned for employees that have not worked on job A012621. Im trying to get the report to only display records for those staff with time on A012621 but still show the other jobs they have worked on.

At the moment Im seeing everybody and every job. Any ideas ?

 

[IanWaterman]

Sorry Misread your post.

YOu can use a sub report.

Filter main report by {SYJOBTRN.JOB} = 'A012621'
Group by employee.

Then build a subreport and link on Employee ID, place SR in Employee froup footer.

Ian

 

[ChrisH1]

Finally got it working by inserting the report itself as the sub report and then hiding the main one, so only the subreport was visible.

Many thanks for helping me get there.