SQL Server Reporting Services allows us to write custom code for valuing report items, modifying their formatting, etc. One way to include custom code in reports is to create a custom assembly (essentially a DLL built in Visual Studio) that can be referenced from within a report. The SSRS server then calls functions within that custom assembly when the report is run.
IPAR makes careful use of custom assemblies, because they can become a maintenance hassle when moving from version to version of SSRS. Also, there is no SSRS intellisense when they are called, and this opaqueness makes them somewhat inconvenient. On the other hand, they allow for encapsulated functionality that would be hard to achieve otherwise.
Using Custom Assemblies
An example of a custom assembly used by IPAR is the parameter parsing routine called LabelValues--the code for that routine is below. The routine is part of the WsuReportingServices.dll file which must be made available to both the SSRS server and an individual programmer's copy of Visual Studio in the following ways:
On the report server (SSRS 2008 R2), the file needs to be loaded to "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin"
On the report server (SSRS 2016), the file needs to be loaded to "C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin"
In pre-2017 Visual Studio versions, the file needs to be available here: "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies" (VS 2013)
In Visual Studio 2017, there are two locations to place all custom DLLs--each DLL needs to be in both directories:
"C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\PrivateAssemblies"
"C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS"
In Visual Studio 2019, there are two locations to place all custom DLLs--each DLL needs to be in both directories:
"C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\PrivateAssemblies"
"C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS"
Once the file is accessible, the routine below is called using syntax like "WsuReportingServices.ParameterFunctions.LabelValues(Fields!ParameterName.Value, "; ", Parameters)" (without the quotes). This particular function takes three inputs: the name of a report parameter, a delimiter (used for multi-valued parameters), and the parameter collection for the report. The function then returns the label associated with the given parameter during the current rendering of the report (or, in the case of a multi-valued param, a delimited list of all parameter labels).
The source code for all WsuReportingServices functions can be found in the "Reports\WsuReportingServices" Mercurial repository. The compiled DLL is also kept here:
S:\Data_Services\ScriptsAndUtilities\SSRS Custom Assemblies
More information on creating and using SSRS custom assemblies can be found here: http://ssrstips.blogspot.com/2009/07/custom-assemblies-in-sql-server.html
Sample Custom Assembly Source Code
The code for our LabelValues assembly is written in C# and looks like this:
using System;
using System.Collections.Generic;
using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;
using System.Text;
namespace WsuReportingServices
{
public class ParameterFunctions
{
public static String LabelValues(string ParameterName, string Delimiter, Parameters ReportParameters)
{
//
Parameter parameter = ReportParameters[ParameterName];
String s = "";
if (parameter.IsMultiValue)
{
Object[] myLabels = (Object[])parameter.Label;
for (int i = 0; i <= (parameter.Count - 1); i++)
{
s = s + Delimiter + (String)myLabels[i];
}
if (s.Length > 0)
{
if (s.Substring(0, Delimiter.Length) == Delimiter)
{
s = s.Substring(Delimiter.Length, s.Length - Delimiter.Length);
}
}
}
else
{
s = (String)parameter.Label;
}
return s;
}
}
}