SQL Server Reporting Services (SSRS) - Tips & Tricks

This article will demo using an XML Data Source type in Reporting Services 2008. We will then consume three different web service methods – simple web service method, parameter based method and a method that returns a dataset. 

Overview:

The Reporting Services, XML Data Provider extension, allows for you to query Web Services directly by parsing the XML structure of the SOAP response. You will need to have working knowledge of the Web Service Namespace, Method, SOAP Action, Parameters, and Schema of the response body. Once we have created our data source we can then specify what data is needed by using the XML Data Provider Query language, from within a dataset. You can learn more about using XML and Web Service Data Sources in Reporting Services here. The code download can be found at the end of this article.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Experience with the ASP.NET Web Service development

Steps Taken:

1. Create ASP.NET Web Service Project 
2. Define the three methods we'll be using
3. Publish the Web Service
4. Create a new Report Server Project
5. Add a new Data Source the uses the XML type 
6. Create report
7. Add Dataset's - use XML Data Provider Query language
8. Run report

Create ASP.NET Web Service

1) Launch Microsoft Visual Studio 2008

2) Select File >> New >> Web site...

3) Select the ASP.Net Web Service Template

4) For the Location, enter "C:\WSHost" and click OK.

5) Delete the default Service.asmx, and Service.cs from the App_Code folder.

6) Create a New Item... select the Web Service Template and name it "DemoServiceRS.asmx". Your Soultion Browser should look similar:

7) Open the DemoServiceRS.cs file.

8) You will notice a method named HelloWorld(), which returns the string "Hello World". We will use this for our non-parameter based Web Service Method.

9) Copy and paste the following parameter based Web Service Method, under the HelloWorld() Method:

[WebMethod]

public string GetReturnValue(string retval)

{

return retval;

}

 

You'll notice the [WebMethod] attribute above the method; attaching the WebMethod attribute to a Public method indicates that you want the method exposed as part of the XML Web service. The GetReturnValue will accept a string parameter and return that value - simple.

10) Next, add the following namespaces to your file:

using System.Data;

using System.Data.SqlClient;

 

This is used for the Dataset we will return in the next method.

11) Copy and paste the following code, under the GetReturnValue(string retval) method:

 

    [WebMethod]

    public DataSet GetTerritorySales()

    {

        string sqlText = "SELECT [TerritoryID],[Name],[CountryRegionCode],[SalesYTD] FROM [AdventureWorks].[Sales].[SalesTerritory]";

        string sqlConn = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

 

        DataSet ds = new DataSet();

        SqlDataAdapter daTerritory = new SqlDataAdapter(sqlText, sqlConn);

 

        daTerritory.Fill(ds, "TerritorySalesYTD");

        return ds;

    }

 

12) Save your work.

13) Right click your Solution Name and select "Publish Web Site".

14) The Publish Web Site dialog with prompt you to enter a Target Location... Enter http://localhost/WSHost, or the location you would like to publish this site to.

15) Click OK. Wait fot the site to complete the publishing process.

You have completed developing and publishing your Web Service, which will be used later in the demo.

 

Create Report Server Project

 

1) Create a new Report Server Project - I assume you have done this before. If not, please read our previous post Introduction to Report Builder 2.0 Part 2 - Creating a Report.

2) Name the project "DemoReportProject".

 

Create Shared Data Source

 

3) Add a new Shared Data Source and enter "AdventureWorksWSDL" for the Name. 

4) Select XML for the type.

5) For the Connection String, enter the location of the Web Service you published earlier in this article. For example, http://localhost/WSHost/DemoServiceRS.asmx. Your Shared Data Source Properties dialog should look similar:

6) From within the left pane, select Credentials.

7) Select the Use Windows Authentication (Integrated Security) option and click OK to close the dialog.

Create Report

 

 

1) Add a new report the project and name it "WSQuery.rdl".

 

Add Dataset's

Non-Parameter Based Query

 

1) Add a new dataset and name it HelloWorld

2) Within the dataset, select AdventureWorkdWSDL for the Data Source

3) Copy and paste the following script to the Query field:

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloWorld"/>
<SoapAction>http://tempuri.org/HelloWorld</SoapAction>
</Query>

Note: The Method element requires you to pass the Web Service Method to be consumed. Notice the Namespace passed in the example above. You have the option to rename this in your ASP.NET Web Service project. The XML Data Provider will auto-generate SOAP Action by appending Method Name and Namespace. For non-.NET Framework Web services, the SOAP Action may differ and will have to be set explicitly in the query.

 

4) Click OK.

5) You should see the HelloWorld Dataset in the Report Data pane.

6) Add a Matrix to the report.

7) Drag and drop the HelloWorldResult field to the matrix.

8) Run the report. You should see something similar:

