Most of the time, you will be able to meet your report requirements using the basic and out of the box functions in your expressions editor; if you need to make more complex functions, you can write your own or use references as we will see next.
You can also add references to the .NET assemblies. Depending on the version of SQL Server you are using, you will be able to add assemblies. For example, you can add the assemblies created for the .NET framework 3.5 if you are working with SQL 2008 or for the .NET framework 4.0 in SQL 2012.
You can add references in the References section by going to the Report | Report Properties menu in Visual Studio. There you will be able to select an assembly that is installed on the GAC (Global Assembly Cache) or browse it from your local drives.
For example, you can add the System.Xml assembly if you need to manage the XML code in your report. Then you can use it in the expressions by writing a function in the Code section of the Report Properties window, as shown in the following code:
Public Function GetName()
Dim mydoc as new System.Xml.XmlDocument
mydoc.LoadXml("<root><customer>damain</customer></root>")
Dim node as System.Xml.XmlNode
node = mydoc.SelectSingleNode("/root/customer")
Return node.InnerText
End FunctionThen you can use this function on any expression as follows:
= Code.GetName()
You can also add references to the custom assemblies, which you create with Visual Studio; in that case you need to be sure that your assembly is signed by a strong key and you copy that assembly on the SSRS server by either installing it on the GAC or in the Report Server's bin folder, which is usually located at C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin. You will also need to edit web.config to add a reference to your custom assembly as follows:
<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\ReportingServices\ReportServer\bin\CustomAssembly.dll"/>
</CodeGroup>Controls in Reporting Services have some limited interactivity with the users; this is because they are mostly intended to be used to show and report data with no interactivity as you would have on a custom application, where the user can interact with controls such as buttons, checkboxes, or radio buttons. That is why you will see these types of controls missing in the report items' toolbox.
You can add some interactivity by using the Actions section of Placeholder Properties as follows:
The options of Action are:
None
Go to report
Go to bookmark
Go to URL
Using this will be similar to handling the click event of any report control. To add a bookmark on your report, just place a Textbox control and go to the Properties window; there you will find the bookmark property, where you will be able to add a name.
Another way to handle the click event is by using the Visibility section and checking the Display can be toggled by this report item: option. This option will also handle the user's mouse click event to show or hide other controls on your report.
Interactive Sorting adds another way to let the user interact with the report by allowing sorting of columns on a table. This way the user can change the default sorting presented by clicking on the column header of the table. Clicking on the header once will sort the records in ascending order, while clicking on it a second time will sort the reports in descending order.
After enabling sorting, a table would look as follows:
