Employee A has a custom field that contains another employee's ID number, which belongs to Employee B. My report shows all of Employee A's information along with the custom field that will have Employee B's ID number. How do I bring in Employee B's employee information (name, job, etc...) and keep them all on one list? Is there a way within a data expression to do a lookup? For example, "lookup [Employee Name] for [EecUDField12] where EecUDField12 is Employee B's ID number.
Thank you for any help!
In report studio, go to the "Queries", the topmost item in the query explorer popout tab. Drag a "join" object from the toolbox to the query area. This will create a new query with two spots for the queries you want to join. You can drop your existing query into both of them, but use two separate queries with different names to keep things a little clearer when dealing with the RS interface. (You will see why in a moment.) Now double-click the join and define the relationship linking the employee B column from one query to the employee id column in the other query. Double-click on the new query and define the data items you want to use in your report. If you used two queries called "main" and "empb", for example, your new query might have data items [main].[name] and [empb].[name].
I hope that gets you pointed in the right direction.