Parameter Based Query

1) Add a new Dataset from the Report Data pane.

2) Name the new Dataset "GetReturnValue".

3) Within the dataset, select AdventureWorkdWSDL for the Data Source.

4) Copy and paste the following code Query field:

<Query>
<SoapAction>http://tempuri.org/GetReturnValue</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetReturnValue">
   <Parameters>
        <Parameter Name="retval" Type="String">
  <DefaultValue>Test</DefaultValue>
 </Parameter>
   </Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Note: You will notice a couple of new elements in this query - Parameters and ElementPath. Keep in mind, Parameters are case-sensitive. An ElementPath indicates how to traverse the XML hierarchical nodes and their attributes in the XML data source. If IngnoreNamespaces is set to true, namespaces in the XML ElementPath and the XML document are ignored.

Report Parameter

5) Add a new Parameter; From the Report Data pane, right click the Parameters folder and click Add Parameter...

6) Enter "retval" for the Name value.

7) Enter "Parameter Value:" for the Prompt value.

8) Make sure the Data type is set to Text.

9) Click OK to save Parameter changes.

Dataset Parameter

10) From within the Report Data pane, right click the GetReturnValue dataset and select Dataset Properties.

11) Click the Parameters pane item on the left.

12) Click Add from the Choose Query Parameter values section.

13) Under the Parameter Name column, enter "retval" (without the quotes).

14) Under the Parameter Value column, use the dropdown to select [@retval].

15) Click OK.

Add Matrix

16) Add a Matrix to the report.

17) Drag and drop the GetReturnValueResponse field to the matrix.

Note: If you attempt to run the report, you may not see a rendered value for GetReturnValueResponse, after entering the Parameter Value and clicking View Report.

Common error: The dataset ‘GetReturnValue’ contains a definition for the Field ‘GetReturnValueResponse’. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The dataset ‘GetReturnValue’ contains a definition for the Field ‘GetReturnValueResponse’. The data extension returned an error during reading the field. There is no data for the field at position 2.

Resolution: This is due to the Field Source having the incorrect value. We need to change the Field Source from GetReturnValueResponse to GetReturnValueResult.

18) Under the GetReturnValue dataset, right click GetReturnValueResponse and select Field Properties.

19) Find the GetReturnValue row and change value under the Field Source column to "GetReturnValueResult".

20) Click OK and Save your work.

21) Run the report. You should see something similar:

You will notice the new Parameter field at the top. You may enter any value for this field and it should render to the report.

Return Dataset Query 

1) Add a new Dataset from the Report Data pane.

2) Name the new Dataset "TerritorySalesYTD".

3) Within the dataset, select AdventureWorkdWSDL for the Data Source.

4) Copy and paste the following code to the Query field:

<Query>
     <SoapAction>http://tempuri.org/GetTerritorySales</SoapAction>
     <Method Namespace="http://tempuri.org/" Name="GetTerritorySales"></Method>
     <ElementPath IgnoreNamespaces="true">GetTerritorySalesResponse{}/GetTerritorySalesResult{}/diffgram{}/NewDataSet{}/TerritorySalesYTD{TerritoryID, Name, CountryRegionCode, SalesYTD}</ElementPath>
</Query>

Note: You will notice the ElementPath has some new values. In short, the values passed here will return what fields we want returned from the dataset. You can learn more about XML and Web Service Data Sources here.

Also, if you are uncertain of the XML Schema, you may run this query in the Query Designer, but exculed everything between the <ElementPath IngnoreNamespaces="true"></ElementPath>. This will return the Dataset ID, Field Names, Data Types, etc. Once you have this information you may construct the element path accordingly.

5) Click OK.

6) Add a Matrix to the report.

7) Drag and drop the following fileds to the matrix: CountryRegionCode, Name and SalesYTD.

8) Save your work.

10) Run the report (dont' forget to pass a parameter, from the previous dataset). You should see something similar:

 

That's all there is to it!

 

Cheers!

Download code here: 

WSHost.zip (3.43 kb)

DemoReportProject1.zip (32.67 kb)

This article explains how to create a recursive hierarchy group, which organizes data in a report to include multiple hierarchical levels. This is helpful when you want to display hierarchical data in a report. For example: employees in an organizational chart, or product subcategory in a product list. The image below is an example: 

 This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0 is installed. Note: We are using Report Builder 2.0 for this example.
• AdventureWorks database
• Experience working with Reporting Services

Steps Taken:

1. Create a new report
2. Add a Data Source and Dataset
3. Add a Table to the report and include dataset field
4. Edit Group properties and reference a Recursive Parent 
5. Add custom format expressions
6. Run code 

Create a new Report

1) Open Report Builder 2.0 and create a new report.

