Check error messages for SQL Server Reporting Services 2008

Enable remote errors for Reporting Services 2008 - Check Reporting Services 2008 Log Files

Introduction

When you are working with Reporting Services 2008 in SharePoint Integrated Mode, you may encounter this error message in a Report Viewer Web Part or the RSViewerPage.aspx.
"For more information about this error, navigate to the report server on the local server machine, or enable remote errors"

If you want to know more about the error, you have 3 solutions:

• Navigate to the Report Server Web Service on the computer where Reporting Services is installed.
• Check the Reporting Services Log Files on the computer where Reporting Services is installed.
• Enable Remote Errors

1 - Navigate to the Report Server Web Service

There is two ways obtaining the Report server URL for Reporting services 2008 in SharePoint Integrated Mode.

In SharePoint Central Administration



In the Reporting Services Configuration Manager.



When you navigate to this URL, you have access to a more explicit error message.

2 - Reporting Services 2008 Log Files location

Depending on the drive you have chosen to install your Reporting Services instance, the physical path to the Log Files where you can search for errors is the following:

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles

You will find errors description with stack traces.

3 - Enable remote errors for Reporting Services 2008

There is no way of enabling Remote Errors modifying the Reporting Services Web.config file
(c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\Web.config).

In order to enable Remote Errors you have to connect to the Database Engine and update a row in the ConfigurationInfo table of the Report Server database.
You can do it performing a Tansact SQL Request



or by editing the table and make the change manually.





You have to restart Reporting Services Service after that.

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.

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

An example of standard subscription for SQL Server Reporting Services 2008 in SharePoint Integrated Mode:
Standard subscription with an expression-based parameter that delivers a pdf file in a SharePoint document library.

Introduction

This tutorial includes the detailed steps for:

• Adding a expression-based parameter to a report in Business Intelligence Development Studio
• Deploying the report
• Adding a standard subscription to the report
• Executing manually the subscription job
• Adding the pdf icon to SharePoint and configuring SharePoint to make the pdf icon appear for pdf files in SharePoint document libraries

01- Tutorial goal

Referring to the msdn documentation for Subscription, we can read the following:
[...]
Standard and Data-Driven Subscriptions

Reporting Services supports two kinds of subscriptions: standard and data-driven.
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.
[...]

So it seems to have a clear difference between standard subscriptions that use static values, and data driven subscriptions that get subscription information at run time by querying an external data source. However, using parameters in standard subscriptions may be still interesting, and I will show how to use a parameter in a standard subscription, and show that the parameter can be set using expressions that allow for example, and in this tutorial case, to calculate a date.

1 - 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 tutorial

• Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

2 - Tutorial Overview

The installation performed in this tutorial includes the following steps:

• Step 1: Modifying the report in BIDS
• Step 2: Deploying the Report
• Step 3: Checking the parameter in SharePoint
• Step 4: Creating a Standard Subscription
• Step 5: Executing the Subscription job manually
• Step 6: Adding the pdf icon to SharePoint
• Step 7: Executing scheduled job

3 - Tutorial

3.1 - Step 1: Modifying the report in BIDS

Open the project ReportProject1

01 - Adding new columns to the report

In BIDS "Report Data" Toll Pane, double click the DatSet1 dataset.
The "Dataset Properties" dialog is opening. Click the "Query Designer" button.



Check the columns SellStartDate and SellEndDate and close the dialog.



Notice that the SQL Request has changed.



And that the two new columns appear in the Dataset.



Drag and drop the two columns in the Design view of the report inside the report template.



Click the "Preview" tab, the report is being generated. Notice that you have dates with 2002 year, and 2003 year.

02 - Adding an expression-based parameter to the Report

In the "Report Data" tool pane of BIDS right click "Parameters" node, then click "Add Parameter..."



The "Report Parameters Properties" dialog is opening.

Type the following:

Name: EditionDate
Prompt: Edition Date
Select "Date/Time" as Data Type.



Then in the left menu, click "Default Value", and select "Specify values".



Click the "fx" button, and locate the function "AddDate", then type the following expression: =DateAdd(DateInterval.Year, -6,Today())
"Year" will be underline in red but just don't mind it will work anyway...
Click "OK", close the dialog.



