Hi-

I am using datashaping to create a hierarchy of recordsets based on the

results of several stored procedures. I am running into trouble with one

that uses a temp table prior to displaying the results. Is there a way to

utilize stored procedures that use temp tables in datashaping? When I

execute the shape statement below I get the error invalid object #pivot.

Thank You,

Dan Clash

shape {{call sp_Product_LogsCreated ('1-1fh','01/01/2000','01/31/2000')}}

append ({{call sp_Product_LogsClosed ('1-1fh','01/01/2000','01/31/2000')}}

relate ProductId to ProductId) as Logs,

({{call sp_Product_LogsReactivated ('1-1fh','01/01/2000','01/31/2000')}}

relate ProductId to ProductId) as React,

({{call sp_Product_Backlog ('1-1fh','01/31/2000')}}

relate ProductId to ProductId) as Back

The LogsClosed Stored Proc.

CREATE PROCEDURE [dbo].[sp_Product_LogsClosed]

AS

select ps.ProductID,

when '1-1HH' then sr.SubGroupId

when '1-2LHD1' then sr.SubGroupId

when '1-CB2P' then sr.SubGroupId

when '1-1G5' then sr.SubGroupId

else 'Other'

end as Workgroup,

sum(datediff(mi,sr.Created, sr.ClosedDate)/1440.) as 'Average',

Count(sr.Row_ID) as Number

into #pivot

from msereport..ServiceRequest sr inner join

msereport..ProductSubgroup ps on ps.ProductID = sr.ProductID

group by ps.ProductID, sr.SubGroupId

select p1.*, (p1.Workgroup + p1.HD + p1.Other) as Total

from (select p.ProductId,

Workgroup,

sum(case p.Workgroup when '1-1hh' then p.Number

when '1-2LHD1' then p.Number

when '1-CB2P' then p.Number

when '1-1G5' then p.Number

else 0 end) as HD,

sum(case p.Workgroup when 'Other' then p.Number else 0 end) as Other,

sum(Average) as 'Average'

from #pivot p

group by p.ProductId) p1

drop table #pivot

GO