Note: If you have not done this before, please view my previous article "Creating a Report" for more information.

2) Save the report to your desired Report Server location and name it "RecursiveHierarchyGroup".

Add Data Source and Dataset

Creating the Data Source

1) From the Report Data pane, click New and select Data Source

the data source dialog will open...

 Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection. 

2) Provide a name for your data source. We will use the default value provided in this example.

3) Select "Use a connection embedded in my report" option

4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.

5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.

6) Enter the same values in the property fields, found in the image above.

1. Test the connection when you are done.
2. If test is successfull, click OK.
3. Click OK to close the Connection Properties window.
4. Click OK to close the Data Source Properties window.
5. You should now see your data source name on the left Report Data pane.

Note: the value in Server Name property is a period "."

Creating the Dataset 

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "Organization" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox:

select hr.EmployeeID, hr.ManagerID, c.FirstName + ' ' + c.LastName as 'Name', hr.Title

from HumanResources.Employee as hr inner join

Person.Contact c on hr.ContactID = c.ContactID

you should have something similar to below:

 

 5) Click OK to save changes and close the window.

Add Table to Report

1) In Design view, add a table, and drag the following dataset fields to display:

1. Name
2. Title

2) Insert a new column to the right of the Title field and name it "Level".

3) Click in the "Click to add Title" field and enter "Organizational Hierarchy"

Your design view should look similar to the image below:

Edit Group properties and reference a Recursive Parent

4) Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. Optionally, you can select View from menu tab and check Grouping. In the Row Groups pane, right-click Details, and select Group Properties. The Group Properties dialog box opens.

5) In Group expressions section, click Add. A new row appears in the grid.

6) In the Group on list, type or select the field to group - EmployeeID.

Your Group Properties General tab should look similar

7) Click the Advanced tab, from within the left pane of the Group Properties dialog.

8) In the Recursive Parent list, type or select the field to group on - ManagerID.

9) Click OK.

10) Run the report. You will notice the list of Employee names and titles. However, we need to do some formatting to indent the hierarchy accordingly.

Add custom format expressions

Indent Hierarchy

1) Right click on the [Name] field and select Text Box Properties...

2) From the left property pane, select the Alignment tab.

3) From under the Padding options section, select the Left expression button.

After clicking the expression button, you should see the following expression dialog.

4) Enter the following script in the expression textbox:

=CStr(2 + (Level()*10)) + "pt"

The Padding properties all require a string in the format nnyy, where nn is a number and yy is the unit of measure. The example expression builds a string that uses the Level function to increase the size of the padding based on recursion level. For example, a row with a level of 1 would result in a padding of (2 + (1*10))=12pt, and a row with a level of 3 would result in a padding of (2 + (3*10))=32pt.

Set Font Style

1) From the left property pane, select Font.

2) Under the Style section, click Bold expression button.

3) Enter the following script in expression textbox:

=IIF(Count(Fields!Name.Value, "Details", Recursive) > 1, "Bold", "Normal")

Essentially the Count function is searching the Fields!Name.Value, from within the "Details" group, recursively. If the recursive parent has children, then the parent is set to Bold, else Normal.

4) Click OK.

5) Right click on the Level field and click Expression...

6) Enter the following script:

=Level()

The Level() Function returns the current level (Integer) of depth in a recursive hierarchy.

7) Click OK.

5) Run the report. You should see similar results.

That's all there is to it.

Cheers!

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 2.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\"

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

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:

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

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

15) Name the column "Products".

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

5) Click OK.

Add field with expression to call the method

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

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 

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

5) Save your work.

You're done!

Cheers!

Download code here:

rsCustomAssembly.zip (27.68 kb)

DemoReportProject1.zip (32.67 kb)

This article we will describe how to enhance a Pie Chart by using the SoftEdge or Concave value, using SQL Server 2008 Reporting Services. These values are available only on a 2D Pie Chart. The image below illustrates both SoftEdge and Concave styles, respectively. We will be using Report Builder 2.0 for this example.

 

This article assumes the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0
• AdventureWorks database
• Experience working with Reporting Services

Set the PieDrawingStyle Property

1) Insert a Pie or Doughnut chart to your desgin surface.

2) Click on the Series section of the Chart. You should see the Series properties on the right Properties Pane.

 

3) From within the Properties Pane, scroll down to the General section and expand Custom Attributes.

4) Select the property PieDrawingStyle and select the SoftEdge value from the dropdown list.

Your chart should look similar to the image below

5) Try changing the PieDrawingStyle to Concave to test other styles.

That's it!

Cheers!  

