I want to send some report definitions to team sitting somewhere else. I don’t have report project as connection to TFS can’t be made from here.  The machine, I have is development environment server where everybody creates reports for learning and demo.

So, One way of doing this to save report one by one from report manager.

The other way of doing this is to use the RS scripter tool. This is great tool, but there is no option to export reports created by specific user.  More details for this is available at http://www.sqldbatips.com/showarticle.asp?ID=62

Another way of doing this is, we can extract information from catalog table reportserver database. I tried but it seems the report definition is stored inside the IMAGE column. IMAGE datatype is deprecated feature, but internally this is being used. For more details on deprecated features can be found here http://technet.microsoft.com/en-us/library/bb510662.aspx

SELECT  ReportPath = c.path
       ,ReportName =  c.name
       ,ReportDefination = CONVERT(XML,CONVERT(VARBINARY(MAX),c.content)) 
FROM dbo.catalog c
WHERE c.type = 2

We can use BCP for expoting data to files

USE ReportServer
GO
--Create a global teble vaiable 
IF OBJECT_ID('tempdb..##reportDetail') IS NOT NULL
	DROP TABLE tempdb..##reportDetail
GO
CREATE TABLE ##reportDetail  
(
ReportName VARCHAR(850),
ReportDefination XML,
RowNumber INT
)

DECLARE @i INT =0;
DECLARE @folderName VARCHAR(MAX) = 'c:\tempreports\';
DECLARE @filename VARCHAR(854) = '';
DECLARE @bcpCommand VARCHAR(8000)  = '';

--suppose we need to extarct all the data from AdventureWork2008SampleReports folder
DECLARE @username varchar(850) = '--- -\chinak';

--Seftynet if somebody doesn't add backshash 
SELECT @folderName = CASE WHEN RIGHT(@folderName,1) = '\' THEN @folderName ELSE @folderName + '\' END;

--Catalog contains all objects on reportserver like folder,datasource,model,report Type. For report defination type is 2.
INSERT INTO ##reportDetail  
(
ReportName,
ReportDefination,
RowNumber 
)
SELECT  ReportName =  c.name
       ,ReportDefination = CONVERT(XML,CONVERT(VARBINARY(MAX),c.content)) 
       ,RowNumber = ROW_NUMBER() OVER (ORDER BY c.itemid)
FROM dbo.catalog c
INNER JOIN dbo.users u ON c.CreatedByID = u.UserID
WHERE c.type = 2
AND u.UserName LIKE @username

SELECT @i = MAX(RowNumber) FROM ##reportDetail
WHILE (@i > 0)
BEGIN
	 SELECT @filename = @folderName + reportname + '.RDL'	 FROM ##reportDetail WHERE  RowNumber = @i
	 SELECT @bcpCommand = 'bcp "SELECT ReportDefination FROM ##reportDetail WHERE RowNumber =' + STR(@i) + ' " queryout "' 
	 SELECT @bcpCommand = @bcpCommand + @FileName + '" -T'
	 EXEC xp_cmdshell @bcpCommand
	 SELECT @i -= 1
END 
GO

--Drop temp table
IF OBJECT_ID('tempdb..##reportDetail') IS NOT NULL
	DROP TABLE tempdb..##reportDetail
GO

Note:- For this script to work, XP_CMDSHELL should be enabled. Please do not enable XP_CMDSHELL on production environmnets as this possesses a security risk. In alternate to this, we can also use SSIS for each loop to export report definition.

After completing this, I realized one more sophisticated way of downloading definitions from report server and its using the GetItemDefinition method. But, I still have to check weather by this meathod, i get all report’s uploaded by a particular person.