Use Report Server 2008 content in SharePoint programming

Adding a report parameter to the name of a file stored in a SharePoint document library programmatically.

Introduction

In the previous post

• Standard subscriptions for SQL Server Reporting Services 2008 in SharePoint Integrated Mode

I have added an expression-based parameter to a Reporting Services 2008 report, this parameter was a date calculated by an expression: 7 years ago date.
Each day, this date increases by one day.
I have also added a subscription to the report, in order to deliver a pdf format version of the generated report in a SharePoint document library.
I have used the report Properties Page of SharePoint to set the subscription parameters in order to increment the pdf file name with a number.
Assume this subscription is scheduled daily. Would not it be interesting to put in the pdf file name instead of this increment number, the calculated date that is the parameter used to generate the report ?

That is what we are going to do in this tutorial, when the subscription will deliver the pdf file in SharePoint, we are going to retrieve programmatically the parameter that belongs to the report server content, and we are going to put this parameter in the pdf file name.
As a picture is worth a thousand words...
This is what we have.



And this is what we want to have, with the date equals the report expression-based parameter and this performed automatically after having written and deployed and Event Handler.



This seems to be easy to do with and Item Added Event Handler listening to the SharePoint document library where our .rdl and .pdf files are stored.
However, it is not as simple as it seems to be. We have to remind that for SQL Server Reporting services 2008 in SharePoint Integrated Mode, the data and metadata of a report are not stored in the same location. These data and metadata are shared between the SharePoint Databases and the Report Server Databases.
This is going to make the task a little bit more complicated.
We have to be aware of which data are stored in the SharePoint Databases, and we will be able to retrieve and act on these data, programming against the Windows SharePoint Services Object Model, but regarding the data stored in the Report Server Databases, we will have to use the Web Methods provided by the Report Server Web Service.

Here is a part of the msdn article that describes what are stored in which databases

Storing and Synchronizing Report server Content With SharePoint Databases

[...]
When you configure a report server to run in SharePoint integration mode, the report server uses the SharePoint configuration and content databases as well as its own internal databases to store content and metadata.

Both Reporting Services and Windows SharePoint Services are distributed server applications that allow you to run services and internal databases on separate computers. Each server stores different kinds of data. Multiple SQL Server relational databases provide the internal storage for both servers. Knowing which type of data is stored in each one can help you understand how the servers work together. It also provides background information that can help you make decisions about how to allocate disk space and schedule database backups.

The following diagram shows the complete set of databases used in a report server deployment that runs in SharePoint integrated mode.



[...]

We can notice that for our task, the .rdl report file, and the pdf file are well stored in the SharePoint Content DataBase of our Web Application, but regarding our subscription parameters, they are stored in Report Server Database. Thus, if we want to write code to manipulate the .rdl file or the .pdf file generated by the subscription job, we can use the WSS Object Model, but for the subscription parameters, we have to use the Web Methods of the Report Server Web Service.

2 - Tutorial Overview

Our project will be divided in the following steps:

• Step 1: Creating a Visual Studio 2008 project
• Step 2: Adding References
• Step 3: Writing C# code
• Step 4: Signing, building and deploying
• Step 5: Adding the Event handler Feature to the project
• Step 6: Deploying, Installing and Activating the Feature
• Step 7: Testing

2 - Prerequisites

01 - Environment

You have a complete environment with SQL Server Reporting services 2008 in SharePoint Integrated Mode on a single computer, or on multiple servers.

If not, you can build one using my previous posts:

• SQL Server Reporting Services 2008 in SharePoint Integrated Mode on a single computer:
  
  
  • Integrating Reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 Installation
  • Integrating Reporting Services 2008 with SharePoint 2007 Step 2 - SharePoint Installation
  • Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1
  • Integrating Reporting Services 2008 with SharePoint 2007 Step 4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2
  • Installing SQL Server 2008 samples: Adventure works (Integrating Reporting Services 2008 with SharePoint 2007 Step 5)
  
  
• SQL Server Reporting Services 2008 in SharePoint Integrated Mode on a multiple servers
  
  
  • SQL Server Reporting Services 2008 in SharePoint Integrated Mode - Distributed Server Deployment with Three computers

02 - Project

You have done the previous tutorials

• Standard subscriptions for SQL Server Reporting Services 2008 in SharePoint Integrated Mode
• Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

3 - Tutorial

3.1 - Step 1: Creating a Visual Studio 2008 project