In this article we will describe how to programmatically add a new Folder and create Properties for the folder, using Reporting Services Web Services. For this example, we will be using the ListChildren, CreateFolder and GetProperties methods. The article also show's how to render a list of Reporting Services catalog items (e.g., reports, data sources, folders, etc) by searching the Property Name and Value. This could be helpful if you plan to group information using Reporting Services and want a way to narrow your search for catalog items.

Note:The use example for the FindItemsByPropertyValue method below may be applied to reports, or any other Reporting Services object that allows for custom properties - for this demo, we will only search against folders.

The ReportingService2005.CreateFolder method adds a new folder the Reporting Services database. Optionally, you can add an array of Property objects to the newly created folder to hold additional information. For example, you may want to create a new folder name KPI's and add a property named Department, with a value of "Sales and Marketing". The really cool thing about creating a custom property value is, it allows you to create metadata to help with categorizing your information. You can then run a search for all named properties of "Department" with a value of "Sales and Marketing".

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• 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#
• Exposure to Web Services

You may download the code below to follow along with article. 

Adding Namespaces

1) Create a new Web Form and name it "CreateFolder.aspx".

2) Open the CreateFolder.aspx.cs and add the following namespaces:

Creating a Folder and Properties

using System.Web.Services.Protocols; /* used for the SoapException */

using RSIntegrationDemo.RS2005; /* this is what I named the reference to the Reporting Services Web Services. */

using System.Data; /* used for our datatable and datarow */

using System.Drawing; /* used for Color object */

 

 1) Add the following method to your CreateFolder.aspx.cs file.

        /// <summary>

        /// Adds a new folder to the Reporting Services database

        /// </summary>

        /// <param name="parentFolder"></param>

        /// <param name="folderName"></param>

        /// <param name="props"></param>

        /// <returns></returns>

        public bool CreateNewFolder(string parentFolder, string folderName, Property[] props)

        {

            rs = new ReportingService2005();

            // Create an instance of the Web service proxy and set the credentials

            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

            try

            {

                rs.CreateFolder(folderName, parentFolder, props);

                Label2.Text = "Success";

                Label2.ForeColor = Color.Blue;

                return true;

            }

 

            catch (SoapException e)

            {

                Label2.Text = "Failed";

                Label2.ForeColor = Color.Red;

                throw (new Exception(e.Detail.InnerXml.ToString()));               

            }

        }

 2) Next, add the following event that calls the method. Essentially, we're just creating an araay of property values and passing that to the CreateNewFolder method.

        protected void btnApply_Click(object sender, EventArgs e)

        {

            // create properties for folder

            Property[] props = new Property[2];

            Property desc = new Property();

            desc.Name = "Description";

            desc.Value = txtDescription.Text;

            props[0] = desc;

            Property department = new Property();           

            department.Name = "Department";

            department.Value = cmbDepartment.SelectedValue;

            props[1] = department;

 

            // add new folder and related properties

            // you can replace '/' with whatever root path you desire

            CreateNewFolder("/", txtFolderName.Text, props);

        }

Search Property Items

The method below traverses though catalog items, from within Reporting Services database, and render only those values that meet the Property Search criteria. We first start off by listing all children from the root (i.e., ListChildren("/", true)). Next, for each item found in the catalog, we pass the path to the item to the GetProperties method to get available properties. If the catalog item has a property, we then compare if the Keyword pass matches the propertis within the catalog item. You will notice the ItemTypeEnum is set to only search type of Folder.

        /// <summary>

        /// Search items by keyword

        /// </summary>

        /// <param name="propertyName"></param>

        /// <param name="keyword"></param>

        /// <returns></returns>

        public DataTable FindItemsByPropertyValue(string propertyName, string keyword)

        {

            rs = new ReportingService2005();

            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

            DataTable dt = new DataTable();

            DataRow dr;

 

            // add column names for dataview

            dt.Columns.Add(new DataColumn("ReportPath", typeof(string)));

            dt.Columns.Add(new DataColumn("ReportName", typeof(string)));

            dt.Columns.Add(new DataColumn("Description", typeof(string)));

 

            // Create the property to retrieve.

            Property retrieveProp = new Property();

            retrieveProp.Name = propertyName;

            Property[] props = new Property[1];

            props[0] = retrieveProp;

 

            CatalogItem[] items;

 

            try

            {

                // if catalog path is empty, use root, if not pass the folde path

                if (keyword.Length != 0)

                {

                    items = rs.ListChildren("/", true); // no recursion (false)

 

                    // iterate through catalog items and populate datatable

                    foreach (CatalogItem item in items)

                    {

                        //If folder is hidden, skip it

                        if (item.Hidden != true)

                        {

                            // ensure only folders are rendered

                            if (item.Type.Equals(ItemTypeEnum.Folder))

                            {

                                Property[] properties = rs.GetProperties(item.Path, props);

                                // check to see if the property value matches the keyword

                                foreach (Property prop in properties)

                                {

                                    if (prop.Value == keyword)

                                    {

                                        dr = dt.NewRow();

                                        dr[0] = item.Path;

                                        dr[1] = item.Name;

                                        dr[2] = item.Description;

                                        dt.Rows.Add(dr);

                                    }

                                }

                            }

                        }

                    }

                }

            }

            catch(Exception e)

            {

                throw (new Exception(e.Message));

            }

 

            return dt;

        }

