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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 22, 2005

MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports - Page 4

By William Pearson

Creating a Data Source Connection

We learned in Managing Reporting Services: Data Connections and Uploads that the purpose of the Report Server is to serve, or to act as a presentation platform, for our reports. It connects to data sources to retrieve the data that it presents in the reports it "hosts." When we refer to a data source in Reporting Services, we are referring to a collection of properties, in effect, that represent a connection to a given data source. This collection of properties has a name, as it does in other applications where data sources exist (a scenario with which most of us are familiar). Data sources contain the following, where applicable:

  • Specification of the data processing extension we use to process queries of the type for which we intend to use the connection;
  • A connection string that allows us to locate the source;
  • Access credentials involved in allowing us to read the data within the source.

As we have noted in various articles of the series, a data source connection can be embedded in a report (where it is typically defined within the creation process); it can also be defined as a shared data source item that is managed by a Report Server. We will be establishing a shared data source item for the Execution Log reports, so that the entire set can reference the same self-contained, underlying data source. In addition to ease of referencing in the reports, our shared data source will provide the benefit of maintenance from a single location, as we shall see.

We will create a shared data source for the reports before we upload them to provide an immediate mechanism to link them to the data they are intended to present. The data source connection will be independent of the reports themselves. Our set of sample reports will share data housed in a single source, the RSExecutionLog database, an excellent application of a shared data source.

NOTE: For the setup of the RSExecutionLog database, which we will require to complete prospective steps of our practice example, see Prepare the Execution Log for Reporting.

Our first step is to start Report Manager.

1.  Click Start.

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


Illustration 6: Navigate to Report Manager ...

3.  Click Report Manager to initialize the application.

NOTE: If Report Manager does not appear 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:

 http://<webservername>/reports

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 shown in Illustration 7.


Illustration 7: Navigate to Report Manager ... Alternative Route

We arrive at the Report Manager Folder View. Let's create a new folder, within which we can isolate the reports we will be uploading.

4.  Click New Folder.

The New Folder page appears.

5.  Type the following into the Name box:

Execution Log Reports

6.  Type the following into the Description box:

Performance & Auditing Reports based upon the Report Server Execution Log

With our input, the New Folder page appears as depicted in Illustration 8.


Illustration 8: The New Folder Page, with Input

7.  Click OK.

We are returned to the Home page Folder View, and see our new folder appear on the Contents tab, as shown in Illustration 9.


Illustration 9: The Execution Log Reports Folder, on the Folder View - Contents Tab

8.  Click the link for the new Execution Log Reports folder, to open it.

We enter the Execution Log Reports folder, which is empty at present.

9.  Click New Data Source atop the Folder View, as depicted in Illustration 10.


Illustration 10: Click New Data Source atop Report Manager Folder View

The New Data Source page appears.

10.  Type the following into the Name box:

RSExecutionLog

11.  Type the following into the Description box:

Shared Data Source for Execution Log Reporting

12.  Ensure that the checkbox to the left of Enable this data source is checked (the default).

13.  Ensure that Microsoft SQL Server is selected in the Connection Type selector.

14.  Type the following into the Connection String text box:

data source="(local)";persist security info=False;initial catalog=RSExecutionLog

15.  Under Connect Using, select Windows NT Integrated Security.

NOTE: Adapt the Data Source and authentication settings to your own environment, as necessary (for example, if the RSExecutionLog database is on another server and access is being attempted across a network, etc.)

With our input, the New Data Source page appears as shown in Illustration 11 below.


Illustration 11: The New Data Source Page, with Input

16.  Click OK to accept the shared data source.

We are returned to the Execution Log Reports page, Folder View, and see our new data source appear on the Contents tab, as depicted in Illustration 12.


Illustration 12: The Data Source Appears on the Folder View, Contents Tab

Having created a data source, we will now upload the sample Execution Log reports from the Report Manager. We will then marry the reports to the common data source we have created.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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