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 Jun 23, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 7

By William Pearson

Select the Report to be Subscribed, and Store Credentials

1.  Click Start.

2.  Navigate to the Reporting Services program group that installs within a typical setup. The equivalent on my PC appears as shown in Illustration 25.

Click for larger image

Illustration 25: Navigate to Report Manager ...

3.  Click Report Manager (circled in Illustration 25 above), to initialize the application.

NOTE: If Report Manager does not appear as a menu item in the manner shown, whether because you declined setup of the program group, a disablement of the feature, or other, unknown reason, simply get there by typing the appropriate URL into the address bar of your web browser. The default URL is as follows:


As an example, my <webservername> would be MOTHER1, the name of my server, and would appear, in this approach, in my browser address line as depicted in Illustration 26.

Illustration 26: Alternative Access to Report Manager ...

We arrive at the Report Manager, Home page, Content tab (Folder view), as shown in Illustration 27.

Illustration 27: Report Manager, Home Page, Content Tab (Folder View)

The sample reports we have published appear above as we left them in Managing Reporting Services: Data Connections and Uploads. Minor changes in the reports since then will not affect this lesson - we will be working with FoodMart Sales in our practice example throughout the lesson. (If you are just joining us with this lesson, simply refer to the upload process in the article, which we have cited.)

4.  Click the link for the FoodMart Sales report, as shown in Illustration 28.

Illustration 28: Accessing the FoodMart Sales Report (Partial Home Page, Content Tab View)

5.  Select a value for the parameter in the ProductFamily selector box (atop the view), if necessary.

6.  Click View Report, as required.

The report executes, as we have discussed earlier, either when we click the link in Report Manager's Folder view, or upon clicking the View Report button, and we again see the Report is being generated message. The report then appears, with the parameter selector dropdown list, set at default, appearing atop the report.

NOTE: If a default had appeared in the ProductFamily selector (this is how the sample FoodMart Sales report is configured upon installation) upon entering the report, the report would have executed automatically. Note that, as we proceed through the steps that follow, some of the settings we prescribe may have been left in place (by those readers, at least, who completed the steps of our last article). We repeat the relevant steps here, so as to keep within our "standalone" objective for the articles of this series.

Let's store credentials at this stage, to allow the report to access data without interruption when it is executed by the Data-Driven Subscription process.

7.  Click the Properties tab at the top of the report page, as shown in Illustration 29.

Illustration 29: Properties Tab for the FoodMart Sales Report (Partial View)

The Properties page appears.

8.  Click the Data Sources link in the left margin of the page, shown in Illustration 30.

Illustration 30: Click the Data Sources Link on the Properties Page (Partial View of Page)

We arrive at the Data Sources page.

9.  Select the radio button labeled A custom data source.

10.  Select OLE DB in the Connection Type selector.

11.  Add the following (either type or cut and paste) in the Connection String box (if it is not there from a previous lesson):

Provider=MSOLAP.2;Client Cache Size=25;Data Source=localhost;
	Initial Catalog=FoodMart 2000;Auto Synch Period=10000

NOTE: Make any modifications to the string to customize it to your own data source. The above reflects my settings, which access a source on my local PC.

12.  Under Connect Using, select the Credentials stored securely in the report server radio button.

13.  Type an appropriate User name and Password in the respective boxes.

14.  Ensure, if appropriate to your environment, that the Use as Windows credentials checkbox is checked.

The Data Sources page appears similar to that shown in Illustration 31, with our modifications.

Illustration 31: The Completed Data Sources Page (Compact View)

15.  Click the Apply button at the bottom of the page to accept and save changes.

16.  Click the View tab, to return to the Report view.

17.  If required, select a ProductType once again in the parameter selector, and click View Report, to run the report again.

Successful execution of the report verifies that the credentials we have supplied are adequate to support the report's operation. This finalizes our general preparation for the steps to create the Data-Driven Subscription. We will proceed to establish the subscription itself in the next section.

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