Open Visual Studio 2008 and create a new Class library project called "TestReportingServices.SubscriptionDelivery".

3.2 - Step 2: Adding References

We are going to write code mixing Windows SharePoint services Model Object and Reporting Services one.
Thus we have to reference both.

01 - Referencing SharePoint

Add a reference to SharePoint.dll, and a namespace directive to Microsoft.SharePoint.

02 - Referencing Reporting Services 2006 Web Service.

In the Solution Explorer pane of your Visual Studio project, right click your project name and click "Add Service Reference".



The "Add Service Reference" dialog is opening, click "Advanced".



The "Service Reference Settings" dialog is opening, click "Add web Reference".



In the "Add web Reference" dialog, type your Report Server url + "/reportservice2006.asmx" in the "URL" field, then go.
(you can find the file on your Report Server computer at this location : C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer)



The dialog will connect the web service and retrieve all the Web methods.



Give a name for the Web Reference that will be used in your code. I personally called it "ReportingServicesWebService".

3.3 - Step 3: Writing C# code

Rename your project default class to AddedAction
Make the class inherit from SPItemEventReceiver
Add an overriden method of SPItemEventReceiver.ItemAdded
Here is the complete AddedAction Class code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint;

using System.Diagnostics;

 

namespace TestReportingServices.SubscriptionDelivery

{

    public class AddedAction: SPItemEventReceiver

    {

        public override void ItemAdded(SPItemEventProperties properties)

        {

            //Apply just to report Libraries

            using (SPWeb myWeb = properties.OpenWeb())

            {

                //the event handler will be trigger only for the Shared Documents document library of

                //the testReportingServices web site

                if (myWeb.Title != ("testReportingServices") || properties.ListItem.ParentList.RootFolder.ToString() != "Shared Documents")

                {

                    return;

                }

            }

            //Apply just to not already processed subscription deliveries.

            if (properties.ListItem.Name.Contains(DateTime.Now.Year.ToString()))

            {

                return;

            }

 

            base.ItemAdded(properties);

 

            try

            {

                SPList reportList = null;

                SPListItem myItem = properties.ListItem;

                string ReportEditionDate = string.Empty;

                string reportName = string.Empty;

 

                //getting report edition date

                try

                {

                    ReportingServicesWebService.ReportingService2006 rs2006 = new ReportingServicesWebService.ReportingService2006();

 

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

 

                    //we have to build the complete URL of the .rdl file to give it as a parameter to the web service

                    //I have hard-coded the name of the .rdl file and let you to write some code

                    //in order to retrieve the .rdl file name

 

                    reportName = "Report1";

 

                    string myListitemURL = properties.ListItem.Url;

                    string ReportFileCompleteUrl = myListitemURL.Remove(myListitemURL.LastIndexOf("/"));

                    ReportFileCompleteUrl += "/" + reportName + ".rdl";

                    ReportFileCompleteUrl = Microsoft.SharePoint.Utilities.SPUrlUtility.CombineUrl(properties.WebUrl, ReportFileCompleteUrl);

 

 

                    ReportingServicesWebService.ReportParameter[] myParameters = rs2006.GetReportParameters(ReportFileCompleteUrl, null, null, null);

                    ReportingServicesWebService.DataSourceCredentials[] myCred = new ReportingServicesWebService.DataSourceCredentials[1];

                    ReportEditionDate = myParameters[0].DefaultValues[0];

 

                }

                catch (Exception ex)

                {

                    //write error message in debug view

                    Debug.WriteLine("TestReportingServices.SubscriptionDelivery  Reporting Services web service error" + ex.Message);

                }

 

                //changing pdf file name:

                using (SPWeb myWeb = properties.OpenWeb())

                {

                    reportList = myWeb.Lists[properties.ListId];

                }

 

                string myItemFolderURL = myItem.Url.Remove(myItem.Url.LastIndexOf("/"));

 

                DateTime dtReportEditionDate = Convert.ToDateTime(ReportEditionDate);

 

                string reportEditionDateEncoded = string.Empty; ;

                string reportEditonDateMonth = string.Empty; ;

                string reportEditonDateDay = string.Empty; ;

 

                if (dtReportEditionDate.Month < 10)

                {

                    reportEditonDateMonth = "0" + dtReportEditionDate.Month.ToString();

                }

                else

                {

                    reportEditonDateMonth = dtReportEditionDate.Month.ToString();

                }

 

                if (dtReportEditionDate.Day < 10)

                {

                    reportEditonDateDay = "0" + dtReportEditionDate.Day.ToString();

                }

                else

                {

                    reportEditonDateDay = dtReportEditionDate.Day.ToString();

                }

 

                reportEditionDateEncoded = dtReportEditionDate.Year + "-" + reportEditonDateMonth + "-" + reportEditonDateDay;

 

                string myItemNewUrl = myItemFolderURL + "/" + reportName + "_" + reportEditionDateEncoded + ".pdf";

 

                //copy the file in the same doc lib with a new name.

                myItem.File.CopyTo(myItemNewUrl, true);

                //delete the old file with the old name

                myItem.File.Delete();

 

            }

            catch (Exception ex)

            {

                //write error message in debug view

                Debug.WriteLine("TestReportingServices.SubscriptionDelivery error:" + ex.Message);

            }

        }

    }

}

