LB,
Here is my SQL Query.
SELECT DISTINCT
participant."name_last", participant."name_first", participant."id", participant."position",
formula."formula", formula."compensation_total",
Participant_Move_in_Trans."Project", Participant_Move_in_Trans."Zone", Participant_Move_in_Trans."Apartment_Type", Participant_Move_in_Trans."Sub_Project_Code", Participant_Move_in_Trans."Monthly_Rate", Participant_Move_in_Trans."Building", Participant_Move_in_Trans."Apartment", Participant_Move_in_Trans."Orig_Move_in_Date", Participant_Move_in_Trans."Reservation_Date", Participant_Move_in_Trans."Commission_Qualifying_Date", Participant_Move_in_Trans."Length_of_Stay_Days", Participant_Move_in_Trans."Tenant_Last_Name", Participant_Move_in_Trans."Corp_Name", Participant_Move_in_Trans."Six_Mo_Lease_Indicator", Participant_Move_in_Trans."Term", Participant_Move_in_Trans."International_Indicator", Participant_Move_in_Trans."Trans_Type", Participant_Move_in_Trans."Lease_Target_Rate", Participant_Move_in_Trans."Trans_Category", Participant_Move_in_Trans."Tier_Elig_Count", Participant_Move_in_Trans."Tier_Level", Participant_Move_in_Trans."Prod_Com_Elig", Participant_Move_in_Trans."Prod_Com_Local", Participant_Move_in_Trans."Prod_Com_Outbound", Participant_Move_in_Trans."Prod_Com_Inbound", Participant_Move_in_Trans."Override_Price_Variation", Participant_Move_in_Trans."Override_Com_Individual", Participant_Move_in_Trans."Min_Individual_Amt", Participant_Move_in_Trans."Other_Com_Type", Participant_Move_in_Trans."Other_Com_Individual_Amt", Participant_Move_in_Trans."Intl_Com_Amt", Participant_Move_in_Trans."Six_Mo_Contract_Com_Amt", Participant_Move_in_Trans."Total_Com",
compensation."compensation_total",
plan_manager."set_period_num", plan_manager."set_period_des", plan_manager."set_year",
LocalYTD."LOC_TRANS_CNT",
InboundYTD."IN_TRANS_CNT",
OutboundYTD."OUT_TRANS_CNT",
InternationalYTD."INTERNATIONAL_TRANS_CNT",
ShortTermYTD."SHT_TRM_TRANS_CNT",
SixMonthYTD."IN_TRANS_CNT",
UnfurnishedYTD."INTERNATIONAL_TRANS_CNT",
WholesaleYTD."INTERNATIONAL_TRANS_CNT"
FROM
{ oj ((((((((((("Oakwood"."dbo"."participant" participant INNER JOIN "Oakwood"."dbo"."Participant_Move_in_Trans" Participant_Move_in_Trans ON
participant."id" = Participant_Move_in_Trans."Participant_Id"

INNER JOIN "Oakwood"."dbo"."compensation" compensation ON
participant."id" = compensation."id"

INNER JOIN "Oakwood"."dbo"."formula" formula ON
participant."id" = formula."id"

LEFT OUTER JOIN "Oakwood"."dbo"."plan_manager" plan_manager ON
participant."keyfield" = plan_manager."keyfield"

LEFT OUTER JOIN "Oakwood"."dbo"."InboundYTD" InboundYTD ON
Participant_Move_in_Trans."Participant_Id" = InboundYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."OutboundYTD" OutboundYTD ON
Participant_Move_in_Trans."Participant_Id" = OutboundYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."InternationalYTD" InternationalYTD ON
Participant_Move_in_Trans."Participant_Id" = InternationalYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."ShortTermYTD" ShortTermYTD ON
Participant_Move_in_Trans."Participant_Id" = ShortTermYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."SixMonthYTD" SixMonthYTD ON
Participant_Move_in_Trans."Participant_Id" = SixMonthYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."UnfurnishedYTD" UnfurnishedYTD ON
Participant_Move_in_Trans."Participant_Id" = UnfurnishedYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."WholesaleYTD" WholesaleYTD ON
Participant_Move_in_Trans."Participant_Id" = WholesaleYTD."Participant_Id"

LEFT OUTER JOIN "Oakwood"."dbo"."LocalYTD" LocalYTD ON
Participant_Move_in_Trans."Participant_Id" = LocalYTD."Participant_Id"}
WHERE
(participant."position" = 'OAKWOOD AC' OR
participant."position" = 'OAKWOOD AE' OR
participant."position" = 'OCH AC' OR
participant."position" = 'OCH AE')
ORDER BY
participant."id" ASC,
Participant_Move_in_Trans."Reservation_Date" ASC,
Participant_Move_in_Trans."Orig_Move_in_Date" ASC,
Participant_Move_in_Trans."Project" ASC,
Participant_Move_in_Trans."Sub_Project_Code" ASC,
Participant_Move_in_Trans."Building" ASC,
Participant_Move_in_Trans."Apartment" ASC
The "Keyfield" is a system field and is of "char" type. I tried to use an equal join from {Particiapnt} to {Plan_Manager} and it gave me no data in the report. Once I change it to left outer join it starts working.
I tried to put the fields (preiod_year) in the details section and they still give me zeros.
I am really grateful for all your time and consideration.
Kchaudhry