The code is provided as a concept to approaching Reporting Services Web Application Integration and is not recommeded for production use.

Optionally, you may consider using a custom user control to handle this functionality.

Cheers!

Download the code here:

RSIntegrationDemo.zip (442.21 kb)

This article will demo the use of Reporting Services SOAP API to integrate with a web application. We will begin by creating a new Web Project in VS 2008 and configuring the web.config so we can authenticate using impersonation. Next, we will reference the ReportingService2005 web services proxy object. Finally, enter some code to render a list of reports from within the Report Catalog.

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

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• 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#
• Exposure to Web Services

Steps Taken:

1. Create a ASP.NET Web Application project
2. Reference the Reporting Services WSDL
3. Add Treeview control to web form
4. Develop code to consume Reporting Services Web Service

Create new ASP.NET Web Application

1) Open Microsoft Visual Studio 2008 from Start >> All Programs >> Microsoft Visual Studio 2008 >> Microsoft Visual Studio 2008

2) From the menubar, select File >> New >> Project

3) Expand the C# node and select the Web child node.

4) From the template pane, select ASP.NET Web Application.

5) Name the project "RSIntegrationDemo".

6) Click OK.

Reference Reporting Services WSDL

1)  From within your Solution Explorer pane, right click on the project name and select Add Web Reference... to create a new web services reference.

2) For URL: enter "http://localhost/reportserver/ReportService2005.asmx?wsdl" (replace localhost with your server name, if different).

3) For Web Reference Name: enter RS2005.

4) Click Add Reference button.

You will notice a new folder named Web References with RS2005 reference located within the folder.

 You have completed referencing the Reporting Services WSDL

Adding a TreeView Control

1)  When you create your project, the ASP.NET Web Application template should have included a default.aspx page, along with a default.aspx.cs file. If you do not have this file, please add a new web form to your project and name it "default.aspx".

2) Open the default.aspx web form in design mode.

3) Point to the Toolbox pane (if not visible) and expand the Navigation tab.

4) Drag on drop the TreeView object to the default.aspx web form.

5) From within the design pane, right click the TreeView item you just added and select properties.

6) From the properties pane on the right, name the TreeView "tvCatalog".

Steps 7-9 are optional.

7) Right click on the TreeView object and click the right arrow to expand the TreeView Tasks.

8) Select Auto Format...

9) From the Select a scheme:, select "XP File Explorer" and then click OK.

10) Save your work.

Consuming the Reporting Services Web Service

1) Open the default.aspx.cs file.

2) In order to communicate with our web service we need to add the following reference to our file.

using RSIntegrationDemo.RS2005;

3) Add the following code above Page_Load(object sender, EventArgs e)

 private ReportingService2005 rs;

 

        protected void Page_Load(object sender, EventArgs e)

        {

 

4) We need to create and instance of ReportingService2005 and pass credentials in order to authenticate to the web service. Enter the following code within the Page_Load event.

 protected void Page_Load(object sender, EventArgs e)

        {

            rs = new ReportingService2005();

            // Create an instance of the Web service proxy and set the credentials

            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

            if(!IsPostBack)

                BuildTreeView();

        }

Note: The DefaultCredentials property applies only to NTLM, negotiate, and Kerberos-based authentication. For ASP.NET applications, the default credentials are the user credentials of the logged-in user, or the user being impersonated. If you have problems authenticating, make sure the application pool account used has permissions to SSRS.  

5) Next, we will add our private methods, which will populate the treeview control with report items from the catalog. The first method, BuildTreeView() creates the Parent Node for our treeview control. The Method then calls the GetCatalogItems() method, which populates the treeview control with Report Items. 

Objects and Methods used:

CatalogItem - This object is used to return a report item from the report server database. We will pass an array of CatalogItem objects.

