EncryptionKeys Backup and Restore

Encryption Keys:
    Reporting Services uses encryption keys to secure credentials and connection information that is stored in a report server database. In Reporting Services, encryption is supported through a combination of public, private, and symmetric keys that are used to protect sensitive data.

Encryption Keys Backup:

• Open the "Reporting Services Configuration Manager"

• Give the Server name and click on "Connect" button.

• Go to "Encryption Keys" section and click on "Backup" button (highlighted in the below screenshot).

• Then "Encryption Key Information" will open. Give the "Password" (any name we can give) and "Key file" path. After that click on "OK" button. It will create backup file in the given location.

Restoring:

• Open the "Reporting Services Configuration Manager".

• Give the Server name and click on "Connect" button.

• Go to "Encryption Keys" section and click on "Restore" button (Highlighted in the above screenshot).

• Specify the backup file and give the required password. Then click on "OK" button.

Upload Multiple RDL Files from Local path to ReportServer (Using RS.exe Utility)

      This article explains how to Upload/Deploy the multiple RDL files from local machine to ReportManager. We can Upload/Deploy multiple RDL files at a time using RS.exe utility. RS.exe utility is a command line utility that can perform many scripted operation related to SQL Server Reporting Services (SSRS).

Note: Rs.exe file path will change based on the SQL Server/SSRS version

EXE file is located any one of the below locations (Based on SQL Server/SSRS Version):
For 64 Bit: \Program Files\Microsoft SQL Server\100\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\100\Tools\Binn
OR

For 64 Bit: \Program Files\Microsoft SQL Server\110\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\110\Tools\Binn

OR

For 64 Bit: \Program Files\Microsoft SQL Server\120\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\120\Tools\Binn

For this process we need to prepare three files

1. Script File (For uploading all RDL Files)
2. Configuration file (We need to specify the "Local folder path and ReportManager folder path")
3. Batch file (To execute the RS.exe based on script present in the script file)

1. Script to prepare ScriptFile (Copy the below script and past it in text file and save with the name and extension as “Upload_Multiple_RDL_files.rss”):

'Script Starting Point
' Script to deploy report to report server
' EXECUTE VIA COMMAND LINE

DIM definition As [Byte]() = Nothing
DIM warnings As Warning() = Nothing

Public Sub Main()

' Variable Declaration
        Dim TextLine As String = ""
        Dim LocalDir As String = ""
        Dim ServerDir As String = ""
        Dim definition As [Byte]() = Nothing
        'Dim warnings As Warning() = Nothing

' Reading Configuration Text file and assigning the values to variables
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objReader As New System.IO.StreamReader(FILE_NAME)
            Do While objReader.Peek() <> -1
                TextLine = objReader.ReadLine()
                Dim parts As String() = TextLine.Split(New Char() {","c})
                'TextLine & objReader.ReadLine() '& vbNewLine
                LocalDir = parts(0)
                ServerDir = parts(1)

                Dim path As String = LocalDir
                Dim fileEntries As String() = Directory.GetFiles(path)
                Dim fileFullPath As String = ""
                For Each fileFullPath In fileEntries


 ' Deploying the Reports
                    Try
                        Dim stream As FileStream = File.OpenRead(fileFullPath)
                        Dim NameWithExt As String = fileFullPath.Replace(path, "")
                        Dim NameOnly As String = NameWithExt.Replace(".rdl", "")
                        definition = New [Byte](stream.Length) {}
                        stream.Read(definition, 0, CInt(stream.Length))

warnings = rs.CreateReport(NameOnly, ServerDir, True, definition, Nothing)

If Not (warnings Is Nothing) Then
DIM warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} PUBLISHED!", NameOnly)
End If

Catch e As IOException
Console.WriteLine(e.Message)
End Try
Next fileFullPath
Loop
         Else

            Dim MsgBox as String = "File Does Not Exist"

        End If
End Sub

'End of the Script

2. Configuration file: In this file we need to specify the source folder path (where the RDL files are located) and Target (ReportServer path, to where we need to upload the RDL files) with comma separate. Please refer the below attached images.

In this example, i have RDL files in two different folders and i want to upload them into different target folders in the report server.

Local Machine Path:
C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Upload_Multiple_RDL_Files\SSRS\SalesReports\
C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Upload_Multiple_RDL_Files\SSRS\Aggregate\

Report Server Path:
/SSRS/SalesReports
/SSRS/Aggregate

3. Command to prepare BatchFile (Copy the below command and past it in text file and save with the name and extension as “UploadRDLFiles.bat”):

Note: Verify the rs.exe file path in your Machine and Update the below path if the file is present in different location.

"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe" -i C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Upload_Multiple_RDL_Files\Upload_Multiple_RDL_files.rss -s "http://admin-pc:8080/ReportServer" -v FILE_NAME="C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Upload_Multiple_RDL_Files\ConfigurationFile.txt"

