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 6

By William Pearson

Let's drill a level lower to reinforce our understanding.

11.  Click the Store folder (now labeled USA) again.

Notice that the flyout selection now represents second level Store dimension categories (states); Store appears at the top (and we can easily "drill up" or "zoom" back to the top level by clicking its name here), with the categories that are beneath the country level appearing as the next lower-level drill candidates, as shown in Illustration 22.

Illustration 22: Drilldown Selection for the Store Dimension - Second Level Categories

12.  Click OR to drill to the Oregon Store data.

"OR" now appears as the label on the Store folder - and the measures that appear in the crosstab adjust accordingly. We have drilled to the Oregon category, state level of the Store dimension, as shown in Illustration 23.

Illustration 23: Effects of the Drill to OR Stores

13.  Click USA on the Store categories list (shown in Illustration 24) to zoom / "drill up" to the USA Store data.

Illustration 24: Zoom Back to the USA Level

14.  Drag the Product folder and drop it to the right of the Stores (now states) list on the report, as shown in Illustration 25. (A "ghost" image will appear when we touch the dragged object to the drop point, shown circled in our illustration.)

Illustration 25: The Drop Point for the Product Dimension

The Product dimension becomes nested within the Store dimension, effectively breaking down the Stores (now summarized by state) by Product. The report appears as shown in Illustration 26.

Illustration 26: The Product Dimension Nested within the Store Dimension

15.  Click the 3D Chart button (see Illustration 27) on the toolbar to present the data in a 3D Bar Chart.

Illustration 27: The 3D Chart Button

PowerPlay displays both the totals and drilled-down data in the 3D chart, as depicted in Illustration 28. Information about any part of the data selected in the chart appears both in the lower edge of the window and in a tooltip that appears upon touching the mouse to the data item. Note also the presence of an MDX-esque address (my term, not Cognos'), partially representing the cell "location" in terms of intersects in the cube, in the bottom left corner of the display, when we click on a given object in the bar chart.

Illustration 28: The Data Displayed as a 3D Bar Chart

We can also right-click any data item in the chart and select Explain from the context menu to see the precise details for the item of data selected. An example Explain dialog appears in Illustration 29.

Illustration 29: Example Explain Dialog for a Selected Bar on the 3D Chart

16.  Close the Explain dialog.

17.  Return to the crosstab report display by clicking the Crosstab button (shown in Illustration 30).

Illustration 30: The Crosstab Button

Now let's take a brief look at PowerPlay in Reporter mode. We can easily shift to Reporter mode with the simple click of a button.

18.  Click the Switch between Explorer and Reporter button (see Illustration 31).

Illustration 31: The Switch between Explorer and Reporter Button

The switch to Reporter is almost unrecognizable, initially. We can ascertain that we are in the Reporter mode, however, by looking at the caption at the top of the PowerPlay window, which now indicates the combined report / cube name as before (mine is PPlay2.ppr of MSSQL_Warehouse) followed by "(Reporter)." (The combined name would have carried the suffix "(Explorer)," previously.)

While there are numerous differences in the Reporter and Explorer modes, perhaps the primary one is the flexibility we have within Reporter to easily modify the report layout, and break somewhat from the rigorous "leveling" operation of Explorer in drilldown and other operations. Let's look at an example of this flexibility in deleting a single category from our report.

1.      Right-click the WA category from the row axis at the left of the report.

2.      Select Delete from the context menu that appears.

A submenu appears, offering us the option to delete Category(s) or Level. This is indicative of the difference to which I refer between the two modes. Explorer only allows us to delete Level at this point (the Category(s) selection is grayed out and disabled). This fits in with what we have discovered, while in Explorer, about its general operation: Remember that we noted that when we drill down in PowerPlay Explorer, we replace a parent category with its child categories in the report - all simultaneously. The Explorer view is always at the "same level" at any given time, and displaying all the members of that level. This is desirable in a browse, perhaps, but makes for inflexible reporting. Hence, we can often rely upon the more versatile properties of Reporter to meet needs that are more specialized.

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