Standard subscriptions 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.
- 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
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.
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
- 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
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.
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:
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.
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.
In the Output pane, check the result of the deployment.
Click the report name link in order to display the report with the RSViewerPage.aspx.
- 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.
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.
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.
The Manage Subscriptions Page is opening.
- 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
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.
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.
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