sql server reporting services: Here is the code I am using, you can disregard the drop statements,
cause they have been taken out. If I choose text in the dataset, I get
the table already exists error, if I choose Stored Procedure, I get an
RPC error, any help would be appreciated. Thanks

DROP TABLE #BPDET_05
SELECT CUSTOMER,INV_DATE,
(CONVERT(MONEY,SALES)+CONVERT(MONEY,EXCISE))'SALES',
(CONVERT(MONEY,RAW)+CONVERT(MONEY,EXCISE))'RAW',
(CONVERT(BIGINT,QTY))'QTY',GRP
INTO #BPDET_05
FROM SQLBPDETAIL_05 AS B
WHERE GRP <> '99' AND REC_CODE NOT IN ('A','C')

DROP TABLE #SALES_WITH_CUST_ID_05
SELECT A.CUSTOMER,CUST_ID,INV_DATE,SALES,RAW,QTY,GRP
INTO #SALES_WITH_CUST_ID_05
FROM #BPDET_05 AS A
LEFT JOIN SQLCUST AS B
ON A.CUSTOMER = B.CUSTOMER

DROP TABLE #SALES_2005
SELECT CUST_ID,
CASE
WHEN GRP = '01' THEN 'PASS '
WHEN GRP = '11' THEN 'LT '
WHEN GRP = '22' THEN 'MT '
WHEN GRP = '35' THEN 'FARM '
WHEN GRP IN ('30','31','38','40','51','89','43')
THEN 'SPECIALTY'
WHEN GRP = '55' THEN 'WHEELS '
ELSE 'OTHER'
END 'GRP',
CASE
WHEN (INV_DATE BETWEEN '01/01/05' AND '01/28/05')
OR SUBSTRING(INV_DATE,7,2) = '04'
THEN QTY
ELSE 0
END 'JANU',
CASE
WHEN (INV_DATE BETWEEN '01/29/05' AND '02/25/05')
THEN QTY
ELSE 0
END 'FEBU',
CASE
WHEN (INV_DATE BETWEEN '02/26/05' AND '04/01/05')
THEN QTY
ELSE 0
END 'MARU',
CASE
WHEN (INV_DATE BETWEEN '04/02/05' AND '04/29/05')
THEN QTY
ELSE 0
END 'APRU',
CASE
WHEN (INV_DATE BETWEEN '04/30/05' AND '05/27/05')
THEN QTY
ELSE 0
END 'MAYU',
CASE
WHEN (INV_DATE BETWEEN '05/28/05' AND '07/01/05')
THEN QTY
ELSE 0
END 'JUNU',
CASE
WHEN (INV_DATE BETWEEN '07/02/05' AND '07/29/05')
THEN QTY
ELSE 0
END 'JULU',
CASE
WHEN (INV_DATE BETWEEN '07/30/05' AND '08/26/05')
THEN QTY
ELSE 0
END 'AUGU',
CASE
WHEN (INV_DATE BETWEEN '08/27/05' AND '09/30/05')
THEN QTY
ELSE 0
END 'SEPU',
CASE
WHEN (INV_DATE BETWEEN '10/01/05' AND '10/28/05')
THEN QTY
ELSE 0
END 'OCTU',
CASE
WHEN (INV_DATE BETWEEN '10/29/05' AND '11/25/05')
THEN QTY
ELSE 0
END 'NOVU',
CASE
WHEN ((INV_DATE BETWEEN '11/26/05' AND '12/30/05') AND
SUBSTRING(INV_DATE,7,2) <> '04')
THEN QTY
ELSE 0
END 'DECU',
CASE
WHEN (INV_DATE BETWEEN '01/01/05' AND '01/28/05')
OR SUBSTRING(INV_DATE,7,2) = '04'
THEN RAW
ELSE 0
END 'JANR',
CASE
WHEN (INV_DATE BETWEEN '01/29/05' AND '02/25/05')
THEN RAW
ELSE 0
END 'FEBR',
CASE
WHEN (INV_DATE BETWEEN '02/26/05' AND '04/01/05')
THEN RAW
ELSE 0
END 'MARR',
CASE
WHEN (INV_DATE BETWEEN '04/02/05' AND '04/29/05')
THEN RAW
ELSE 0
END 'APRR',
CASE
WHEN (INV_DATE BETWEEN '04/30/05' AND '05/27/05')
THEN RAW
ELSE 0
END 'MAYR',
CASE
WHEN (INV_DATE BETWEEN '05/28/05' AND '07/01/05')
THEN RAW
ELSE 0
END 'JUNR',
CASE
WHEN (INV_DATE BETWEEN '07/02/05' AND '07/29/05')
THEN RAW
ELSE 0
END 'JULR',
CASE
WHEN (INV_DATE BETWEEN '07/30/05' AND '08/26/05')
THEN RAW
ELSE 0
END 'AUGR',
CASE
WHEN (INV_DATE BETWEEN '08/27/05' AND '09/30/05')
THEN RAW
ELSE 0
END 'SEPR',
CASE
WHEN (INV_DATE BETWEEN '10/01/05' AND '10/28/05')
THEN RAW
ELSE 0
END 'OCTR',
CASE
WHEN (INV_DATE BETWEEN '10/29/05' AND '11/25/05')
THEN RAW
ELSE 0
END 'NOVR',
CASE
WHEN ((INV_DATE BETWEEN '11/26/05' AND '12/30/05') AND
SUBSTRING(INV_DATE,7,2) <> '04')
THEN RAW
ELSE 0
END 'DECR',
CASE
WHEN (INV_DATE BETWEEN '01/01/05' AND '01/28/05')
OR SUBSTRING(INV_DATE,7,2) = '04'
THEN SALES
ELSE 0
END 'JANS',
CASE
WHEN (INV_DATE BETWEEN '01/29/05' AND '02/25/05')
THEN SALES
ELSE 0
END 'FEBS',
CASE
WHEN (INV_DATE BETWEEN '02/26/05' AND '04/01/05')
THEN SALES
ELSE 0
END 'MARS',
CASE
WHEN (INV_DATE BETWEEN '04/02/05' AND '04/29/05')
THEN SALES
ELSE 0
END 'APRS',
CASE
WHEN (INV_DATE BETWEEN '04/30/05' AND '05/27/05')
THEN SALES
ELSE 0
END 'MAYS',
CASE
WHEN (INV_DATE BETWEEN '05/28/05' AND '07/01/05')
THEN SALES
ELSE 0
END 'JUNS',
CASE
WHEN (INV_DATE BETWEEN '07/02/05' AND '07/29/05')
THEN SALES
ELSE 0
END 'JULS',
CASE
WHEN (INV_DATE BETWEEN '07/30/05' AND '08/26/05')
THEN SALES
ELSE 0
END 'AUGS',
CASE
WHEN (INV_DATE BETWEEN '08/27/05' AND '09/30/05')
THEN SALES
ELSE 0
END 'SEPS',
CASE
WHEN (INV_DATE BETWEEN '10/01/05' AND '10/28/05')
THEN SALES
ELSE 0
END 'OCTS',
CASE
WHEN (INV_DATE BETWEEN '10/29/05' AND '11/25/05')
THEN SALES
ELSE 0
END 'NOVS',
CASE
WHEN ((INV_DATE BETWEEN '11/26/05' AND '12/30/05') AND
SUBSTRING(INV_DATE,7,2) <> '04')
THEN SALES
ELSE 0
END 'DECS',
QTY AS TOTAL_UNITS,
SALES AS TOTAL_SALES,
RAW AS TOTAL_RAW
INTO #SALES_2005
FROM #SALES_WITH_CUST_ID_05

