Reporting Services, access dataset from RDL custom code module

Posted on 2007-10-01
Last Modified: 2010-06-23
Using Reporting Services in SQL Server 2005, is it possible to access the report's dataset(s) from within a function implemented in the report's custom code module?

The custom code module I'm talking about is the one you see when you select Report - Report Properties from the VS 2005 Report Designer, in the Code tab.  I'd like to be able to perform lookups into the report's datasets within the body of a function in this code module.

For example:

   function LookupStoreName(ZipCode as string) as String
      '// ... code to access dataset, do some logic, return a value
   end function

To save you some trouble: yes I know that I could call a SQL Server function in a dataset definition. I need this functionality to be callable from a textbox value expression and not as a Fields!XXX.value or Parameters!XXX.value reference.  It needs to function independent of the report's main dataset.

-- Craig
Question by:CraigYellick
    LVL 18

    Accepted Solution

    I'm afraid there is no way for custom code (or referenced custom assemblies) to access the reports datasets.

    The only way around this (that I have found so far) is to pass a connection string as a parameter to each function. The connection string can be retrieved by a dataset and stored in an internal report parameter.

    LVL 11

    Author Comment

    I suspected as much, but before going through all those gyrations I though I'd better ask. Would be embarrassing to find there was a simple reference available. If intellisense worked in the custom code window it might yield a solution. Oh, well.  Thanks for the confirmation.

    Expert Comment

    How would one go about doing this "The only way around this (that I have found so far) is to pass a connection string as a parameter to each function. The connection string can be retrieved by a dataset and stored in an internal report parameter." ?

    Can someone please give an example? thanks.
    LVL 18

    Expert Comment

    Sample code attached below

    To call it, use
       =Code.QueryDatabase("Data Source=myServer; Initial Catalog=myDatabase; UID=myUser; Password=myPwd;")

    (obviously change it to match your security settings etc!)

    The next problem you will find with this is setting up the code access security on the server when you deploy it. That gets a little more complicated...
    Function QueryDatabase(ByVal Connection As String) As String
       Dim sVal as String
       Dim permission As System.Data.SqlClient.SqlClientPermission
       permission = New System.Data.SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted)
       Dim oConn As New System.Data.SqlClient.SqlConnection
       oConn.ConnectionString = sConnection
       Dim oCmd As New System.Data.SqlClient.SqlCommand
       oCmd.Connection = oConn
       oCmd.CommandText = "SELECT TOP 1 Value FROM Table WHERE..."
       sVal = oCmd.ExecuteScalar()
       Return sVal
    End Function

    Open in new window


    Expert Comment

    Thanks much. I did get it to work on my computer after adding the reference and taking out the 's' in sConnection on the line " oConn.ConnectionString = sConnection". But when I deploy it to the server it doesn't work. Any more suggestions? Thanks again.
    LVL 18

    Expert Comment

    Sorry about the 's' - typo!

    The problem is code access security.
    You can not reference any .Net assemblies without setting up the appropriate security on the server.
    You need to se up your rssrvpolicy.config file in Program Files\MSSQL.x\Reporting Services\ReportServer\

    Put this
                  <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" />

    Just before this


    If you put it in the wrong place SSRS will not work. BACK UP THE FILE FIRST!!!
    LVL 1

    Expert Comment

    I ran into the same problem as mendylosh (report not working once deployed), followed your advice by adding the code to the rssrvpolicy.config file, and am still running into the same problem.  Basically everything in my report is working except for the part that uses the custom code.  Do you have any additional advice?  Below is the custom code I am using, which works fine in Visual Studio:
    Shared offset as Integer
    Shared currentgroup as Object
    Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
    Dim sSQL as New System.Data.SqlClient.SqlCommand 
    Dim conSQL As New System.Data.SqlClient.SqlConnection
    Dim permission As System.Data.SqlClient.SqlClientPermission
       permission = New System.Data.SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted)
     If (group = currentgroup or group < 1)
        conSQL.ConnectionString ="Data Source=CtraxServer1; Initial Catalog=CTXControl; User Id=sa; Password=act;"
        sSQL.Connection = conSQL
        sSQL.CommandText = "INSERT INTO MultiPage (CustNo) VALUES ('" & group & "')"
        offset = pagenumber - 1
        currentgroup = group
      End If
      Return pagenumber - offset
    End Function

    Open in new window

    LVL 18

    Expert Comment

    As a test, you can remove all security from the report server code by changing this

                                    Description="This code group grants default permissions for code in report expressions and Code element. ">

    to use


    This will remove all security, and allow ANY .Net code to be run from within your reports. This opens up a massive security hole, but it is a good first step just to get things working. You can then play with the code access of the System.Data assemblies until you find something that works.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now