3.4 - Step 4: Signing, building and deploying

01 - Signing the dll

As we are going to deploy the dll into the GAC, we have to sign it with a strong name.

02 - Building and deploying

Build your dll.
In the solution explorer of Visual Studio, right click the bin directory and select "Open folder in Windows Explorer". Then open the "debug" folder.
Open the GAC (c:\Windows\assembly) and drag and drop your dll into the GAC.



Then locate the dll in the Gac and right click it and click "properties".
In the opening dialog, select and copy the Public Key Token".

3.5 - Step 5: Adding the Event handler Feature to the project

We are not going to deploy the feature and the dll using WSPBuilder, but as we might do it later, I used to place my Features in "12\TEMPLATE\FEATURES" directory. Inside the "FEATURES" folder, create 2 xml files, Feature and Elements.
Here is the complete and final view of the Solution Explorer of the project.



Here is the Feature code:

<?xml version="1.0" encoding="utf-8" ?>

<Feature xmlns="http://schemas.microsoft.com/sharepoint/"

         Scope="Web"

         Title="test reporting Services - Subscription delivery name changing"

         Id="BF57BFA5-0D9B-4012-9B6D-DD4A1763E9FE">

  <ElementManifests>

    <ElementManifest Location="Elements.xml"/>

  </ElementManifests>

</Feature>

Here is the Elements code:

do not forget to replace the public key token with the one you have just copied.

<?xml version="1.0" encoding="utf-8" ?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

  <Receivers ListTemplateId="101">

    <Receiver>

      <Name>AddedEventHandler</Name>

      <Type>ItemAdded</Type>

      <SequenceNumber>1000</SequenceNumber>

      <Assembly>TestReportingServices.SubscriptionDelivery, Version=1.0.0.0, Culture=neutral, PublicKeyToken=553168aa1c81f131</Assembly>

      <Class>TestReportingServices.SubscriptionDelivery.AddedAction</Class>

      <Data></Data>

      <Filter></Filter>

    </Receiver>

 </Receivers>

</Elements>

3.6 - Step 6: Deploying, Installing and Activating the Feature

Copy the project feature in your SharePoint Server 12 directory:



Then open a command prompt and install the feature:



Then, open your SharePoint Site, go to your Site Settings, and open your "Site Features" page in order to manually activate the previously installed feature.

3.7 - Step 7: Testing

Now open SQL Server Management Studio and connect to the Database Engine in order to execute manually you subscription job.



Notice that you can rename your job, fortunately.
Now our Event Handler will be triggered when the Subscription job will add a pdf file to the SharePoint document library.
It will find the report name, will pass its complete url to the Report Server Web Service, will get the subscription parameter value for the current day, will encode this date, will generate a new file name with this date, will copy the just added pdf file with the new name, then delete the old file with the old name. And you will get that result:



Every hour, the modified time of the last file will increase by an hour, and the day after, a new file will appear with the six years ago date inside its name.

4 - Code modifying, and deployment for multiple server environment

Very important:

01 - When, you will compile your code after having made changes and redeploy the .dll into the GAC, to see the code modification effects, don't do an IIS Reset as usual, but re-start the Reporting Services service.



02 - If you are working in a multiple servers environment where Report Server and SharePoint are on two separate computers, you have also to deploy the Event Handler dll to the GAC of the Report Server computer and restart the Reporting Services service in order the Event Handler works on the SharePoint computer. Furthermore, if you use a tool as debug view to trace your Event Handler process, you have to start your tool on the Report Server computer, and not on the SharePoint one.