Advanced Record Selection 1

[kj27]

Hello All,

I'm not sure if this is possible because I haven't been able to do find a method so far. I'm using Crystal XI. We have a static parameter with manager's names. What I would like to do is filter record selection to only employees under the selected manager.

The employee and manager's user record is stored in the UserInfo table which is linked to a UserSecurity table (using UserID field). An employee record in UserSecurity has the ManagerID set to the manager's UserID.

So the lookup is not direct. It would have to be something like:

{UserSecurity.ManagerID} =
Select UserID from UserInfo Where Name = {?ManagerName}

I don't think SQL expressions are designed to support something like this. Any suggestions would be greatly appreciated.

Thanks.

 

[lbass]

I think you should be using the Manager ID for your parameter value and then show the corresponding UserInfo.Name field as the description field in the parameter (you can set it to show ony the Manager's name). Then your selection formula would simply be:

{UserSecurity.ManagerID} = {?Manager}

-LB

 

[kj27]

Thanks LB! This is now working exactly as desired. One question - I'm using the UserSecurity.ManagerID as the underlying value field and UserInfo.UserName as the description field. But the Value drop-down only populates when editing the parameter if the description field is set to None. For now, I have selected all the necessary Value items from the drop-down which is populated dynamically. Then I set the Description field to UserInfo.UserName and manually type in the actual names (descriptions). I think the join is OK because both tables are linked using UserID.

 

[lbass]

I thought this was a static parameter. You would need to have the userinfo table linked to the usersecurity table, and then you should be able to just select the value and description from the dropdowns and then select append all values.

If this is a dynamic parameter, please let me know.

-LB