In the "Report Data" tool pane, you can now see "@EditionDate" parameter.

03 - Changing Dataset Query

Re-open the "Dataset Properties" dialog and add a condition to the SQL request:

where (sellEndDtae < @EditionDate)



Then replay a Preview of the report.





Notice that the Edition Date parameter is now present as a field at the top of the preview pane, and that there is now no more date older than the year 2002.

3.2 - Step 2: Deploying the Report

Right click the project then click deploy.



In the Output pane, check the result of the deployment.

3.3 - Step 3: Checking the parameter in SharePoint

We are now going to check the effect of the parameter addition in SharePoint.

01 - Report Displaying

Go to the SharePoint site document library where you deployed the report, and locate it.



Click the report name link in order to display the report with the RSViewerPage.aspx.



Notice:

• The Page comes now with a new field corresponding to the previously created parameter. The parameter label is what you typed in the "Prompt" field in BIDS.
• The parameter is mentioned and is the 7 years ago day date.
• The Data are filtered, based on this parameter.

you have exactly the same effect in the SharePoint SQL Server Reporting Services Report Viewer Web Part.



Go back to the RSViewerPage, and in the parameter field, increase the parameter date of one year, and click "Apply"
The report is being re-generated, and you will notice that you have now dates with the 2003 year.

02 - parameter properties

Navigate backward to come back in the document library, and expand the report contextual menu, then click "Manage Parameters".



The Manage Parameter Page is opening.



You notice that you have now the possibility to modify some parameter properties in SharePoint instead of going back to BIDS.

3.4 - Step 4: Creating a Standard Subscription

01 - Opening Manage Subscriptions Page

Go back to the document library, expand again the contextual menu of the report, but this time, click "Manage Subscriptions".



The Manage Subscriptions Page is opening.

02 - Defining Subscription properties

here are the properties used for this subscription:

• Delivery Extension : a SharePoint library
• Path of this doc lib: http://sharepoint2007:81/Shared Documents
• File Name: SevenYearsAgo_Sales
• Output Format: Acrobat pdf File
• Overwrite Options: Create a file with unique name
• Output Format: Acrobat pdf File
• Delivery Event: for the moment I let the default option every week on Monday 8 am.
• Parameters: Of course I let the value for the expression-based parameter as defined in BIDS

3.5 - Step 5: Executing the Subscription job manually

Of course, as I do not want to wait to next Monday morning to see my generated pdf file, there is a way to execute the subscription job manually.
Go to the machine where SQL Server Database Engine is installed and open the SQL Server Management Studio, or if you are in single machine environment, open the SQL Server Management Studio.

Locate SQL Server Agent, expand its node, then expand "Jobs" node. You will have one job with a GUID name.



Right click this job and click "Start the job at step".



The job is starting...



...then executing and completing with success.



Go back to the SharePoint document library and find the pdf file that has been delivered by the job.



Click the file to display the report in pdf format (if you have installed Adobe reader on the machine).



Go back to the Manage Subscriptions Page and notice that the delivery of the pdf file is now notified.

3.6 - Step 6: Adding the pdf icon to SharePoint

You noticed that the pdf file in the document library had not an Adobe icon allowing to make it more recognizable.
There is a way to customize SharePoint in order to add Adobe pdf icon to the pdf files in SharePoint document library.
download a pdf icon like this one,



and paste it in this folder on your SharePoint machine:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES



Rename the icon in "icpdf.gif".



Open the following xml file:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\DOCICON.xml




locate the "p" letter and add this line:
<Mapping Key="pdf" Value="icpdf.gif" />



Then perform a IIS Reset command



Go back to the SharePoint document library and refresh the page that will take some time to refresh due to the IIS Reset command. You will see the pdf icon appears.

3.7 - Step 7: Executing scheduled job

Go back to the Subscriptions Properties Page and parameter the job to be executed every hour.



A few hours later, another pdf files appear in the document library. Notice that SharePoint has incremented automatically the pdf name. as defined in the subscription properties. I have added to the view the "Title" column in order to also show the effect of that property defined in the Subscription Properties Page