Pause

After preparing the three files (Script File, Configuration file and Batch File), edit the batch file and update the "Rs.exe file path, .RSS script file path, ReportServer URL,  and Configuration file path".

• Rs.exe file path: Update with the Rs.exe file path (file is located at \Program Files\Microsoft SQL Server\110\Tools\Binn)

• .RSS script file path: Update with the .rss script file path (where we placed the ScriptFile "Upload_Multiple_RDL_files.rss" file).

• ReportServer URL: Update with the required ReportServer URL (To where we need to upload the RDL files).

• Configuration file path: Update the file with required source and targets paths.

Sample Screenshot of the files:

After Updating the Script and Batch files, double click on the batch file then execution will start.

Note: We are uploading/Deploying only RDL files, so we may receive the below message. We can ignore that.

Message:

The dataset “<<Dataset name>>” refers to the shared data source “<<Dataset name>>”, which is not published on the report server. The shared data source “<<Dataset name>>” must be published before this report can run.

Note: If the Reports are referring to share data sources those may not work, if so edit RDL files Data source from report manager and updated with appropriate Data source.

See Also:
Download the all RDL files from ReportManager (Using RS.exe Utility)

Download the all RDL files from ReportManager (Using RS.exe Utility)

      This article explains how to download the all RDL files from ReportManager. We can download all the RDL files at a time using RS.exe utility. RS.exe utility is a command line utility that can perform many scripted operation related to SQL Server Reporting Services (SSRS).

Note: Rs.exe file path will change based on the SQL Server/SSRS version

EXE file is located any one of the below locations (Based on SQL Server/SSRS Version):
For 64 Bit: \Program Files\Microsoft SQL Server\100\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\100\Tools\Binn
OR

For 64 Bit: \Program Files\Microsoft SQL Server\110\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\110\Tools\Binn

OR

For 64 Bit: \Program Files\Microsoft SQL Server\120\Tools\Binn

For 32 Bit: \Program Files (x86)\Microsoft SQL Server\120\Tools\Binn

For this process we need to prepare two files

1. Script File (For downloading all RDL Files)
2. Batch file (To execute the RS.exe based on script present in the script file)

1. Script to prepare ScriptFile (Copy the below script and past it in text file and save with the name and extension as “Download_all_RDL_files.rss”):

'Script Starting Point
'This script downloads the all RDL files from ReportServer

Sub Main()
    Dim items As CatalogItem() = _
        rs.ListChildren("/", true)

    For Each item As CatalogItem in items
        If item.Type = ItemTypeEnum.Folder Then
            CreateDirectory(item.Path)
        Else If item.Type = ItemTypeEnum.Report Then
            SaveReport(item.Path)
        End If
    Next
End Sub

Sub CreateDirectory(path As String)
    path = GetLocalPath(path)
    System.IO.Directory.CreateDirectory(path)
End Sub

Sub SaveReport(reportName As String)
    Dim reportDefinition As Byte()
    Dim document As New System.Xml.XmlDocument()

    reportDefinition = rs.GetReportDefinition(reportName)
    Dim stream As New MemoryStream(reportDefinition)

    document.Load(stream)
    document.Save(GetLocalPath(reportName) + ".rdl")
End Sub

