Stored Procedure To Report Via table adapter!?

[HeavenCore]

Hi, this is doing my HEAD in lol, hope you can help. I need to produce a report (i am using the MS reportviewer system) to evaluate a jobs costs. Easy enough, i have dozens of reports working fine with normal table adapters, but to evaluate Job cost takes several quries so i wrote a stored procedure to return a row of all the information:

ALTER PROCEDURE Job_Cost_Details_2
	@JobID int
AS
	Declare	@HireCost money
	Declare	@wages money
	Declare	@JobPlantCharge money
	Declare	@Stock money
	Declare	@WaistAmount money
	Declare	@Expenses money
	Declare	@QuoteID int
	Declare	@QuoteAmount money
	Declare	@TotalCosts money
	Declare	@Proffit money
	
SET @HireCost = ISNULL((SELECT SUM(HireCost) AS TotalHireCost FROM tblHire WHERE HireJob_fk = @JobID),0);

SET @wages = ISNULL((SELECT SUM(WagesRate * WagesHours) from tblWages where WagesFKJob = @JobID),0);
SET @JobPlantCharge = ISNULL((SELECT SUM(JobPlantCharge) AS TotalPlantCost FROM tblPlantJob WHERE JobPlantJob_fk = @JobID),0);
SET @Stock = ISNULL((SELECT SUM(StockToJobPrice) AS TotalStockCost FROM tblStockToJob WHERE StockToJobJobs_fk = @JobID),0);
SET @WaistAmount = ISNULL((SELECT SUM(WaistAmount * WaistCostPerUnit) FROM tblWaist WHERE WaistJob_fk = @JobID),0);
SET @Expenses = ISNULL((SELECT SUM(ExpensesCost) AS TotalOtherCost FROM tblExpenses WHERE ExpensesJob_fk = @JobID),0);

SET @QuoteID = (SELECT fk_JobJobQuote FROM tblJobs WHERE JobDatabaseID = @JobID);

SET @QuoteAmount = ISNULL((SELECT QuotePrice FROM tblQuote WHERE QuoteID = @QuoteID),0);

SET @TotalCosts = (@HireCost + @wages + @JobPlantCharge + @Stock + @Expenses + @WaistAmount);

SET @Proffit = (@QuoteAmount - @TotalCosts);

CREATE TABLE ##Results (
	HireCost money,
	wages money,
	JobPlantCharge money,
	Stock money,
	WaistAmount money,
	Expenses money,
	QuoteID int,
	QuoteAmount money,
	TotalCosts money,
	Proffit money);
	
Insert into ##Results (
		HireCost,
		wages,
		JobPlantCharge,
		Stock,
		WaistAmount,
		Expenses,
		QuoteID,
		QuoteAmount,
		TotalCosts,
		Proffit
	)
	Values
	(
		@HireCost,
		@wages,
		@JobPlantCharge, 
		@Stock,
		@WaistAmount,
		@Expenses,
		@QuoteID,
		@QuoteAmount,
		@TotalCosts,
		@Proffit 
	);

Select * From ##Results;

OK, so this works great in ms sql management studio, i get:

HireCost         wages            JobPlantCharge   Stock            WaistAmount      Expenses         QuoteID     QuoteAmount      TotalCosts       Proffit          
---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------- ---------------- ---------------- ---------------- 
5                297.5            44               45.98            144              0                1           500              536.48           -36.48           
(1 row(s) affected)
(1 row(s) returned)

but when i try to create a table adapter to get this infromation, i just an error:

Invalid Object Name '##Results'

What am i doing wrong?

 

[HeavenCore]

Nevermind, after digging through MSDN for about an hour i found the the .net2 Table adapter IDE is incompatible with temp tables and as a work around simply use the tabe data type instead.

 

[cjard]

Your problem can be solved with a query, something like:

SELECT
  j.JobId,
  q.QuotePrice,
  ISNULL(HireCost,0) as TotalHireCost,
  ISNULL(WagesCost ,0) as TotalWagesCost ,
  ISNULL(PlantCost,0) as TotalPlantCost,
  ISNULL(StockCost,0) as TotalStockCost,
  ISNULL(WaistCost,0) as TotalWaistCost,
  ISNULL(ExpensesCost,0) as TotalExpensesCost,


FROM

tblJobs j
INNER JOIN
tblQuote q
ON
  j.fk_JobJobQuote = q.QuoteID 

LEFT OUTER JOIN
(SELECT SUM(HireCost) as HireCost FROM tblHire GROUP BY HireJob_fk) h
ON
  j.JobDatabaseID = h.HireJob_fk

LEFT OUTER JOIN
(SELECT SUM(WagesRate * WagesHours) as WagesCost FROM tblWages GROUP BY WagesFKJob ) w
ON
  j.JobDatabaseID = w.WagesFKJob 

LEFT OUTER JOIN
(SELECT SUM(JobPlantCharge) as PlantCost FROM tblPlantJob GROUP BY JobPlantJob_fk ) p
ON
  j.JobDatabaseID = p.JobPlantJob_fk 

LEFT OUTER JOIN
(SELECT SUM(StockToJobPrice) AS StockCost FROM tblStockToJob GROUP BY StockToJobJobs_fk) s
ON
  j.JobDatabaseID = s.StockToJobJobs_fk

LEFT OUTER JOIN
(SELECT SUM(WaistAmount * WaistCostPerUnit) AS WaistCost FROM tblWaist GROUP BY WaistJob_fk ) wa
ON
  j.JobDatabaseID = wa.WaistJob_fk 

LEFT OUTER JOIN
(SELECT SUM(ExpensesCost) AS OtherCost FROM tblExpenses GROUP BY ExpensesJob_fk ) e
ON
  j.JobDatabaseID = e.ExpensesJob_fk 

WHERE 
  j.JobDatabaseID = @JobID

I'd have the reports package work out the total and the profit, but its not difficult to put it into the query.. My fingers just got a little tired..