Pass SSRS Field Values to URL

Recently a coworker wanted to pass a field value (ID) to a URL in order to go directly to a specific Web Page from an SSRS report.  This can be done in SSRS by creating a dynamic expression in the Action section of the Text Box or Item that will be clicked.  If you choose the Go To URL option you can create an expression that will do the job. Below is an example.

Dynamic expression that simply concatenates a URL with the value of a field in the report.  In this example the name of the field is ID.  

="http://www.somedomain.com/id="&Fields!ID.Value

This example uses javascript to open a new window. 

Notice: The format is different. When using javascript you have to use + instead of & to concatenate. 

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?"+ Fields!ElementID.Value + "','_blank'))"

This example opens a new window and uses the Right Function on the Field Value

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?r"+ Right(Fields!PEIMSRecord.Value,3)+ "','_blank'))"

This example opens a new window and grabs the url from a field within a database table.

="javascript:void(window.open('"+Fields!RefURL.Value+"','_blank'))"

Contributors: Mark Lansdon, Trey Lawyer

SSRS - Top Directory

Below is a script that can be used to strip out the '/' from the SSRS Path so you can display the Top Folder/Directory.

Replace(Left(Catalog.Path,CHARINDEX('/',Catalog.Path,2)-1),'/','') As Top_Folder

The following query will return information regarding subscriptions located on your SQL Server.

SELECT catalog.NAME,
       catalog.type,
       catalog.description,
       catalog.creationdate,
       catalog.modifieddate,
       subscriptions.laststatus,
       subscriptions.lastruntime,
       users.username,
       catalog.path,
       Replace(LEFT(catalog.path, Charindex('/', catalog.path, 2) - 1), '/', '')
       AS
       Top_Directory
FROM   catalog
       INNER JOIN subscriptions
               ON catalog.itemid = subscriptions.report_oid
       LEFT OUTER JOIN users
                    ON catalog.createdbyid = users.userid
                       AND catalog.modifiedbyid = users.userid
                       AND subscriptions.modifiedbyid = users.userid
                       AND subscriptions.ownerid = users.userid
WHERE  subscriptions.lastruntime >= '8/1/2015'; 

Download All RDL Files

The following is a great PowerShell Script for downloading all RDL files from the ReportServer Database.  Without a script to download the RDL files you would have to go into each report from the Web Interface and select Download.  This script could save days worth of work.


<# .SYNOPSIS
      Export of all SSRS reports datasources and images
   .DESCRIPTION
      This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database
      to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -.
      Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension.
      Please change the "Configuration data" below to your enviroment.
      Works with SQL Server 2005 and higher versions in all editions.
      Requires SELECT permission on the ReportServer database.
   .NOTES
      Author  : Olaf Helper
      Requires: PowerShell Version 1.0, Ado.Net assembly
   .LINK
      GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx
#>


# Configuration data
[string] $server   = "ServerName";        # SQL Server Instance.
[string] $database = "ReportServer";        # ReportServer Database.
[string] $folder   = "\\Some Location";          # Path to export the reports to.

# Select-Statement for file name & blob data with filter.
$sql = "SELECT CT.[Path]
              ,CT.[Type]
              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent
        FROM dbo.[Catalog] AS CT
        WHERE CT.[Type] IN (2, 3, 5)";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$rd = $cmd.ExecuteReader();

$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($rd.Read())
{
    Try
    {
        # Get the name and make it valid.
        $name = $rd.GetString(0);
        foreach ($invalid in $invalids)
           {    $name = $name.Replace($invalid, "-");    }

        If ($rd.GetInt32(1) -eq 2)
            {    $name = $name + ".rdl";    }
        ElseIf ($rd.GetInt32(1) -eq 5)
            {    $name = $name + ".rds";    }

        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $name);

        $name = [System.IO.Path]::Combine($folder, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($name), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        $bt = $rd.GetSqlBinary(2).Value;
        $bw.Write($bt, 0, $bt.Length);
        $bw.Flush();
        $bw.Close();
        $fs.Close();
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}

# Closing & Disposing all objects
$rd.Close();
$cmd.Dispose();
$con.Close();
$con.Dispose();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");

Reference:
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227

FnSplit Function

The FnSplit Function comes in handy when using stored procedures within SSRS reports.  The function splits out the values for parameters and separates the values with commas.  This is needed if you want to select multiple values within a report.  There is an option for multiple values in the Report Builder and if your SQL is In-Line within the report you can simply use the following:

Select Field
From Table
Where School in (@SCHOOL)

However, this does not work properly in Stored Procedures.

There may be other options, but the one I use most often is the FnSplit Function.

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))

Depending on the report criteria you may need to add more code.  For example, if you want to select all values and have the ability to also select multiple values you will need to add an If statement to the stored procedure.

The first thing you have to do is add an option to your parameter.  If your parameter is a query you can do something like the following:

Select SchoolID, SchoolName
From Schools
Union
Select '000', '000-All Schools'

Stored Procedure:

If @School = '000'
Begin

Select Field
From Table
End

Else

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))
End

Find Active Directory Group - Report Security

Report Builder has a built-in function for User ID, but if you want to limit access or include expressions based on Active Directory Groups you will have to use custom code.

In the Report Properties, go to the Custom Code window and type in the following code:

Public Function IsMemberOfGroup() As Boolean


If System.Threading.Thread.CurrentPrincipal.IsInRole("Your AD Group Name") Then


     Return True


Else


    Return False


End If


End Function

 You can call the code in any expression window by using the following code: Code.FunctionName() 

Change image depending on Ad Security Group

=IIF(Code.IsMemberOfGroup()="True", "questionmarkrpt","questionmarkrptblank")

 Go to a specific link depending on AD Security Group. 

=IIF(Code.IsMemberOfGroup() = "True", "javascript:void(window.open('http://Google.com', '_blank'))", "javascript:void(window.open('http://yahoo.com', '_blank'))")

SSRS Report Builder - Table Column Width

I have found that some of the properties in the Report Builder for table columns do not work properly.  This is very frustrating, because what is rendered in the Report Builder is not the same as what is rendered on the server when the report is executed.  What I have found to work is to create an expression in the value of the cell to minimize the column width.

Say you have a field with a lot of data and it is separated by a special character.  This will cause the column to render one line because there is no separation in the data.  

Place this code in the value expression of the Text Box Property.  

=Replace(Fields!MyField.Value, ";", Environment.NewLine)

Change the ";" character to modify the separator that is present in your data. This will replace each special character with a new line, therefore, narrowing the table column width and giving your rendered report a better look.

SSRS Report Builder - Remove Element from Report Export

To render an element on a report but not print the element in the export file, place the code below in the element's "Visibility" Hidden property.

=NOT(Globals!RenderFormat.IsInteractive)

Use the code below to exclude from specific export files. 

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

 This table represents the Render Format Name to use in the expression. 

Renderer	RenderFormat.Name	RenderFormat.IsInteractive
Preview in BIDS or rendered through Report Manager	RPL	True
XML file with report data	XML	False
CSV (comma delimited)	CSV	False
TIFF file or Print button	IMAGE	False
PDF	PDF	False
MHTML (web archive)	MHTML	True
Excel	EXCEL	False
Word	WORD	False