sql server - SSRS Form/Report and Outer Join Results -

i've been tasked developing enrollment form based on benefit coverage employees have , not have current year. each page of full report show information each individual employee typical information displayed @ top , listing of available benefit types column next displaying possible dollar figure indicate employee paying type of coverage. if column blank, employee doesn't have coverage.

this form looks like:

i've written query employs left outer joins returns row every benefit type master benefits table of rows including employee information , payment amount 1 single employee. when modify query retrieve results more 1 employee results set includes additional rows benefits employee paying for. thought need work out dataset total number of rows = number of employees * number of benefits. maybe i'm going wrong way though.

searched high , low felt need reach out community i've learned from. yeah, first time lurker post. situation has been described , concisely.

edit: here sql gets me results i'm looking on 1 person. when ask additional employees returns rows benefits pay on. benefits table has row benefit employee pays for. pr_deduction_master table has benefits. don't ask me code designation... seems implementation poorly done in respects.

select emp.a_name_last + ', ' + emp.a_name_first 'name',   emp.a_employee_number, emp.e_ssn, emp.e_email,    emp.ed_p_address1, emp.ed_p_address_city, emp.ed_p_address_state, emp.ed_p_address_zip,    a.a_deduction_code, a.a_ded_desc_long, b.a_employee_number, b.dh_emp_amount (select distinct a_deduction_code, a_ded_desc_long pr_deduction_master a_deduction_code in  (2000,2005,2007,2008,2010,2015,2020,2025,2027,2030,2040,2045, 2050,8055,8205,8210,8215,8220,8225,8230,8235,8240,8245,8250, 8252,8253,8255,8260,8280,8290,8295,8300,8305,8310,8315,8316, 8700,8701,8702,9100,9150,9151,9200)) left outer join (select distinct a_employee_number, a_deduction_code, dh_emp_amount pr_ded_history a_employee_number in (603) , dh_pr_warr '10816a') b on a.a_deduction_code = b.a_deduction_code left outer join (select a_employee_number, a_name_last, a_name_first, ed_p_address1, ed_p_address_city, ed_p_address_state, ed_p_address_zip, e_ssn, e_email  pr_employee_master) emp on b.a_employee_number = emp.a_employee_number order a_ded_desc_long 

i hope make gag much. novice trying better here. i'll these suggestions. appreciate responses.

edit 2: following daniel e's advice, "take [employees] cross apply [benefits] before left joining table of [employeebenefits]" cross apply shows me what's needed below.

employee table , benefits table

when try variation of joins 3rd table, deductions history table each employees deductions recorded month, undesired results. i'm looking join third table , display null on lines employees didn't have type of benefit deduction , dollar amount benefits paid on previous period.

to accomplish trying achieve went close daniel e. said.

take [employees] cross apply [benefits] before left joining table of [employeebenefits] – daniel e.

the difference utitilized cross join. when left joined [employeebenefits] table still having difficulty because trying apply filter table in turn made inner join. in turn solved nesting query against table filter.

again, thank thoughts , help!!