Advanced OLAP Reporting

Up to this point, the OLAP Expert and OLAP Report Creation Wizard have demonstrated the capability of Crystal Reports to rapidly create OLAP-based reports. More than these capabilities, Crystal Decisions provides advanced analytic capabilities against OLAP data sources through some advanced OLAP-oriented features in Crystal Reports and through a sister product called Crystal Analysis. The last four sections of this chapter introduce some of these advanced features for Crystal Reports; Crystal Analysis is introduced in Chapters 19 and 20.

Interacting with the OLAP Grid

Crystal Reports provides some powerful interactive OLAP features from directly within the Crystal Reports Preview and Design tabs. Figure 16.16 displays the right-click menu that appears when right-clicking on the year 1998 member in this chapter's sample report.

Figure 16.16. Advanced OLAP features are provided in the right-click menu.

Advanced features made available here include conditional member highlighting, hiding and showing members for asymmetrical reporting, adding calculations, adding filters, reordering members, changing the member caption, expanding members (that is, drilling into the children members), adding sorts, and adding automatic totals to the OLAP grid. Although exploring these features in detail is beyond the scope of this chapter, it is important to note their availability for enhancing your OLAP grid presentations and reports. For detailed information on all these functions, review Chapters 19 and 20 where the same functionality for Crystal Analysis is presented.

One feature of note for now is the active nature of the column and row dimensions in the OLAP grid. By double-clicking on any member in either the row or column headingsand assuming that the selected member has lower level members (children)the OLAP grid dynamically expands to include that member's children in the grid. In OLAP parlance, this is called drilling-down. Figure 16.17 shows the result of drilling-down on the 1998 Header in this chapter's sample report.

Figure 16.17. Sample OLAP-based report with 1998 member's children expanded.

A dimension member can subsequently have its children contracted by double-clicking on the parent member. This feature enables you to interactively determine the best static viewpoint to provide to the business user audience for the report.

Pivoting OLAP Grid

After an OLAP grid has been added to a report, as in this chapter's sample, Crystal Reports provides the capability to easily swap the grid's columns and rows. In OLAP parlance, this is referred to as pivoting the OLAP grid. Figure 16.18 highlights this chapter's sample report after being pivoted with this function. To access this function, right-click on the OLAP grid and select the Pivot OLAP Grid option. Pivoting the OLAP grid does not affect any OLAP charts or maps already on the report.

Figure 16.18. A preview of the sample report after pivoting the OLAP grid. Notice how the chart has changed.

This function is particularly useful when attempting to decide which viewpoint of the involved OLAP grid will be most useful to the business users of the report.

Using the Cube View Functionality

The Cube View (previously called the OLAP Analyzer) is a powerful worksheet analysis tool first introduced in version 9 of Crystal Reports. The Cube View is initiated through the View Cube option on the right-click menu of the OLAP grid (make sure that you don't have any specific grid objects selected) and is accessed through a new tab, titled Cube View, in the Crystal Reports Designer (see Figure 16.19). Report designers and analysts familiar with other OLAP interface tools will be instantly comfortable with the Analyzer because it provides access to the OLAP cube through a traditional OLAP worksheet.

Figure 16.19. The Cube View tab launched by the OLAP Analyzer provides a powerful analytic tool for report designers and power users.

Unlike the OLAP grid presented in the Crystal Reports Preview tab, the Cube View tab's worksheet is designed for rapid analysis of the underlying OLAP data through a rich and interactive interface not available in the OLAP grid. Dimensions can be rapidly shifted, swapped, and nested by double-clicking on them and dragging them into any of the row, column, or paged dimension areas. Dimension members can be quickly expanded and contracted by clicking on their associated + or icons. Additional calculations, sorts, filters, automatic totaling, exception highlighting, and custom captions can also be accessed through a right-click menu in the OLAP Analyzer view of the cube.

The Cube View is a powerful new report design tool because it lets Crystal Reports developers create some very powerful flat views of the underlying multidimensional/OLAP data in a very short timeframe and subsequently format the created OLAP grid in the Preview tab.

CAUTION

Although both the Cube View tab and the In-Place OLAP Grids within the Crystal Reports Designer offer much of the same functionality, not all the work handled in the Cube View is necessarily translated back into the related OLAP grid on Crystal Reports. Exception Highlighting and Field Formatting are two good examples of functionality that does not cross over. It is generally recommended that the majority of formatting work be done in-place within the Crystal Report's Design or Preview tabs and that cube and dimension orientation be the primary focus of the Cube View tab.

 

Using Charts and Maps Based on OLAP Grids

As described in Chapter 8 and discussed briefly in the "Adding Charts via the OLAP Report Wizard" section earlier in this chapter, OLAP grid data can be presented through visually appealing charts and maps. To create either a chart or a map based on OLAP data, an OLAP grid must pre-exist on your report as a data source for the chart/map to be based on. Selecting the Insert Chart or Map command from the Insert menu (or the respective icons on the Insert toolbar) enables the creation of an OLAP-based visualization.

The creation process for both charts and maps requires the specification of an On Change Of field. This is the field that the chart or map will break its summaries on (for example, country, state, product, sales rep, and so on). An additional optional Sub-divided On field can be specified as well. The results of specifying an extra variable to divide the data on will have different results for various chart types. Explore these different charts to find those most suitable for your business problem. Using the Sub-divided On field with a map adds either a bar or pie chart to every main region on the selected map. An example of this might be a pie chart depicting the breakdown of sales for each country.

CAUTION

It is imperative that the On Change Of field be a geographic-based field when creating a map. Otherwise, the mapping component returns an empty map.