Manitou Core - SQL Query - Extracting Custom Reports/RDL Files from the Database

01/03/2024 10:38 am EST

The scripts below will assist with extracting the reports as an rdl file from the Manitou database. Please read through the following information before running the scripts.

Finding the Report to Extract

Before starting, you will first need the report ID of the customer report before using this script. This can be found with the steps below.

Open the Supervisor Workstation
Go to Report Templates (Reports ? Report Templates).
From the Report Templates screen, expand External, and then Custom.
Select the Custom Report you want to extract/export.
The Report ID is located at the top of the Report Template.
The Report ID will be entered into the editable fields of Script #2.

Quick Notes

There are 3 scripts and they must be ran in the order listed
- Enable OLE Automation Procedures
- Extract the RDL/Custom Report
- Disable OLE Automation Procedures (DO NOT FORGET THIS STEP)
If there are multiple locales with custom reports for each locale, this may fail. Please see Chris Narowski or any conversion tech for details on how to proceed if this is an issue.
The report will be saved to C:/TEMP/ by default with the description as the file name. If the file path is changed in the script, the folder must exist for the script to run.

Script #1

/*------------------------------------------	
Enable OLE Automation Procedures (run separately)
*/------------------------------------------

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

Script #2

USE MANITOU
/*------------------------------------------
	Editable Declerations
*/------------------------------------------
DECLARE
@ReportID smallint = 1001
, @outSavePath varchar(50) = 'C:\TEMP'

/*------------------------------------------
	DO NOT EDIT Declerations
*/------------------------------------------
, @i smallint
, @rdlexport int
, @fileData varbinary(max)
, @filePath varchar(max)

/*------------------------------------------
	Set Data Into Temp Table
*/------------------------------------------
DECLARE @fileTable TABLE(id int identity(1,1), [FileName] varchar(100), [File_Content] varBinary(max) )
INSERT INTO @fileTable([FileName], [File_Content])
SELECT DESCR, OBJECT FROM BOBJECT WHERE TYPE = 1000 and SUBTYPE = @ReportID

/*------------------------------------------
	Save Data From Temp Table to File
*/------------------------------------------
IF(SELECT COUNT(*) from @fileTable) = 1	BEGIN
		SELECT
		@fileData = [File_Content],
		@filePath = @outSavePath + '\' + [FileName] + '.rdl'
		FROM @fileTable		

EXEC sp_OACreate 'ADODB.STREAM', @rdlexport OUTPUT; --Create Stream Instance of rdlexport
EXEC sp_OASetProperty @rdlexport, 'Type', 1; --Set the type of data for Stream object (Type 1 = Binary, Type 2 = Text)
EXEC sp_OAMethod @rdlexport, 'Open'; --Opens Stream Object
EXEC sp_OAMethod @rdlexport, 'Write', NULL, @fileData; --Writes extracted data into Stream Object
EXEC sp_OAMethod @rdlexport, 'SaveToFile', NULL, @filePath; --Saves Stream Object as whole to File
EXEC sp_OAMethod @rdlexport, 'Close'; --Closes the Stream Object
EXEC sp_OADestroy @rdlexport; --Closes Stream Instance of rdlexport

		print 'File Created at ' + @filePath

		SELECT @fileData = NULL, @rdlexport = NULL, @filePath = NULL, @i = 0
	END
ELSE
	print CHAR(13) + CHAR(10) + 'Failed to create file, either no reports exist, or multiple reports with the subtype specified exist and should be cleaned.' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'This can also occur when multiple locales exist, in which case the script may need to be edited to specify a locale.'

Script #3


/*------------------------------------------
	Disable OLE Automation Procedures (run separately)
*/------------------------------------------

sp_configure 'Ole Automation Procedures', 0;  
GO  
RECONFIGURE;  
GO  
sp_configure 'show advanced options', 0;  
GO  
RECONFIGURE;  
GO

Was this article helpful?