Select Expert Formula 2

[Donzi255]

I have a field called asset_type. The field can be PDA (B); PC (C); or Laptop (L). I would like to see only the employees with all (3) asset types. My current selction shows me employees with just a PC, or just laptop or PC and laptop and all 3. How do I modify my selction to see employees with all 3 asset types ONLY, excluding the other employees.
I am runnning CR XI on SQL 2003.

Thanks

 

[kskid]

Insert a count or distinct count of asset_type within employee

go to Select Expert->Show Formula->Group Selection->Formula Editor and enter the following

Count ({table.asset_type},{table.employee}) = 3

You might use distinct count if an employee can have more than one of a given asset. For example, a distinct count would result in 2 instead of 3 if he had 2 PC's and 1 PDA.

 

[DataDog]

I wouldn't rely on any counts. I would say in the select expert :
(asset_type = 'B' and asset_type = 'C' and asset_type = 'L')

DataDog
If God wanted us to count in Hexadecimal, then why did he only give us A fingers?

 

[kskid]

I am assuming that the employee table and asset table is linked by employee_id

If you have an employee table and an asset table, then you will have a one to many relationship of employee to asset because you stated that an employee can have more than one asset. If so, then your selection formula must be

{asset.asset_type} in ['B','C','L']

After grouping by employee, insert a distinct count of {asset.asset_type}. This distinct will count the number of different assets the employee has in his possession. So if he has 5 PC's and a laptop, the distinct count will be 2 and not 7.

If you are wanting all employees that have all 3 assets, then the group selection will get them for you.

Distinct Count ({asset.asset_type},{employee.employee_id}) = 3

 

[lbass]

Select only the three types in your record selection formula:

{table.asset_type} in ["L","C","B"]

Insert a group on {table.employee} and then go to report->selection formula->GROUP and enter:

distinctcount({table.asset_type},{table.employee}) = 3

If you want to perform calculations across employees, you must use running totals, since non-group-selected records will contribute to the more usual inserted summaries.

-LB

 

[Donzi255]

Yes, assumptions correct. Asset_Type table & Employee table linked by emp_no. Yes, my selection formula is {asset.asset_type} in ['B','C','L']. I grouped by last name and inserted a distinct count. This resulted in the employee with 3 assets and the employee with 2 assets and the employee with 1 asset. Once I inserted the DistinctCount in the group selection = 3. IT WORKED !!!!!

THANKS !!!

 

[lbass]

Sorry about posting after the solution was already presented--I stepped away and then submitted without checking.

-LB