ListChildren - This will return a list of children withing a specified folder. For example, if we wanted all folder, reports, datasources, etc. returned, we would simply pass the following: 

 /// <summary>

        /// Build Treeview

        /// </summary>

        private void BuildTreeView()

        {

            TreeNode RootNode = new TreeNode();

            // give root node a name

            RootNode.Text = "Report Server";

            RootNode.Expanded = true;

            // add node to treeview object

            tvCatalog.Nodes.AddAt(0, RootNode);

            // populate treeview with catalog items

            GetCatalogItems(string.Empty, RootNode);           

        }

 

        /// <summary>

        /// recursivly get folder items from report

        /// server catalog; render to treeview control

        /// </summary>

        /// <param name="catalogPath"></param>

        /// <param name="parentNode"></param>

        private void GetCatalogItems(string catalogPath, TreeNode parentNode)

        {

            CatalogItem[] items;

 

            try

            {

                // if catalog path is empty, use root, if not pass the folder path

                if (catalogPath.Length == 0)

                {

                    items = rs.ListChildren("/", false); // no recursion (false)

                }

                else

                {

                    items = rs.ListChildren(catalogPath, false); // no recursion (false)

                }

 

                // iterate through catalog items and populate treeview control

                foreach (CatalogItem item in items)

                {

                    // if folder is hidden, skip it

                    if (item.Hidden != true)

                    {

                        // ensure only folders are rendered

                        if (item.Type.Equals(ItemTypeEnum.Folder) &

                            (item.Type != ItemTypeEnum.DataSource

                            & item.Name != "Data Sources"))

                        {

                            TreeNode folderNode = new TreeNode(item.Name, null);

                            folderNode.ImageUrl = GetNodeImage(item.Type);

                            folderNode.Text = item.Name;

                            folderNode.Value = item.Path;

                            folderNode.ToolTip = item.Name;

                            //Add the node to the parent node collection                           

                            parentNode.ChildNodes.Add(folderNode);

                            // recurse                      

                            GetCatalogItems(item.Path, folderNode);

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                throw (new Exception(ex.Message));

            }

            finally

            {

                rs.Dispose();

            }

        } 

6) Add the following method to handle the node image

 /// <summary>

        /// Gets corresponding node image

        /// (e.g., folder, reports,..)

        /// </summary>

        /// <param name="type"></param>

        /// <returns></returns>

        private string GetNodeImage(ItemTypeEnum type)

        {

            string imagePath = string.Empty;

 

            switch (type)

            {

                case ItemTypeEnum.Folder:

                    imagePath = folderImage;

                    break;

                case ItemTypeEnum.Report:

                    imagePath = reportImage;

                    break;

                case ItemTypeEnum.LinkedReport:

                    imagePath = reportImage;

                    break;

            }

 

            return imagePath;

        }

 

7) Add the following Constants 

const string reportImage = "~/images/report.gif";

const string folderImage = "~/images/folder.gif";

You can get the images from the download below.

Optionally, you may consider using a custom user control to handle this functionality.

Download code here:

RSIntegrationDemo.zip (442.21 kb)

This article will provide an example of how to create a Shared Data Source and publish it to your Report Server. You may use the data source while developing reports in Report Designer and Report Builder 2.0 tools. For this example, we will be using SQL Server Business Intelligence Development Studio 2008.

This article assumes the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0
• AdventureWorks database
• Limited exposure to Reporting Services

Create Report Server Project

1) Open Business Intelligence Development Studio from Start >> All Programs >> Microsoft SQL Server 2008 >> SQL Server Business Intelligence Development Studio

2) From the menubar, select File >> New >> Project

3) Select the Report Server Project template. Give the project a name and save to your desired path location.

4) Click OK.

Set Deployment Properties

1) From the Solution Explorer pane, right click and select Properties.

 2) The Property Pages diaglog will open up. Enter the following values for the properties:

• OverwriteDataSources = False
• TargetDataSourceFolder = Data Sources
• TargetReportFolder = DataSourceDemo
• TargetServerURL = http://localhost/reportserver

3) Click OK.

Create Shared Data Source

1) From within the Solution Explorer pane, right click Shared Data Sources >> Add New Data Source

The Shared Data Source Properties dialog shows

2) Enter "AventureWorks" for the Name field.

3) For Type, select Microsoft SQL Server from the dropdown list.

4) For the Connection String, click the Edit... button on the right of the textbox. The Connection Properties dialog will prompt you for values.

5) Use the Server Name dropdown to select your Server location, or if you are running it locally, simply enter a period "." (without the quotes) for a value. 

6) For the Log on to the Server section, select Use Windows Authentication option.

7) For the Connect to Database section, select Select of enter database name option. Using the dropdown, find AdventureWorks database.

Your Connection Properties dialog should look similar to the following:

8) Click the Test Connection button to test. You should received Test Connection Succeeded.

9) Click OK to save changes.

10) Click OK again to close Shared Data Source Properties window.

You should see the AdventureWorks data source in your Solution Explorer pane.

Publish Data Source

1) Right click on the AventureWorks.rds >> Deploy

The data source will be deployed to the Server location you specified earlier in this article. Look at the Output window for the deployment results. You should see Deploy Succeeded in the lower status bar.

