SSRS Report Definition From a Simple Query (SSRS)

When you have a need to check your report from SSRS instance, you can run the following query and view the XML content from ReportRDL column to a new query window by clicking on the XML content.
You will see the full code of your report.
(The Type value 2 in query is representing Report.)
SELECT ItemID,Name,[Type] , Path, ModifiedDate
,CAST(CONVERT(varbinary(max),Content) as XML) ReportRDL
FROM ReportServer.dbo.Catalog
WHERE Type =2 and Name = 'MyreportName'

By using dynamic SQL with bcp command to download all .rdl files to local folder.

Here is a handy script from:

http://www.sqlservercentral.com/blogs/juggling_with_sql/2013/08/07/ssrs-download-all-rdl-files-from-report-server-in-one-go/


-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Update the currently configured value for advanced options.
RECONFIGURE
GO

-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO

-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO

-- Update the currently configured value for advanced options.
RECONFIGURE
GO


--Replace NULL with keywords of the ReportManager's Report Path,
--if reports from any specific path are to be downloaded
DECLARE @FilterReportPath AS VARCHAR(500) = NULL

--Replace NULL with the keyword matching the Report File Name,
--if any specific reports are to be downloaded
DECLARE @FilterReportName AS VARCHAR(500) = NULL

--Replace this path with the Server Location where you want the
--reports to be downloaded..
DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'

--Used to prepare the dynamic query
DECLARE @TSQL AS NVARCHAR(MAX)

--Reset the OutputPath separator.
SET @OutputPath = REPLACE(@OutputPath,'\','/')

--Simple validation of OutputPath; this can be changed as per ones need.
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''
BEGIN
  SELECT 'Invalid Output Path'
END
ELSE
BEGIN
   --Prepare the query for download.
   /*
   Please note the following points -
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding.
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one.
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF).
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”.
      While it is supported, it can cause problems with the conversion to XML, so it is removed.
   */
   SET @TSQL = STUFF((SELECT
                      ';EXEC master..xp_cmdshell ''bcp " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                    FROM
                      [ReportServer].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')

  --SELECT @TSQL

  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @TSQL
END


			

			
					

SSAS Errors: Errors in the metadata manager. The dimension with ID of ‘Something Name’ referenced by the ‘CubName’ cube, does not exist.

The steps to fix the problem:

1. Stop SSAS service(any way you know);
2. Rename (or delete) the data file folder (example: …OLAP\Data\My Cub Name.0.db\”;
3. Start SSAS service and Connect through SSMS to delete the SSAS database.
4. Redeploy cub database from BIDS and it should work this time.

A useful post to help to solve this problem is from:

SSAS errors: Errors in the metadata manager. The dimension with ID of ‘xxx’ referenced by the ‘yyy’ cube, does not exist.