Function GetLocalPath(rsPath As String) As String
    Return rootPath + rsPath.Replace("/", "\")
End Function

'End of the Script

2. Command to prepare BatchFile (Copy the below command and past it in text file and save with the name and extension as “DownloadRdlFiles.bat”):

Note: Verify the rs.exe file path in your Machine and Update the below path if the file is present in different location.

"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe" -s "http://admin-pc:8080/ReportServer" -i C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Download_rdl_files\Download_all_RDL_files.rss -v rootPath="C:\Kiran\Technology\SQLServer\SSRS\DeploymentScript\Download_rdl_files\RDL_files\\"
Pause

After preparing two files (Script File and Batch File), edit the batch file and update the "Rs.exe file path, ReportServer URL, .RSS script file path and RootPath (Local path, where we need to keep the RDL files)".

• Rs.exe file path: Update with the Rs.exe file path (file is located at \Program Files\Microsoft SQL Server\110\Tools\Binn)

• ReportServer URL: Update with the required ReportServer URL (From where we need to download RDL files)

• .RSS script file path: Update with the .rss script file path (where we placed the ScriptFile "Download_all_RDL_files.rss" file).

• RootPath: Update with the required path (where we need to place the downloaded RDL files)

Sample Folder Structure:

After Updating the Script and Batch files, double click on the batch file then execution will start.

Also:
Upload Multiple RDL Files from Local path to ReportServer (Using Rs.Exe Utility)

SSRS Error: Maximum request length exceeded.

Maximum request length exceeded:

         By default we can Deploy/Upload a report with maximum size 4 MD, into ReportManager. When we try to Deploy/Upload a report more than 4 MB size, then we will get the error “Maximum request length exceeded”.

Error During deploy:

Error while uploading:

Solution:

         We can increase the default size by updating the "Web.config" in the below two locations.

1. %\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager
2.  %\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer

• Edit the “Web.config” file.
•  Go to the tag “<httpRuntime executionTimeout="90000" />”.

• Update the tag with required length

               <httpRuntime executionTimeout="90000" />

• Save the file
• Restart the SSRS Service.

Easy way to replace existing ssrs hyperlink URLs with required URLs

After development, during UAT/Production move we need to replace the development URL’s with UAT/Production URL’s. If the report is having more columns, then it is bit difficulty to identify which column is having hyperlink.

Easy way to identify and replace hyperlinks:

• Open the SSRS solution.
• Right click on the required report and select “View Code”.

•  Then SSRS report will open in xml format.

• Press “Ctrl+F” then “Find and Replace window” will open. Type “http”, then click on “Find Next” then it will show the URL’s which are present in the report.
• Replace the URL with required URL.

If the parameter values are available in xml file

----------------------- XML File loading into temp table ------------------------
-- Temp table creation
CREATE TABLE #Xlm
(
XML_Data xml
)

-- XML File loading
INSERT INTO #Xlm(XML_Data)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Kiran\SQLServer\SSRS\HyperLinksNew.xml', SINGLE_BLOB) AS x;


----------------------- Query to extract required URL from temp table ------------------------
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XML_Data FROM #Xlm

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT URL
FROM OPENXML(@hDoc, 'Invoice/InvoiceDetail')
WITH
(
URL [varchar](100) 'URL',
Description [varchar](100) 'Description',
ActiveFlg [varchar](100) 'ActiveFlg'
)
where description = 'Google'
AND ActiveFlg = 1

EXEC sp_xml_removedocument @hDoc

If the parameter values are available in text/csv file

----------------------- File loading into temp table ------------------------

-- Temp table creation

CREATE TABLE #Hyperlink

(

Sl_Num INT

,URL VARCHAR(1000)

,Description VARCHAR(500)

,ActiveFlg SMALLINT

)

-- Temp table inserrtion

BULK

INSERT #Hyperlink

FROM 'C:\Kiran\SQLServer\SSRS\HyperLinks.csv'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

----------------------- Query to extract required URL from temp table ------------------------

SELECT URL FROM #Hyperlink

WHERE Description = 'Google'

AND ActiveFlg = 1

Drill-through or Linked Reports

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18

    It provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

Example:

• Open BIDS and Create New solution or Open existing solution.
• Create "ParameterizedReport" report.
• After that Add a new report with the name "DrillThroughOrLinkedReport".

• Create "Data Source" and "Dataset". While dataset creation use Dept table instead of Employee table.
• Query: 
• SELECT        DEPT_ID, Name, Location
  FROM            DEPT

• Click on "OK" button.
• In designer window insert a table and map the columns.

• Select "DEPT_ID", right click and select 'Text Box Properties.." (highlighted in the below screen).

• Then the "Text Box Properties" window will open.

• Go to "Action" section.
• Select the "Go to report" check box.
• Specify a report: select the required report(ParameterizedReport) from the drop-down list.
• Click on "Add" button then add the parameter.
• After that click on "OK" button.
•  Click on "Preview" then report will generate.

• If we click on Dept_ID (10,20,30 or 40) then that particular department related employee information will open.
• Now i am going to click on "20" then the 20th dept related employee information should display.

 

•  Linked report is generated as expected.

Sub Reports

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17

    A main report that is a container for multiple related reports is called sub report.

Example:

• Report1: MultivaluedParameters.
• Report2: DeptReport

The Above two report i will call into a new report.

• In the Same solution add a new report with the name "SubReport".

• Right click on Parameters.

•  Select "Add Parameters.." then "Report Parameter Properties" will open.

• Give the required names (Highlighted in the above screen). Then click on "OK" button.

• In "Design" Window: Right click and select 'Insert => Subreport.

•  Then Sub report will insert.

• Right click on the "Subreport" and select "Subreport Properties".

• Then "Subreport Properties" window will open.

• Name: Give the required name (DepartmentReport).
• Use this report as a subreport: Select the required report (DeptReport) from the drop-down list.
• Go to 'Parameters' section.

• Click on 'Add' button, then 'Name and Value' will enable. Then give the parameter details and then click on "OK" button.

• One sub report (DeptReport) is added. Same way Add second report (MultivaluedParameters).

• Go preview section and give the parameter value, and then click on "View Report" button. then report will generate.