Line Chart for Analysis Services Data - Page 2
April 30, 2009
Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we will perform the addition and setup of the Line Chart data region, for the reasons noted above. (We typically work with a similar report copy in articles that are focused on the individual chart types elsewhere in the series.) 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 column, 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 session more efficiently.
Preparation: Create a Clone Report within the Reporting Services Development Environment
As we have noted, 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 functioning 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, 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. Some of us may 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 as a basis for our Line chart type practice exercise. Creating a clone of the report means we can make changes to select components (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. Such uses may form 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:
With a clone Analysis Services report file within our Reporting Services 2005 Project, we are ready to begin our hands-on practice session with the Line chart type, within which we will accomplish in the next section.
Preparation: Modify the Analysis Services Report Clone for Use within Our Practice Session
We will next make a few modifications to prepare the report for our practice session. Lets open the report clone we created above in Layout view (for those of us not already there), upon which we can commence our overview steps.
1. Right-click DBJ_OLAP_Report.rdl in the Solution Explorer.
2. Select Open from the context menu that appears, as shown in Illustration 1, as necessary.
DBJ_OLAP_Report.rdl opens in Layout view.
Filter the Primary Dataset to Limit the Data Retrieved
First, we will modify the primary dataset underlying the existing report, called ProductData. We will simply place filters on returned data to limit the size and complexity of our ultimate report.
1. Click the Data tab.
2. Ensure that the ProductData dataset appears within the data selector.
3. On the Metadata tab, within the pane that appears under the dataset selector (which now indicates ProductData), expand the Date dimension by clicking the + sign to its immediate left.
4. Expand the Calendar folder that appears underneath the newly expanded Date dimension.
5. Click the Date.Calendar Quarter of Year attribute hierarchy to select it.
6. Drag the Date.Calendar Quarter of Year attribute hierarchy to the Query pane, dropping it to the immediate left of the Sales Reason column that is already in place.
7. Click the Date.Calendar Year hierarchy to select it.
8. Drag the Date.Calendar Year attribute hierarchy to the Query pane, dropping it to the immediate left of the newly added Date.Calendar Quarter of Year column.
The Query pane, with the newly added Calendar Quarter of Year and Year columns, appears as partially presented in Illustration 2.
9. Click the Edit Selected Dataset (...) button to the immediate right of the Dataset selector, as depicted (circled) in Illustration 3.
The multi-tabbed Dataset dialog opens.
10. Click the Filters tab.
11. In the first row of the Filters box, within the leftmost Expression column, select =Fields!Calendar_Year.Value, as shown in Illustration 4.
12. In the Operator column (to the immediate right of the Expression column), select the >= operator.
13. Type (or cut and paste) the following into the Value column (to the immediate right of the Value column):
Because we are stating that we want only Calendar Years greater than or equal to CY 2003, we know we will retrieve only two years data into the dataset (the Adventure Works cube contains Calendar Years 2001 through 2004).
14. In the second row of the Filters box, within the leftmost Expression column, once again, select =Fields!Sales_Reason.Value.
15. In the Operator column (to the immediate right of the Expression column), select the = operator.
16. Type (or cut and paste) the following into the Value column (to the immediate right of the Operator column):
The Filters tab of the Dataset dialog appears as presented in Illustration 5.
17. Click OK to accept our addition, and to dismiss the Dataset dialog.