DROP TABLE #SUMMARY_2005
SELECT CUST_ID,
SUM(JANS)'JANS',
SUM(FEBS)'FEBS',
SUM(MARS)'MARS',
SUM(APRS)'APRS',
SUM(MAYS)'MAYS',
SUM(JUNS)'JUNS',
SUM(JULS)'JULS',
SUM(AUGS)'AUGS',
SUM(SEPS)'SEPS',
SUM(OCTS)'OCTS',
SUM(NOVS)'NOVS',
SUM(DECS)'DECS',
SUM(JANR)'JANR',
SUM(FEBR)'FEBR',
SUM(MARR)'MARR',
SUM(APRR)'APRR',
SUM(MAYR)'MAYR',
SUM(JUNR)'JUNR',
SUM(JULR)'JULR',
SUM(AUGR)'AUGR',
SUM(SEPR)'SEPR',
SUM(OCTR)'OCTR',
SUM(NOVR)'NOVR',
SUM(DECR)'DECR',
SUM(JANU)'JANU',
SUM(FEBU)'FEBU',
SUM(MARU)'MARU',
SUM(APRU)'APRU',
SUM(MAYU)'MAYU',
SUM(JUNU)'JUNU',
SUM(JULU)'JULU',
SUM(AUGU)'AUGU',
SUM(SEPU)'SEPU',
SUM(OCTU)'OCTU',
SUM(NOVU)'NOVU',
SUM(DECU)'DECU',
SUM(TOTAL_UNITS)'TOTAL_UNITS',
SUM(TOTAL_SALES)'TOTAL_SALES',
(SUM(TOTAL_SALES)-SUM(TOTAL_RAW))'TOTAL_GP'
INTO #SUMMARY_2005
FROM #SALES_2005
GROUP BY CUST_ID
ORDER BY CUST_ID

