No data seen in Report Studio

Hello ,
I’m working on WO Component Report and adding Po No’s to Production Order data source using Syntax Join . The join was successfully tested i was able to see the PO_HDR table in our data source . However , when i add the po’no’s i dont see any data but we have all the data related to the PO’Nos .

Here’s the join i used

  LEFT JOIN po_hdr on (po_hdr.order_date = prod_order_hdr.order_date)
SELECT
  TOP 100 ' ' placeholder,
  prod_order_hdr.prod_order_number,
  prod_order_hdr.order_date,
  oe_hdr.order_no,
  prod_order_hdr.source_location_id,
  location.location_name,
  inv_mast.item_id,
  inv_mast.item_desc,
  component_item_info.item_id,
  component_item_info.item_desc,
  prod_order_hdr.company_id,
  oe_hdr.order_date,(
    CASE WHEN prod_order_line_component.service_labor_uid IS NULL THEN prod_order_line_component.qty_used ELSE prod_order_line_comp_labor.hours_worked END
  ) AS actual_qty,(
    CASE WHEN prod_order_line_component.service_labor_uid IS NULL THEN 'Item Component' ELSE 'Labor' END
  ) AS component_type,
  oe_hdr.date_created,
  prod_order_line_component.qty_requested,
  prod_order_line_component.line_number,
  prod_order_line_component.prod_order_number,
  oe_hdr.original_promise_date,
  oe_hdr.taker,
  oe_hdr.source_id,
  oe_hdr.source_location_id,
  comp_class_id1.class_description,
  comp_class_id2.class_description,
  comp_class_id3.class_description,
  comp_class_id4.class_description,
  comp_class_id5.class_description,
  class_id1.class_description,
  class_id2.class_description,
  class_id3.class_description,
  class_id4.class_description,
  class_id5.class_description,
  prod_order_hdr.approved,
  prod_order_line.cancel,
  customer.customer_name,
  prod_order_line.qty_to_make,
  oe_hdr.promise_date,
  oe_line.disposition,
  prod_order_line.deposit_bin,
  prod_order_line_component.qty_canceled,
  prod_order_hdr.expected_completion_date,
  oe_hdr.order_priority_uid,
  prod_order_hdr.release_date,
  prod_order_line_component.qty_allocated,
  prod_order_line_component.qty_needed,
  prod_order_line_component.qty_on_pick_tickets,
  po_hdr.po_no,
  po_hdr.dts_original_po_no,
  prod_order_line_component.date_created,
  po_hdr.order_date,
  po_hdr.date_created,
  po_hdr.expected_date,
  po_hdr.expected_ship_date,
  po_hdr.date_due,
  po_hdr_notepad.note,
  address.name,
  inventory_supplier.supplier_id,
  prod_order_hdr.entered_by,
  po_hdr_notepad.po_no