That's it! You have completed Creating a Shared Data Source.

Cheers!

This article will provide you with a tip on how to define your own palette for charts, using Reporting Services 2008. We will be using Report Builder 2.0 for this example.

This article assumes the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0
• AdventureWorks database
• Experience working with Reporting Services
• Have experience in creating both a datatsource and dataset

Create report

1) Open Report Builder 2.0 from Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0.

2) From the upper left corner, click the red Report Builder Ribbon button, click Save As.

3) From within the left pane, select Recent Sites and Servers. Navigate to the location of where you would like to save your report.

4) Enter a Name (SalesChartDemo.rdl) for your report and click OK.

Create Data Source

For this section, you may follow the steps from Introduction to Report Builder 20 Part 2 Creating a Report. This site will provide details along with images.

1) From the Report Data pane, create a new data source; select an existing shared data source.

2) Name the data source AdventureWorks. 

3) You will be using the AdventureWorks sample database for this demo, please select this db as the source.

4) Click OK to save and close the Data Source Properties window.

Create Dataset

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "Sales" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox.

SELECT SalesTerritory, SUM([2004]) AS Y2004

FROM Sales.vSalesPersonSalesByFiscalYears

GROUP BY SalesTerritory

5) Click OK to save and exit the Dataset Properties window.

Add Chart to Report

1) Click on the Chart Wizard icon  from within the Design Surface. This should open the New Chart dialog.

2)  Select Choose and existing in this report option and select Sales from the list.

3) Click Next.

4) Select Pie from the Chart Type list.

5) Click Next.

6) Now you will arrange your chart fields. Please place the fields to the propert location:

• SalesTerritory --> Series
• Y2004 --> Values

7) Click Next.

8) Select Corporate from the Styles list and click Finish.

 You should see the following:

Set Chart Width and Height

1) Click once, anywhere on the Chart.

2) Make sure the report Properties Pane is visible. If not, select View from the menubar on top and check Properties.

3) From within the Properties Pane, scroll down and find Position >> Size; expand Size.

4)  Enter 5.5in for the Width and 2.51042in for the Height. Your report design and properties should look similar to the following:

5) Click Run to view the report. You have successfully added a chart to your report.

6) After you have viewed the report, select Design from the Report Ribbon. In the next section, we will define a new Color Palette.

Define Color Palette

1) From within the Design Surface, click once on your Chart. Notice the Chart properties are visible in the Properties Pane.

2)  From within the Properties Pane, scroll to the Chart section and click Palette.

3) Select Custom from the Palette dropdown.

4) A few properties above the Palette property you will find Custom Palette Colors. Select the ellipsis button (...) from the right of the property name to edit the list of colors.

5) Click Add to add a color, or simply modify an existing color from the right pane, Appearance section.

Note: When selecting a color from the dropdown list, you have the option to use an Expression. Here, you may pass a specific color using hex values. For example, use #F0E68C for Khaki.

6) Place the color in the order you would like by selecting the Member on the left pane and click the up or down arrow  from the middle of the dialog.

7) Click OK after you have completed your changes.

8) Run the report to see your changes.

You have completed defining a custom palette for your chart.

 

Cascading parameters are used to organize and limit the number of available values for the user. For example, you may have one parameter that filters a product category, and the second parameter used to list products related to product category. In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters. We will be using Report Builder 2.0 for this demo, but you may use the concepts presented below, while designing a report using BIDS.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0
• AdventureWorks database
• Experience working with Reporting Services

Create report

1) Open Report Builder 2.0 from Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0.

2) From the upper left corner, click the red ribbon button, click Save As.

3) From within the left pane, select Recent Sites and Servers. Navigate to the location of where you would like to save your report.

4) Enter a Name for your report and click OK to save and close the Save As Report window. You should see a similar view.

Create Data Source

For this section, you may follow the steps from Introduction to Report Builder 20 Part 2 Creating a Report. This site will provide details along with images.

1) From the Report Data pane, create a new data source; select an existing shared data source.

2) You will be using the AdventureWorks sample database for this demo, please select this db as the source.

3) Click OK to save and close the Data Source Properties window.

Create Data Set and Parameters

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "TerritorySales" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox.

SELECT ST.Name AS Territory, SUM(DET.LineTotal) AS SalesAmount

FROM Sales.SalesPerson SP

    INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

    INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID

    INNER JOIN Sales.SalesTerritory ST ON SP.TerritoryID = ST.TerritoryID

    INNER JOIN Production.Product P ON DET.ProductID = P.ProductID

    INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID      

    INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID

WHERE (PC.ProductCategoryID = @ProductCategory)

AND (PS.ProductSubcategoryID IN (@ProductSubcategory))

AND (SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate)

