Support Parameterization from Analysis Services - Page 2
February 26, 2008
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
Lets 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 samples 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.
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.
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.
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).
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.