Multiple temp tables in stored procedure and creating ssrs

Question

I am new to ssrs. I have a stored procedure which has input parameters. Based on those parameters, I am creating a temp table and then I have 5 other temp tables. And the final temp table has the result set.

I have to use this in SSRS. I created the data source and in data set, provided the stored procedure name. Parameters are showing correctly, but the dataset shows different columns which are not the same as in the final table.

I have question that if I am using 6 temp tables, and the final table t6 is the result - do I have to make a query statement like @sql6 = @sql1+ @sql2 + @sql3 + @sql4+ @sql5

@sql is temp tables data?

I don't know what to do. Maybe writing one SQL statement but still want to find out. how to do it. This is my stored procedure:

CREATE PROCEDURE [dbo].[cdcr_rpt_ahpmap_billing]
     (@startdate DATE,
      @enddate DATE,
      @location VARCHAR(MAX))
AS
    execute sp_msdroptemptable #t1
    execute sp_msdroptemptable #t2
    execute sp_msdroptemptable #t3
    execute sp_msdroptemptable #t4
    execute sp_msdroptemptable #t5
    execute sp_msdroptemptable #t6

    select 
        ep.person_id, ep.enc_id, pe.enc_nbr, pe.enc_timestamp 
    into 
        #t1 
    from 
        encounter_payer ep 
    inner join 
        patient_encounter (nolock) pe on ep.enc_id = pe.enc_id
    inner join 
        master_im_ mm (nolock) on pe.person_id = mm.person_id
                               and pe.enc_id = mm.enc_id
    inner join 
        location_mstr lm (nolock) on pe.location_id = lm.location_id
    where 
        ep.payer_id in ('96D3C808-E89F-45D6-9426-BA943767B78B', 
                        '2F08F790-7C21-4E50-92F1-137638DF448C', 
                        'CC55FFA2-A97C-48D8-8737-33B60C05F881', 
                        'D541C86F-9A6F-435E-8587-566992E0357F')
        and pe.billable_ind = 'Y'
        and mm.visit_type not in ('Chart Update', 'Nurse Visit', 'Patient Communication')
        and pe.person_id not in (select person_id from dbo.View_NG_test_patients)
        and lm.location_name in (select * from dbo.fnSplit(@Location, ', '))    
        and cast(pe.enc_timestamp as date) between @startdate and @enddate
    group by 
        ep.enc_id, pe.enc_nbr, ep.person_id, pe.enc_timestamp
    having 
        count(*) > 1
    order by 
        pe.enc_nbr

    select 
        t.person_id, t.enc_id, t.enc_nbr, t.enc_timestamp, 
        pm.payer_name, pm.payer_id, tr.tran_amt, tr.type 
    into 
        #t2
    from
        #t1 t
    inner join 
        encounter_payer ep (nolock) on t.person_id = ep.person_id
                                    and t.enc_id = ep.enc_id
    inner join 
        payer_mstr pm (nolock) on ep.payer_id = pm.payer_id
    left outer join 
        transactions tr (nolock) on t.person_id = tr.person_id
                                 and t.enc_id = tr.source_id
                                 and ep.payer_id = tr.payer_id
    where 
        ep.payer_id in ('96D3C808-E89F-45D6-9426-BA943767B78B', 
                        '2F08F790-7C21-4E50-92F1-137638DF448C', 
                        'CC55FFA2-A97C-48D8-8737-33B60C05F881', 
                        'D541C86F-9A6F-435E-8587-566992E0357F')
    order by 
        t.enc_nbr

    --for getting transactions amount sum
    SELECT
        t.enc_nbr, t.payer_name, t.type,
        SUM(t.tran_amt) transaction_amt
    FROM 
        #t2 t 
    GROUP BY  
        t.enc_nbr, t.payer_name, t.type 
    ORDER BY
        t.enc_nbr, t.payer_name, t.type 


    --for getting transactions amount sum as shown in practice management horizontally.
    select p.* into #t3
    from #t2  pivot (
        sum(tran_amt) for type in ([C],[A])
    ) as p
    order by enc_nbr

    --select encounter number in which map is null.
    select *  into #t4 from  #t3
    where enc_nbr in (select enc_nbr from #t3
                      where payer_name in ('M MAP Supp NGS' , 'M MAP Supp Noridian')
                      and c is null and a is null)
    order by enc_nbr

    --select only ones for which claims date created.
    select t.enc_nbr, t.enc_timestamp , t.payer_name , t.c, c.create_timestamp, c.claim_id, t.enc_id into #t5
    from #t4 t left join claims c 
    on t.person_id = c.person_id
    and t.enc_id = c.enc_id
    and t.payer_id = c.payer_id
    order by t.enc_nbr



    --select only ones for mentioned cptcodes
    select count(*) over(partition by t.enc_nbr )as 'count', t.enc_nbr , t.enc_timestamp ,
    t.payer_name , t.c , t.create_timestamp into #t6
    from #t5 t inner join claim_charges c 
    on t.claim_id = c.claim_id
    and t.enc_id = c.enc_id
    where claim_cpt4_code_id in ('92002', '92004', '99202', '99203', '99204', '99205', 'G0101', 'Q0091',
                                 '92012', '92014', '99212', '99213', '99214', '99215', 'G0402', 'G0438',
                                 'G0439', '90791', '90792', '90832', '90834', '90837', '90839', '90845', 'G0467')    
    order by t.enc_nbr


    --select only ones for which claims date not created.
    select enc_nbr[Encounter No.], enc_timestamp[Encounter timestamp], payer_name[Payer Name],
    create_timestamp[Claims create timestamp] from #t6
    where count = 1
    and payer_name not in ('M MAP Supp NGS', 'M MAP Supp Noridian')

I just want to display the result of #t6 in ssrs

Alan Schofield Alan Schofield · Accepted Answer · 2019-08-05T09:30:54

SSRS will use the first recordset that is output from the dataset query. So, if your SP only outputs from #6 (which it appears to) then this is all that SSRS will 'see'.

Put another way....Run the proc in SSMS and if you only get one resultset returned then you're OK. If you get more than one, only the first will be visible to SSRS.

Multiple temp tables in stored procedure and creating ssrs

1 Answers