March 19, 2008 at 9:26 am
Hi,
I write a storeprocedure which create a pivot table.
But,I can not show the resul set in my report in report server 2005.
how can I do?
my procedure
ALTER PROCEDURE LocalAgenda21
(
@id int
--@startdate DATETIME,
-- @enddate DATETIME
)
AS
SET NOCOUNT ON
CREATE TABLE #Columns
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText NVARCHAR(100)
)
INSERT INTO #Columns
(
ColumnText
)
SELECT Name+'/'+cast(WorkBase_WorkgroupSessions.OId as nvarchar(12)) AS oturum
FROM WorkBase_WorkgroupSessions
WHERE (WorkgroupSessionsId = 751)
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
CREATE TABLE #Rows
(
RowIndex INT IDENTITY (0, 1),
RowText VARCHAR(100)
)
INSERT INTO #Rows
(
RowText
)
SELECT Distinct Common_Accounts_1.FullName+'/'+cast(Common_Accounts_1.OId as nvarchar(12)) as member
FROM Common_Positions INNER JOIN
Common_Accounts AS Common_Accounts_1 ON Common_Positions.PersonId = Common_Accounts_1.OId
WHERE (Common_Positions.LocationPositionsId = 751)
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@RowIndex INT,
@MaxRowIndex INT,
@RowText VARCHAR(50),
@SQL VARCHAR(1000)
SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns
SELECT @RowIndex = 0,
@MaxRowIndex = MAX(RowIndex)
FROM #Rows
WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex
SELECT @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(50) NULL DEFAULT 0'
EXEC (@SQL)
if not(exists(select RowText from #Rows where RowText='ADI-SOYADI'))
begin
SELECT @SQL = 'INSERT #Rows (RowText,' + QUOTENAME(@ColumnText) + ') VALUES (''ADI-SOYADI'','''+cast(''+@ColumnText+'' as varchar(100))+''')'
EXEC (@SQL)
end
WHILE @RowIndex <= @MaxRowIndex
BEGIN
SELECT @RowText = RowText
FROM #Rows
WHERE RowIndex = @RowIndex
SELECT @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' =
(SELECT COUNT(dbo.WorkBase_AssignerWorkgroupSessionPositions.TaxpayerId) AS katildi
FROM dbo.WorkBase_WorkgroupSessions INNER JOIN
dbo.WorkBase_AssignerWorkgroupSessionPositions ON
dbo.WorkBase_WorkgroupSessions.OId = dbo.WorkBase_AssignerWorkgroupSessionPositions.SessionParticipantsId
WHERE dbo.WorkBase_WorkgroupSessions.OId =' +LTRIM(substring(@ColumnText,charindex('/',@ColumnText)+1,len(@ColumnText)-charindex('/',@ColumnText)+1))+' AND
dbo.WorkBase_AssignerWorkgroupSessionPositions.TaxpayerId =' + LTRIM(substring(@RowText,charindex('/',@RowText)+1,len(@RowText)-charindex('/',@RowText)+1))+')'+' WHERE #Rows.RowIndex= ' + CAST(@RowIndex AS VARCHAR(12))
EXEC (@SQL)
SELECT @RowIndex = @RowIndex + 1
END
SELECT @ColumnIndex = @ColumnIndex + 1
END
DECLARE @ColumnTempIndex INT,
@MaxColumnTempIndex INT,
@ColumnTempText VARCHAR(50)
SELECT @ColumnTempIndex = 0,
@MaxColumnTempIndex = MAX(ColumnIndex)
FROM #Columns
WHILE @ColumnTempIndex <= @MaxColumnTempIndex
BEGIN
SELECT @ColumnTempText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnTempIndex
SELECT @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnTempText) + '= '''+ substring(@ColumnTempText,0,charindex('/',@ColumnTempText))+''' WHERE RowText = ''ADI-SOYADI'''
EXEC (@SQL)
SELECT @ColumnTempIndex = @ColumnTempIndex + 1
END
DROP TABLE #Columns
SELECT #Rows.*
FROM #Rows
ORDER BY #Rows.RowText
Drop TABLE #Rows
------------------------------------------------------------------------------------------
March 20, 2008 at 11:54 am
If you stored procedure executes fine in SSMS, there is no reason it will not do so in SSRS. You need to make sure your SSRS dataset is pointing to the stored procedure. You also need to make sure that the fields return from the stored procedure are defined in your dataset.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply