Accessing Database Record in Sql Server Reporting Service (SSRS) Custom Code

SSRS can use Custom Code to have more flexible way to achieve complex tasks like calculation, conditional result, and also accessing records on database.

Custom Code use System.Data Class to work with database which means read a records. This class have to be registered in SSRS config file and also have to be referenced in Report file.

In this blog post I will show you on how to read sql server record using SSRS Custom Code. I use SQL Server 2005 Express Edition and the database is AdventureWorks sample database.
I will list all Product Category with count number of product in specific category. I use Custom Code to calculate how many of product in category.

Here’s how to make Database enabled SSRS Custom Code:

Enable System.Data class to SSRS:

• Backup rssrvpolicy.config file. In my notebook it is located at C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer.
• Open rssrvpolicy.config file
• Add these line to the bottom of CodeGroup Tag:

  <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DataAccessGroupSQL" Description="Code Group for SQl Server Data Access">
  	<IMembershipCondition class="UrlMembershipCondition" version="1" 		Url="C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll" />
  </CodeGroup>
  

  Save rssrvpolicy.config file. So the altered file line will become like this screen shot:

  

• Change the PermissionSetName to FullTrust on CodeGroup Name’s “Report_Expressions_Default_Permissions”

  

• Restart the SSRS Services. You can use Reporting Services Configuration to restart it.

Making a database connection inside ssrs custom code:

• Open SSRS Visual Studio and Add new Item -> Report
• Create New Dataset with expression:

  select ProductCategoryID, Name from Production.ProductCategory order by ProductCategoryID
  

• Make a layout like screen shot below

  
  Under CategoryID header there is expression of Fields!ProductCategoryID.Value and under Name header there is Fields!Name.Value.
  I leave No of Product with blank for now.

• Go to report menu, properties, click Reference tab.

  
  Add reference with System.Data Class.

• Click Code Tab and write below Visual Basic code:

  function get_noofproduct(byval categoryid as string) as string
  
  dim query as string = "select count(*) from Production.Product as product, Production.ProductSubcategory as subcategory, "
  query = query & " Production.ProductCategory as category where product.ProductSubcategoryID=subcategory.ProductSubcategoryID "
  query = query & " and subcategory.ProductCategoryID=category.ProductCategoryID and category.ProductCategoryID=" & categoryid
  
  Dim conn As New System.Data.SqlClient.SqlConnection("Data source=.\SqlExpress;initial catalog=AdventureWorks;integrated security=true")
  conn.Open()
  
  Dim comm As New System.Data.SqlClient.SqlCommand(query, conn)
  dim cntproduct as string = comm.ExecuteScalar()
  conn.Close()
  
  return cntproduct
  
  end function
  

  This code has one paremeter category id and with this parameter it calculates how many record of product on categories.

• Go to layout section and write expression under No of Product column with =code.get_noofproduct(Fields!ProductCategoryID.Value) to call the custom code with include specific category id

Regards,
Agung Gugiaji