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 has provided 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 hour introduce some of these advanced features.

Interacting with the OLAP Grid

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

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

Advanced features made available here include conditional member highlighting, adding calculations, adding filters, reordering members, expanding members, adding sorts, and adding automatic totals to the OLAP grid. Although exploring these features in detail is beyond the scope of this book, it is important to note their availability for enhancing your OLAP grid presentations and reports.

One feature of note 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 headings and assuming that the selected member has lower level members (children) the OLAP grid will dynamically expand to include that member's children in the grid. In OLAP parlance, this is called drilling-down. Figure 20.17 highlights the result of drilling-down on the 1998 Header in this hour's sample report.

Figure 20.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 the report designer to interactively determine the best static viewpoint to provide to the business user audience for the report.

Pivoting OLAP Grids

After an OLAP grid has been added to a report, as in this hour's sample, Crystal Reports has provided the ability to easily swap the grid's columns and rows. In OLAP parlance, this is referred to as pivoting the OLAP grid. Figure 20.18 highlights this hour'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 will not affect any OLAP charts or maps already on the report.

Figure 20.18. A preview of the sample report after pivoting the OLAP grid.

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

Using the OLAP Analyzer

The OLAP Analyzer is a powerful worksheet analysis tool new to version 9 of Crystal Reports. The OLAP Analyzer is initiated through the Launch Analyzer option on the right-click menu of the OLAP grid (make sure that you don't have any specific grid objects selected) and is exposed through a new tab, titled Cube View, in the Crystal Reports Designer (see Figure 20.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 20.19. The Cube View tab launched by the OLAP Analyzer.

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.

This new Cube View in version 9 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.

Using Charts and Maps Based on OLAP Grids

As alluded to in Hour 11 and discussed briefly in the "Adding Charts in the OLAP Report Wizard" section earlier, 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) will enable 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 on specifying an extra variable to divide the data on will have different results for various chart types. It is encouraged that these different charts are explored to find those most suitable for your business problem. Using the Sub-divided On field with a map will add 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 breakup of sales for each country.

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