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 19, 2007

Mastering OLAP Reports: Extend Reporting Services with Custom Code - 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 basic Custom Code. The focus of our efforts will be the addition of code to meet a specific business need, into an OLAP report containing a Matrix data region (the mechanics behind adding the capability, not the design of the report itself). Because of time limitations, we will be working with a simple, pre-existing sample report – in reality, the business environment will typically require more sophistication. The process of adding embedded code is the same in real world scenarios, with perhaps a more complex set of underlying considerations.

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my 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 keep to the focus of the article more efficiently.

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 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 exercise. Creating a “clone” of the project 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, 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: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters. Let’s open the report and make the necessary settings to place it into such a state from which we can commence our practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.

Illustration 1: Opening the New Report ...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.

Illustration 2: The MDX Query Builder

To save time, and to maintain our focus upon our current topic, the use of custom code within Reporting Services, we will dispense with the graphical design option within the MDX Query Designer, substituting a simple MDX query for the existing query. Our intent in doing so is to supply a dataset that will support a modified report, whose elements lend themselves to our hypothetical custom code requirement.

4.  Click the Design Mode button, within the Data tab toolbar, to toggle from the graphical design view to the query view, as shown in Illustration 3.

Illustration 3: Toggle from Design View ...

The MDX Query Designer shifts to query view, as depicted in Illustration 4 below.

Illustration 4: The MDX Query Builder in Query View

Here we see the MDX syntax within the Query pane, as likely generated from the design view of the MDX Query Designer.

5.  Replace the existing query (cutting and pasting is fine) with the following syntax:

--Modified query for purposes of practice session, DB Journal RS038
   { [Measures].[Internet Sales Amount]} ON AXIS(0), 
   NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS * 
      DESCENDANTS({[Sales Territory].[Sales Territory].[All Sales Territories]}, 
         [Sales Territory].[Sales Territory].[Sales Territory Country], 
      SELF_AND_BEFORE))}ON AXIS(1) FROM [Adventure Works]

The query appears within the Query pane, as shown in Illustration 5 below.

Illustration 5: The New Query in the Query Pane

6.  Click the Execute Query button (!), to the left of the Design Mode button in the toolbar, to run the query and populate the Results pane, as partially depicted in Illustration 6.

Illustration 6: Execute the Query to a Populate the Results Pane (Partial View)

Having modified the query to one which is more appropriate to our practice session, we will now make further adjustments to meet our ends. First, we will remove a query parameter reference, to complete our alterations of the ProductData dataset.

7.  Click the ellipses (...) button to the right of the Dataset selector (currently displaying ProductData).

8.  Click the Parameters tab on the Dataset dialog that opens.

9.  Click the box containing ProductCategory, in the Name column of the top row of the Parameters list (the only populated row).

10.  Click the Delete (“X”) button to the right of the Parameters list, to delete the sole Parameter reference, as shown in Illustration 7.

Illustration 7: Click Delete to Discard the Parameter Reference ...

11.  Click OK, to accept our removal of the query parameter reference, and to dismiss the Dataset dialog.

We will next remove the ProductList dataset, whose mission in life is primarily to provide picklist support for the Report Parameter within our current report – a parameter which we will soon remove as a part of preparation.

12.  Select ProductList within the Dataset selector atop the Data tab.

13.  Click the Delete Selected Dataset button to the right of the Dataset selector, as depicted in Illustration 8.

Illustration 8: Click the Delete Selected Dataset Button ...

14.  Click Yes on the Microsoft Report Designer that appears, confirming our wish to delete the dataset.

The ProductList dataset is deleted. Next, we will remove the sole Report Parameter in the report, as we will have no need for it within the scenario we are preparing for our practice session.

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