Variable, Custom Code and Assembly reference in SSRS

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Published:
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS.

Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps need to be applied. Let's look in details.


1. Variable in SSRS


If you want to use or hold value through out the request then refer the SSRS variables. It is available in any part of report and lifetime of variable is request to server. It reset when new request comes to server.

You can create variable using Menu=>Report=>Report Properties=>Variables

Add variables to SSRS
You can use this variable in calculation of field or anywhere in the report as below:

Use of Variable in Field expression

2. Custom Code in SSRS


Custom Code is very useful where you can not achieve desire result using Sum/Avg/Count/Running Total.

For example in report you are displaying records (Products) those have price > $10 That time you can hide those rows in report but total not eliminate hidden rows in count. In these type of case it's good to have custom code. Let's see how can we create custom code.

You can create custom code at Menu=>Report=>Report Properties=>Code

Sample of Custom Code
Note: Make sure SSRS support custom code in VB only.

Let's time to see how to use the custom code in report fields.

Use of Custom Code function in Field expression

3. Assembly Reference in SSRS


This is the very crucial part of the Assembly reference in SSRS. It is very useful when you want to use logic in other reports rather than in single one (Shared custom code).

To do that Just create one Library (As you know it is basic task.) Build that and paste it to

Consider for SQL 2008

To load assembly in SSRS
%\Program Files%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

To load assembly in BIDS (Development environment)
%\Program Files%\Microsoft Visual Studio %Installed Version%\Common7\IDE\PrivateAssemblies

Now time to consume or add assembly reference to SSRS.

You can add assembly reference  at Menu=>Report=>Report Properties=>References

Add assembly reference
1. Click on Add Assembly reference and select your library.
2. Add class and create instance in second frame.

Use of assembly function in Field expression

That's it for now. Hope meet with new Topic again.

Thanks,

Alpesh Patel
4
9,424 Views
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.