--SUMMARIZE BY CUST_ID AND GRP

DROP TABLE #SUMMARY_2005_GRP
SELECT CUST_ID,GRP,
SUM(JANS)'JANS',
SUM(FEBS)'FEBS',
SUM(MARS)'MARS',
SUM(APRS)'APRS',
SUM(MAYS)'MAYS',
SUM(JUNS)'JUNS',
SUM(JULS)'JULS',
SUM(AUGS)'AUGS',
SUM(SEPS)'SEPS',
SUM(OCTS)'OCTS',
SUM(NOVS)'NOVS',
SUM(DECS)'DECS',
SUM(JANR)'JANR',
SUM(FEBR)'FEBR',
SUM(MARR)'MARR',
SUM(APRR)'APRR',
SUM(MAYR)'MAYR',
SUM(JUNR)'JUNR',
SUM(JULR)'JULR',
SUM(AUGR)'AUGR',
SUM(SEPR)'SEPR',
SUM(OCTR)'OCTR',
SUM(NOVR)'NOVR',
SUM(DECR)'DECR',
SUM(JANU)'JANU',
SUM(FEBU)'FEBU',
SUM(MARU)'MARU',
SUM(APRU)'APRU',
SUM(MAYU)'MAYU',
SUM(JUNU)'JUNU',
SUM(JULU)'JULU',
SUM(AUGU)'AUGU',
SUM(SEPU)'SEPU',
SUM(OCTU)'OCTU',
SUM(NOVU)'NOVU',
SUM(DECU)'DECU',
SUM(TOTAL_UNITS)'TOTAL_UNITS',
SUM(TOTAL_SALES)'TOTAL_SALES',
(SUM(TOTAL_SALES)-SUM(TOTAL_RAW))'TOTAL_GP'
INTO #SUMMARY_2005_GRP
FROM #SALES_2005
GROUP BY CUST_ID,GRP
ORDER BY CUST_ID,GRP

--CALCULATE CURRENT YEAR

DROP TABLE #BPDET_06
SELECT CUSTOMER,INV_DATE,
(CONVERT(MONEY,SALES)+CONVERT(MONEY,EXCISE))'SALES',
(CONVERT(MONEY,RAW)+CONVERT(MONEY,EXCISE))'RAW',
CONVERT(BIGINT,QTY)'QTY',GRP
INTO #BPDET_06
FROM SQLBPDETAIL AS B
WHERE GRP <> '99' AND REC_CODE NOT IN ('A','C')


DROP TABLE #SALES_WITH_CUST_ID_06
SELECT A.CUSTOMER,CUST_ID,INV_DATE,SALES,RAW,QTY,GRP
INTO #SALES_WITH_CUST_ID_06
FROM #BPDET_06 AS A
LEFT JOIN SQLCUST AS B
ON A.CUSTOMER = B.CUSTOMER

DROP TABLE #SALES_2006

First off. Is this a stored procedure? Everything you have here is a lot to
look at. It seems to be doing lots of work with temp tables and then a
single select statement at the end. There is absolutely no problem doing
this. I do this sort of thing all the time.

Have you ever used a stored procedure with RS before (I want to make sure
you know how to use successfully call it from RS).

If this is not a stored procedure you cannot do this. Although you can put
T-SQL as the source of the dataset due to caching and other issues temp
tables will not work. You should put this code into a stored procedure.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]