Full Justification in SSRS

SSRS, RS UTILITY AND OLE AUTOMATION

The ability to full justify is a functionality that has never existed in SSRS since its inception and apparently Microsoft is still not supporting it. After much trial and error, I have come up with a workaround that hopefully will help a lot of you guys out there.

Prerequisite:

MS Word has to be installed in the same server as Reporting Services.
OLE Automation procedures need to be enabled.
xp_cmdshell needs to be enabled.
SQL Server Service Account needs to be added to the list of Administrators in the Windows UAC.

Disadvantages of this method:

This workaround cannot be used if more than one user is simultaneously trying to export the SAME report.
Difficult to trace if a COM object failure occurs. Usually the only solution is a Server restart. (But this rarely happens)

1) TO ENABLE OLE AUTOMATION PROCEDURES, EXECUTE THE FOLLOWING COMMANDS:

sp_configure 'show advanced options', 1

RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1

RECONFIGURE;

2) TO ENABLE XP_CMDSHELL, EXECUTE THE FOLLOWING COMMANDS:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

3) CREATE THE FOLLOWING STORED PROCEDURE BY EXECUTING THE BELOW SCRIPT AGAINST THE ADVENTURE WORKS 2012 DATABASE:

{AdventureWorks2012 database can be downloaded here:
https://msftdbprodsamples.codeplex.com/releases/view/55330 }

################################################################################

USE [AdventureWorks2012]

/****** Object: StoredProcedure [dbo].[usp_FullJustify] Script Date: 3/17/2015 11:05:25 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create PROCEDURE [dbo].[usp_FullJustify]

@RDLfilepath VARCHAR(200), -- Path of the RDL File

@ScriptFileLocation VARCHAR(200), -- Local file location for the RSS Script file

@DestinationFilePath VArchar(200), -- Local file location for the RDL Export

@DestinationFileName VARCHAR(100), -- RDL Export File Name

@ReportServer VARCHAR(100), -- Usually in the format :http://<ServerName>/ReportServer

@WSDL VARCHAR(100) -- Usually in the format :http://<ServerName>/ReportServer/ReportExecution2005.asmx?wsdl

SET NOCOUNT ON

/************************************************************************************

Locals

************************************************************************************/

DECLARE @CmdOutput TABLE (

ID INT IDENTITY(1,1),

OutputString VARCHAR(1000) )

DECLARE

@ReturnVal INT,

@ThisProcName VARCHAR(100),

@StepDescription VARCHAR(MAX),

@StepResult VARCHAR(200),

@Error INT,

@ErrorMessage VARCHAR(500),

@TempFile varchar(500),

@DocFile varchar(300),

@Cmd varchar(8000),

@ExecutedBy varchar(100) = suser_sname()

SET @ReturnVal = 0

SET @ThisProcName = ISNULL(OBJECT_NAME(@@PROCID), 'usp_FullJustify')

SET @ErrorMessage = ''

SET @Error = 0

--VARIOUS FORMATS IN WHICH DOC FILES CAN BE SAVED. CHOOSE BASED ON REQUIREMENT

--****************************************************************************

declare @wdFormatDocument int = 0

declare @wdFormatDocument97 int = 0

declare @wdFormatDocumentDefault int = 16

declare @wdFormatDOSText int = 4

declare @wdFormatDOSTextLineBreaks int = 5

declare @wdFormatEncodedText int = 7

declare @wdFormatFilteredHTML int = 10

declare @wdFormatFlatXML int = 19

declare @wdFormatFlatXMLMacroEnabled int = 20

declare @wdFormatFlatXMLTemplate int = 21

declare @wdFormatFlatXMLTemplateMacroEnabled int = 22

declare @wdFormatHTML int = 8

declare @wdFormatPDF int = 17

declare @wdFormatRTF int = 6

declare @wdFormatTemplate int = 1

declare @wdFormatTemplate97 int = 1

declare @wdFormatText int = 2

declare @wdFormatTextLineBreaks int = 3

declare @wdFormatUnicodeText int = 7

declare @wdFormatWebArchive int = 9

declare @wdFormatXML int = 11

declare @wdFormatXMLDocument int = 12

declare @wdFormatXMLDocumentMacroEnabled int = 13

declare @wdFormatXMLTemplate int = 14

declare @wdFormatXMLTemplateMacroEnabled int = 15

declare @wdFormatXPS int = 18

IF object_id('tempdb..##STAGE') IS NOT NULL

BEGIN

DROP TABLE ##STAGE

END

CREATE TABLE ##STAGE

(Command VARCHAR(8000));

BEGIN TRY

INSERT INTO ##STAGE

VALUES

(

'public Sub main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

rs.Url = "'+ @WSDL +'"

Dim execHeader AS New ExecutionHeader()

Dim ReportPath As String = "'+@RDLfilepath +'"

rs.ExecutionHeaderValue = execHeader

rs.LoadReport(ReportPath, Nothing)

Dim Format As String = "Word"

Dim devInfo As String = Nothing

Dim extension As String = Nothing

Dim mimeType As String = "application\msword"

Dim encoding As String = Nothing

Dim warnings() AS Warning = Nothing

Dim stremIds() As String = Nothing

Dim result() As Byte = Nothing

result = rs.Render(Format, devInfo, extension, mimeType, encoding, warnings, stremIds)

Dim stream as filestream = file.create("'+@DestinationFilePath +@DestinationFileName +'",result.length)

stream.write(result,0,result.Length)

stream.close()

End Sub'

)

END TRY

BEGIN CATCH

SET @Error = @@ERROR

SET @StepResult = ERROR_MESSAGE()

GOTO ENDOFPROC

END CATCH

SET @TempFile = @ScriptFileLocation + 'ExecuteRDL.rss'

SET @Cmd = 'bcp "SELECT COMMAND FROM ##STAGE" queryout ' + @TempFile + ' -T -c'

BEGIN TRY

EXEC @ReturnVal = master..xp_cmdShell @Cmd

END TRY

BEGIN CATCH

SET @Error = @@ERROR

SET @StepResult = ERROR_MESSAGE()

GOTO ENDOFPROC

END CATCH

SET @cmd = 'rs -i ' + @TempFile + ' -s ' + @ReportServer + ' -e exec2005'

-- Call RDL

BEGIN TRY

DELETE FROM @CmdOutput

INSERT @CmdOutput

EXEC @ReturnVal = master..xp_cmdShell @cmd--, NO_OUTPUT

END TRY

BEGIN CATCH

SET @Error = @@ERROR

SET @StepResult = ERROR_MESSAGE()

GOTO ENDOFPROC

END CATCH

IF @ReturnVal != 0 BEGIN

--Attempt to get the errors from @CMDOutput by concatenating rows

SET @StepResult = NULL

SELECT TOP 2

@StepResult = COALESCE(@StepResult + ' || ', '') + CAST(RTRIM(co.OutputString) AS VARCHAR(MAX))

FROM

@CmdOutput co

WHERE

co.OutputString IS NOT NULL

ORDER BY

co.ID DESC

select * from @CmdOutput

SET @StepResult =ISNULL(@StepResult, 'ERROR: ReturnVal = ' + CAST(@ReturnVal AS VARCHAR))

GOTO ENDOFPROC

END

--Instantiate OLE Automation Environment with MS Word

DECLARE @WordObject INT, @objdocument INT, @objDoc INT,@RetCode INT, @Document INT , @Filename VARCHAR(255)

EXEC @RetCode = sp_OACreate 'Word.Application', @WordObject OUTPUT, 4