2009/09/08

Reporting Services 2005 and Embedded Code

I’ve a got a report where a field must be populated calling a “cascading” stored-procedure from the main query.

There 2 ways to do it:

  • To Create a sub-report and embedding it in the field just to retrieve the sp result (easy to deploy, but weak in performance)
  • Use SSRS Embedded code with ADO.net to invoke the stored-procedure populating the field.

Obviously the second way is “more” pragmatic (it guarantees the best performance but… from a developer perspective it’s inelegant).

Well I’ve chosen the second way so I needed to setup the SSRS .Net CAS permission.

  1. Open the Report Server application directory (something like C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager)
  2. Open rsmgrpolicy.config
  3. Add a new PermissionSet to allow execution of SQL code

                            <PermissionSet
                                    class="NamedPermissionSet"
                                    version="1"
                                    Name="Corra-Expression">
                                <IPermission
                                        class="SqlClientPermission"
                                        version="1"
                                        Unrestricted="true"
                                />
                                <IPermission
                                        class="SecurityPermission"
                                        version="1"
                                        Flags="Execution"
                                />

  4. Modify the predefined Code Group (for Reporting Services expressions) after having backed-up the previous one:

                            <CodeGroup
                                    class="UnionCodeGroup"
                                    version="1"
                                    PermissionSetName="Corra-Expression"
                                    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="blablabla"
                                />

  5. Now you’re able to invoke ADO.net from SSRE expressions.