Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 27, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions - Page 5

By William Pearson

Creating and Using a Standard Subscription

1.  Click the Properties tab atop the FoodMart Sales report view.

We arrive at the Properties page.

2.  Click the Data Sources link in the left margin of the page.

We arrive at the Data Sources page. Here we will embed our data source access credentials, for purposes of completing the immediate exercise.

3.  Click the radio button labeled Credentials stored securely in the report server in the Connect Using: section (in the lower half) of the Data Sources page.

4.  Type your actual Windows User name and Password information into the respective boxes. (The information that is being passed from Windows with integrated security in force).

NOTE: If you are using a different authentication method than Windows integrated security, make the appropriate adjustments on this page to embed your credentials into the report.

The Data Sources page appears as shown in Illustration 13, with my adjustments.

Illustration 13: Data Sources Page: Settings to Store Credentials

5.  Click Apply to apply the settings.

6.  Click the Subscriptions tab (shown circled in the above illustration).

The Subscriptions page appears, with a "no items to show in this view" statement in place, assuming no subscriptions have been created. Illustration 14 depicts this circumstance.

Illustration 14: Subscriptions Page: No Subscriptions Defined

Because the report is parameterized, and because we know the parameter that we wish to pass upon its execution (we wish to present the Non-Consumables sales data), we need to pass the parameter as part of the process of subscription; otherwise the manual elements of report generation that existed before we obtained the new business requirement will remain with us. We will begin our setup of subscription for the FoodMart Sales Report by taking the following steps:

7.  Click the New Subscription link (shown circled in the above illustration).

The Subscription properties page opens. Here we make the settings for the subscription we are creating.

8.  Select Report Server File Share in the Delivered by: selector box.

The options on the properties page change to reflect those that are relevant to our Report Server File Share delivery choice. It is important to keep in mind that, although we can chose to have any report delivered to a respective designated file share, any reports that contain interactive features (including drill-down, drill-through, and so forth) will be delivered as static files.)

9.  Modify File Name to reflect the following:

Foodmart Sales Report

10.  In the Path box, type in a valid share name.

The name must be in UNC (Uniform Naming Convention) format. For example, my entry here is as follows:


A Books Online reference specifically states that trailing backslashes (or "slatches," as I have been calling them awhile now) are not to be used here.

11.  In the Render Format selector, select Excel (unless you do not have MS Excel on your PC. If that is the case, substitute Web Archive).

Our report file, with an .xls extension, will be delivered to the specified file share with the name we have given it. Within the Render Format selector, we can select various file formats (to the delight of many clients to whom I have presented this feature to date, most of whom had to write macros, etc., to handle the same functionality in Cognos, Business Objects, and other enterprise solutions). Options include:

  • XML file with report data
  • CSV (comma delimited)
  • TIFF File
  • Acrobat (PDF) file
  • Web page for IE 5.0 or later (.htm)
  • Web page for most web browsers (.htm)
  • Web archive
  • Excel

Others are certainly possible (Lotus, etc.), with the flexible API afforded by Reporting Services, as well as through other easy approaches.

12.  Add the appropriate credentials to access the file share stipulated, if required.

13.  Click the Select Schedule button in the Subscription Processing Options section (lower half of the page).

The related properties page opens, providing access to the scheduling settings for the subscription we are creating.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM