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 May 6, 2003

Reporting Options for Analysis Services Cubes: MS FrontPage 2002 - Page 7

By William Pearson

Restricting User Actions within the PivotTable List

While we can design highly flexible functionality into a PivotTable List, we also have options within the design environment to disable specific capabilities, should we deem it in the best interests of information consumers, or from within the perspective of the organization, its operating units, and so forth. For example, we might want to provide information in fixed views, or to limit switching of dimensions within the row and column axes while providing only a subset of functionality in the areas of drill-down, or modification of member properties within the PivotTable List. Unlike Excel PivotTable Reports, where a restricted set of operations might generally be attained with the implementation of VB macros, other programming, and other "workarounds," the PivotTable List again provides enhanced opportunities to easily build controls into the actions made available to targeted users.

Let's return to the PivotTable List designed in the last exercise to explore some of these options, and to practice the incorporation of these basic restrictions into the design environment.

1.      Return to design mode by clicking the Normal tab.

2.      Click the Sr Manager drop-down arrow button.

3.      Select All (make sure it's checked, versus simply "shaded in"), as shown in Illustration 24 below.

Illustration 24: Select All at the Sr Manager Dropdown Arrow

4.      Click OK to display the complete set of CEO direct reports/senior managers.

5.      Click either Salary (000) (previously Employee Salary) label to select the associated columns.

Both Salary (000) columns should be highlighted.

6.      Right-click the highlighted area and click Remove Total (as shown in Illustration 25) to prevent salaries from being included in the view to be made available to information consumers.

Illustration 25: Select Remove Total on the Context Menu

We'll now assume for this exercise that the currently displayed PivotTable List represents exactly what we want to be made available to its intended audience.

7.      Select the entire PivotTable List by clicking the Report Title Bar (the blue bar with Microsoft Office PivotTable 10.0 in white, atop the PivotTable List).

8.      Right-click, and select Commands and Options on the context menu.

The Commands and Options dialog appears, defaulting at the Captions tab.

9.  Ensure the Select Caption selector remains at Report Title Bar.

10.  In the Caption field, type in Headcount and Salary Data.

11.  Change the Font to Arial Narrow, with a Color of SkyBlue (the first color swatch in the fourth line down in the color selection palette.)

The Commands and Options dialog - Captions tab appears, with our changes, as shown in Illustration 26.

Illustration 26: The Commands and Options Dialog - Captions Tab

12.      Click the Behavior tab in the Commands and Options dialog and make the following selections:

  1. Type "22000" in the Maximum Height box.
  2. Type "22000" in the Maximum Width box.

These setpoints (the ones above are simply examples) can be used to physically prevent the PivotTable List from expanding beyond the specified boundaries and from overrunning PC screens, and other such, well, behavior. If the PivotTable List grows larger than these parameters (due to AutoFit features or for other reasons), a scrollbar will appear, so that users can access the entire report, while visual dimensions are maintained. We can also take advantage of various Show / Hide capabilities here, as we can see with an examination of the Behavior tab. The Commands and Options dialog - Behavior tab appears in Illustration 27 below.

Illustration 27: The Commands and Options Dialog - Behavior Tab

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