Any other way to do this other than a subreport?

[lawsonreports]

I know this is simple, but I am new to Crystal, and I'm stumped.

I have a table with these fields (greatly simplified):
Emp#, License_type, Active_flag

There are many License_types for each Emp#, but only one where Active_flag = Y.

Here is what I want: Show all records, by employee, for each employee that has a License_type = &parameter and Active_flag = Y.

So, for all employees that have an active license type of X(parameter), I want to see ALL of their licenses. Is there a way to do this without using a subreport?

 

[Turkbear]

Hi,
Use a Record selection formula much like the example you posted:

({Table.LicenseTypefield = {?Parameter} 
and
{Table.ActiveFlagField} = 'Y')

That should get you what you want




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lawsonreports]

Thanks for the quick reply. But that will only return the row with the active flag. I need to show ALL of the licenses that exist in the table (active or not, any type), for each employee that meets that criteria (has a licensetype = &parm and activeflag = Y) So I am hitting the table twice, so to speak.

 

[lbass]

Don't use these criteria in the record selection formula area. Instead insert a group on {table.emp#} and then create a formula {@haslic}:

if {Table.LicenseTypefield} = {?Parameter} and
{Table.ActiveFlagField} = 'Y' then 1

Then go to report->selection formula->GROUP and enter:

sum({@haslic},{table.emp#}) >= 1

-LB

 

[lawsonreports]

On the first formula - do I create that as part of the group (on the options tab)? Or just create a formula by itself, and put it...where? Sorry - I am trying it, I just am missing a step somewhere I guess.

 

[lawsonreports]

Okay - I got a little further. My formula {@HasLicense} looks like this:

If {EMPCODES.CODE} = {?LicenseType} and {EMPCODES.ACTIVE} = 'Y' then 1

and my GROUP selection formula is:

Sum ({@HasLicense},{EMPCODES.EMPLOYEE})>=1

But for each employee it is only returning the one active row, not all the rows (active or not, any type). What am I missing?

 

[lbass]

Remove the record selection criteria from the selection formula->record area, and only use the group selection area for the sum > 1 formula. This will return all records for those employees who meet your criteria.

-LB

 

[lawsonreports]

There are no formulas in the record selection area, only the Group Selection area is being used, and it has the sum formula. Still only getting the one active row darn it.

I don't want to take any more of your time but I do appreciate your help - I'll keep playing around with it and post back if I figure it out.

 

[Turkbear]

Hi,one thing is still not clear:
If you want ALL the records where a license type that matches the criteria is held and not just active licenses, why are you using {EMPCODES.ACTIVE} = 'Y' as part of the criteria?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Are you using more than one table? This could be a join issue. I know your first post implied you are only using one table, but the above solution should work. You should also make sure you don't have any section suppression by going to the section expert and checking the detail->suppress->x+2 area.

-LB

 

[lawsonreports]

Turkbear - Sorry if I was not clear. I want to return ALL license data, for those employees that have an active license of a certain type.

So if the license parameter is entered as "CL" and these employees have the following licenses:

Lic.
Employee# Type Active
101 CL Y
PV N
TP N

102 CL N
PV Y
PP N

103 CL Y

I want to see all of the license data for employees 101 and 103 only. I do not want any data returned for 102.

All of the license data is in one table. I do link the employee table, just on company & emp#, to get the name and SSN. I don't think that is the problem, but I don't know for sure.

That said - I tried another solution using an alias of the license table...and it worked! I'd still love to figure out the other way though...just for my own stubborn curiosity.

 

[lbass]

Did you create {@HasLicense} in the formula expert? My suggestion should have worked. Do you have any other record or group selection criteria? Any suppression?

Not sure it will make a difference but try using a left outer join to the table containing the name and SSN.

-LB