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 Jul 18, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances - Page 4

By William Pearson


Add the Chart Item to the Layout Canvas

Recall in our last session that we "redirected" the Multi-Layer_Calculations report (which was itself a modified clone of a sample report that we installed along with Reporting Services), to a clone of the Sales sample cube. We created the cube copy, called Percent of Total, in the preparation section of the practice exercise. Within the Percent of Total cube, we created the Percent Total Sales calculated member, as support for our subsequent work with Reporting Services, where we compared and contrasted the cube-based calculation with a report-based calculated field that generated the same results.

Our focus within the previous article was the derivation of the percent of total calculation itself (in answer to many requests I receive for guidance in deriving this capability for reporting purposes), coupled with added considerations surrounding "where to put the intelligence" within an integrated BI solutions' "layers," (both within the immediate context of percent of total, and for similar calculations and capabilities in general). In this article, we will somewhat arbitrarily call upon both calculations to support our pie chart, mainly to demonstrate the mechanics for doing so for each, but keep in mind that the "home" of the support structure of the calculation remains an important consideration from many perspectives, including overall system optimization, security, and others.

Let's open the new report and get started with the chart region.

1.  Within the RS018 project tree in the Solution Explorer, double-click the new PercTotalPie report, to open it.

The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 9.

Illustration 9: The New Report in Layout View

As we learned in my introductory article, Master Chart Reports: Pie Charts in Reporting Services, the process of building a chart report consists of dragging the chart item onto the Layout tab, and adjusting it, while setting properties as appropriate to meet report specifications. "Borrowing" the dataset that supports the existing matrix will save us preparation time, and allow us to get directly to the chart manipulation topics that we will take up in this section.

2.  Drag the right canvas edge to about the 11-inch mark atop the screen area.

3.  Drag the bottom edge of the canvas to about the 6-inch mark on the scale to the left of the Layout tab.

4.  Select View --> Toolbox (as shown in Illustration 10), from the main menu to place the Toolbox within easy reach (if it already appears, simply disregard this step).

Illustration 10: Calling the Toolbox to View

The toolbox window should appear similar to that depicted in Illustration 11. Mine is pinned to the upper left corner of the design environment, where I find it most convenient. This is, of course, subject to your own choices. (Note also, as an aside, that I dock my Fields and Server Explorer panes in this area, most of the time, to maximize design real estate.)

Illustration 11: The Toolbox, Pinned to the Upper Left Corner of the Design Environment

5.  Click the Chart button (at the bottom of the Toolbox pane).

6.  Hover the mouse cursor to the right of the existing matrix data region, at approximately the 6-inch point on the scale atop the Layout canvas, and at about the ½-inch point on the scale to the left of the canvas, as shown in Illustration 12.

Illustration 12: Click Approximately at the "X" Point to Begin Drawing ...

The cursor becomes a small chart icon in combination with crosshairs when held above the Layout canvas. This indicates that we can click to "anchor" the point, from which we wish to draw the box that the chart will inhabit.

7.  Starting at the point indicated on the canvas, click, and then, holding the mouse button down, drag to create a box that reaches to the bottom right corner of the canvas.

The Layout view, with the box we have drawn, appears similar to that depicted in Illustration 13.

Illustration 13: Drawing the Box to Position the Chart

8.  Release the mouse to drop the chart item.

The chart region appears, in its generic manifestation, as shown in Illustration 14. (Don't worry about getting the lineup perfect - it is actually best to "realign" after we get done building the chart, to compensate for any changes we make in its size, for how it ultimately looks in the report as opposed to initial expectations, etc.

Illustration 14: The Generic Chart Item Appears

NOTE: Should you accidentally "drop" the chart item in a manner that you wish to realign, you can simply move the item by clicking inside the chart, then pointing to the now shaded border, to drag it to a new location.

Clicking again on the border will also allow you to expand / contract the chart shape. (The nuances are easy to learn with a little practice.) Double-clicking the chart item will make the "drop regions," seen above, appear, should they be hidden.

The chart item is now in place, and we are ready to populate it with the appropriate fields from the dataset, to endow it with the characteristics required to meet the requests of the information consumers.

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