SQL Server Performance

Two Datasets one Table - can it be done?

Discussion in 'SQL Server 2005 Reporting Services' started by L0st_Pr0phet, Nov 27, 2008.

  1. L0st_Pr0phet Member

    Hi,
    I am new to reporting services building so sorry if this is a stupid question. I have two datasets that reference two SQL Servers. What I want to do is build a table with three columns from dataset 1 and two columns from dataset 2 where say column a from dataset 1 matches column d in dataset 2.
    I keep getting either only the first row being replicated through the table or a sum being replicated. Is there a way of doing this:
    Two dataset queries:
    D1:
    Select
    RPAN8 [Customer number],
    ABALPH [Customer Name],
    ROUND((sum(RPAAP)/100),0) as [Amount Open],
    A5ACL [Credit Limit],
    ROUND((A5ACL - sum(RPAAP)/100),0) as [Variance]
    from PRODDTA.F03B11 a
    inner join PRODDTA.F0301 b on a.RPAN8= b.A5AN8
    inner join PRODDTA.F0101 c on a.rpan8 = c.aban8
    Where RPAN8 between '3000000' and '3999999'
    And RPAAP <> 0
    Group by RPAN8,A5ACL,ABALPH
    D2:
    select c.company_name, b.cust_id,
    SUM( a.wo_total) as Work_unbilled
    from JM_Work_Order a
    Inner Join JM_Job b ON a.job_no = b.job_no
    Inner Join sys_phone_list c ON b.cust_id = c.list_id
    Inner Join JM_WO_Transaction d ON a.wo_no = d.wo_no and a.wo_seq = d.wo_seq
    where a.Phase_code IN ('PBil','Bill','Aprv')
    Group By c.company_name, cust_id
    Order by SUM( a.wo_total) desc
    the link would be on [customer number] & cust_id
    cheers
    stew
  2. satya Moderator

    I think you cannot join datasets in RS. You either need to do this in a stored procedure OR you should think about subreports. Usually what you need to do can be accomplished with subreports. Probably a tour to Books Online for SSRS will hep you.
  3. Madhivanan Moderator

    Create a stored procedure that joins the queries and get comman data from it and design the report based on this sp

Share This Page