GROUP BY ST.Name

ORDER BY SUM(DET.LineTotal) DESC

 You should have a similar view below

 5) Click OK to save changes and close the window.

6) From within the Report Data pane, expand the Parameters node. You'll notice several new parameters, as shown below.

Set Parameter Data Types and Default Values

1) From within the Report Data pane, right click @StartDate and select Parameter Properties.

2) From within the left pane of the Report Parameter Properties window, make sure General is selected.

3) Under Data Type, select Date/Time.

4) From within the left pane of the Report Parameter Properties window, select Default Values.

5) Select the Specify Values option.

6) Click Add.

7) Enter in 2001-01-01 and click OK.

8) Repeat the same steps for the @EndDate parameter, but set the default value to 2004-07-01

You are finished with setting the data types and default values.

Create Product Category Dataset and set Parameter values

1) From within the Report Data pane, select New >> Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductCategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECT DISTINCT PSC.ProductCategoryID AS ProductCategoryID, PSC.Name AS Category

FROM Production.ProductCategory AS PSC

      INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID 

 

 5) Click OK to save your changes and close the Datset Properties window.

 The ProductCategoryList dataset should be visible in the Report Data pane.

Set the Product Parameter available and default values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductCategory, and then click Parameter Properties.

2) Check "Allow Multiple Values"

3) Click Available Values from the left pane.

4) From the right pane, select “Get values from a query” option.

5) For the Dataset value, select ProductCategoryList from the dropdown list.

6) Value field = ProductCategoryID.

7) Label field = Product.

6) Click OK.

Create Product Subcategory Dataset and set Parameter values

1) From within the Report Data pane, select New >> Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductSubcategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECT DISTINCT ProductSubcategoryID AS ProductSubcategoryID, PSC.Name AS Subcategory

FROM Production.ProductSubcategory AS PSC

      INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID

WHERE PSC.ProductCategoryID = @ProductCategoryID

5) Click OK to save your changes and close the Datset Properties window.

 The ProductSubcategoryList dataset should be visible in the Report Data pane.

Set the ProductSubcategory Parameter available values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductSubcategory, and then click Parameter Properties. Click Available Values from the left pane.

2) From the right pane, select “Get values from a query” option.

3) For the Dataset value, select ProductSubcategoryList from the dropdown list.

4) Value field = SubcategorySubcategoryID.

5) Label field = Subcategory.

6) Click OK.

7) Click Run, from the ribbon above the Report Data pane.

You should see the following

 

8) Select an item from the Product Category dropdown and notice how Product Subcategory is populated with values.

Next, you could go back to design mode and add a Table or Matrix to the report to render data.

Cheers!

 

Reporting Services provides the ability to add references to embededed VB.NET code from within the report. You can use this feature to create reusable functions that are called more than once in a report. This post walks you though the steps needed to add embedded code to your reports.

Note: Custom code can include new custom constants, variables, functions, or subroutines. You can include read-only references to built-in collections such as the Parameters collection. However, you cannot pass sets of report data values to custom functions; specifically, custom aggregates are not supported.   

The article assumes you have the following:

• Experience in designing reports in Reporting Services
• Both SQL Server and Reporting Services installed
• Business Intelligence Development Studio (BIDS)

Adding Embedded Code

1) Open and existing report project from BIDS.

2) From the Solution Explorer, double click on an existing report, or simply create a new one.

3) Select Design tab, and click your mouse anywhere in the design pane.

4) From the Toolbox pane, drag and drop a TextBox object to the report designer.

5) From the top menu bar select Report >> Report Properties

Note: if you don't see the Report menu item, try clicking your mouse anywhere in the design view; you should then see the menu item.

6) The Report Properties window opens. From within the left pane, select Code.

7) Enter the following VB.NET code in the Custom Code field.

Public Function Sec(ByVal angle As Double) As Double
    ' Calculate the secant of angle, in radians.
    Return 1.0 / Math.Cos(angle)
End Function

Public Function Sinh(ByVal angle As Double) As Double
    ' Calculate hyperbolic sine of an angle, in radians.
    Return (Math.Exp(angle) - Math.Exp(-angle)) / 2.0
End Function

Public Function GetPi() As Double
    ' Calculate the value of pi.
    Return 4.0 * Math.Atan(1.0)
End Function

8) Click OK to save and close the Report Properties window.

9) Right click on the Text Box you added previously and select Expression...

10) Enter the following code in the text box:

=Code.GetPi()

11) Click OK to save and close the Expression window.

12) Click the Preview tab to run the report. You should see a similar result.

That is it! Try calling the remaining functions to test your code.

Cheers!

function reference: http://msdn.microsoft.com/en-us/library/thc0a116(VS.80).aspx