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