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

Advertisements

3 responses to “Accessing Database Record in Sql Server Reporting Service (SSRS) Custom Code

  1. Hi ,
    Hope you doing good.
    i serched for your mail id but i ddnt found from anywere. I am writing here coz i dont have any other way. Sorry if you mind.
    I am new to the SQL reporting. I am strated a report with 9 dtasets from 3 diff databases. I was doing this report from past 2 weeks.
    i have a requirement
    in dataset A i have like

    ProdName Range1 range2 sum from dataset B Sum from Dataset C

    Nav 101 501 ? ?

    Sql 601 1200 ? ?

    code.calculate(range1,range2)

    in calculate i want to calcuate the sum of amount from DATASET otherthan tablix(from onotherdataset) in between my range.

    Is that Possible?

    I tried using with the SQL function but i cannot get the filter value which resides in other dataset. I tried to pass it throug the parameter by setting defualt values but not succeeded.

    finally i tried with the custome code. I tried the code below
    Function westsubsum() As decimal
    Dim conn As New System.Data.SqlClient.SqlConnection (“Data Source=asharaf;Initial Catalog=test;Integrated Security=true”)
    conn.Open()
    Dim Cmd As New System.Data.SqlClient.SqlCommand
    Cmd.Connection = conn
    Cmd.CommandText = “select dbo.calculatesumwithdate(1000000,2000000,’12/31/2008′)”
    Dim totval As Decimal = Cmd.ExecuteScalar()
    conn.Close()
    return totval
    end function
    but i am getting #error in that

    or is there any otherway to achieve this?

    I have around 9 diff dtasets. I am using Report builder 3.0 to build report.

    can u help me to solve this issue? Thanks in advance.

    Regards,
    Asharaf P

  2. Thanks for this very clear explanation! I have been searching for exactly this for the past couple of days.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s