Create a method to handle Stacked Values field in a Table or Matrix, using SSRS

In this article we will examine developing a method to handle stacked values within a field, while using a table or matrix. There may be multiple ways to handle stacked values approach, but we will focus on delivering the solution using a custom assembly. Code download is available at the end of the article.

For this example, we will be using Report Desinger, Visual Studio 2008 and ASP.NET/C# for sample code.

This article assumes you have the following:

  • SQL Server 2008 and Reporting Services installed
  • (optional) Report Builder 3.0
  • Microsoft Visual Studio 2008 installed
  • Reporting Services 2008 installed and a configured Report Server
  • Experience with Reporting Services 2005/2008 
  • Experience with ASP.NET and C#

Steps Taken:

  1. Create a Custom Assembly
  2. Copy assembly to ReportServer and PrivateAssemblies folders
  3. Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 
  4. Create a Report to render Vendor data
  5. Reference the custom assembly
  6. Add field with expression to call the method

Creating a Custom Assembly

1) Open Visual Studio 2008 and create a new Project >> Class Library. Name the project "rsCustomAssembly".

2) Delete the Class1.cs

3) Create a new Class... name it "Vendor.cs"

4) Copy and paste the following code to the file.

#region "***** Disclaimer *****"

 

/*=============================================================================

  File: Default.cs

 

  Summary:  This sample demonstrates creating a method that returns a list of

            Vendor Products by VendorID - returns a string value.

            The value can then be used in a table/matrix field to return

            stacked values, in Reporting Services.

           

---------------------------------------------------------------------

 

 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY

 KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

 IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

 PARTICULAR PURPOSE.

=============================================================================*/

 

#endregion

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data.SqlClient;

using System.Security.Permissions;

 

namespace rsCustomAssembly

{  

    public class Vendor

    {

        [PermissionSetAttribute(SecurityAction.Assert, Unrestricted = true)]

        public static String GetVendorProducts(int vendorID)

        {

            StringBuilder nRetVal;

 

            SqlConnection oConn = new SqlConnection();

            oConn.ConnectionString = "Data Source=(local); " +

                "Initial Catalog=AdventureWorks;Integrated Security=True";

            oConn.Open();

            SqlCommand oCmd = new SqlCommand();

            oCmd.Connection = oConn;

            oCmd.Parameters.AddWithValue("@VendorID", vendorID);

            oCmd.CommandText = "SELECT p.Name as 'CompanyName' " +

                "FROM Production.Product p " +

                "INNER JOIN Purchasing.ProductVendor pv on pv.ProductID = p.ProductID " +

                "WHERE pv.VendorID = @VendorID";

            SqlDataReader myReader;

            myReader = oCmd.ExecuteReader();

            nRetVal = new StringBuilder();

 

            try

            {

                while (myReader.Read())

                {

                    nRetVal.Append(myReader.GetString(0) + "\r\n");

                }              

               

                return nRetVal.ToString();

            }

            catch (Exception e)

            {

                return e.Message;

            }

            finally

            {

                // close reader and connection and return value

                myReader.Close();

                oConn.Close();

            }           

        }      

    }

}

5) Save your project. Don't close your IDE.

Copy assembly to ReportServer and PrivateAssemblies folders

1) Make your assembly available to Report Designer and Report Server in Reporting Services. To do this, you must copy your .dll to the Report Designer (PrivateAssemblies) folder and to the Report Server (ReportServer) folder.

Note: depending on your installation, the paths may be different.

  • For Reporting Services 2008, copy the .dll to the following folders:
    Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
    Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin
  • For Reporting Services 2005, copy the .dll to the following folders:
    Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
    Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

2) Since you may be modifying your custom assembly in the future, you may consider adding the following script to your project Build Events. Place the script in the Post-Build event command line: section. This will eliminate the need to manually copy and paste the .dll every time you need to publish a new version.

copy "$(TargetPath)" "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\"
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\"

reporting builder 2.0

3) Save your changes to the project and run a build. Your .dll should be copied over the defined paths in your Post-build event command line.

