Monday, March 16, 2009  by Marc Charmois - About Me / Previous Posts / Archives - Home

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 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
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.




Labels: , , ,

7 Comments:

Anonymous Anonymous said...

Hi there,

Great post!
I can use most of it with modifications. Just one question (I seem to be stuck with this): when you deploy this solution to another server (provided that you have dev server and test server), how do you setup the web service URL - do you put it in the MOSS web application web config only, or also in the RS config file?

Thanks a lot!

July 22, 2009 at 1:28 PM  
Blogger Marc Charmois said...

Hi,

Thank you for this comment. Actually I was waiting for someone to publish a comment about this concern or ask the question.

Because I had the same concern while posting this article and found the solution after.

Actually, when you are deploying SharePoint and Reporting Services all together in the same Server Farm, an entry for the Reporting Services Web Service is created in SharePoint among the other SharePoint Web Services under the _vti_bin virtual directory.

So you can use this entry and replace in my code the hard coded URL by a relative URL that will be working in any environment.

Hope that helps.

Marc

July 22, 2009 at 4:19 PM  
Anonymous Anonymous said...

Thanks for the quick reply!

I am not sure I understand completely your idea.
You are right about the RS web service being available under the MOSS site IIS directory. I can now successfully use the http://[MOSS URL]/_vti_bin/reportserver/reportservice2006.asmx - which is fine.
But I do not know how to specify this URL as relative - if I try to use ~/_vti_bin/reportserver/reportservice2006.asmx, Visual studio gives me an error message.

Thanks a lot!
Regards,
Yordanka

July 24, 2009 at 1:40 PM  
Blogger Marc Charmois said...

You are welcome...

You are right, my answer does not completely solve the concern, since you still cannot use a relative url or a parameter and you still have to hardcode a server name.
However, the good point is, using an URL that references the MOSS server allows you to have a DEV environment with a single machine and to be allowed to deploy to a TEST environment where MOSS and Reporting Services are on 2 different servers (since there is no more reference to the Report Server).

Regarding the way of using a parameter or a relative url when calling a Web Service in Visual Studio, I have already performed researches on the topic, and I have to gather them in a new post. But what I advice you to do for the moment and what I am doing always myself is

1 to give to your DEV MOSS Server the same name than your TEST MOSS Server,
2 to use the same port number,

and you are done!

Of course this is easy to do only if your DEV environment is on a different network than your TEST environement, for example in virtual machines on your Laptop, in order to be allowed to give the same name to two different machines.
If not you maybe have to play with host headers, host files, Visual Studio configuration, and I even don't know if it is possible.

Last, did you try with "localhost"?
It should work and is maybe the best shortcut to get a fast result. Unfortunately, I don't think using "localhost" will be possible if you have the Report Server and the SharePoint one in two different machines...

Hope that helps,

regards.

Marc

July 25, 2009 at 5:14 PM  
Anonymous Anonymous said...

Hi there,

A final comment here :) I did find a solution and because lately I see so much blogs with no final solution, here this is:
If you modify the URL of the web service programmatically and set it to:
rs2006.Url = properties.WebUrl + "/_vti_bin/reportserver/reportservice2006.asmx";

the issue is solved - no hardcoded values.

This will only work in SharePoint integrated mode but there is no hardcoded URL.
Another approach I tried (unsuccessfully) was to set the URL proeprty of the web reference in VS 2008 to dynamic. For some reason this did not work at all - I added the config entry created by VS2008 to the MOSS application web.config file but it did not get loaded and I constantly got an error saying "the remote name cannot be resolved".

Once again: thank you for the very useful post!

July 30, 2009 at 11:56 AM  
Blogger Marc Charmois said...

Yordanka,

thank you very much for your contribution.
I am happy that my post was a starting point that encourages people like you to participate and find and share such useful tips and tricks.

Thanks to YOU!

Cheers,

Marc

August 5, 2009 at 10:07 PM  
Anonymous Anonymous said...

Hi,

can you pls post the complete code with rs2006.Url = properties.WebUrl + "/_vti_bin/reportserver/reportservice2006.asmx" modification.

December 13, 2009 at 10:26 PM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home