SQL Server Performance

Result from few Datasets output in 1 table

Discussion in 'SQL Server Reporting Services' started by teckeng, Mar 22, 2004.

  1. teckeng New Member

    Hi all ... I have a problem of putting all the results together from few datasets. Since I have a sql query statement which output more than 1 recordset, and the multiple results from the query will be passed to another new query as parameter.<br />So can I know anyone got any idea of that? thankssss in advance[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  2. ssp320 New Member

    You can try use a Union query
  3. knarf New Member

    you are trying to do a rollup of data ?

    Rollup
    =======
    1) create a sp
    2) In the sp create a #temp table
    3) insert into #temp with your various SELECTS
    4) use standard SELECT ( and sum(field)if needed) with GROUP BY from the temp table

    Note: if you are doing std star fact-table - dim-table datawarehousing create view its makes rollups much easier

  4. teckeng New Member

    Hi Knarf,

    Do you mind telling me in detail how to do the Rollup process that you mentioned above? Because I am beginner to this reporting service. I know how to create a sp, but how to create #temp table in sp? how to insert various SELECTs into #temp? Cheers...
  5. knarf New Member

    OK sample of simple rollup getting YTD (year to date)
    and PYTD (previous year to date) from an orders table.

    I've not checked or parsed code so expect errors

    #########################################
    CREATE PROCEDURE usp_ytdrollup

    @Year Int
    AS

    -- CREATE TEMP TABLE

    create #temp ( customer, YTD real, PYTD real)

    -- INSERT RECORDS INTO #temp

    insert into #temp
    select customer, sum(ordervalue) as YTD, 0 as PYTD
    FROM ORDERS
    Group By customer
    Where (DatePart(yyyy, OrderDate)) = @Year


    insert into #temp
    select customer, 0 as YTD, sum(ordervalue) as PYTD
    FROM ORDERS
    Group By customer
    Where (DatePart(yyyy, OrderDate)) = (@Year - 1)


    -- GET RECORDS FROM TEMP TABLE

    select customer, sum(YTD), sum(PYTD)
    from #temp
    group by customer

    GO
    ==============================================


    contents of # temp
    ------------------
    cust1 443 0
    cust1 0 334

    Output
    ---------
    cust1 433 334


    So YTD and PTD appear on the same row.
  6. teckeng New Member

    ok .. now I have created a stored procedure that basically follow the example above.
    Am I going to create the temptable manually before running the store procedure OR the temptable will be created once you run the stored procedure?? My temp table name is called "temptable".

    Because I got another problem when I run it in Reporting Service. Error is : Invalid object name temptable.
  7. knarf New Member


    " Reporting Service. Error is : Invalid object name temptable."

    Are you creating a real temp table i.e. with # at the start, if you are you have run it witihn a SP or witihn an SP that calls other SPs.


    Frank
  8. teckeng New Member

    Below is the sample of the store procedure that I created.

    *************************************************************
    CREATE PROCEDURE usp_gettotalseats
    @oprndate datetime
    AS

    create #temp (filmcd,filmtitle,seats)

    insert into #temp
    select bkg_film_cd, 0 as filmtitle, sum(bkg_total_seats)
    FROM G2_TICKET_BOOKINGS
    Group By bkg_film_cd
    Where bkg_oprn_date>@oprndate

    insert into #temp
    select flm_film_cd, flm_film_title, 0 as seats
    from g2_Films
    Group By flm_film_cd

    select filmcd,filmtitle,sum(seats)
    from #temp
    group by filmcd

    go
    **************************************************************

    Any wrong there? cheers.....
  9. knarf New Member

    Lots wrong with your SP,
    You have to check the #temp table fields match your field form your
    other tables

    This should work; Syntax checked OK

    ##############################################################
    CREATE PROCEDURE usp_gettotalseats
    @oprndate datetime
    AS

    create table #temp (filmcd varchar(30), filmtitle varchar(30) ,seats int)

    insert into #temp
    select bkg_film_cd, 0 as filmtitle, sum(bkg_total_seats)
    FROM G2_TICKET_BOOKINGS
    Where bkg_oprn_date>@oprndate
    Group By bkg_film_cd

    insert into #temp
    select flm_film_cd, flm_film_title, 0 as seats
    from g2_Films
    Group By flm_film_cd

    select filmcd,filmtitle,sum(seats)
    from #temp
    group by filmcd

    go
    ####################################################################################

    Frank

  10. teckeng New Member

    Knarf,

    still the same thing happened after I change the declaration. Invalid object name '#temp'.
    Any help? cheers
  11. teckeng New Member

    The exact error is - Could not generate a list of fields for the query. Invalid object name #temp.
    cheers
  12. knarf New Member


    Place it in query anylsizer and run it one statement at a time.

Share This Page