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 Feb 21, 2006

Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1 - Page 3

By William Pearson


Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the requested enhancements we have discussed with the client information consumer group. We will perform this, and the other steps of our practice session, from inside the BI Development Studio.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.

Prepare the Reporting Services Development Environment for Our Practice Example

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) Microsoft SQL Server 2005. Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Within our practice procedures, we will begin with the Sales Reason Comparisons report as it exists today. Based upon the existing matrix data region, the report does the job for which it was designed, at least within the limited scope of the original vision. In this, the first half of this two-part article, we will use the original data region to assist us in creating a quick matrix data region that presents identical data elements and numerical results. In Part 2, we will continue our enhancement efforts with the matrix data region, continuing to use the pre-existing data region to verify the accuracy and completeness of our new matrix data region. (We will ultimately dispense with the original data region.) We will discover that, with a few enhancements, the new matrix data region will be quite adequate to present what the original data region did from the outset. Moreover, the new matrix will be superior in several other capabilities, some of which particularly suit the recently expressed business needs.

Making preparatory modifications, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Business Intelligence Development Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we will launch the SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 3.

Illustration 3: Launching SQL Server Business Intelligence Development Studio

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File --> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 4.

Illustration 4: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 5.

Illustration 5: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 6.

Illustration 6: The Solution Opens within BI Development Studio ...

Let's first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

9.  Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 7.

Illustration 7: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 8.

Illustration 8: The Connection Properties Dialog with Default Settings ...

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\M1MSSQL2K5, as depicted in Illustration 9.)

Illustration 9: The Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 10.

Illustration 10: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 11.

Illustration 11: The Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

We are now ready to open the Sales Reason Comparisons sample report, and to use it as a model as we proceed with the practice exercise.

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