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 Jun 16, 2003

Reporting Options for Analysis Services Cubes: Cognos PowerPlay - Page 7

By William Pearson

Keep in mind that, as many new users of PPWIN soon find out, while it is simple enough to shift from Explorer to Reporter, switching back can cause unexpected results. The reason for this is that the flexibility we have just discovered (with the deletion we performed a perfect example) can cause problems in Explorer, which wants to display all members at a given "level" of a browse. My experience has been that it is often efficient to get the report as close as possible to complete, before making the switch to Reporter, for this reason, but many strategies exist, and some are certainly better than others are for specific reporting scenarios.

3.      Click Category(s) to remove the WA line item from the report.

We note that the report appears as shown in Illustration 32.

Illustration 32: Our Report - sans the WA Category

While we have removed the WA row entirely, the total (or USA) row has remained the same, meaning that we have not experienced automatic update; this is another attribute of the Reporter mode that it is important to remember, despite its flexibility for reporting purposes.

Say we need a total, at this point, for the report in general, and that total, not too surprisingly, needs to be accurate. There are a couple of ways to approach this, but let's go about it in a manner that will afford us exposure to other attributes of the Reporter mode.

4.      Click the USA block of the bottom row to highlight it.

5.      Press the Delete key (another way to delete a category).

We have removed the total (or USA) row. We will now create a total row to replace it, through the creation of a calculation that accurately adds the totals of the two remaining state rows.

6.      Highlight both the CA and OR rows, beginning with the CA row. ([CTRL]-Click is used to highlight non-adjacent objects, as it is in many Windows applications, and can be employed here.)

7.      From the top menu, click Calculate -> Add.

The Add dialog appears.

8.      Type the word "Totals" into the Label box.

The Add dialog appears as shown in Illustration 33.

Illustration 33: The Completed Add Dialog Box

9.      Click OK to close the Add dialog.

The new Totals row appears, as shown in Illustration 34.

Illustration 34: The New Totals Row

(If the order of the highlighted rows has somehow gone astray, and the new total appears between the CA and OR rows, simply drag the new row below the OR row and drop at the bottom of the report. You can also delete and re-create (within Reporter) to achieve the same effect.

10.  Highlight the Time column that appears in the report (it carries na throughout, at this point).

11.  Press Delete to remove the column.

The column disappears from our report. We would not have been able to perform this action in Explorer mode, as we discussed earlier.

12.  Highlight the 1997 and 1998 columns, in that order.

13.  Click Calculate -> Subtract from the top menu.

The Subtract dialog box appears.

14.  Select the 1998 - 1997 radio button.

15.  Type "Change" into the Label box.

The Subtract dialog box appears as depicted in Illustration 35.

Illustration 35: The Completed Subtract Dialog

16.  Click OK.

The new Change column appears.

17.  Click the 1997 column.

18.  [CTRL]-Click the new Change column.

The idea here is to highlight both columns simultaneously, as shown in Illustration 36.

Illustration 36: The Properly Highlighted Column Selection

19.  Click Calculate -> Percent from the top menu.

The Percent dialog box appears.

20.  Select the Percent (Change, 1997) radio button.

21.  Type "% Change" into the Label box.

The Percent dialog box appears as shown in Illustration 37.

Illustration 37: The Completed Percent Dialog

22.  Click OK.

The new % Change column appears, as shown in Illustration 38.

Illustration 38: The % Change Column Appears

We might have added divers other features to this report, or to its Explorer forebear, such as exception highlighting, various formatting schemes, additional calculations, and so forth. However, let's move on, for now, after saving our new report.

23.  Click File -> Save As from the top menu.

24.  Save the file as % Change.ppr in a convenient location.

25.  Select File -> Exit to close PowerPlay for Windows.

We will conclude our brief overview of PowerPlay for Windows reporting for an Analysis Services cube at this point. Much of the body of functionality that is available can be reviewed within the online documentation and other sources, just as it might be for a natively generated PowerCube.

Now, let's take a look at the other main option for PowerPlay analysis and reporting, PowerPlay Web.

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