SSRS – Download all RDL files from Report Server in one go.

Introduction

People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- 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

Once successfully executed, the below script with the required changes could be executed to download the files -

--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

Conclusion

Hope, this helps & save a lot of your valuable time.

Happy Reporting!

SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted

DECLARE @NoOfChars AS INT = LEN(@Value)

DECLARE @Permutations TABLE(Value VARCHAR(20)) --Make sure the size of this Value is equal to your input  string length (@Value)

;WITH NumTally AS (--Prepare the Tally Table to separate each character of the Value.

  SELECT 1 Num

  UNION ALL

  SELECT 

    Num + 1 

  FROM 

    NumTally 

  WHERE 

    Num < @NoOfChars

),Chars AS ( --Separate the Characters

SELECT

  Num,

  SUBSTRING(@Value,Num,1) Chr

FROM

  NumTally  

)

--Persist the Separated characters.

INSERT INTO @Permutations

SELECT Chr FROM Chars

--Prepare Permutations

DECLARE @i AS INT = 1

WHILE(@i < @NoOfChars)

BEGIN

  --Store the Permutations

  INSERT INTO @Permutations

  SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated  Chars.

    P1.Value + P2.Value

  FROM 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = @i) P1 

  CROSS JOIN 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = 1) P2

  --Increment the Counter.      

  SET @i += 1  

  --Delete the Incorrect Lengthed Permutations to keep the table size under control.

  DELETE FROM @Permutations WHERE LEN(Value) NOT IN (1,@i)

END

--Delete InCorrect Permutations.

SET @i = 1

WHILE(@i <= @NoOfChars)

BEGIN

  --Deleting Permutations which has not used "All the Chars of the given Value".

  DELETE 

  FROM 

    @Permutations

  WHERE

    Value NOT LIKE '%' + SUBSTRING(@Value,@i,1) +'%'

  --Deleting Permutations which have repeated incorrect character.  

  DELETE 

  FROM 

    @Permutations

  WHERE

    LEN(Value) - LEN(REPLACE(Value,SUBSTRING(@Value,@i,1),'')) != 

    LEN(@Value) - LEN(REPLACE(@Value,SUBSTRING(@Value,@i,1),''))

  SET @i += 1  

END

--Selecting the generated Permutations. 

SELECT Value FROM @Permutations

Hope, this script helps!

Please share your suggestions if you have any to improve this logic.

SSRS # How to achieve Dynamic Sorting in SSRS?

Introduction
I wrote a blog on “Dynamic Sorting” in SSRS sometime back. Even though I successfully achieved sorting the data dynamically, I failed to find out a way of applying the “Sort Direction” dynamically as the Sort Direction did not accept an expression to manage it.
Recently, I came across a trick which solves this very easily purely in SSRS. Let’s jump on to the implementation now without wasting much time.

Implementation
Continuing with the same example of sorting 3 columns – Col1,Col2 & Col3 in any preferred order, we can specify the Sort Direction dynamically in the following way -
1. Define 3 additional parameters - @Col1SortDirection,@Col2SortDirection & @Col3SortDirection of STRING type.

2. While setting the Sort Options for Tablix or Group, we have to define sorting for double the number the sorting columns as under -














The Sort By expressions will be defined as under -
a. IIF(Parameters!Col1SortDirection.Value = "Ascending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
b. IIF(Parameters!Col1SortDirection.Value = "Descending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
c. IIF(Parameters!Col2SortDirection.Value = "Ascending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
d. IIF(Parameters!Col2SortDirection.Value = "Descending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
e. IIF(Parameters!Col3SortDirection.Value = "Ascending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
f. IIF(Parameters!Col3SortDirection.Value = "Descending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)

Note: we can use any CONSTANT value to replace Nothing used in above expression.

3. Pass the required Sort Direction, say - @Col1SortDirection = “Ascending”, @Col2SortDirection = “Descending” & @Col3SortDirection = “Ascending” or in any preferred value.

4. Pass the values for other parameters as explained in my last post.

Conclusion
With this we can now easily control the dynamic sorting with full flexibility of specifying the Column Names & the Sort Direction both dynamically using pure SSRS.
Hope, this will help you a lot!

How to hide Placeholder in SSRS 2008?

Introduction

For the ones who are working with SSRS, Placeholder is not new for them. So, today I will not go into explaining what is Placeholder, How and Where it is used, etc. I will straight away target the issue mentioned and try to explain a possible way to overcome that. However, for the ones who do not know about Placeholder, please visit this MSDN link.

Problem

Let’s first try to understand the problem I am talking about. Please have a look at Diag: 1-

Diag: 1

Here, as you have noticed, 4 different placeholders are used to make up the details for the Attribute column; 2 each for Labels & their respective values. Now, the requirement is such that the Labels & their Values for the Sub Category are to be shown only if the Value exists ELSE even the label should not be shown. This can be easily managed using the below expression for the Label & Value placeholders respectively -

Diag: 2

  Diag: 3

But the main issue here is the BLANK SPACE CREATED refer Diag: 1. There is not direct property available to Hide the Placeholder. Hence, the need arises to find out some work around.

Solution

The expected output is -

Diag: 4

To get the expected output, the main credit goes to the below Placeholder property -

Diag: 5

We just need to select HTML as the Markup type and then set the Value expression as under -

Diag: 6

And yes, we are done !

Conclusion

This was the solution I could manage to find out. There might exist other solutions as well. Please revert back if you are aware of any such solution by adding a comment below.

Dynamic Sorting in SSRS

Introduction
Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -
 Implementation
Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -

1. Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
2. Pass the name of the columns which need to sorted in the required order. Say -
   • @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
   • OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
   • OR any preferred order
3. In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
   • =Fields(Parameters!SortCol1Name.Value).Value
   • =Fields(Parameters!SortCol2Name.Value).Value
   • =Fields(Parameters!SortCol3Name.Value).Value

This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”

Conclusion
With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.

Workarounds for SSRS 2008 Font Rendering Issue in ReportViewer 10.0

Problem
A report developed using SSRS 2008 either through Visual Studio 2010 or BIDS 2008 does not renders properly when viewed in the Report Viewer 10.0 via Remote Desktop or any 3rd party applications using Terminal Services in the background.
Remote Rendering

Local Rendering

Workarounds

1. Change the Screen Resolution to 1024 X 768 or any 4:3 aspect ratio for both the Remote machine as well as the local machine.
   
2. Export the report as PDF and then take the print outs if required.
   
3. If the above two solutions does not work for you, unfortunately you will have to look back to SSRS 2005.

Hope, this saves you some time of searching.

Lookup functions in SSRS 2008 R2 - III

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the previous posts of Employees & Departments, suppose we want to display the name of the departments to which the Employee is related to, we can use the following expression -

Table Structure of Employees Table

ID	EmployeeID	DepartmentIDs
1	1	1,2,3
2	2	2,3

Table Structure of Departments Table

ID	Name
1	D1
2	D2
3	D3

Required Output

ID	EmployeeID	Departments
1	1	D1,D2,D3
2	2	D2,D3

To get the Departments, we can use the following expression in SSRS -

=Join(MultiLookup(Split(Fields!DepartmentIDs.Value,","),

   Fields!ID.Value,Fields!Name.Value,"Departments")),

   ",")

Here, we have used 3 functions -

1. Split() – To convert the comma separated DepartmentIDs into a value array.


2. Multilookup() – To find the Name of departments for the matching ID.


3. Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.

Conclusion

So, with this we have covered all the 3 lookup functions provided in SSRS 2008 R2 and have learned how to make best use of them to fetch data from multiple datasets.