Craig Yellick
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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...
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
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="FullTru st" Name="DataAccessGroupSQL" Description="Code Group for SQl Server Data Access">
<IMembershipCondition class="UrlMembershipCondit ion" version="1" Url="C:\WINDOWS\Microsoft. NET\Framew ork\v2.0.5 0727\Syste m.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!!!
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="FullTru
<IMembershipCondition class="UrlMembershipCondit
</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
As a test, you can remove all security from the report server code by changing this
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="Executi on"
Name="Report_Expressions_D efault_Per missions"
Description="This code group grants default permissions for code in report expressions and Code element. ">
<IMembershipCondition
class="StrongNameMembershi pCondition "
version="1"
PublicKeyBlob="00240000048 0000094000 0000602000 0002400005 2534131000 4000001000 100512C8E8 72E28569E7 33BCB12379 4DAB55111A 0570B3B3D4 DE3794153D EA5EFB7C3F EA9F2D8236 CFF320C4FD 0EAD5F6778 80BF6C181F 296C751C5F 6E65B04D38 34C02F792F EE0FE45291 5D44AFE74A 0C27E0D8E4 B8D04EC52A 8E281E01FF 47E7D694E6 C7275A09AF CBFD8CC827 05A06B20FD 6EF61EBBA6 873E29C8C0 F2CAEDDA2"
/>
</CodeGroup>
to use
PermissionSetName="FullTru st"
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.
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="Executi
Name="Report_Expressions_D
Description="This code group grants default permissions for code in report expressions and Code element. ">
<IMembershipCondition
class="StrongNameMembershi
version="1"
PublicKeyBlob="00240000048
/>
</CodeGroup>
to use
PermissionSetName="FullTru
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.
ASKER