Link to home
Start Free TrialLog in
Avatar of Craig Yellick
Craig YellickFlag for United States of America

asked on

Reporting Services, access dataset from RDL custom code module

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
ASKER CERTIFIED SOLUTION
Avatar of PFrog
PFrog
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Craig Yellick

ASKER

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.
Avatar of mendylosh
mendylosh

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.
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)
   permission.Assert()
 
   Dim oConn As New System.Data.SqlClient.SqlConnection
   oConn.ConnectionString = sConnection
   oConn.Open()
   
   Dim oCmd As New System.Data.SqlClient.SqlCommand
   oCmd.Connection = oConn
   oCmd.CommandText = "SELECT TOP 1 Value FROM Table WHERE..."
   sVal = oCmd.ExecuteScalar()
   oConn.Close()
 
   Return sVal
 
End Function

Open in new window

Thanks much. I did get it to work on my computer after adding the System.data.dll 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.
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" />
              </CodeGroup>

Just before this

            </CodeGroup>
          </CodeGroup>
        </PolicyLevel>
      </policy>
    </security>
  </mscorlib>
</configuration>


If you put it in the wrong place SSRS will not work. BACK UP THE FILE FIRST!!!
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)
   permission.Assert()
 
 If (group = currentgroup or group < 1)
    conSQL.ConnectionString ="Data Source=CtraxServer1; Initial Catalog=CTXControl; User Id=sa; Password=act;"
    conSQL.Open()
    sSQL.Connection = conSQL
    sSQL.CommandText = "INSERT INTO MultiPage (CustNo) VALUES ('" & group & "')"
    sSQL.ExecuteScalar()
    conSQL.Close()
  Else
    offset = pagenumber - 1
    currentgroup = group
  End If
  Return pagenumber - offset
End Function

Open in new window

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

                        <CodeGroup
                                class="UnionCodeGroup"
                                version="1"
                                PermissionSetName="Execution"
                                Name="Report_Expressions_Default_Permissions"
                                Description="This code group grants default permissions for code in report expressions and Code element. ">
                            <IMembershipCondition
                                    class="StrongNameMembershipCondition"
                                    version="1"
                                    PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100512C8E872E28569E733BCB123794DAB55111A0570B3B3D4DE3794153DEA5EFB7C3FEA9F2D8236CFF320C4FD0EAD5F677880BF6C181F296C751C5F6E65B04D3834C02F792FEE0FE452915D44AFE74A0C27E0D8E4B8D04EC52A8E281E01FF47E7D694E6C7275A09AFCBFD8CC82705A06B20FD6EF61EBBA6873E29C8C0F2CAEDDA2"
                            />
                        </CodeGroup>

to use

      PermissionSetName="FullTrust"

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.