FROM
  prod_order_hdr
  INNER JOIN prod_order_line ON prod_order_line.prod_order_number = prod_order_hdr.prod_order_number
  AND COALESCE(prod_order_line.cancel, 'N') <> 'Y'
  AND prod_order_line.completed = 'N'
  INNER JOIN prod_order_line_component ON prod_order_line_component.prod_order_number = prod_order_line.prod_order_number
  AND prod_order_line_component.line_number = prod_order_line.line_number
  AND COALESCE(prod_order_line_component.disposition, '') <> 'C'
  INNER JOIN (
    SELECT
      prod_order_line_component.prod_order_line_component_uid,
      COALESCE(service_labor.service_labor_id, inv_mast.item_id) 'item_id',
      COALESCE(
        service_labor.service_labor_desc,
        inv_mast.item_desc
      ) 'item_desc'
    FROM
      prod_order_line_component
      INNER JOIN inv_mast ON inv_mast.inv_mast_uid = prod_order_line_component.inv_mast_uid
      LEFT JOIN service_labor ON prod_order_line_component.service_labor_uid = service_labor.service_labor_uid
  ) AS component_item_info ON component_item_info.prod_order_line_component_uid = prod_order_line_component.prod_order_line_component_uid
  INNER JOIN inv_mast ON prod_order_line.inv_mast_uid = inv_mast.inv_mast_uid
  INNER JOIN inv_mast AS inv_mast_comp ON prod_order_line_component.inv_mast_uid = inv_mast_comp.inv_mast_uid
  INNER JOIN inventory_supplier ON prod_order_line_component.supplier_id = inventory_supplier.supplier_id
  AND inv_mast_comp.inv_mast_uid = inventory_supplier.inv_mast_uid
  INNER JOIN location ON prod_order_hdr.source_location_id = location.location_id
  LEFT JOIN prod_order_line_comp_labor ON prod_order_line_component.prod_order_line_component_uid = prod_order_line_comp_labor.prod_order_line_component_uid
  LEFT JOIN prod_order_line_link ON prod_order_line_link.prod_order_number = prod_order_line.prod_order_number
  AND prod_order_line_link.prod_order_line_number = prod_order_line.line_number
  AND prod_order_line_link.trans_type = 'O'
  LEFT JOIN oe_line ON oe_line.oe_line_uid = prod_order_line_link.transaction_uid
  LEFT JOIN oe_hdr ON oe_line.order_no = oe_hdr.order_no
  LEFT JOIN customer ON oe_hdr.customer_id = customer.customer_id
  AND oe_hdr.company_id = customer.company_id
  LEFT JOIN contacts ON (contacts.id = oe_hdr.contact_id)
  LEFT JOIN company ON (prod_order_hdr.company_id = company.company_id)
  LEFT JOIN service_technician ON (
    prod_order_line_comp_labor.service_technician_uid = service_technician.service_technician_uid
  )
  LEFT JOIN contacts AS contacts_2 ON (service_technician.contacts_id = contacts_2.id)
  LEFT JOIN class AS class_id1 ON (inv_mast.class_id1 = class_id1.class_id)
  AND (class_id1.class_type = 'IV')
  AND (class_id1.class_number = 1)
  LEFT JOIN class AS class_id2 ON (inv_mast.class_id2 = class_id2.class_id)
  AND (class_id2.class_type = 'IV')
  AND (class_id2.class_number = 2)
  LEFT JOIN class AS class_id3 ON (inv_mast.class_id3 = class_id3.class_id)
  AND (class_id3.class_type = 'IV')
  AND (class_id3.class_number = 3)
  LEFT JOIN class AS class_id4 ON (inv_mast.class_id4 = class_id4.class_id)
  AND (class_id4.class_type = 'IV')
  AND (class_id4.class_number = 4)
  LEFT JOIN class AS class_id5 ON (inv_mast.class_id5 = class_id5.class_id)
  AND (class_id5.class_type = 'IV')
  AND (class_id5.class_number = 5)
  LEFT JOIN class AS comp_class_id1 ON (
    inv_mast_comp.class_id1 = comp_class_id1.class_id
  )
  AND (comp_class_id1.class_type = 'IV')
  AND (comp_class_id1.class_number = 1)
  LEFT JOIN class AS comp_class_id2 ON (
    inv_mast_comp.class_id2 = comp_class_id2.class_id
  )
  AND (comp_class_id2.class_type = 'IV')
  AND (comp_class_id2.class_number = 2)
  LEFT JOIN class AS comp_class_id3 ON (
    inv_mast_comp.class_id3 = comp_class_id3.class_id
  )
  AND (comp_class_id3.class_type = 'IV')
  AND (comp_class_id3.class_number = 3)
  LEFT JOIN class AS comp_class_id4 ON (
    inv_mast_comp.class_id4 = comp_class_id4.class_id
  )
  AND (comp_class_id4.class_type = 'IV')
  AND (comp_class_id4.class_number = 4)
  LEFT JOIN class as comp_class_id5 ON (
    inv_mast_comp.class_id5 = comp_class_id5.class_id
  )
  AND (comp_class_id5.class_type = 'IV')
  AND (comp_class_id5.class_number = 5)
  LEFT JOIN po_hdr on (po_hdr.order_date = prod_order_hdr.order_date)
  LEFT JOIN po_hdr_notepad on (po_hdr_notepad.po_no = po_hdr.po_no)
  LEFT JOIN address ON (address.id = inventory_supplier.supplier_id)


the PO’Nos .

Did you test the SQL in SSMS? It looks like your join on the PO_HDR is not specific enough and will pull too many records.