Note: Sometimes you may receive an error stating the .dll file could not be copied over to the defined path. This may be due to the fact you have a report open that references the assembly, so the active assembly in ReportServer/bin or PrivateAssemblies folder is locked. You may either close the report in the design view, or simply restart your SQL Server Report Services service from the Windows Services Management Console.

Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 

This custom assembly will require more permissions than the default Execution level granted in Reporting Services. We need to make some security change to code access in order for this work. A common indication that your custom assembly requires special code access permissions is the "#Error" value rendered when you run your report, instead of the expected result.

Note: before making any changes to your .config files, please make a backup of the files and save them to a new location. You may need to recover the backup files if you corrupt active .config files.

RSPreviewPolicy.config

1)  Navigate to the location of your RSPreviewPolicy.config ("Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it. 

RSSrvPolicy.config

1)  Navigate to the location of your RSSrvPolicy.config ("Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it.

Create a Report to render Vendor data

1) Open Business Intelligence Development Studio (BIDS) - Start >> All Programs >> Microsoft SQL Server 2008 >> SQL Server Business Intelligence Development Studio

2) Create a new Report Server Project - File >> New >> Project >> Project Type: Business Intelligence Projects >> Templates: Report Server Projects 

3) Name the Project "DemoReportProject" and click OK.

4) From within the Report Data pane, add a new data source. Use the AdventureWorks database and name the data source "AdventureWorks". If you do not have the AdventureWorks database you can get it here.

5) From within the Report Data pane, add a new Dataset.

6) Name the Dataset "VendorDS".

7) Select the AdventureWorks database for the Data Source.

8) Select the Text option for the Query Type

9) Enter the follow script in the Query textbox:

SELECT        VendorID, Name, FirstName, LastName, Phone
FROM            Purchasing.vVendor

reporting builder 2.0

10) Click OK.

11) Add a Matrix to your design surface. 

12) Drag and drop both Name and Phone, from the VendorDS dataset, to the Rows section of the Matrix. Add a Textbox for the title of the report. Your report should similar to the following:

reporting builder 2.0

13) Click on the Company Name column so that the column has the focus.

reporting builder 2.0

14) Right click on the column and select Insert Column >> Inside Group Right

15) Name the column "Products".

reporting builder 2.0

16) Save your work.

Reference the custom assembly

1) Make sure you are in the Design surface of Reporting Services. From the menubar, select Report >> Report Properties. This will open the Report Properties dialog.

2) From the left pane of the Report Properties dialog, select References.

3) Click Add from under the Add or remove assemblies section.

4) Click the Browse tab and navigate to your ReportServer/bin location. Select the rsCustomAssembly.dll and click OK.

Your Report Properties dialog should look similar

reporting builder 2.0

5) Click OK.

Add field with expression to call the method

1) Under the Products column, right click the Textbox and select Expressions... 

reporting builder 2.0

2) Copy and paste the  following code in the Value editor.

=rsCustomAssembly.Vendor.GetVendorProducts(Fields!VendorID.Value)

Notice how the call is made to the method: <Namespace>.<Class>.<Method>

Note: If you want to use instance-based methods, you will have go back to the Report Properties >> References tab, and then define the Class name and Instance name in Add or remove classes section.

3) Click OK.

Your Matrix should look something similar 

reporting builder 2.0

4) Preview the report by selecting the Preview tab. Your report should render similar results.

reporting builder 2.0

5) Save your work.

You're done!

Cheers!

Download code here:

rsCustomAssembly.zip (27.68 kb)

DemoReportProject1.zip (32.67 kb)

Comments (2) -

Thank you for your article, how would you setup the security if you use LINQ instead of using System.Data.SqlClient?  

Hi Iman,

There may be mutiple ways to approach this, but one approach which comes to mind is passing the user id "User!UserID" to a method in your custom referenced assembly (assuming the method accepts a value). The User!UserID will pass <domain>\<name> to the method; you can then handle your authentication process. I hope this helps.

Comments are closed