Problem with a stored Proc in my Report 1

[Cooperdam]

I have this report that I wanted to make a slight mod. to the Stored proc. But it won't work for some reason. In the DataSource, for Query, I give the S/P name and identify it as "Stored Procedure" although it seems to go to "TExt". I can run it in the Design mode this S/P, and it asks for parmas, I enter and it works, it gives good data output, but it gives a message:
"Could not update a list of fields for the Query. Verify that you can connect to the datasource and that your query syntax is correct".

The following is the stored proc. Perhaps someone can see what is wrong.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE [dbo].[spu_CRSS_Section3aNAME]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
)
AS
BEGIN

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES

SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)

SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )

SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )

SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )


SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source

INTO #temp

FROM sop10200 AS SOP10200 INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL
INNER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP10100.CUSTNAME = RM00101.CUSTNAME

WHERE
(DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,

SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source

FROM sop30300 AS SOP30300 LEFT OUTER JOIN
iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE

LEFT OUTER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC =

IV40600.USCATVAL LEFT OUTER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP30200.CUSTNAME = RM00101.CUSTNAME

WHERE
(DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)

SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT

INTO #FinalReport
FROM #temp
WHERE (CUSTNAME = @Customer)


UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END


UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 END


--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT

INTO #SUMMARY
FROM #FinalReport
GROUP BY CUSTNAME,
CPRCSTNM


UPDATE #SUMMARY
SET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END


UPDATE #SUMMARY
SET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END


UPDATE #SUMMARY
SET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END


UPDATE #SUMMARY
SET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END


UPDATE #SUMMARY
SET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 + BILLED3 + BILLED4


SELECT * FROM #SUMMARY

END

 

[RiverGuy]

SSRS has problems resolving the metadata when you use a #Temp table. Try using a @Table variable or derived tables/CTEs instead.

 

[Cooperdam]

Thank you RG, this is a new concept for me, the derived tables. I will have to investigate this idea.
If you can,please provide any background that would help me.

 

[RiverGuy]

Here's an example. Let's say you have a table and you want to summarize data by date to work off of. You could create a #Temp table like this:

SELECT DateColumn, SUM(Amount) AS Amount
INTO #Temp1
FROM SomeTable
GROUP BY DateColumn

SELECT * FROM #Temp1

A derived table is a type of subquery. For example

SELECT * FROM

(SELECT DateColumn, SUM(Amount) AS Amount
FROM SomeTable
GROUP BY DateColumn) a

A table variable is similar to a #Temp table, although it is scoped as a variable would be. For example:

DECLARE @SomeTable TABLE (DateColumn DATETIME, Amount DECIMAL(18,2))

INSERT INTO @SomeTable 
SELECT DateColumn, SUM(Amount) AS Amount
FROM SomeTable
GROUP BY DateColumn

SELECT * FROM @SomeTable

Another thing to try, which I am pretty sure will not work, but is worth trying, would be to use a #Temp table but explicitly declare the definition beforehand. For example:

CREATE TABLE #SomeTable (DateColumn DATETIME, Amount DECIMAL(18,2))

INSERT INTO #SomeTable 
SELECT DateColumn, SUM(Amount) AS Amount
FROM SomeTable
GROUP BY DateColumn

SELECT * FROM #SomeTable

DROP TABLE #SomeTable

All have their pros and cons, which you can learn more about on the web or MSDN.