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 26, 2008

Support Parameterization from Analysis Services - Page 2

By William Pearson

Preparation

Preparation: Create a Clone Report within the Reporting Services Development Environment

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) the MSSQL Server 2005 integrated business intelligence suite. Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms 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 will leave us with 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

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working 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. (The default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercises. Creating a “clone” of the report means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation. We can, therefore, use the original as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution, in general.

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed, in the next section, to make modifications for our subsequent practice session.

Preparation: Add the Analysis Services Database to the Project

We will continue our preparation by adding the Analysis Services database – with which we ascertained connectivity in the “Ascertain Connectivity of the Shared Data Source” section above - within our newly created project in the Business Intelligence Development Studio. I typically like to set up a lab environment for each of my client or research projects where I have both the respective UDM and reports involved with the engagement within an integrated solution in Visual Studio. This ensures ease in testing cube modifications through to the report layer from a single, central location, as well as providing the advantage of effective source control, among numerous other conveniences. For example, in this particular case, I will have both a copy of the sample Adventure Works DW and the AdventureWorks Sample Reports projects added into a single solution within the Business Intelligence Development Studio, where I can access all member objects from one point, the Solution Explorer.

Continuing within our newly created project in the Business Intelligence Development Studio, take the following steps:

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

2.  Click Analysis Services Database ... from the cascading menu, as shown in Illustration 1.


Illustration 1: Selecting an Analysis Services Database into the Project ...

The Connect to Database dialog appears.

3.  Ensure that the radio button to the immediate left of Connect to existing database (atop the dialog) is selected.

4.  Type the appropriate name within the Server input box.

5.  Select the appropriate name within the Database selector (the Analysis Services database with which we ascertained connectivity of our report clone above), just underneath the Server input box.

6.  Click the radio button to the immediate left of Add To Solution (in the bottom section of the dialog), to select this option.

The Connect to Database dialog appears similar to that depicted in Illustration 2.


Illustration 2: The Connect to Database Dialog, with Our Input

7.  Click OK to accept our input, and to dismiss the dialog.

The Reading database from the server... message box appears briefly, as shown in Illustration 3.


Illustration 3: Reading the Database from the Server ...

The Adventure Works DW Analysis Services project opens, and we see the various associated objects appear within Solution Explorer, as depicted in Illustration 4 (with Dimensions folder collapsed).


Illustration 4: The Adventure Works DW Analysis Services Project Joins the Solution ...

We can now access our sample report and its underlying Analysis Services database, and thus test cube enhancements through to the report layer, from a single, central development environment.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date