Problem with this Stored proc in my Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Problem with this Stored proc in my Report

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-13 : 09:25:05

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 09:38:37

Maybe you can have a look for the columns returned by [select * from #SUMMARY] and then have a look to wanted columns in your report. Maybe there is a difference?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources